What you would learn in MySQL High Performance Tuning Guide course?
Check out the steps involved with the query execution
MySQL and the Client/Server Protocol
Learn how the Query Optimizer does its job
The Storage Engine Layer makes MySQL stand out
Searching Candidates for Query Optimizations
The Performance Schema is an excellent tool for optimizing querying because it allows us to measure the time.
It identifies the queries that cause the most load on the system. It's often not worth your time to optimize the top 1000 queries.
How do you find the correct information among the myriad of performance metrics and views available in MySQL?
Understanding why UPDATE and DELETE statements can also be read, even though they are primarily written. This will impact I/O and the response time.
Analyzing the Queries
Before each query, MySQL creates a plan.
How to read query plans and understand the workings of each of the underlying node types on MySQL
Here are some examples to illustrate how EXPLAIN output is used to identify where MySQL spends its time on your query.
What are the essential pieces of information to look out for when trying to spot performance problems?
Choosing the Primary Key and the Clustered Index
Choosing the correct primary index when working with index-organized tables such as those in MySQL is essential.
We will see how the primary key can affect sequential or random I/O, how extensive secondary indexes are, and how many pages must be read into the buffer.
We will see why an optimal primary key concerning the clustered Index is as tiny (in bytes) keeps increasing monotonically and groups the rows that we often query and within a short distance from each other.
Indexing to Performance
What are the main functions of an index?
We will see which tables need an index and which ones are better without one.
We will examine how MySQL uses statistics for the best index selection and how we can assist it in making better decisions.
Common errors in composite indexes
How to determine the order in which columns are placed for multicolumn indexes
You must be able to understand how indexes work and to make decisions based on this understanding.
What are the use-cases that Redundant Indices can be helpful for? And when should they be avoided?
To verify the actual performance of your numbers, you should establish benchmarks.
MySQL Server Configurations
Consider these best practices to help you tune MySQL.
What are the three options MySQL has that MySQL will most benefit from, besides non-default values? And how do you set them?
InnoDB provides an overview of the data lifecycle to help us make configuration changes.
For specific uses, adjust buffer pool instances to handle high concurrent workloads or parallel execution for particular queries.
MySQL Transactions & Locks
Transactions are vital to maintaining data integrity, but if they are not used correctly, they can lead to severe performance problems.
Get an overview of why locks are necessary and how to decrease their impact.
How indexes, splitting transactions, and the isolation level can reduce the number of locks
Monitoring options and what reporting tables can be used to identify lock problems.
Introduction to Scaling MySQL
We'll explain what it means to scale MySQL.
Learn how to scale various types of loads
Replication Why we need to use Read-Pools and Health Checks and Service Discovery
What are the main concerns and pitfalls of Shard the database?
How to Partition Data in MySQL
What are the benefits and use-cases of a Queue?
The Fundamental Concepts of Scaling Writes With Sharding
- Learn how MySQL optimizes, executes queries.
- What is the most time MySQL spends on your query?
- The few questions that cause the most load on the system are the ones to be addressed.
- How can you find the correct information among the myriad performance views and metrics in MySQL?
- Based on statistics and cost decisions, how are queries automatically planned and executed before execution?
- What is a Clustered Indice?
- How do you choose the best Primary Key?
- Find out which tables need an index and which tables work better without one.
- How can we help the MySQL Optimizer make more informed decisions?
- What are the most common mistakes when using indexes for Multiple Columns?
- What are the uses-cases that Redundant Indexes can be helpful for? And when should they be avoided?
- Discuss the Best Practices in Server Configurations
- What are the three most valuable options for MySQL, other than default values? And how can you set them up?
- InnoDB data lifecycle, so we can have background knowledge for configuration changes.
- Learn why Transactions and Locks matter and how to minimize their impact.
- Introduction to MySQL Scaling Options
- Scaling Reads With Replication: Fundamental Concepts
- How to partition MySQL data
- Fundamental Concepts of Scaling Writes With Queueing And Sharding