MySQL High Performance Tuning Guide
2h 49m 22s
English
Paid
October 14, 2024
Tips for Tuning MySQL like a Pro. Learn how to prevent MySQL performance problems. Understand how MySQL optimizes and executes queries.
More
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
Watch Online MySQL High Performance Tuning Guide
Join premium to watch
Go to premium
# | Title | Duration |
---|---|---|
1 | Query Execution Basics | 02:02 |
2 | Utility Layer | 02:33 |
3 | SQL Layer | 04:26 |
4 | Storage Engine Layer | 02:03 |
5 | Conclusions | 00:30 |
6 | Making use of Performance Schema | 04:11 |
7 | Top 10 Time-Consuming Queries | 03:33 |
8 | Why Are Queries Slow? | 01:30 |
9 | Finding Candidate Queries for Optimization | 02:37 |
10 | Table I/O and File I/O for Selects | 01:45 |
11 | Table I/O and File I/O for Updates and Deletes | 03:34 |
12 | The Error Summary Tables | 01:41 |
13 | Conclusions | 00:37 |
14 | How does the Optimiser work? | 02:12 |
15 | EXPLAIN basics | 01:34 |
16 | Example 1: Single Table, Table Scan | 02:36 |
17 | EXPLAIN ANALYZE | 01:13 |
18 | Example 2: Single Table, Index Access | 00:43 |
19 | Example 3: Multicolumn Index | 00:59 |
20 | Example 4: Two Tables With Subquery And Sorting | 01:55 |
21 | Spotting jumps in runtime | 04:42 |
22 | Hot and cold cache behaviour | 00:51 |
23 | Conclusions | 00:39 |
24 | Section Overview | 00:53 |
25 | The Clustered Indexes | 01:02 |
26 | Choosing a Good Primary Key | 03:44 |
27 | Use Case: Inserting rows in primary key order with InnoDB | 05:04 |
28 | Optimal Primary Key in Practice | 01:42 |
29 | Introduction | 04:37 |
30 | Why add a Secondary Index? | 03:47 |
31 | When should you Add or Remove Indexes? | 02:14 |
32 | Why Index Statistics are Important? | 02:07 |
33 | How to help MySQL make better decisions? | 04:22 |
34 | Covering Indexes | 03:38 |
35 | Motivation | 01:52 |
36 | Columns Order | 02:00 |
37 | Composite Index Hands-on | 05:07 |
38 | Using Redundant Indexes | 04:53 |
39 | Composite Indexes Extra Remarks | 01:52 |
40 | Conclusions | 00:31 |
41 | Configuration Best Practices | 04:44 |
42 | InnoDB Overview | 02:56 |
43 | Adjusting the Buffer Pool Size | 03:49 |
44 | Adding more Buffer Pool Instances | 02:06 |
45 | Why Dumping the Buffer Pool? | 01:09 |
46 | Tuning Flushing Pages Process | 02:47 |
47 | Tuning Redo Log Buffer | 01:51 |
48 | Tuning Redo Log Files | 03:03 |
49 | Parallel Query Execution | 01:29 |
50 | Adjusting the Query Buffers | 02:00 |
51 | Conclusions | 00:56 |
52 | Impact of Transactions | 01:07 |
53 | Managing Locks | 03:45 |
54 | Purpose of Undo Logs | 02:22 |
55 | Reduce Locking - Transaction size and age | 02:02 |
56 | Reduce Locking - Indexes | 01:48 |
57 | Reduce Locking - Transaction Isolation Levels | 03:51 |
58 | Monitoring Transactions | 01:46 |
59 | Monitoring Locks | 00:48 |
60 | Conclusions | 00:58 |
61 | Scaling MySQL Intro | 00:50 |
62 | Read Versus Write Loads | 02:31 |
63 | Replication - Scaling Reads with Read Pools | 03:38 |
64 | Replication - Service Discovery for Read Pools | 02:23 |
65 | Queuing | 02:36 |
66 | Sharding Introduction | 03:28 |
67 | Sharding Concerns | 04:06 |
68 | How to Partition Data in MySQL | 03:16 |
69 | Multiple Partitioning Keys | 01:57 |
70 | Querying Across Shards | 01:29 |
Similar courses to MySQL High Performance Tuning Guide
Database DevOps from Start to Finish
Duration 2 hours 40 minutes 37 seconds
Course
The Complete SQL Bootcamp: Go from Zero to Hero
Duration 8 hours 45 minutes 49 seconds
Course
Database Design & Implementation
Duration 2 hours 7 minutes 49 seconds
Course
SQL & MySQL Subqueries: Understanding the weird parts
Duration 8 hours 53 minutes 49 seconds
Course
PostgreSQL Bootcamp : Go From Beginner to Advanced, 60+hours
Duration 67 hours 20 minutes 55 seconds
Course
Learn Database Design with MySQL
Duration 6 hours 14 minutes 51 seconds
Course
Ultimate Expert Guide: Mastering PostgreSQL Administration
Duration 10 hours 19 minutes 42 seconds
Course
Zero To Shipped
Duration 24 hours 38 minutes 44 seconds
Course
100 Days Of Code: The Complete Web Development Bootcamp 2024
Duration 78 hours 51 minutes 55 seconds
Course