MySQL performance tuning
You find bottlenecks with performance_schema and the sys schema, tune InnoDB memory and disk settings for the workload and measure before and after with sysbench.
Level 401 · 17 lessons · 15 practice labs
Lessons
Measuring
- The tuning method: measure, change, measure again
- A baseline with sysbench
- Wait analysis with performance_schema
- sys schema reports
- Digesting queries: pt-query-digest
- Short task: find the bottleneck of a slowing server
InnoDB settings
- Sizing the buffer pool
- The redo log and flush settings
- Disk I/O capacity
- Connections and the thread pool
- Temporary tables and sort memory
- Short task: tune my.cnf for the workload
Schema and query
- Online schema changes
- Safe experiments with invisible indexes
- Histograms and optimizer hints
- Hot rows and contention
- Capstone: the shop database that collapses at peak hour
Practice labs
- Take a baseline with sysbench: Run the read-write test and record transactions per second and latency percentiles.
- Prove the effect of one setting change: Repeat the same test three times with one setting changed and show the difference is not noise.
- Prepare a test that mimics real load: Write a custom load script that mimics Lale Market's query mix and compare results with real metrics.
- Find the top wait event: List from performance_schema the wait events where the server spends most time.
- Find full scans with sys reports: Use sys schema reports to list queries doing full scans and spilling temporary tables to disk.
- Clean out unused and duplicate indexes: Find unused and redundant indexes with sys reports and make them invisible before dropping to watch the effect.
- Enable and read the slow query log: Set the threshold to one second, run load and find the three slowest queries in the log.
- Digest queries and prioritise: Run the log through a digest tool and rank the top five query families by total time.
- Find the bottleneck of a slowing server: Combine query digests and wait data to find the root cause of Defne Kitabevi's server slowing every afternoon.
- Size the buffer pool correctly: Size the buffer pool on a server with a poor hit ratio by memory and data size and measure the gain.
- Tune redo for a write-heavy workload: Change redo log size and flush settings and measure write speed against the durability trade-off.
- Reduce sorts spilling to disk: Find reports that create on-disk temporary tables and fix the memory settings and the query together.
- Fix a plan with a histogram: Add a histogram to a skewed column to fix a wrong join order.
- Use an optimizer hint deliberately: Force the right index with a hint on a query choosing the wrong one and argue whether it is a lasting fix.
- Resolve contention on a hot row: Change a design that updates one counter row on every order and causes lock waits at peak time.
About this course
You find bottlenecks with performance_schema and the sys schema, tune InnoDB memory and disk settings for the workload and measure before and after with sysbench.
What you will learn
- Measuring
- InnoDB settings
- Schema and query
Prerequisites
You should have practised the topics of the same path up to level 301.
Lab environment
Lessons run in your own Kali Linux terminal in the browser, connected to a private lab network. Nothing to install.
Frequently asked questions
- Is this course right for my level?
- The course is at level 401; it assumes you know the topics of the lower-level courses.
- Is there anything to install?
- No. Every lesson runs in your own lab environment that opens in the browser.
- How do lessons work?
- Each lesson has 8 to 10 steps and a short quiz. Early steps show the command to type, later steps give less help; if two attempts fail, the solution opens.
- Can I try it for free?
- Yes. The first three lessons of every course are free.