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