MySQL Performance Tuning: Best Practices

Introduction

Database performance tuning is a continuous, iterative process of identifying potential database optimizations and applying the findings. Performance tuning is a never-ending cycle of measuring database system performance, identifying bottlenecks, implementing fixes and improvements, then measuring performance again to assess the change and identify further opportunities.

Database performance tuning helps keep your system in optimal shape. It is about ensuring the lowest query latency and the highest query throughput. Effective tuning strategies also require organizations to track the impact of changes and workload fluctuations on database performance.

This article will focus on the most common database workload for MySQL and MariaDB —online transaction processing (OLTP). We’ll deep dive into five MySQL performance tuning best practices to help you get the most out of your production databases and avoid minor issues from escalating into service incidents.

Before we begin, let’s get some technical housekeeping out of the way. Some of the best practices in this article will assume you have direct access to the self-managed database server. We also assume the database is the sole application running on the server and that all its resources are available to it. While we’ll primarily focus on MySQL and MariaDB databases using the InnoDB query engine, the majority of this article’s content also applies to Google Cloud SQL, Amazon Aurora, Microsoft Azure DB for MySQL, Percona deployments, and other MySQL-compatible managed services.

Summary of MySQL performance tuning best practices

	Best Practices		Description
Establish a baseline for MySQL performance tuning	Show the current status of database performance using tools and internal metrics
Implement a change execution process	Prepare and follow a process of applying tuning changes; each executed change may interact with others, both positively and negatively
Perform SQL query tuning	Analyze the EXPLAIN output, index hints, and optimizer switches, and cover basic indexing strategies
Tune the operating system and database	Swap files, tune disk schedulers, and use MySQL/InnoDB tuning for particular hardware and workloads (I/O-bound, CPU-bound)
Measure performance post-change	Track typical follow-up issues related to fixing a bottleneck after the change has been implemented

Establish a baseline for MySQL performance tuning

Tuning is necessarily relative. It is only by firmly establishing what is “normal” that we can easily identify abnormal behavior. Ideally, the goal of any tuning exercise should be to go from X to Y by Z, where “X” and “Y” are specific, measurable key performance indicators and “Z” is a specific timeline. This best practice is all about establishing a baseline for database performance.

Please check whether this text should be the same font and size as the surrounding body text. This also applies to the other links throughout the document.

These are used by SEO to hyperlink to other articles on the SW website. So yeah, that is fine

Why is a baseline a must-have for effective MySQL performance tuning?

Everything starts with measurement. Implementing a change, on its own, is futile if you cannot tell what the outcome of the change is. In some cases, you can guess that adding an index to a column used in a WHERE condition, which does a table scan, will probably be a good idea. Most of the time, though, it’s not so clear. Let’s say you disabled an InnoDB Adaptive Hash Index (AHI), something you may want to do when your workload is highly concurrent.

How does this change affect your workload? AHI has its own purpose—it speeds up index access by building a hash index on top of the most frequently accessed B-tree indexes—so at what moment is the right time to disable it? Another example: you have fast storage on your database server (e.g., SSD, NVMe drives) and you want to benefit from it. You can tweak innodb_io_capacity and innodb_io_capacity_max, but what is the “good” value? 1,000? 5,000? 20,000?

Applying any change blindly may improve, reduce, or have no impact on your database system’s performance. Unless you know your baseline, there’s no way to tell the difference.

Note: When tuning based on your baseline, only change one element at a time. Otherwise, you won’t know which of the multiple changes helped, hurt, or were neutral.

How to track MySQL performance tuning-related metrics

In 8.4.6 MySQL Community Server - GPL, you will find 495 different metrics exposed by the SHOW GLOBAL STATUS command, which prints out different metrics that can be tracked in MySQL.

mysql> \P grep "rows in set"
PAGER set to 'grep "rows in set"'
mysql> SHOW GLOBAL STATUS\G
495 rows in set (0.010 sec)

Beyond SHOW GLOBAL STATUS, MySQL 8.4.6 also includes 113 tables in performance_schema, 78 in information_schema, and 101 in the sys schema

What metrics should you track? As many as you can, but not necessarily for the reason you think. Not all metrics are equally important. You will learn shortly which ones you should monitor. Having said this, it’s still good to have as much data as possible, because in some cases, subtle details matter—especially after a couple of iterations, when all low-hanging fruit has been addressed. It’s good to have the option to dig deeper to understand where the bottleneck is.

What MySQL metrics to track and why

Which database metrics organizations measure matters, as those numbers quickly become areas of focus. With this in mind, let’s examine what database metrics teams should measure across three key categories.

Global metrics (critical metrics for the operation of every system)

CPU utilization: High CPU usage may indicate inefficient queries; it also gives you a heads-up for scale up
Memory utilization: This relates to the different in-memory buffers created by MySQL; it can also cause database crashes, e.g., running out of memory may trigger the Out of Memory (OOM) killer on the MySQL process
I/O utilization: I/O is critical for query performance; monitoring database writes can help you identify when you should start considering sharding your database
Network: This is typically the least important metric, unless you use network-attached storage, which is common in the cloud

Query performance (user experience and stable performance)

Execution time: This tells you what the experience is for most users, so you want the execution time to be relatively fast and predictable; it’s common to measure not only execution time itself but the 99th or 95th percentile (i.e., 99% or 95% of queries meet/exceed this execution time)
Wait time: Time spent by queries waiting on other elements (e.g., I/O, CPU, access to internal structures); the fewer waits, the more stable and predictable the overall performance
Locking contention: Queries interact with each other, leading to concurrent access to the same data, and locks are introduced to enforce serialization; the fewer locks, the more stable the performance of the whole system

Database metrics (main database metrics to monitor)

InnoDB redo log metrics (checkpointing): Checkpointing performance is critical in write-heavy workloads, where the key is storing data on disk efficiently
Buffer pool metrics: The InnoDB buffer pool stores frequently accessed data and recently written data; understanding its health helps determine whether the active data set fits in memory or is mostly loaded from disk, and this information could reveal whether the CPU or the disk is the bottleneck
InnoDB I/O metrics: These metrics help to see what kind of workload you’re facing when observed in conjunction with the buffer pool and allow you to understand how heavy the I/O pressure is; this is critical information, especially in cloud systems with network-attached storage, which are typically slower than directly attached disks
Handlers: SQL handlers are the window into how the database executes queries—whether they use index lookups, index scans, or full table scans, and how many database sorts are executed and temporary tables created; these metrics are crucial to understanding the workload

MySQL performance optimization is not only about metrics. If you are facing a performance issue, access to data alone will not solve your problem—understanding the specific issue within the broader workload will. For this, you have to see the correlation between different metrics. What you observe might be one layer of the issue, while the root cause lies elsewhere.

You can easily go from memory spikes to I/O flushing in one debugging session. This might be between five and 10 different graphs, found in several separate locations (e.g., system metrics, InnoDB I/O metrics, query-related metrics). A customized dashboard in a tool such as SolarWinds^® Database Performance Analyzer (DPA) can give you a clear view across different types of metrics, correlated by time. Such an approach helps ensure a better understanding of what is happening in the database internals. A more holistic approach saves time that would otherwise be spent on debugging performance problems.

Using DPA to determine the root cause of I/O-related performance issues.

Implement a change execution process

After identifying a database tuning opportunity, teams need to implement the change safely. This best practice explains how this can be done.

Sample change execution flow

As an example, end users recently created a support ticket indicating a report that has always performed well during their monthly reporting cycle now takes five times as long as it did only last month. We start the root-cause analysis process and, after comparing current metrics to our baseline, can easily verify the report is indeed problematic. In fact, we soon learn the report has a couple of new SQL queries that were added since the last reporting cycle. We start with the baseline to verify through telemetry whether we are experiencing a performance anomaly.

Next, by studying the new report compared to the old, we identify the improvement opportunities, such as adding a new index, reverting to the original report, or tuning the newly added SQL queries. Then, we apply one optimization at a time to the staging environment. We make sure to make only one optimization at a time, testing each one and verifying through telemetry to see which one(s) provides the best remedy.

Ideally, the staging environment is a full copy of the production, since the volume of data in the database can impact behavior and performance. There, you can properly validate any change you want to make. This would be in a perfect world. In the real world, the staging environment is more often than not a scaled-down environment where only a basic validation can be done. For example, if your production database is 4TB, it might be too costly to reproduce the entire production database in staging. So, you have to use a scaled-down version.

If you find performance does not improve after testing each of the optimizations, it’s time to roll back the change. On the other hand, if you find a definitive improvement, you can implement it in production, followed by constant performance measurement and monitoring.

This is why baselines are so important. When making any change, compare it to the baseline. If there is a performance degradation of some sort, roll back the change. If everything is fine, use the current setup as the performance baseline for any further changes.

Change management best practices

When implementing changes related to database performance, teams should follow these three change management best practices:

Never execute multiple changes at the same time

Compare the performance after the change with the baseline
If the change consists of many separate elements, you won’t be able to tell their impact

Use a version tracking solution for the changes you make

Track changes in the configuration by storing config files in the git repository
Keep track of indexes or schema changes by storing SHOW CREATE TABLE output in git

Automate the change process by turning it into a pipeline

Pull requests give you the option to do reviews and discuss changes before they are applied
This can be a part of a cooperation with the development team to review any schema changes they want to apply

Perform SQL query tuning

In OLTP workloads, well-tuned queries are, most commonly, the source of performance improvements. Unless there are significant configuration problems, database configuration tuning typically won’t have as much impact as query tuning. Because query tuning often offers significant opportunities for performance gains, it is one of the most essential MySQL performance tuning best practices. The steps below can help teams achieve meaningful performance improvements.

Step 1: EXPLAIN—query execution plans

For every query, MySQL generates a query execution plan. All relational databases use query execution plans based on a cost-based optimizer. MySQL analyzes the query itself, checks the WHERE clause for conditions, and examines the table structure, available indexes, and sample data to determine the best way to execute the query. EXPLAIN is a way for you to understand the choices made by the cost-based optimizer and how the query will be executed.

mysql> EXPLAIN SELECT * FROM sbtest1 WHERE id BETWEEN 20000 AND 40000 AND c LIKE '8%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 39748
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.003 sec)

The query above shows a basic SQL EXPLAIN you’re likely to see in different online articles and posts. The most important characteristics are:

There are no partitions involved
"type: range” means there’s a range scan (due to BETWEEN)
It is possible to use a PRIMARY index
The PRIMARY index is going to be used
The length of the key is 4 (this depends on the type and number of columns in the index)
The table has not been referred to, as there are no JOINs
An estimated ~40k rows will be accessed, but around 11% of them will be filtered out
Using WHERE means there’ll be an additional filtering step applied on top of the index scan

Step 2: EXPLAIN FORMAT=json

mysql> EXPLAIN FORMAT=json SELECT * FROM sbtest1 WHERE id BETWEEN 20000 AND 40000 AND c LIKE '8%';

{
  "query_block": {
    "select_id": 1,
    "cost_info": {
      "query_cost": "7986.55"
    },
    "table": {
      "table_name": "sbtest1",
      "access_type": "range",
      "possible_keys": [
        "PRIMARY"
      ],
      "key": "PRIMARY",
      "used_key_parts": [
        "id"
      ],
      "key_length": "4",
      "rows_examined_per_scan": 39748,
      "rows_produced_per_join": 4416,
      "filtered": "11.11",
      "cost_info": {
        "read_cost": "7544.95",
        "eval_cost": "441.60",
        "prefix_cost": "7986.55",
        "data_read_per_join": "3M"
      },
      "used_columns": [
        "id",
        "k",
        "c",
        "pad"
      ],
      "attached_condition": "((`sbtest`.`sbtest1`.`id` between 20000 and 40000) and (`sbtest`.`sbtest1`.`c` like '8%'))"
    }
  }
}

This is more detailed, which is useful for more complex queries.

Step 3: EXPLAIN ANALYZE

mysql> EXPLAIN ANALYZE SELECT * FROM sbtest1 WHERE id BETWEEN 20000 AND 40000 AND c LIKE '8%'\G

EXPLAIN: -> Filter: ((sbtest1.id between 20000 and 40000) and (sbtest1.c like '8%'))  (cost=7987 rows=4416) (actual time=0.734..23.8 rows=2042 loops=1)
    -> Index range scan on sbtest1 using PRIMARY over (20000 <= id <= 40000)  (cost=7987 rows=39748) (actual time=0.647..22 rows=20001 loops=1)

The most important feature of this format is it shows actual metrics (e.g., execution time, rows examined), not only estimates (queries executed under the hood).

Indexing strategies

Indexing is a deep subject, so we’ll cover the basic, but important, pieces of information you need to get started. First, five core concepts:

PRIMARY KEY (PK) – A unique index also used for building a storage structure for the table in MySQL/InnoDB; ideally, you want it to be as small a data type as practical, such as an INT or BIGINT, as accessing any data requires traversing the PK
UNIQUE KEY – A regular index with uniqueness enforcement, as there can be no two rows with the same index value; these are also sometimes called “alternative keys”
Single-column index – An index created on a single column
Composite index, multi-column index – An index created on more than one column
Covering index – An index that covers all the data required for the query; a single index lookup is enough to provide the result, and no data lookup to the base table is required

Composite and covering indexes require a deep dive. Suppose we have a table with this structure:

CREATE TABLE `sbtest1` (
  `id` int NOT NULL AUTO_INCREMENT,
  `k` int NOT NULL DEFAULT '0',
  `c` char(120) NOT NULL DEFAULT '',
  `pad` char(60) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`),
  KEY `k_1` (`k`)
) ENGINE=InnoDB AUTO_INCREMENT=100001

It has one PK and one index on column “k”. What if we have this query:

SELECT pad FROM sbtest1 WHERE k=49851 AND c LIKE '9%';

If we check the index, it shows index k_1 is used:

mysql> EXPLAIN SELECT pad FROM sbtest1 WHERE k=49851 AND c LIKE '9%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: ref
possible_keys: k_1
          key: k_1
      key_len: 4
          ref: const
         rows: 106
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.002 sec)

This is one column, but there are two columns in the WHERE clause. Let’s create a composite index:

mysql> ALTER TABLE sbtest1 ADD KEY idx_k_c (k, c);
Query OK, 0 rows affected (0.363 sec)
Records: 0  Duplicates: 0  Warnings: 0

Now, EXPLAIN shows this new index is used:

mysql> EXPLAIN SELECT pad FROM sbtest1 WHERE k=49851 AND c LIKE '9%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: range
possible_keys: k_1,idx_k_c
          key: idx_k_c
      key_len: 484
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.007 sec)

Out of the possible indexes “k_1” and “idx_k_c,” the former was used. This can be confirmed by looking at the key_len column. The rows accessed are now estimated to be 14 instead of 106. We will also filter 100% of the rows using indexes.

When working with composite indexes, it’s important to keep in mind they’re accessed in ordinal position from left to right, and all prior columns must be used. For example, an index on (k, pad, c) for this particular query would be pointless, as the “id” column is not used in the query. In this case, only the “k” column would be useful, but given there’s already an index on “k,” there’s no use for a composite index like this. However, this doesn’t mean the index shouldn’t be created. Maybe there’s a query with WHERE k = ? AND pad = ? AND c = ? In such a case, an index on (k, pad, c) would be used.

As for covering indexes, let’s consider the following query:

mysql> EXPLAIN SELECT pad FROM sbtest1 WHERE k=49851\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: ref
possible_keys: k_1,idx_k_c
          key: k_1
      key_len: 4
          ref: const
         rows: 106
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.002 sec)

It uses an index on “k” for filtering. But if we create an index on “k” and “pad,” it’s as follows:

mysql> ALTER TABLE sbtest1 ADD KEY idx_k_pad (k, pad);
Query OK, 0 rows affected (0.239 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> EXPLAIN SELECT pad FROM sbtest1 WHERE k=49851\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: ref
possible_keys: k_1,idx_k_c,idx_k_pad
          key: idx_k_pad
      key_len: 4
          ref: const
         rows: 106
     filtered: 100.00
        Extra: Using index

Then we have created a covering index.

Note: “Extra: Using index” means “idx_k_pad” has been used not only for the filtering (on the “k” column) but also for retrieving data from the “pad” column. In this case, the lookup into the table is no longer necessary—all required data is available in the index.

Before you start indexing everything, you should keep in mind: every index has a cost to maintain. It’s automatic and runs in the background, but for every write to the table, all affected indexes must also be updated. This process adds overhead and slows down database performance. The performance impact can add up quickly, making it important for teams to manage indexes. For example, administrators should remove duplicate or unused indexes. Additionally, teams should weigh the pros and cons before adding a new index.

DPA Index Advisors.

DPA has a feature called Index Advisors, which can help database administrators check where indexes are likely to be missing based on the workload. It can also estimate the impact of adding an index, making it easier to decide whether it is worth adding.

Details of a specific Index Advisor entry in DPA.

Step 1: Query rewriting

The MySQL optimizer gets smarter every release, and inefficient patterns that were a problem in the past might be optimized on the query execution plan level. However, there are often still queries you can manually optimize. Let’s review an example involving a couple of tables.

SELECT s1.id
FROM sbtest1 AS s1
WHERE EXISTS (SELECT 1 FROM sbtest2 s2 WHERE s2.k = s1.k AND s2.k IN (10,20))
   OR EXISTS (SELECT 1 FROM sbtest3 s3 WHERE s3.k = s1.k AND s3.id BETWEEN 1000 AND 2000);

We are querying the sbtest1 table using an OR condition related to the sbtest2 and sbtest3 tables.

We have two dependent subqueries, which means for every row in the sbtest1 table, subqueries are executed on the sbtest2 and sbtest3 tables.

OR can fairly easily be converted into a UNION. If we rewrite the query to the following form, converting the subqueries to JOIN statements and separating both OR clauses into two UNION statements, we get the following:

SELECT DISTINCT s1.id
FROM sbtest1 AS s1
JOIN sbtest2 AS s2 ON s2.k = s1.k
WHERE s2.k IN (10,20)
UNION
SELECT DISTINCT s1.id
FROM sbtest1 AS s1
JOIN sbtest3 AS s3 ON s3.k = s1.k
WHERE s3.id BETWEEN 1000 AND 2000;

Then we can remove the dependent subquery from the query execution plan.

+----+--------------+------------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------------------------------------+
| id | select_type  | table      | partitions | type  | possible_keys | key     | key_len | ref         | rows | filtered | Extra                                     |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------------------------------------+
|  1 | PRIMARY      | s2         | NULL       | range | k_2           | k_2     | 4       | NULL        |    2 |   100.00 | Using where; Using index; Using temporary |
|  1 | PRIMARY      | s1         | NULL       | ref   | PRIMARY,k_1   | k_1     | 4       | sbtest.s2.k |    6 |   100.00 | Using index                               |
|  2 | UNION        | s3         | NULL       | range | PRIMARY,k_3   | PRIMARY | 4       | NULL        | 1874 |   100.00 | Using where; Using temporary              |
|  2 | UNION        | s1         | NULL       | ref   | PRIMARY,k_1   | k_1     | 4       | sbtest.s3.k |    6 |   100.00 | Using index                               |
|  3 | UNION RESULT | <union1,2> | NULL       | ALL   | NULL          | NULL    | NULL    | NULL        | NULL |     NULL | Using temporary                           |
+----+--------------+------------+------------+-------+---------------+---------+---------+-------------+------+----------+-------------------------------------------+

This change reduces execution time from 1.3 seconds to 0.08 seconds. This simple example demonstrates why it is essential not only to verify indexing but also to examine the query structure.

Step 2: Index hints

The MySQL optimizer is not always right, and it can be frustrating when you know it’s not. Luckily, there are some ways you can convince it to do what you want.

Index hints (FORCE INDEX, USE INDEX, IGNORE INDEX)
Optimizer switches

Index hints are fairly simple—they tell the optimizer to try to use a given index, force it to use a given index (or no index at all), or confirm it will not use a given index. An example might be as follows:

mysql> EXPLAIN SELECT pad FROM sbtest1 WHERE k=49851 AND c LIKE '9%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: range
possible_keys: k_1,idx_k_c
          key: idx_k_c
      key_len: 484
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.017 sec)

The optimizer decided to use a composite index on both “k” and “c.” If we want to use index “k_1,” we can modify the query and use the index hint:

mysql> EXPLAIN SELECT pad FROM sbtest1 FORCE INDEX(k_1) WHERE k=49851 AND c LIKE '9%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: ref
possible_keys: k_1
          key: k_1
      key_len: 4
          ref: const
         rows: 6
     filtered: 11.11
        Extra: Using where
1 row in set, 1 warning (0.004 sec)

As you can see, only the “k_1” index was considered by the optimizer. You should track and document such custom tuning, as it may change its behavior in the future, for example, between MySQL versions.

Step 3: Optimizer switches

Every version of the MySQL optimizer changes and gets new features and heuristics to modify the behavior of its cost-based estimates. Those features may affect the query execution plans, and not always in a good way. This is especially true for upgrades between major MySQL versions, where the changes in the optimizer might be significant.

MySQL allows tuning the optimizer by disabling optimizations on the global and query levels. Global settings are stored in the variable:

mysql> show global variables like '%switch%'\G
*************************** 1. row ***************************
Variable_name: optimizer_switch
        Value: index_merge=on,index_merge_union=on, (...)
1 row in set (0.016 sec)

To disable an optimization, you can exclude it from this string and update the variable. Make sure you keep the changes documented for future reference. In some cases, you may want to disable an optimization on the query level.

mysql> EXPLAIN SELECT pad FROM sbtest1 WHERE k=49851 AND c LIKE '9%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: range
possible_keys: k_1,idx_c,idx_k_c
          key: idx_k_c
      key_len: 484
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using index condition
1 row in set, 1 warning (0.008 sec)

This query uses Index Condition Pushdown (Extra: Using index condition). As an example, suppose this is inefficient, and we want to disable the optimization:

mysql> EXPLAIN SELECT /*+ SET_VAR(optimizer_switch='index_condition_pushdown=off') */ pad FROM sbtest1 WHERE k=49851 AND c LIKE '9%'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: sbtest1
   partitions: NULL
         type: range
possible_keys: k_1,idx_k_c
          key: idx_k_c
      key_len: 484
          ref: NULL
         rows: 14
     filtered: 100.00
        Extra: Using where
1 row in set, 1 warning (0.005 sec)

As you can see in the “Extra” column, there’s no ICP used here.

Step 4: Slow query collecting

You have been reading about query optimization, but one question might be where to find those queries needing optimization. MySQL has a slow query log, which can be configured to store the queries whose execution time is longer than the value of the long_query_time variable. For example, we can enable slow query log and configure it to log all the queries by setting the variable to 0 (probably not something you want to do on production, unless the workload is not too heavy):

mysql> SET GLOBAL slow_query_log=ON;
Query OK, 0 rows affected (0.026 sec)

mysql> SET GLOBAL long_query_time=0;
Query OK, 0 rows affected (0.003 sec)

mysql> set global log_slow_extra=ON;
Query OK, 0 rows affected (0.002 sec)

Typically, the slow log is stored in a file located in the slow_query_log_file location. It will contain entries related to each of the queries, for example:

# Time: 2025-08-29T18:48:59.073764Z
# User@Host: sbtest[sbtest] @  [172.17.0.1]  Id:    75
# Query_time: 0.000121  Lock_time: 0.000000 Rows_sent: 100  Rows_examined: 200 Thread_id: 75 Errno: 0 Killed: 0 Bytes_received: 79 Bytes_sent: 12471 Read_first: 0 Read_last: 0 Read_key: 1 Read_next: 100 Read_prev: 0 Read_rnd: 0 Read_rnd_next: 10
1 Sort_merge_passes: 0 Sort_range_count: 0 Sort_rows: 100 Sort_scan_count: 1 Created_tmp_disk_tables: 0 Created_tmp_tables: 1 Start: 2025-08-29T18:48:59.073643Z End: 2025-08-29T18:48:59.073764Z
SET timestamp=1756493339;
SELECT DISTINCT c FROM sbtest4 WHERE id BETWEEN 49854 AND 49953 ORDER BY c;

You can see information about rows examined, execution time, lock time, rows sent, temporary tables, scans, etc. This is all information about the one particular query. Slow logs may take up GBs of disk space on busy servers, and manually reviewing them is not feasible, so having a tool with the ability to analyze them is critical. DPA comes with the “Find SQL” feature, which analyzes the slow query log for you and prints out the most problematic queries found.

Query analysis in DPA.

For each of those queries, you can do a deep-dive analysis, checking the suggestions from several advisors.

Detailed SQL query information in DPA.

You can also check the query execution plans associated with the query.

SQL query execution plans in DPA.

Tune the operating system and database

Database and OS configuration settings should be tuned to match the available hardware and the demands of the workload. There are two main types of workloads from a hardware perspective:

CPU-bound – With these workloads, performance is limited by the lack of CPU resources or thread contention, so the database is waiting on the CPU; reasons for this happening include inefficient queries that do not use indexes, too many connections, long-running transactions, and lock contention on tables
I/O-bound – I/O bound workloads are characterized by the database waiting on I/O operations executed on the storage layer, and you can see it as high I/O wait in the CPU load metrics exposed by Linux; reasons for this include a high number of writes to the database, on-disk temporary tables, large table scans on slow storage devices, and too many concurrent reads

A CPU-intensive workload with high system CPU load would benefit from reducing contention points, e.g., increasing the number of buffer pool instances, disabling AHI, and, in general, attempting to eliminate or at least reduce mutex contentions. High user CPU load should point you toward reviewing queries. On the other hand, an I/O-bound workload requires proper I/O configuration to be applied.

Tuning for CPU-bound workloads

When a workload is CPU-bound, teams can optimize by configuring settings related to:

InnoDB buffer pool – The main memory structure in MySQL, which stores recently accessed (both read and written) data; you can split the buffer pool into multiple segments (by using innodb_buffer_pool_instances) to reduce contention (buffer is protected by a mutex)
AHI (Adaptive Hash Index) – The AHI is an in-memory hash index structure built on the most frequently accessed index pages, which speeds up index lookups for workloads where queries are repetitive, use point lookups, and lack concurrency; if the workload doesn’t match the ideal scenario, it often becomes a point of contention because a mutex also protects it
Table open cache – This structure stores pointers to tablespaces on disk (data storage files); it can be split into several segments, reducing contention when concurrency is high
InnoDB thread concurrency – This setting defines how many threads, concurrently, can work inside InnoDB; defaults (0, unlimited) are good for the majority of workloads, but for high-concurrency workloads on high-end servers with tens of virtual CPUs, you may try fine-tuning them

Tuning with prepared statements

It is useful to keep in mind that generating a query execution plan uses CPU. Consider using prepared statements—queries with a precomputed query execution plan. Here’s an example. You want to query the sbtest1 table using the PK (“id” column). Instead of running the query over and over again, you can prepare it:

PREPARE stmt FROM 'SELECT id, k, c, pad FROM sbtest1 WHERE id = ?';

At this point, the query execution plan is calculated. Then we can execute it with different “id” values:

SET @id_val = 100;
EXECUTE stmt USING @id_val\G
SET @id_val = 5000;
EXECUTE stmt USING @id_val\G

Finally, deallocate the prepared statement:

mysql> DEALLOCATE PREPARE stmt;

Tuning for I/O-bound workloads

For I/O-bound workloads, tuning efforts should focus on settings such as:

innodb_flush_log_at_trx_commit – Reduces I/O pressure by trading less I/O operations per commit for reduced durability
innodb_io_capacity and innodb_io_capacity_max – Governs how many I/O operations InnoDB can execute
innodb_log_file_size – Defines the size of the redo logs, which govern how big a write pressure InnoDB can handle (redo logs store all changes caused by incoming transactions)
Writer and reader threads – Enable significant I/O parallelism on modern database servers.

On the Linux OS side, administrators can tune disk schedulers to optimize performance. The following command can display the current scheduler used by the system:

root@ubuntu-4gb-nbg1-1:~# cat /sys/block/sda/queue/scheduler
[none] mq-deadline

These days, you probably want to stick to “none” or “mq-deadline” for fast hardware. In the example above, “none” is the currently used scheduler. You can change this by editing the file and saving it as the root user.

Another way to release I/O pressure is to tune memory configurations. When the number of queries per second increases, the number of memory structures created per connection, per query, or per join may increase. Tuning memory configurations, such as join_buffer_size, read_buffer_size, read_rnd_buffer_size, and sort_buffer_size, can help optimize memory and thereby I/O.

Tuning those settings is not easy. You’ll need to follow an iterative approach to examine how each configuration change affects the status quo and act accordingly.

Measure performance post-change

Once you make a change, it’s important to monitor the database’s performance. Teams should be certain to monitor:

Query latency (99th percentile)
CPU utilization
I/O trends

If you use a tool such as DPA, you can use a feature such as “Baseline” to help you see the change in the current performance compared to what it used to be in the past; this makes it much easier to see what has changed and how. Teams should carefully monitor performance, as it is relatively common to find that fixing one bottleneck reveals another.

Utilizing “Baseline” in DPA to identify changes in performance over time.

Maybe your UPDATEs used inefficient WHERE clauses, and by fixing this through indexing, you transitioned from 10k UPDATEs running over 10 minutes to 10k UPDATEs running over 30 seconds. This, in turn, puts pressure on the I/O subsystem, which then becomes the hot spot.

Fixing thread contention may cause your CPU usage to spike—all those queries waiting on the mutex don’t wait on it anymore and can happily use whatever CPU is left, slowing down other queries in the process.

Conclusion

Performance tuning is a continuous process, not a one-time exercise. Workloads change over time, and so do performance requirements. Fortunately, the SQL performance tuning best practices we’ve covered in this article can help teams identify bottlenecks more easily, fix performance problems, and maintain stable database operations.

Having a tool that provides full visibility over database performance, such as SolarWinds Database Performance Analyzer, simplifies the process. With the right tools, organizations have easy access to actionable performance metrics and can stay on top of minor issue indicators before they create a production outage.

Stop guessing. Start optimizing.

Try DPA for Free