COURSE OBJECTIVE:
Course Objectives To provide the skills necessary to monitor and tune MySQL database performance.
TARGET AUDIENCE:
Who will the Course Benefit? Anyone who needs to monitor and tune the performance of MySQL databases.
COURSE PREREQUISITES:
Requirements Delegates must have a working knowledge of MySQL Database Administration. This course is run on a Linux operating system,a basic knowledge of Linux/UNIX is recommended but is not essential.
COURSE CONTENT:
MySQL Performance & Tuning Training Course Course Contents – DAY 1 Course Introduction • Administration and Course Materials • Course Structure and Agenda • Delegate and Trainer Introductions Session 1: INTRODUCTION TO PERFORMANCE TUNING • Tuning Overview • Resolving Performance Issues • Recommended Approach to Tuning • Items to Evaluate • Where to look for performance issues • Develop a monitoring and tuning plan • Building a New Database for Performance • Tuning an Existing Database for performance • Set Suitable performance goals Session 2: MYSQL PERFORMANCE TUNING TOOLS • Tuning Overview • Hardware optimization • Increase RAM and use faster RAM • Use more CPU cores • Use a clustered database • Optimize the operating system • Use indexes to improve performance • Optimize queries • Optimize tables • Assign suitable memory allocations • Set values for system variables that affect performance • Use benchmarking tool Session 3: STATEMENT TUNING • Overview of Statement Tuning • Identifying and improve Problem Queries • The Optimizer • Understand the output from the Explain command • Monitor queries using the Information Schema Processlist table • Optimization strategies • Optimizations for derived tables • Filesort with small LIMIT optimization • Limit rows examined • Query limits and timeouts • Abort statements that exceed a specific time to execute Session 4: INDEXES • An overview of MariaDB indexes • Types of MySQL indexes • Make efficient usage of indexes • Assess the size of an Index • Resolve queries without accessing some tables referred to in the query • Force query plans using index hints • Find rows in a table using named indexes • Ignore indexes using an index hint • How indexes impact table joins • InnoDB Cached Indexes Information MySQL Performance & Tuning Training Course Course Contents – DAY 2 Session 5: SERVER CONFIGURATION AND MONITORING • Set suitable values for server configuration variables • Use server status variables to monitor performance • Use table caching • Store key distributions for a table with the ANALYZE TABLE command • Reclaim unused space and defragment data with the OPTIMIZE TABLE command • Use multi-threading • Solve connection issues Session 6: THE INNODB ENGINE • Transactions • Crash recovery with the innodb engine • Effects of innodb locking on performance • Monitoring InnoDB Locks in MySQL • MySQL Disable Deadlock Detection • Monitor the performance of the InnoDB engine • Set and monitor caches and buffers • Configuring data files for performance • Configuring the log files for performance Session 7: OVERVIEW OF CLUSTERING FOR PERFORMANCE • The Performance Advantages of Clustering • Performance Issues and Clustering • The NDB Cluster • The Galera Cluster • The Percona XtraDB Cluster • MySQL InnoDB Cluster • The Federated Engine Session 8: OPTIMIZING THE PERFORMANCE OF DUMPING AND LOADING DATA • SQL statements versus delimited data • Parameters affecting dump performance • Parameters affecting load performance Session 9: PARTITIONING TABLES FOR PERFORMANCE • The concept of partitioned tables • How partitioning can improve performance • Range partitioning • Hash partitioning • Key partitioning • List partitioning • Composite partitioning or subpartitioning • Partition Pruning • Adding,dropping and coalescing partitions • Convert a non-partitioned table to a partitioned table
FOLLOW ON COURSES:
Further Learning Apache Web Server