MySQL性能调优配置
目的
对于不同业务场景，通过在调整数据库的参数配置，可以有效提升服务器性能。
方法
使用如下配置文件参数启动数据库，默认配置文件路径为/etc/my.cnf, 其中MySQL软件安装及数据存放路径根据实际情况修改。
[mysqld_safe] log-error=/data/mysql/log/mariadb.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 #关闭ssl max_connections=2000 #设置最大连接数 back_log=2048 #设置会话请求缓存个数 performance_schema=OFF #关闭性能模式 max_prepared_stmt_count=128000 #file innodb_file_per_table #设置每个表一个文件 innodb_log_file_size=1500M #设置logfile大小 innodb_log_files_in_group=32 #设置logfile组个数 innodb_open_files=4000 #设置最大打开表个数 #buffers innodb_buffer_pool_size=230G #设置buffer pool size,一般为服务器内存60% innodb_buffer_pool_instances=16 #设置buffer pool instance个数，提高并发能力 innodb_log_buffer_size=64M #设置log buffer size大小 #tune sync_binlog=1 #设置每次sync_binlog事务提交刷盘 innodb_flush_log_at_trx_commit=1 #每次事务提交时MySQL都会把log buffer的数据写入log file，并且flush(刷到磁盘)中去 innodb_use_native_aio=1 #开启异步IO innodb_spin_wait_delay=180 #设置spin_wait_delay 参数，防止进入系统自旋 innodb_sync_spin_loops=25 #设置spin_loops 循环次数，防止进入系统自旋 innodb_flush_method=O_DIRECT #设置innodb数据文件及redo log的打开、刷写模式 innodb_io_capacity=20000 # 设置innodb 后台线程每秒最大iops上限 innodb_io_capacity_max=40000 #设置压力下innodb 后台线程每秒最大iops上限 innodb_lru_scan_depth=9000 #设置page cleaner线程每次刷脏页的数量 innodb_page_cleaners=16 #设置将脏数据写入到磁盘的线程数 #perf special innodb_flush_neighbors=0 #检测该页所在区(extent)的所有页，如果是脏页，那么一起进行刷新，SSD关闭该功能 innodb_write_io_threads=16 #设置写线程数 innodb_read_io_threads=16 #设置读线程数 innodb_purge_threads=32 #设置回收已经使用并分配的undo页线程数 sql_mode=STRICT_TRANS_TABLES,NO_ENGINE_SUBSTITUTION,NO_AUTO_VALUE_ON_ZERO,STRICT_ALL_TABLES
|
参数名称 |
参数含义 |
优化建议 |
|---|---|---|
|
innodb_thread_concurrency |
InnoDB使用操作系统线程来处理用户的事务请求。 |
建议取默认值为0，它表示默认情况下不限制线程并发执行的数量。 |
|
innodb_read_io_threads |
执行请求队列中的读请求操作的线程数。 |
根据CPU核数及读写比例进一步更改来提高性能。 |
|
innodb_write_io_threads |
执行请求队列中的写请求操作的线程数。 |
根据CPU核数及读写比例进一步更改来提高性能。 |
|
query_cache_size |
设置query_cache_size大小。 |
Query Cache（查询缓存）是一个众所周知的瓶颈位，即使在并发量不高的时候也会出现。最好的选择是从一开始就禁用它。通过设置 query_cache_size = 0，建议禁用查询缓存。 |
|
innodb_buffer_pool_instances |
开启多个内存缓冲池，把需要缓冲的数据hash到不同的缓冲池中，这样可以并行的内存读写。 |
建议设置8~16。 |
|
innodb_open_files |
在innodb_file_per_table模式下，限制Innodb能打开的文件数量。 |
建议此值调大一些，尤其是表特别多的情况。 |
|
innodb_buffer_pool_size |
缓存数据和索引的地方。 |
通常建议内存的70%左右。 |
|
innodb_log_buffer_size |
缓存重做日志。 |
默认值是64M，建议通过查看innodb_log_wait，调整innodb_log_buffer_size大小。 |
|
innodb_io_capacity |
innodb 后台线程每秒最大iops上限。 |
建议为IO QPS总能力的75%。 |
|
innodb_log_files_in_group |
重做日志组的个数。 |
- |
|
innodb_log_file_size |
重做日志文件大小。 |
如果存在大量写操作，建议增加日志文件大小，但日志文件过大，会影响数据恢复时间。 如果是非生产环境，测试极限性能时，尽量调大日志文件。 如果是商用场景，需要考虑数据恢复时间，综合折中后设置日志文件大小。 |
|
innodb_flush_method |
Log和数据刷新磁盘的方法：
|
建议O_DIRECT模式。 |
|
innodb_spin_wait_delay |
控制轮询的间隔。 |
根据真实场景调试，直到看不到splin_lock热点函数等。 |
|
innodb_sync_spin_loops |
控制轮询的虚幻次数。 |
根据真实场景调试，直到看不到splin_lock热点函数等。 |
|
innodb_lru_scan_depth |
LRU列表的可用页数量。 |
默认值是1024，非生产环境，测试极限性能可以适当调大，减少checkpoint次数。 |
|
innodb_page_cleaners |
刷新脏数据的线程数。 |
建议与innodb_buffer_pool_instances相等。 |
|
innodb_purge_threads |
回收undo的线程数。 |
- |
|
innodb_flush_log_at_trx_commit |
|
非生产环境，测试极限性能，可以设置为0。 |
|
innodb_doublewrite |
是否开启二次写。 |
非生产环境，测试极限性能，可以设置为0，关闭二次写。 |
|
ssl |
是否开启安全连接。 |
安全连接对性能影响较大，非生产环境，测试极限性能，可以设置为0；商用场景，根据客户需求调整。 |
|
skip_log_bin |
是否开启binlog。 |
非生产环境，测试极限性能在参数文件中增加此参数，关闭binlog选项。 |
|
innodb_checksum_algorithm |
数据完整性校验。 |
非生产环境，测试极限性能设置成none，不启用算法校验。 |
|
binlog_checksum |
Binlog完整性校验。 |
非生产环境，测试极限性能设置成none，不启用算法校验。 |
|
innodb_log_checksums |
Log完整性校验。 |
非生产环境，测试极限性能设置成0，关闭log checksum。 |
|
foreign_key_checks |
外键校验。 |
非生产环境，测试极限性能设置成0，关闭外键校验。 |
|
performance_schema |
是否开启性能模式。 |
非生产环境，测试极限性能设置为OFF，关闭性能模式。 |
