English Delivery Only: MySQL Performance & Tuning

Who will the Course Benefit?

Anyone who needs to monitor and tune the performance of MySQL databases.

Course Objectives

To provide the skills necessary to monitor and tune MySQL database performance.

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

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.

Further Learning

• Apache Web Server