Tune MySQL configuration for performance

Choose MySQL parameters for tuning

You can set MySQL server configuration parameters in an option file or on the mysqld command line.

For persistent tuning, use an option file so the configuration can be reviewed, version controlled, and applied consistently when MySQL restarts. For more information, see the MySQL Specifying Program Options documentation.

The examples in this section are intended for the [mysqld] group in a MySQL option file.

Connections and prepared statements

The following example shows intentionally high limits that can be useful in benchmark environments where you want to prevent connection or prepared statement limits from affecting the test result:

    

        
        
# Intentionally high benchmark limits.
max_connections=100000
max_prepared_stmt_count=4194304

    

The max_connections parameter doesn’t directly improve performance. It raises the number of client connections MySQL can accept. Higher values consume more memory and can also depend on operating system limits such as file descriptors, so size this value for your expected peak connection count.

The max_prepared_stmt_count parameter limits the total number of prepared statements across the server. The default protects the server from excessive memory use. Increase this value only when a benchmark or application needs more prepared statements, and avoid carrying an oversized value into production without measuring memory impact.

Dedicated server configuration

If the system runs only MySQL, enable the dedicated server setting:

    

        
        
innodb_dedicated_server=ON

    

The innodb_dedicated_server parameter automatically sizes selected InnoDB settings for a server dedicated to MySQL. Check the MySQL dedicated server documentation for the MySQL version you deploy because the behavior can change between releases.

In MySQL 8.4+, innodb_dedicated_server automatically configures innodb_buffer_pool_size and innodb_redo_log_capacity. On systems with more than 4 GiB of RAM, innodb_buffer_pool_size is set to 75% of detected system memory.

The auto-sized values are a useful starting point, but you can still override individual settings when workload requirements or performance data justify the change.

Buffer pool size

The innodb_buffer_pool_size parameter is one of the most important MySQL performance settings. It controls how much memory InnoDB can use to cache indexes and table data.

If MySQL runs on a dedicated server, innodb_dedicated_server=ON can be enough to size the buffer pool. If you don’t use innodb_dedicated_server, or you want to test a specific value, set innodb_buffer_pool_size based on your workload and available memory:

    

        
        
# Example value for a dedicated 128 GiB test system.
innodb_buffer_pool_size=96G

    

The MySQL InnoDB buffer pool documentation suggests setting the buffer pool size to as much as 80% of system memory on a dedicated database server. Leave memory for the operating system, connections, background MySQL memory use, and any other processes on the system.

Confirm the current buffer pool size:

    

        
        
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';

    

Buffer pool instances

For large buffer pools, multiple buffer pool instances can improve concurrency by reducing contention as different threads read and write cached pages.

    

        
        
# Example value for a large buffer pool.
innodb_buffer_pool_instances=8

    

The innodb_buffer_pool_instances setting takes effect only when innodb_buffer_pool_size is at least 1 GiB. The total buffer pool is divided across the instances, so keep each instance at least 1 GiB. Test values such as 4, 8, or 16 with your workload, and avoid creating so many instances that each one becomes too small.

Redo log capacity

For write-heavy workloads, you can keep innodb_dedicated_server=ON and explicitly set a larger redo log capacity than the value MySQL calculates automatically:

    

        
        
innodb_dedicated_server=ON
# Example override for write-heavy workloads.
innodb_redo_log_capacity=32G

    

The innodb_redo_log_capacity parameter controls the amount of disk space used for redo log files. It was introduced in MySQL 8.0.30. In earlier MySQL versions, innodb_log_file_size and innodb_log_files_in_group control redo log size and behavior.

The auto-sized innodb_redo_log_capacity value is a starting point. Increasing it can improve write-heavy workloads by giving InnoDB more redo log space before checkpoints limit write throughput. An explicit innodb_redo_log_capacity value overrides the value calculated by innodb_dedicated_server for that variable, while still allowing innodb_dedicated_server to size other settings such as innodb_buffer_pool_size.

Larger redo logs use more disk space and can increase crash recovery time, so test values against both performance and operational requirements.

Redo log writer threads

For write-heavy workloads, check whether dedicated redo log writer threads are enabled:

    

        
        
SHOW VARIABLES LIKE 'innodb_log_writer_threads';

    

The innodb_log_writer_threads parameter controls whether InnoDB uses dedicated log writer threads for redo log write and flush work.

The default behavior changed after MySQL 8.4. In MySQL 8.4, innodb_log_writer_threads defaults to ON. In MySQL 9.1, the default changed to ON when the server has at least 32 available logical processors, and OFF otherwise. In current MySQL 9.x releases, the default also accounts for binary logging: it is ON when binary logging is enabled and the server has at least 32 available logical processors, ON when binary logging is disabled and the server has more than 4 available logical processors, and OFF otherwise.

This means the same option file can use different redo log writer behavior depending on the MySQL version, logical processor count, and whether binary logging is enabled. For version-to-version comparisons, record the current value before testing and consider setting it explicitly:

    

        
        
innodb_log_writer_threads=ON

    

Dedicated log writer threads can help high-concurrency systems, especially write-heavy workloads where redo logging is active. For lower-concurrency systems, disabling dedicated log writer threads can perform better, so treat this as a measured tuning option rather than a universal setting.

Huge pages

Enable large page support in the MySQL configuration:

    

        
        
large-pages=ON

    

Turning on large-pages can improve performance when MySQL uses a large InnoDB buffer pool. Larger pages map more memory per translation entry, which can reduce page-table walks and TLB pressure.

Huge pages must also be configured at the Linux kernel level. For huge page setup instructions, see the system, kernel, compiler, and library settings section of this Learning Path.

Divide the buffer pool size by the huge page size to estimate the number of huge pages needed. Use the Hugepagesize value from /proc/meminfo, and allocate enough huge pages for a total huge page space equal to or slightly larger than the buffer pool.

You don’t usually need to change other memory parameters unless you observe a specific issue. One optional area to test is InnoDB buffer pool prefetching . Lowering innodb_read_ahead_threshold from the default can help workloads with predictable sequential access patterns, while innodb_random_read_ahead can help some workloads and hurt others. Treat these settings as workload-specific experiments.

Disk flush behavior

For MySQL versions earlier than 8.4, test innodb_use_fdatasync:

    

        
        
innodb_use_fdatasync=ON

    

The innodb_use_fdatasync parameter allows InnoDB to use fdatasync() instead of fsync() for operating systems and flush methods that support it. This can improve write performance because fdatasync() flushes file data without forcing unrelated metadata updates, such as timestamps to disk. Metadata required to retrieve the data is still flushed. In MySQL 8.4, this setting defaults to ON.

Transaction durability settings

Use the following settings when transaction durability and consistency are required (these are the default in MySQL 8.4+):

    

        
        
sync_binlog=1
innodb_flush_log_at_trx_commit=1

    

The sync_binlog parameter controls how often MySQL synchronizes the binary log to disk.

The innodb_flush_log_at_trx_commit parameter controls how InnoDB writes and flushes redo log records at transaction commit.

When both settings are 1, MySQL prioritizes transaction durability over peak write throughput. Relaxing either setting can reduce how often transactions wait for binary log and redo log data to be flushed to durable storage. This can improve parallel transaction execution and write throughput, especially on write-heavy workloads, but it increases the time window where committed transactions exist only in memory or operating system cache.

I/O capacity configuration

Increasing parallelism can help InnoDB use available storage resources more efficiently. For older MySQL versions on SSD-backed or cloud storage, test values similar to the MySQL 8.4 defaults:

    

        
        
innodb_io_capacity=10000
innodb_io_capacity_max=20000

    

The innodb_io_capacity parameter tells InnoDB how many I/O operations per second it can issue to storage for background work. Before MySQL 8.4, the default was 200, which is often too low for modern SSD and cloud storage. In MySQL 8.4, the default is 10000.

The innodb_io_capacity_max parameter provides a higher limit for bursts of background work. It defaults to 2x innodb_io_capacity. Use that relationship as a starting point, then adjust the values based on storage capability and measured performance.

Spin lock configuration

Use the following setting as a starting point for testing lock polling behavior:

    

        
        
innodb_sync_spin_loops=30

    

The innodb_sync_spin_loops parameter controls how many times a thread checks whether an InnoDB mutex or rw-lock is available before yielding execution to another thread.

This setting applies to internal InnoDB synchronization objects, including the latches that protect storage engine structures such as indexes. It does not control transaction lock behavior, such as shared (S), exclusive (X), intention shared (IS), or intention exclusive (IX) locks. Transaction locks enforce SQL isolation semantics. When a transaction lock request conflicts with an existing lock, the transaction waits until the conflicting lock is released; transaction locks do not spin longer because of this parameter.

Profiling MySQL under heavy load on Arm with Linux perf can show time spent waiting for locks. Increasing innodb_sync_spin_loops can reduce context switching when locks are released quickly, but setting it too high can waste power and delay other useful work. Keep the default value, 30, unless profiling and measured performance show that a different value helps. For more information, see the MySQL Configuring Spin Lock Polling documentation .

What you’ve learned

You’ve now explored various MySQL parameters you can tune for improved performance.

You can use the guidance in this Learning Path to optimize the performance of your MySQL databases on Arm.