Database Parameter Tuning

Purpose

For different service scenarios, tuning database parameters can improve server performance.

Procedure

Different databases have their own parameter configuration files. This section uses MySQL as an example. The default path of the MySQL configuration file is /etc/my.cnf.

Table 1 lists the MySQL database parameters for tuning, descriptions, and tuning suggestions.

Table 1 MySQL database parameters for tuning

Parameter Name

Description

Tuning Suggestion

innodb_thread_concurrency

Specifies the number of concurrent threads used by the InnoDB storage engine to execute queries.

In high-concurrency scenarios, adjust this parameter to reduce resource contention at the InnoDB layer.

The default value 0 is recommended, indicating that the number of concurrent threads is not limited by default.

innodb_read_io_threads

Specifies the number of I/O threads used by the InnoDB storage engine to perform read operations.

Set this parameter based on the number of CPU cores and read/write ratio to improve performance.

The recommended value ranges from 1 to 24.

innodb_write_io_threads

Specifies the number of I/O threads used by the InnoDB storage engine to perform write operations.

Set this parameter based on the number of CPU cores and read/write ratio to improve performance.

The recommended value ranges from 1 to 32.

innodb_buffer_pool_instances

Specifies the number of instances in the InnoDB buffer pool.

Increase the number of instances in the buffer pool to reduce the contention of hotspot memory pages, which can improve the concurrency of memory reads.

The recommended value ranges from 8 to 32.

innodb_open_files

Specifies the maximum number of files that can be opened by the InnoDB storage engine in the innodb_file_per_table mode.

Set this parameter to a larger value, especially when there are a large number of tables.

innodb_buffer_pool_size

Specifies the size of the InnoDB buffer pool. The InnoDB buffer pool is a memory area used to store data and indexes of InnoDB tables.

Set this parameter to a larger value for more data to be cached in the memory, reducing I/O operations.

The recommended value is about 70% of the memory.

innodb_log_buffer_size

Specifies the size of the InnoDB transaction log buffer pool.

If the value is too small, the InnoDB transaction log buffer pool may be frequently flushed to drives, deteriorating performance.
If the value is too large, excessive memory resources are occupied, affecting system stability.

Properly increasing the size of the log buffer pool can improve the transaction submission efficiency and reduce log flushes, especially in large transaction and high-concurrency scenarios. Based on the value of innodb_log_wait, adjust the value of innodb_log_buffer_size. Increase the value of innodb_log_buffer_size for the following scenarios:

If the value of innodb_log_wait is not 0 and keeps increasing, the space of the InnoDB log buffer pool is insufficient. As a result, innodb_log_buffer needs to wait.
If the value of innodb_log_wait is high, the InnoDB storage engine needs more log buffer pool space.

The default value is 64 MB.

innodb_io_capacity

Specifies the number of available I/O operations per second (IOPS) of the InnoDB background thread.

It is recommended that this parameter be set to 75% of the total drive IOPS capacity. (The drive IOPS refers to the IOPS when the data block size equals the value of innodb_page_size.)

innodb_log_file_size

Specifies the size of the InnoDB log file.

If there is a large number of write operations, it is recommended that the log file size be increased. However, if the log file size is too large, the data restoration time can be affected.
If it is for testing the performance limit in a non-production environment, increase the log file size as much as possible.
If it is in commercial scenarios where the data restoration time needs to be considered, set the log file size after making a compromise.

innodb_flush_method

Specifies the mode in which the InnoDB storage engine flushes data to drives.

Different flushing mode has impact on the performance and data security of the InnoDB storage engine.

It is recommended that this parameter be set to the O_DIRECT mode. That is, data is directly flushed to drives through the OS buffer pool.

innodb_spin_wait_delay

Specifies the polling interval for spin_lock.

Hotspot functions can be used to optimize this parameter. It is recommended that this parameter be optimized especially for the system (device) with a single-instance. For the small specification single-instance scenario, use the default value.

The value 180 is recommended for MySQL 8.0.20 and the value 20 is recommended for MySQL 8.0.25.

innodb_sync_spin_loops

Specifies the number of polling loops for spin_lock.

The recommended value is 25.

innodb_spin_wait_pause_multiplier

Specifies the polling interval random number for spin_lock.

The default value is 50. The recommended value ranges from 25 to 50.

innodb_lru_scan_depth

Specifies the number of available pages of the LRU list in the InnoDB buffer pool.

If it is for testing the performance limit in a non-production environment, increase the value to reduce the number of checkpoints.

The default value is 1024.

innodb_page_cleaners

Specifies the number of threads for refreshing dirty data.

It is recommended that this parameter be set to the same value as innodb_buffer_pool_instances.

innodb_purge_threads

Specifies the number of threads for recycling undo.

innodb_flush_log_at_trx_commit

Specifies the log flush behavior when a transaction is committed.

If it is for testing the performance limit in a non-production environment, set the value to 0. That is, logs are cached in memory instead of being flushed to drives immediately when a transaction is committed.

innodb_doublewrite

Enables or disables double write.

If it is for testing the performance limit in a non-production environment, set the value to 0 to disable double write.

ssl

Enables or disables secure connections.

Secure connections have a great impact on performance. If it is for testing the performance limit in a non-production environment, set the value to 0. In commercial scenarios, make adjustments based on customer requirements.

table_open_cache_instances

Specifies the number of partitions for caching table handles in MySQL.

The recommended value ranges from 16 to 32.

table_open_cache

Specifies the number of tables opened by Mysqld.

The recommended value is 30000.

skip_log_bin

Enables or disables binlog.

If it is for testing the performance limit in a non-production environment, add this parameter to the configuration file to disable binlog.

Add the following content to the configuration file:

skip_log_bin
#log-bin=mysql-bin

innodb_checksum_algorithm

Enables or disables the data integrity check.

If it is for testing the performance limit in a non-production environment, set the value to none to disable the data integrity check.

binlog_checksum

Enables or disables the binlog integrity check.

If it is for testing the performance limit in a non-production environment, set the value to none to disable the binlog integrity check.

innodb_log_checksums

Enables or disables the log integrity check.

If it is for testing the performance limit in a non-production environment, set the value to OFF to disable the log integrity check.

foreign_key_checks

Enables or disables the foreign key check.

If it is for testing the performance limit in a non-production environment, set the value to OFF to disable the foreign key check.

performance_schema

Enables or disables the performance schema.

If it is for testing the performance limit in a non-production environment, set the value to OFF to disable the performance schema.