March 10-12, 2010
Montreal, Canada

Advanced MySQL DBA Training

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

  • 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

Oracle

Morgan rejoined the MySQL team at Oracle in 2013 as MySQL Community Manager, having previously worked for MySQL and Sun Microsystems. He blogs from tocker.ca, and especially likes the new features in MySQL that make operations life easier.

Read More