MySQL High Performance Tuning Guide

Tips for Tuning MySQL like a Pro | Learn how to prevent MySQL performance problems

Created by: Lucian Oprea
Software Engineer

What Will I Learn?

• Understand how MySQL optimizes and executes queries.
• Find where MySQL spends most time on your query, and Why?
• Finding those few queries that are responsible for most of the load on the system.
• How to find the relevant information out of the plethora of performance views, and metrics available in MySQL?
• How queries are automatically planned before execution, based on statistics and cost decisions?
• Understand what a Clustered Index is?
• How to choose the optimal Primary Key?
• Find which tables are in need for an index, and which tables are better without one.
• How we can help the MySQL Optimizer make better decisions?
• Which are the common pitfalls, when using indexes on Multiple Columns?
• Which are the use-cases, that make Redundant Indexes useful, and when to avoid them?
• Cover the Best Practices for making Server Configurations
• Which are the three options that MySQL is most likely to benefit, from non-default values, and also how to set them?
• Data lifecycle in InnoDB, so that we have background knowledge when making configuration changes.
• Get an overview of why Transactions and Locks are needed and some ways to reduce their impact.
• Introduction to MySQL Scaling Options
• Fundamental Concepts for Scaling Reads with Replication
• How to Partition Data in MySQL
• Fundamental Concepts for Scaling Writes with Queueing and Sharding

Requirements

• You need access to a Windows/Mac/Linux PC with 10GB of free disk space
• Basic familiarity with database objects such as tables and indexes is expected
• Some familiarity with Linux will be helpful

Target audience

• Developers interested in advanced database internals that impact application design and performance
• Database administrators
• Everyone interested in building better MySQL applications