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
Requirements
-
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
Description
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
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
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
Tuning Apache Spark: Powerful Big Data Processing Recipes Course
Download Now Content From: https://www.udemy.com/course/mysql-high-performance-tuning-guide/