Tuning the MySQL Configuration

Adjust the MySQL configuration by modifying the file in the default configuration path /etc/my.cnf. The following is an example of an optimized MySQL configuration file.

[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
default_authentication_plugin=mysql_native_password
port=3306
user=root

max_connections=6000  #Set the maximum number of connections.
back_log=4000  #Set the number of cached session requests.
performance_schema=OFF  #Disable the performance mode.
max_prepared_stmt_count=1280000
large-pages  #Use huge pages.

#file
innodb_file_per_table  #Set one file for each table.
innodb_log_file_size=2048M  #Set the log file size.
innodb_log_files_in_group=8  #Set the number of log file groups.
innodb_open_files=10000  #Set the maximum number of tables that can be opened.
table_open_cache_instances=24  #Set the number of opened handle partitions.

#buffers
innodb_buffer_pool_size=24G  #Set the buffer pool size, which is usually 75% of the VM memory.
innodb_buffer_pool_instances=16  #Set the number of buffer pool instances, which is to improve the concurrency capability.

#tune
default_time_zone=+8:00
thread_cache_size=2000
sync_binlog=1  #Synchronize binary logs each time a transaction is submitted.
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 logs to drives.
innodb_use_native_aio=1  #Enable asynchronous I/O.
innodb_spin_wait_delay=4  #Set the spin_wait_delay parameter to prevent system spin.
innodb_sync_spin_loops=6  #Set the spin_loops loop times to prevent system spin.
innodb_flush_method=O_DIRECT  #Set the direct 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=8000  #Set the number of dirty pages flushed by the page cleaner thread each time.
innodb_page_cleaners=14  #Set the number of threads for writing dirty data to drives.
innodb_spin_wait_pause_multiplier=2  #Set a multiplier value used to determine the number of PAUSE instructions in spin-wait loops.

#perf special
innodb_flush_neighbors=0  #Flush all dirty pages in the same extent. For SSDs, disable this function.
innodb_write_io_threads=5 #Set the number of write threads.
innodb_read_io_threads=2  #Set the number of read threads.
innodb_purge_threads=1  #Set the number of undo page threads to be purged.
innodb_read_ahead_threshold=70
innodb_random_read_ahead=OFF
innodb_old_blocks_pct=44
innodb_old_blocks_time=800
sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES

log-bin=mysql-bin  #Enable binary logs.
ssl=0  #Disable SSL.
table_open_cache=30000  #Set the maximum number of open tables cached in one table cache instance.
max_connect_errors=2000
innodb_adaptive_hash_index=0

mysqlx=0