MySQL Performance Tuning – Essential Optimization Guide

MySQL performance tuning is essential for maintaining an efficient and responsive database. This guide explores key optimization techniques that can enhance MySQL performance, improve stability, and reduce bottlenecks.

Introduction

MySQL Performance Tuning is essential for maximizing the efficiency of your database, ensuring it operates at peak performance. While many standard optimizations focus on query tuning and indexing, a deeper dive into system-level adjustments can provide even greater benefits. Utilizing a dedicated system mount point for the MySQL data directory can enhance I/O performance, reduce system contention, and improve overall database responsiveness.

Additionally, leveraging tools like MySQLTuner allows you to automatically fine-tune configurations based on real-time performance metrics, ensuring that every resource is optimized. For high-performance systems, NUMA interleave and the jemalloc memory allocator are critical in reducing latency and improving memory management, especially on systems with complex hardware architectures. In this guide, we’ll explore these essential optimizations and how they can significantly boost your MySQL database’s speed, stability, and scalability.

Prerequisites

The performance recommendations mentioned here are applicable to any MySQL instance running on any Linux operating system. For this guide, I was using MySQL on the latest Debian Linux. If you would like to learn how to install Debian on your computer, please check our guides below:

I also used the Percona version of MySQL, and if you would like to know how to set it up, please check our guide on that topic:

MySQL Performance Tuning – Dedicated Mount Point

If you’re using MySQL to support a home lab or a small website that relies on MySQL as a background service, then performance tuning might be unnecessary. While it doesn’t hurt to optimize, you likely won’t see significant benefits for such a setup.

On the other hand, if you are running an application or a heavy-traffic website that heavily relies on MySQL, then MySQL performance tuning is mandatory. One of the first steps you can take, even before installing MySQL, is to ensure you have a dedicated disk array for your MySQL dataset. The benefits of such a setup have a significant impact on MySQL performance. Let’s observe some:

• Improved Performance
  • Reduced I/O Contention: Since MySQL has its own disk array, it doesn’t compete with the OS, logs, or other applications for read/write operations.
  • Better Sequential and Random Read/Write Speeds: Disk arrays, especially RAID setups, improve both sequential reads and writes (essential for large queries) and random access (critical for InnoDB transactions).
  • Optimized Disk Scheduling: With MySQL on a separate array, you can fine-tune the filesystem mount options and scheduler specifically for database workloads (e.g., mq-deadline scheduler for SSDs).
• Higher Reliability and Data Integrity
  • RAID Redundancy: If the array is configured with RAID 1, 5, 6, or 10, it provides fault tolerance, meaning disk failures won’t immediately lead to data loss.
  • Less Risk of Corruption: Since the MySQL dataset is isolated, the risk of data corruption due to OS-level disk failures or logs filling up the root partition is minimized.
  • Better Crash Recovery: MySQL can recover more quickly since its data is stored on a dedicated volume that isn’t affected by non-database activities.
• Easier Maintenance & Scalability
  • Easy Expansion: You can increase storage space without disturbing the OS, either by adding more drives to the array or switching to a larger array.
  • More Flexibility in Performance Tuning: You can fine-tune MySQL-specific storage options, such as innodb_flush_method=O_DIRECT, to optimize disk usage.
• Better Write Handling (Especially for InnoDB)
  • MySQL Writes Efficiently to a Dedicated Disk: InnoDB utilizes a double-write buffer and redo logs, which perform more efficiently on a dedicated disk with optimized flushing.
  • Parallelism in Writes: If the disk array supports multiple disks and RAID striping (e.g., RAID 10), MySQL can write data in parallel, reducing bottlenecks.

I know for a fact that some of the world’s highest-traffic websites are using MySQL servers with a dedicated RAID-10 disk array for their MySQL datasets. For ultimate performance, they utilize high-capacity, ultra-high-performance SSDs or NVMes. Typically, such arrays consist of 4 to 8 disks (depending on the capacity needs) in the RAID-10 array. If you would like to learn how to configure a RAID-10 array on Linux, please check out our article on that topic:

Required Directories

Once installed on Debian, MySQL creates default directories in /var/lib, specifically mysql, mysql-files, and mysql-keyring. The one holding the dataset, /var/lib/mysql, is the one we’re interested in. Once you have your high-performance disk (or array) ready, you can create a directory for the MySQL dataset there. In my case, I have /home directory mounted on a four-disk RAID-10 array, so I did it there:

mkdir -p /home/mysql/dataset

I specifically created the /home/mysql/dataset directory because I want binlogs and relaylogs also on my RAID-10 array mount. In a standalone MySQL instance, unless you have a specific use case, there is no need for binlogs or relay logs. But they are required for replicated MySQL environments, so in case I want to replicate my MySQL one day, I created those folders too:

mkdir /home/mysql/{bin-logs,relay-logs}

At this stage, the MySQL service can be stopped, and the data synched:

systemctl stop mysql.service
rsync -avP /var/lib/mysql/. /home/mysql/dataset/.

Once the sync is complete, you must change the ownership of the new MySQL directories, so the process can access the data:

chown -R mysql:mysql /home/mysql

Configuration Adjustment

The “main” MySQL configuration file is located at /etc/mysql/mysql.cnf. By default, once Percona MySQL is installed, the file doesn’t contain any configuration options for MySQL. It only holds two “includes”:

!includedir /etc/mysql/conf.d/
!includedir /etc/mysql/mysql.conf.d/

The includes (their locations) define where you can find the actual MySQL config files. I usually don’t require /etc/mysql/mysql.conf.d directory. You cannot just delete that extra line. Once MySQL receives its first update, it will also reset the main configuration file and restore the include.

To make the change permanent, an alternative configuration must be implemented. First, copy the main configuration file to a new file:

cd /etc/mysql
cp mysql.cnf local-mysql.cnf

Now open the file and remove the line containing !includedir /etc/mysql/mysql.conf.d/:

#
# The Percona Server 8.0 configuration file.
#
# For explanations see
# http://dev.mysql.com/doc/mysql/en/server-system-variables.html
#
#
# * IMPORTANT: Additional settings that can override those from this file!
#   The files must end with '.cnf', otherwise they'll be ignored.
#

!includedir /etc/mysql/conf.d/

To set this new alternative configuration as the default, the update-alternatives command must be used. First, add it as an option for my.cnf file:

update-alternatives --install /etc/mysql/my.cnf my.cnf /etc/mysql/local-mysql.cnf 50

Now you set it as a new default configuration by executing the following:

update-alternatives --config my.cnf

A menu will be displayed, letting you select the new configuration:

There are 4 choices for the alternative my.cnf (providing /etc/mysql/my.cnf).

  Selection    Path                        Priority   Status
------------------------------------------------------------
  0            /etc/mysql/mariadb.cnf       500       auto mode
  1            /etc/mysql/local-mysql.cnf   50        manual mode
  2            /etc/mysql/mariadb.cnf       500       manual mode
  3            /etc/mysql/my.cnf.fallback   100       manual mode
* 4            /etc/mysql/mysql.cnf         300       manual mode

Press <enter> to keep the current choice[*], or type selection number: 1
update-alternatives: using /etc/mysql/local-mysql.cnf to provide /etc/mysql/my.cnf (my.cnf) in manual mode

In my example, I selected option number 1, and my new configuration was set as default. Having it set up this way, each time MySQL receives an upgrade, the alternative configuration we just implemented will not be altered.

Once this is in place, you must add a couple of options to the configuration so MySQL can be started. Start by opening the /etc/mysql/conf.d/mysql.cnf file, and add the following options:

[mysqld]

# Disable MySQL-X protocol
mysqlx = 0

# Listen Address
bind-address = 192.168.100.1

# Runtime Components
pid-file        = /var/run/mysqld/mysqld.pid
socket          = /var/run/mysqld/mysqld.sock
datadir         = /home/mysql/dataset

As you can see, the option for datadir is set to reflect the directory on a dedicated disk/array (created above).

Mount Options

For testing and development environments, storing MySQL data on a dedicated disk or array with the default mount options is sufficient. However, for production environments, several additional non-standard mount options should be considered for optimal performance. In my production use case, I store MySQL data on a RAID-10 array with an ext4 file system. The mount itself is defined with the following options:

/dev/md2    /home/mysql/    ext4    defaults,relatime,nodiscard,nodelalloc,nobarrier,data=ordered,errors=remount-ro  0       0

Let’s examine those options a bit:

• relatime
  • One may think noatime would be a better option, but relatime has almost no overhead compared to noatime and still maintains sane atime values.
• nodiscard
  • Relevant if you are using SSDs or NVMes. You can use the discard option instead, but only if your MySQL instance is not I/O-intensive. If it is, I recommend using nodiscard and setting a periodic TRIM job in the cron. For HDDs, this option (both discard and nodiscard) is non-applicable.
• nodelalloc
  • Delayed allocation (default option for ext4) means that data is kept in memory longer before being written to disk. If the system crashes before a write happens, data loss is possible. Deactivating it (nodelalloc) ensures that writes are committed to disk sooner, reducing the risk of data loss.
• nobarrier
  • Write barriers introduce additional disk flush operations to ensure data integrity, but they can slow down MySQL performance, especially for workloads with frequent writes, such as InnoDB-heavy databases.
• data=ordered
  • Complementary to nodelalloc and nobarrier. Also a default for ext4, it adds a layer of protection to ensure that MySQL’s disk writes remain consistent.

The options above are recommended for production MySQL instances, which typically run on dedicated servers (MySQL only). While they will significantly improve MySQL performance, there are some caveats. Some options, such as nobarrier and data=ordered, can lead to data loss or corruption in the event of power failures.

To avoid this, ensure you have a proper production environment. Ensure servers have redundant power supplies and are connected to a UPS. If UPS is not an option, consider battery-backed RAID controllers or enterprise-grade SSDs with power-loss protection.

MySQL Performance Tuning – MySQLTuner

MySQLTuner is a Perl script that provides real-time performance analysis and tuning recommendations for MySQL databases. It helps database administrators optimize MySQL configurations based on workload patterns and resource utilization. You can install it by downloading required files:

cd /tmp
wget http://mysqltuner.pl/ -O mysqltuner.pl
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/basic_passwords.txt -O basic_passwords.txt
wget https://raw.githubusercontent.com/major/MySQLTuner-perl/master/vulnerabilities.csv -O vulnerabilities.csv

Before executing the script, please consider the following:

• MySQL Uptime
  • Run MySQLTuner after at least 24-48 hours of uptime for accurate recommendations.
• Apply Changes Gradually
  • Apply changes one at a time, and monitor MySQL performance.
• MySQLTuner Recommendations
  • Don’t blindly follow all recommendations. Some depend on workload patterns.
• Other Tools
  • Combine MySQLTuner with other tools, such as Percona Toolkit and SHOW GLOBAL STATUS, for a deeper analysis.

I’ve run MySQLTuner 24 hours after I installed my instance of MySQL, and the following were recommendations given:

General recommendations:
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
    skip-name-resolve=ON
    innodb_redo_log_capacity should be (=32M) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.

Based on the recommendations, I have the option to set skip-name-resolve=ON and innodb_redo_log_capacity=32M in the MySQL configuration. However, I have decided against doing so. The reasons outlined above are why I will not proceed with these changes. The goal is to adjust the configuration options not merely according to the default MySQL settings at installation, but to tailor them based on the specific hardware being used. Let’s explore this further in the next section of the guide.

MySQL Performance Tuning – Per-Hardware Optimization

I have MySQL installed on a machine with an 8-core CPU and 32GB of RAM. It has a “perfect/optimal” CPU-to-RAM ratio of 1:4 (one core per 4GB of RAM). The MySQL dataset is stored on 4xSSDs assembled in a RAID-10 array. My dataset is larger than the amount of RAM available on the machine, so not all the data will fit in memory. This is important, and it will serve as the starting point for determining the optimal setting for my MySQL server.

When installed, the Percona instance of MYSQL sets the InnoDB storage engine as the default. This is the prerequisite for everything else in this guide. You can check the default storage engine by executing the following in the MySQL prompt:

show engines;

With Percona instance, the result will be similar to the one below:

+--------------------+---------+
| Engine             | Support |
+--------------------+---------+
| ndbcluster         | NO      |
| PERFORMANCE_SCHEMA | YES     |
| InnoDB             | DEFAULT |
| MEMORY             | YES     |
| MyISAM             | YES     |
| FEDERATED          | NO      |
| ndbinfo            | NO      |
| MRG_MYISAM         | YES     |
| BLACKHOLE          | YES     |
| CSV                | YES     |
| ARCHIVE            | YES     |
+--------------------+---------+

In case you use some other version of MySQL, like Oracle or MariaDB, and the InnoDB storage engine is not set as default, you can set it by adding the following option in the MySQL configuration at /etc/mysql/conf.d/mysql.conf:

# Default Storage Engine
default-storage-engine = InnoDB

Once applied, this option is not recursive. That means it will not change the storage engine for existing databases, and you will have to do that yourself if required. With the default storage engine set, let’s examine the related configuration options and how they will benefit MySQL performance.

InnoDB Settings – Buffer Pool Size

The best thing you can do for your MySQL server is always to assume the worst-case scenario. Consider it being under a constant load and doing lots of load-intensive operations. To withstand that, plan a decent dedicated machine, with lots of RAM and a multi-core CPU.

As I mentioned above, my machine has 32GB of RAM and an 8-core CPU. We will focus on the total amount of RAM here, as well as an option in MySQL called innodb_buffer_pool_size.

• What is InnoDB Buffer Pool?
  • Essentially, it is a reserved area of physical RAM that MySQL uses to cache data and index pages for faster processing. It’s a crucial performance feature that helps minimize the need for disk access, improving MySQL’s overall speed and efficiency.
• What are the benefits of InnoDB Buffer Pool?
  • Faster Data Access: By caching data and index pages in RAM, the buffer pool helps reduce the number of disk reads, resulting in significantly faster data access.
  • Reduced Disk I/O: Every time a request is made to the database, InnoDB first tries to retrieve the data from the buffer pool (in memory), which is significantly faster than accessing the data from disk. This improves both read and write operations.
  • Better Query Performance: The buffer pool plays a significant role in improving query performance. Frequently accessed data, such as hot rows or tables, remains in memory, allowing queries that require this data to execute much more quickly.
  • Dynamic Memory Usage: The buffer pool can dynamically adjust as MySQL processes more data; however, the size you configure will limit the amount of data that can be cached. In large systems, a larger buffer pool can lead to better performance by allowing MySQL to cache more data.

So, how do we pick the optimal value for the InnoDB buffer pool? The rule of thumb is allocating 80-90% of physical RAM to the MySQL process on a dedicated server. With 32GB of RAM on my server, I allocated 24GB to the buffer pool, leaving enough RAM for the operating system and background processes. You can set it up by adding the following option to the MySQL configuration file at /etc/mysql/conf.d/mysql.conf:

innodb_buffer_pool_size = 24G

You can create a new configuration file dedicated to performance options to avoid congestion in the main MySQL configuration file. To do so, open a new file at /etc/mysql/conf.d/performance-mysql.conf and set the buffer pool size option there.

InnoDB Settings – Buffer Pool Instances

It is recommended that you set the value for innodb_buffer_pool_instances equal to the number of CPU cores on your server, but setting it to more than that offers no advantage and might even be counterproductive.

What exactly is a buffer pool instance? To clarify, we need to discuss the InnoDB engine’s option known as innodb_buffer_pool_chunk_size. The default value for this option is 128MB, which works well for most use cases, except when the MySQL dataset exceeds a few hundred gigabytes. The InnoDB buffer pool chunk size refers to the amount of physical memory (RAM) allocated to a chunk when requested by the MySQL process.

In my setup, I have allocated 24GB to the buffer pool and configured innodb_buffer_pool_instances to 8, since I have an 8-core CPU. Let’s take a closer look at the calculations:

# THE AMOUNT OF RAM EACH INSTANCE CAN CONSUME
innodb_buffer_pool_size / innodb_buffer_pool_instances = RAM consumption per instance
24GB / 8 = 3GB

# THE NUMBER OF CHUNKS PER INSTANCE
RAM consumption per instance / innodb_buffer_pool_chunk_size = number of chunks per instance
3GB / 128MB = 24

# TOTAL NUMBER OF CHUNKS PER INNODB BUFFER POOL
number of chunks per instance * innodb_buffer_pool_instances = total chunks
24 * 8 = 384

The idea behind innodb_buffer_pool_instances is to reduce mutex contention – that is, many threads trying to lock access to the same buffer pool simultaneously. On multi-core systems, more threads (often mapped to cores) can run in parallel. So, splitting the buffer pool into multiple instances lets them access memory more concurrently. You can set it up by adding the following option to the MySQL configuration file at /etc/mysql/conf.d/mysql.conf:

innodb_buffer_pool_instances = 8

Since version 5.7, MySQL has implemented an auto-tune mechanism that sets the optimal number of InnoDB instances. If you don’t explicitly set innodb_buffer_pool_instances, it will automatically choose a sane default based on innodb_buffer_pool_size. Just be aware, MySQL’s auto-tuning logic won’t go beyond 16 instances unless the buffer pool is enormous.

The only use case where this “logic” can be implemented differently is if you have a small dataset and your MySQL load is not high. For instance, let’s say you have a 3GB dataset. My recommendation is to set both innodb_buffer_pool_size and innodb_buffer_pool_chunk_size to 4GB and run only one buffer pool instance. This way, the buffer pool is one large contiguous space, but let’s observe pros and cons:

• PROS
  • Maximum Cache Efficiency: all hot pages are in one place.
  • No risk of fragmented cache: same hot table pages split across instances.
  • Less overhead: fewer internal structures and metadata.
• CONS
  • High Concurrency: If you have high concurrency, there could be mutex contention (multiple threads locking access to the single pool).
  • Multiple CPUs: On servers with many CPU cores and parallel queries, this can become a bottleneck — but only if concurrency is high.

MySQL Performance Tuning – Summary

With all the options above in place, the MySQLTuner suggestions can also be implemented now. Let’s check them out again:

General recommendations:
    Configure your accounts with ip or subnets only, then update your configuration with skip-name-resolve=ON
    Be careful, increasing innodb_redo_log_capacity means higher crash recovery mean time
Variables to adjust:
    skip-name-resolve=ON
    innodb_redo_log_capacity should be (=6G) if possible, so InnoDB Redo log Capacity equals 25% of buffer pool size.

To implement the recommendations, you must edit the MySQL configuration file at /etc/mysql/conf.d/mysql.conf and paste in the following options:

skip-name-resolve=ON
innodb_redo_log_capacity = 6G

Make sure that innodb_redo_log_capacity equals 25% of innodb_buffer_pool_size. In my example, the buffer pool size is 24GB, so 25% of that is 6GB. With that in place, and if you rerun MySQLTuner, no more recommendations should be shown.

I will conclude this guide, as it has become quite lengthy. In the next installment of my MySQL performance series, I will explore more advanced topics. I’ll cover NUMA capabilities, memory allocators, and transparent huge pages, and explain how tuning these features can enhance MySQL performance. Thank you for reading, and stay tuned for more!