Peak MySQL Performance Tuning Training
for Developers & DBAs
About the training
Peak MySQL Performance Tuning Training is an intensive training for developers and system engineers who have some experience with MySQL and want to become real experts in MySQL Performance Management.
The course was created and is delivered by Aurimas Mikalauskas – database architect with over 17 years of field experience with MySQL – 6 years as DBA and 11 years as a senior architect. As the focus of this training is on getting the best possible performance out of MySQL, we’ll be covering inner workings of MySQL, indexing, query and schema optimization, concurrency control, storage engines, profiling, hardware and a number of other areas (see curriculum), and we’ll be practicing how to apply this knowledge in real-life.
In a nutshell:
- Training length: 1-3 days (see curriculum)
- Delivered: on-site or in a remote location
- Training level: advanced
- Designed for: Developers & DBAs / SREs
- Delivered by: Aurimas Mikalauskas, senior architect
- Experience managing Linux OS and MySQL
- Familiarity with MySQL Command Line
- Basic SQL skills
Full MASTER training consists of 2 training days and an integration day. However, it is purposefully designed in a way that you can choose from either 1, 2 or 3 day training depending on your current needs.
DAY 1 – MySQL performance expert
This is the most important and most immediately applicable of the three days as it covers Key MySQL Performance Management skills, namely – MySQL Configuration, Query and Schema Optimization.
Here’s what we cover on day one:
1. MySQL Configuration
Configuring MySQL correctly is extremely important. But it only works if you know exactly what each tunable does. We won’t go through all 400 tunables (most of them don’t impact performance anyway), instead we’ll focus on top 17 that are really important for performance. And we’ll discuss:
- How to approach MySQL configuration tuning and how not to
- What each of the 17 tunable does and when do you want to tune them
- Configuration differences between different versions of MySQL
- What you should NOT tune
2. MySQL Schema Optimization
Here’s what we’ll go over in this module:
- MySQL Data Type optimization
- Normalized -vs- Denormalized schema
- How to use summary tables
- Alternatives to MySQL for solving certain challenges
- How to make schema changes online
3. Query Optimization 101
To tell you the truth, most performance gains always come from properly executed query optimization. That’s where MySQL consultants spend most time. And that is also why we are going to be spending a lot of time here. Here’s what we’re going to cover:
- The Method – how to approach query optimization correctly
- Profiling – what are different ways to profile MySQL
- Indexing basics – how indexes work and what are different types of indexes
- EXPLAIN – what is explain and how to use it when optimizing queries
4. Advanced Query Optimization
Having gotten the basic understanding of indexing, it’s time to delive into more advanced topics. We’ll look deeper into how B+TREE indexes work and how to use that for our benefit. We’ll also discuss which types of optimizations are supported in which versions of MySQL. Topics will include:
- Range queries
- Multi-column indexes
- Covering indexes
- Index cardinality / selectivity
- How queries are parsed
- Reading handler statistics
- Query transformation
- Group by optimization
- Order by optimization
- Multi-range read optimization
- Index Condition Pushdown
- Custom hash indexes
- And on and on
DAY 2 – Expert integration
Day 2 is integration day. It is where we put skills learned on day 1 into practice. I found that when I’m working closely with the students to apply what they had just learned, it greatly helps them retain those skills. This practice is also very illuminating, because some of the things we learn on day one are actually hard to believe, until you see it with your own eyes and on your own systems.
1. MySQL Server utilization review
We will take one of the MySQL servers (preferably live systems) and review them. And we’ll begin with a server utilization review. That is, we will look at different hardware metrics and we’ll discuss what they mean in current context and what could they mean in other contexts.
2. MySQL status counter review
Next, we’ll go over MySQL status counters to understand better how to look at them, which counters to pay attention to and which can be ignored. I will also share some case studies from my personal experience with other customers.
3. MySQL configuration review
Now we will look at MySQL configuration that’s running currently in production. We will go over each of the key variables again and we’ll put them in the context of the current system and we’ll see what we should change here based on our findings while reviewing the counters.
4. Query & Schema review and optimization
And this is where we’ll be spending most of our time. We’ll collect some queries from live system(s), we’ll profile them and then we’ll go through each of the queries that have most impact and try to optimize them.
DAY 3 – MySQL performance master
Day 3 is a MySQL deep dive and I only recommend it to companies that already have a pretty skilled DBA/SRE team and want to take their skills to the next level so they can troubleshoot various MySQL issues with ease.
On this day students will learn how MySQL interacts with server hardware – CPUs, DISKs, MEMORY. We’ll go really deep into MySQL internals – from concurrency control to transaction logging. And finally, we’ll discuss different types of application architectures and MySQL role in scaling application.
1. How hardware and OS interacts with MySQL
We will start with a discussion on each hardware component and how it relates to MySQL: CPUs, Memory, Disk subsystems. We’ll cover questions such as:
- What hardware components are important to consider
- What type of disks I should be using for MySQL and why
- How do magnetic and solid state drives work internally
- Should I get more RAM or faster disks
- What hardware & OS settings matter for best MySQL Performance
- How file systems work, what file systems work best with MySQL
2. MySQL server architecture
In this section, we’ll take a look at key MySQL architecture details:
- Concurrency control and locking in MySQL InnoDB storage engine
- MVCC implementation
- How different transaction isolation levels work and how they impact MySQL performance
- Transaction logging – what it is, how it works
- Optimizations in InnoDB storage engine
- Other Storage engine overview
3. Application architecture
We’ll discuss what strategies we can employ for scaling with MySQL from one server to high performance highly available cluster, finishing with the most common architecture mistakes. Here’s some of the items we’ll cover:
- Scaling-up -vs- Scaling-out
- How do you know when it’s really time to scale
- What are different scale-out strategies
- How you can use replication for scale-out and when it makes sense to
- Partitioning and sharding
4. What’s new in MySQL 5.7 & MySQL 8.0
We’ll finish day 3 with an overview of new features in recent versions of MySQL, especially 5.7 and 8.0 to understand what current performance and architecture problems they may help you solve. Some of the things we’ll cover:
- Generated (Virtual) Columns
- Query Rewrite
- New optimizer switch hints
- Invisible indexes
- EXPLAIN for CONNECTION
- EXPLAIN FORMAT=JSON
- Histogram statistics
- Optimizer costs
Aurimas Mikalauskas is a database architect with over 17 years of experience with MySQL. He started building websites on MySQL 3.23 back in 1999, few years later, when the internet started exploding in Lithuania, he was leading scaling of four largest websites in Lithuania (pazintys.lt, autoplius.lt, klase.lt and supermama.lt).
In 2006 Aurimas joined Percona (then MySQLPerformanceBlog) as their first MySQL consultant where he worked for over 9 years with companies such as BBC, Rocket Internet, Engine Yard, Boardreader, biggest social networks and hundreds other startups and large corporations. He is regularly speaking at various tech conferences, such as PLMCE, Zabbix or FrOSCon on the topic of MySQL Performance Management.
He is actively consulting over at Speedemy (you can try hiring him here) and occasionally (2-4 times a year) he takes on a challenge to train a new generation of MySQL Experts.
I’m interested. What’s next?
I’m sold out for this year. If you want to know when the next window opens, leave your email and I will keep you posted: