MySQL High Performance Tuning Guide
Tips for Tuning MySQL like a Pro | Learn how to prevent MySQL performance problems
What you’ll learn
MySQL High Performance Tuning Guide
Understand how MySQL optimizes and executes queries.
Find where MySQL spends most time on your query, and Why?
Finding those few queries that are responsible for most of the load on the system.
How to find the relevant information out of the plethora of performance views, and metrics available in MySQL?
How queries are automatically planned before execution, based on statistics and cost decisions?
Understand what a Clustered Index is?
How to choose the optimal Primary Key?
Find which tables are in need for an index, and which tables are better without one.
How we can help the MySQL Optimizer make better decisions?
Which are the common pitfalls, when using indexes on Multiple Columns?
Which are the use-cases, that make Redundant Indexes useful, and when to avoid them?
Cover the Best Practices for making Server Configurations
Which are the three options that MySQL is most likely to benefit, from non-default values, and also how to set them?
Data lifecycle in InnoDB, so that we have background knowledge when making configuration changes.
Get an overview of why Transactions and Locks are needed and some ways to reduce their impact.
Introduction to MySQL Scaling Options
Fundamental Concepts for Scaling Reads with Replication
How to Partition Data in MySQL
Fundamental Concepts for Scaling Writes with Queueing and Sharding
You need access to a Windows/Mac/Linux PC with 10GB of free disk space
Basic familiarity with database objects such as tables and indexes is expected
Some familiarity with Linux will be helpful
- View the steps involved in Query Execution
- How the Cient/Server Protocol affects MySQL
does its job
Storage Engine Layer
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
, 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
, 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
to choose the best index, and how we can help it to make better decisions.
- 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
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
can help to reduce the number of locks
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
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
Which are the use-cases and advantages for using a
- Fundamental Concepts for Scaling Writes with Sharding
Who this course is for:
- Developers interested in advanced database internals that impact application design and performance
- Database administrators
- Everyone interested in building better MySQL applications
- Last updated 8/2021
If the links does not work, contact us we will fix them
Content From: https://www.udemy.com/course/mysql-high-performance-tuning-guide/