MySQL High Performance Tuning Guide

MySQL Architecture

• View the steps involved in Query Execution
• How the Cient/Server Protocol affects MySQL
• Understand how
  
  
  
  
  
  the
  
  
  
  Query Optimizer
  
  
  
  does its job
• How the
  
  
  
  Storage Engine Layer
  
  
  
  makes
  
  
  
  
  
  MySQL unique

Finding Candidates for Query Optimizations

• Making use of The Performance Schema, which is a gold mine for spotting querying for optimization because it allows us to measure where the time goes
• Finding the few queries that are responsible for most of the load on the system. Checking to the top 1000 queries it’s usually not worth your time to optimize.
• How to find the relevant information out of the plethora of performance views and metrics available in MySQL
• Understand why UPDATE and DELETE statements are also reads even though they are primarily write statements. This will have an impact on I/O, and therefore to the response time.

Analyzing the Queries

• Understand why MySQL creates a
  
  
  
  query plan
  
  
  
  , before each query request
• How to read query plans, and understand how each of the underlying node types works on MySQL
• A series of examples, to show how the EXPLAIN output can be used, to identify
  
  
  
  where MySQL spends time on your query
  
  
  
  , and why
• Which are the important pieces of information, to look for in order to spot performance issues

The Clustered Index and Choosing the Primary Key

• When we work with index-organized tables, as the ones in MySQL, the choice of the primary index, is very important.
• We’ll see how the primary key, can impact the amount of random, or sequential I/O, the size of secondary indexes, and how many pages need to be read into the buffer pool.
• We’re going to understand, why an optimal primary key, with respect to the clustered index,
  
  
  
  is as small
  
  
  
  (in bytes) as possible, keeps
  
  
  
  increasing monotonically
  
  
  
  , and groups the rows we query frequently, and within a short distance of each other.

Indexing for performance

• Which are the main function of an index?
• We’ll see exactly which tables are in need for an index, and which tables are better without one.
• We’ll check how MySQL relies on
  
  
  
  statistics
  
  
  
  to choose the best index, and how we can help it to make better decisions.

Composite Indexes

• Common mistakes of composite indexes
• How to decide
  
  
  
  the order of columns
  
  
  
  for multicolumn indexes
• Be able to reason through how indexes work, and to choose them based on that understanding, not on rules of thumb
• Which are the use-cases, that make
  
  
  
  Redundant Indexes
  
  
  
  useful, and when to avoid them.
• Work on some benchmarks, to check the actual performance in numbers.

MySQL Server Configurations

• Check how we should approach MySQL configuration changes by considering some best practices, so that we can successfully tune MySQL.
• Which are the three options that MySQL is most likely to benefit, from non-default values, and also how to set them.
• Go through an overview of the data lifecycle in InnoDB, so that we have background knowledge when making configuration changes.
• Adjust parameters that are important for certain use-cases, for example, buffer pool instances for high concurrent workloads, and how to make use of parallel execution for certain queries.

MySQL Transactions and Locks

• Transactions are very important to ensure data integrity but when used wrong they can cause severe performance issues.
• Get an overview of
  
  
  
  why locks are needed
  
  
  
  and some ways to reduce their impact.
• How indexes, splitting the transaction and the
  
  
  
  isolation levels
  
  
  
  can help to reduce the number of locks
• 
  
  Monitoring options
  
  
  
  and what
  
  
  
  reporting
  
  
  
  
  
  tables
  
  
  
  to check to spot lock problems.

Introduction to Scaling MySQL

• What it means to scale MySQL and, we’ll walk through the different axes where we may need to scale.
• Understand how to scale
  
  
  
  different kinds of loads
  
  
• 
  
  Replication
  
  
  
  and why we need to make use of Read-Pools, Health Checks, and Service Discovery
• Which are the main concerns and pitfalls when deciding to
  
  
  
  Shard
  
  
  
  the database
• How to
  
  
  
  partition data
  
  
  
  in MySQL
• Which are the use-cases and advantages for using a
  
  
  
  Queue
  
  
• Fundamental Concepts for Scaling Writes with Sharding