Advanced MySQL DBA Training

Other training is also available:

Abstract

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".

Course Outline

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

Recap and Survey Form.

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

Morgan Tocker View trainer page

Percona, Inc

Morgan is a MySQL Expert, with interests in Performance Tuning,
scalability and High Availability. He is a regular speaker on these
topics at MySQL (and PHP) conferences in the United States and Canada.

Before joining Percona, Morgan worked as a Technical Instructor for
MySQL (and then Sun Microsystems) in Canada where he taught courses on
High Availability, Performance Tuning and Database Administration. He
has also previously worked in the MySQL Support Team, and provided DRBD
support.

Morgan lives in Montreal, Canada. He's content in settling there after
moving from Australia in 2007.