MySQL Performance & Tuning

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

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