- Home
- Montreal 2010
- Sessions
This tutorial is about running a High Performance MySQL Application in production. You will learn how to inherit an application, and then start running diagnostics to find out where your performance problems are, using the same approach I have as a database consultant. We then move onto tuning queries, tuning MySQL configuration and understanding how InnoDB works internally to start tuning it. This tutorial comes with prerequisites: You need to have some production experience with MySQL, and understand concepts such as "what is a storage engine".
Introduction - 30 mins
- House Keeping Issues
- How to think about Performance
- Capacity and Response
Retrofitting Performance Wins - 30 mins
- Explaining the Approach
- Using OS diagnostic tools as sanity checks
- Using mk-query-digest to step inside MySQL
Optimizing Queries - 60 mins
- Using Explain
- Query Optimizer edge cases and Limitations
- Example cases for denormalization
Statistics in MySQL - 30 mins
- SHOW GLOBAL STATUS
- SHOW ENGINE INNODB STATUS
- Discussing Limitations of both commands, and aggregation tools
Changes to Architecture - 30 mins
- Hard problems to solve (persistence, affinity)
- How to think about architecture
- What MySQL is bad at
- Three different caching strategies
A Closer look at InnoDB - 90 mins
- Architecture Overview
- Checkpoints and main thread
- The tradeoff between performance and recoverability
- Multiversion concurrency control
- Clustered indexes
- Adaptive Hash
- Insert Buffer
- Doublewrite buffer
- Transaction Log Files
SHOW INNODB STATUS - 30 mins
- Walk through with XtraDB and InnoDB.
Limitations in InnoDB (aka XtraDB) - 60 mins
- A long list of all of the identified problems.
- Improvements to diagnostics first!
- CPU scalability
- Import/Export tables
- IO scalability enhancements (and example usage)
- InnoDB Plugin features (compression, fast alter table)
- Fast Crash Recovery
- Adaptive checkpointing
- Insert buffer control
- Data dictionary control
- Additional Undo Slots
Operational Issues - 15 mins
- A list of some common problems (i.e. ALTER TABLE not online)
- Hands on example of using MMM and master/slave switching.
Hardware and OS - 15 mins
- The quick wins (filesystems, network settings, schedulers)
- The more complicated (some options that are workload dependent)
- disk benchmarking tools
- RAID controllers
Conclusion - 15 mins
Target Audience
Database administrators who are familiar with MySQL maintenance and
administration looking to improve their capacity forecasting skills,
to do more with existing hardware, and to shorten their incident
response time.
Training Details
Duration: 1 day (Tuesday)
Cost: $300
Maximum capacity: 15 persons
Requirements: A laptop with a text editor and MySQL installed