Modifying Database Parameters

Purpose

Modify database parameter settings to improve server performance.

Procedure

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

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

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

[mysqld]
basedir=/usr/local/mariadb
tmpdir=/data/mariadb/tmp
datadir=/data/mariadb/data
socket=/data/mariadb/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
transaction_isolation=READ-COMMITTED #Set the database transaction isolation level.

#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=50000 #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=10240M #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_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.

#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.
thread_handling=pool-of-threads #Use thread pool.
thread_pool_size=100 #Control the size of the thread group.
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	Operating system threads used by InnoDB to process the user transaction requests	The default value 0 is recommended, that is, the number of concurrent threads is not limited by default.
innodb_read_io_threads	Number of threads that process the read requests in the request queue	Set this parameter based on the number of CPU cores and the read/write ratio.
innodb_write_io_threads	Number of threads that process the write requests in the request queue	Set this parameter based on the number of CPU cores and the read/write ratio.
query_cache_size	Size of the query cache	Query cache is a known bottleneck even when the number of concurrent requests is small. You are advised to disable it from the beginning. Set query_cache_size to 0 to disable the query cache.
innodb_buffer_pool_instances	Number of memory buffer pools. Enable multiple memory buffer pools to hash data to be buffered to different buffer pools. In this way, the memory can be read and written concurrently.	Set it to a value from 8 to 16.
innodb_open_files	Number of files that can be opened by InnoDB 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	Size of the buffer that caches data and indexes	Recommended value: 70% of the memory
innodb_log_buffer_size	Size of the buffer that caches redo logs	Default value: 64 MB. Set this parameter based on the value of innodb_log_wait.
innodb_io_capacity	Maximum IOPS of InnoDB background threads	Recommended value: 75% of the total I/O QPS
innodb_log_files_in_group	Number of redo log groups	-
innodb_log_file_size	Size of the redo log file	A larger value is recommended for write-intensive scenarios. However, large redo log file causes long restoration time of data. When testing the ultimate performance in a non-production environment, you can set it to the maximum value. In commercial scenarios, the data restoration time needs to be considered.
innodb_flush_method	Method of flushing drives for logs and data. The options include the following: datasync: The data write operation is considered complete when data is written to the buffer of the operating system. Then, the operating system flushes the data from the buffer to drives and updates the metadata of files in drives. O_DSYNC: Logs are written to drives, and data files are flushed through fsync. O_DIRECT: Data files are directly written from the MySQL InnoDB buffer to drives without being buffered in the operating system. The write operation is completed by the flush operation. Logs are buffered by the operating system.	Recommended value: O_DIRECT
innodb_lru_scan_depth	Number of available pages in the LRU list	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	Number of threads for refreshing dirty data	Set it to the same value as innodb_buffer_pool_instances.
innodb_purge_threads	Number of threads for purging undo	-
innodb_flush_log_at_trx_commit	0: writes binlog every second no matter whether transactions are submitted. 1: writes the content in the log buffer to drives each time a transaction is submitted. The log files are updated to drives. This mode delivers the best security. 2: writes data to the operating system cache each time a transaction is submitted, and the operating system updates data to the drives. This mode delivers the best performance.	When testing the ultimate performance in a non-production environment, set this parameter to 0.
innodb_doublewrite	Whether to enable the double write function	When testing the ultimate performance in a non-production environment, set this parameter to 0 to disable double write.
ssl	Whether to enable 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.
skip_log_bin	Whether to enable binlog	When testing the ultimate performance in a non-production environment, add this parameter with binlog disabled to the parameter file.
performance_schema	Whether to enable the performance monitoring view	When testing the ultimate performance in a non-production environment, set this parameter to OFF to disable the performance monitoring view.

Parent topic: Database Tuning