Modifying Database Parameters

Purpose

Modifying MySQL database parameters is one of the important methods to improve database performance. The MySQL database is a dynamic management system. In actual applications, MySQL performance may deteriorate due to improper parameter settings. Therefore, modification of MySQL parameters is needed to optimize the performance.

Method

Start the database by using the following parameters in the configuration file. The default configuration file path is /etc/my.cnf. Change the MySQL software installation path and data storage path to match your tuning.

[mysqld_safe]
log-error=/data/mysql/log/mysql.log
pid-file=/data/mysql/run/mysqld.pid

[client]
socket=/data/mysql/run/mysql.sock
default-character-set=utf8

[mysqld]
basedir=/usr/local/mysql
tmpdir=/data/mysql/tmp
datadir=/data/mysql/data
socket=/data/mysql/run/mysql.sock
port=3306
user=root
default_authentication_plugin=mysql_native_password
ssl=0 #Disable SSL.
max_connections=2000  #Set the maximum number of connections.
back_log=2048  #Set the number of cached session requests.
performance_schema=OFF #Disable the performance schema.
max_prepared_stmt_count=128000

#file
innodb_file_per_table=on #Set one file for each table.
innodb_log_file_size=1500M #Set the log file size.
innodb_log_files_in_group=32 #Set the number of log file groups.
innodb_open_files=4000 #Set the maximum number of tables that can be opened.

#buffers
innodb_buffer_pool_size=230G #Set the buffer pool size, which is generally 60% of the server memory.
innodb_buffer_pool_instances=16 #Set the number of buffer pool instances to improve the concurrency capability.
innodb_log_buffer_size=64M #Set the log buffer size.

#tune
sync_binlog=1 #Set the number of sync_binlog transactions to be submitted for drive flushing each time.
innodb_flush_log_at_trx_commit=1 #Each time when a transaction is submitted, MySQL writes the data in the log buffer to the log file and flushes the data to drives.
innodb_use_native_aio=1 #Enable asynchronous I/O.
innodb_spin_wait_delay=180 #Set the spin_wait_delay parameter to prevent system spin.
innodb_sync_spin_loops=25 #Set the spin_loops loop times to prevent system spin.
innodb_spin_wait_pause_multiplier=25 # Set a multiplier value used to determine the number of PAUSE instructions in spin-wait loops.
innodb_flush_method=O_DIRECT # Set the open and write modes of InnoDB data files and redo logs.
innodb_io_capacity=20000 # Set the maximum IOPS of InnoDB background threads.
innodb_io_capacity_max=40000 # Set the maximum IOPS of InnoDB background threads under pressure.
innodb_lru_scan_depth=9000 #Set the number of dirty pages flushed by the page cleaner thread each time.
innodb_page_cleaners=16 #Set the number of threads for writing dirty data to drives.
table_open_cache_instances=32 # Set the maximum number of table cache instances.
table_open_cache=30000 # Set the maximum number of open tables cached in one table cache instance.

#perf special
innodb_flush_neighbors=0 #Check all pages in the extent where the page is located. If the page is dirty, flush all pages. This parameter is disabled for SSDs.
innodb_write_io_threads=16 #Set the number of write threads.
innodb_read_io_threads=16 #Set the number of read threads.
innodb_purge_threads=32  #Set the number of undo page threads to be purged.
innodb_adaptive_hash_index=0

sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

**Table 1** Database parameters
Parameter	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, that is, 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 the read/write ratio.
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 the read/write ratio.
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 memory hotspot pages, thereby improving the concurrency of memory reads. Recommended value: 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.	A larger value is recommended, 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 for storing data and indexes of InnoDB tables.	Increase the value of this parameter to cache more data in the memory, reducing I/O operations. Recommended value: 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, resulting in performance deterioration. 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 the number of log flushes, especially in large transaction and high-concurrency scenarios. Adjust the value of this parameter by viewing the value of innodb_log_wait. Increase the value of this parameter in either of the following scenarios: If the value of innodb_log_wait is not 0 and keeps increasing, the space of the innodb_log_buffer of the InnoDB storage engine is insufficient. As a result, innodb_log_buffer needs to wait. If the value of innodb_log_wait is high, the InnoDB storage engine requires 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 threads.	Recommended value: 75% of the total drive IOPS capability. (The drive IOPS refers to the IOPS when the data block size is innodb_page_size.)
innodb_log_files_in_group	Specifies the number of log files in the InnoDB log file group.	-
innodb_log_file_size	Specifies the size of an InnoDB log file.	A larger value is recommended for write-intensive scenarios. However, large size of the redo log file prolongs data restoration. When testing the ultimate performance in the non-production environment, increase the log file size as large as possible. In commercial scenarios, consider the data restoration time when setting this parameter.
innodb_flush_method	Specifies the methods in which the InnoDB storage engine flushes data to drives.	Different flush method can affect the performance and data security of the InnoDB storage engine. Recommended value: O_DIRECT. Data is directly written to drives through the OS buffer pool.
innodb_spin_wait_delay	Specifies the spin_lock polling interval.	Hotspot functions can be used to optimize this parameter, especially in the single-instance scenario of the entire system. For the single-instance scenario of small specifications, use the default value. Recommended value: 180
innodb_sync_spin_loops	Specifies the spin_lock polling times.	Hotspot functions can be used to optimize this parameter, especially in the single-instance scenario of the entire system. For the single-instance scenario of small specifications, use the default value. Recommended value: 25
innodb_spin_wait_pause_multiplier	Specifies the random number used to control the spin_lock polling interval.	Hotspot functions can be used to optimize this parameter, especially in the single-instance scenario of the entire system. For the single-instance scenario of small specifications, use the default value. Default value: 50; recommended value: 25 to 50
innodb_lru_scan_depth	Specifies the number of available pages in the LRU list in the InnoDB buffer pool.	Default value: 1024. When testing the ultimate performance in the non-production environment, you can increase the value to reduce the number of checkpoints.
innodb_page_cleaners	Specifies the number of threads for refreshing dirty data.	Set it to the same value as innodb_buffer_pool_instances.
innodb_purge_threads	Specifies the number of threads for purging undo.	-
innodb_flush_log_at_trx_commit	Specifies the log flushes when a transaction is committed.	Set the value to 0 in a non-production environment to test the extreme performance. It indicates that logs are not written to drives immediately when a transaction is committed. Instead, logs are cached in the memory.
innodb_doublewrite	Enables or disables double write function.	When testing the ultimate performance in a non-production environment, set this parameter to 0 to disable double write.
ssl	Enables or disables SSL.	SSL has great impact on performance. When testing the ultimate performance in a non-production environment, set this parameter to 0 to disable SSL. In commercial scenarios, set this parameter based on customer requirements.
table_open_cache_instances	Specifies the number of partitions for caching table handles in MySQL.	Recommended value: 16 to 32
table_open_cache	Specifies the number of tables opened by Mysqld.	Recommended value: 30000
skip_log_bin	Enables or disables binlog.	When testing the ultimate performance in a non-production environment, add this parameter to the configuration file and disable binlog. Add the following information to the configuration file: skip_log_bin #log-bin=mysql-bin
performance_schema	Enables or disables the performance schema.	When testing the ultimate performance in a non-production environment, set this parameter to OFF to disable the performance schema.

Parent topic: Database Tuning