MySQL企业级参数调优实践

一、序言

MySQL提供了很多参数给到我们去调整以最大的去挖掘其性能，本文以生产数据库实例配置，了解参数配置背后的思考，最后还会给一份优化过后的配置，可在其基础上进行调整。

二、参数说明

symbolic-links=0

禁止MySQL使用软连接，这里其实是出于数据库加固的考虑，使用软连接，尤其是用root运行mysqld时，攻击者将可以通过数据库获得root权限，又由于数据同时存储在系统分区，将导致攻击者可以修改系统分区的任意文件；当使用mysql命令修改软连接对应的数据库，会修改存储在系统分区的实际文件，可能对系统内的其他文件进行删除和重命名，MySQL8默认是禁止。

lower_case_table_names=1

该参数用来设置大小写敏感，有三个值，
0：表名的存储和比较是大小写敏感；
1：表名存储在磁盘是小写，比较的时候是大小写不区分；
2：表名存储的是大小写敏感，但是比较的时候是小写。linux下默认是0，window下默认是1。

skip-name-resolve
禁止DNS解析，MySQL会在用户登录过程中对客户端IP进行DNS反查，不管是使用IP登录还是域名登录，这个反查的过程都是在的。所以如果MySQL所在的服务器的DNS有问题或者质量不好，那么就会影响到请求响应的速度。

slow_query_log=ON
long_query_time=20
slow_query_log_file = /home/mysql/slow_sql.log

开启慢查询日志，默认不开启，设置慢查询的阈值是20s，以及触发慢查询的时候日志输出的文件目录为/home/mysql/slow_sql.log。

max_connections=1000

设置MySQL数据库的最大连接数，默认是200，有时候遇到数据库报too many connections，就是连接数不够了，业务正常的情况下可以适当调整这里的参数。同时innodb_thread_concurrency这个参数也控制了并发线程数，吗，5.6之后默认值为0，即不限制。

innodb_flush_log_at_trx_commit=2

配置刷盘策略，MySQL提供了三种策略，
0：每隔1s会将buffer写入os cache，同时触发刷盘，但是每次事务结束并不会触发buffer写入，MySQL或者OS挂掉后，会丢失1s的数据；
1：每次事务提交的时候写入cache，同时触发刷盘，MySQL或者os挂了后数据不会丢失，但是性能较差；
2：每次事务提交写入os cache，交由操作系统去刷盘，如果MySQL挂了，数据是不会丢失，但是如果os挂了，数据会丢失。
其中innodb_max_dirty_pages_pct可以控制脏页刷盘的比例，默认比例是75%，推荐配置50%；

innodb_spin_wait_delay=6

设置自旋锁在放弃CPU时间片之前进行多少次等待，默认是6，设置过大会降低性能

table_open_cache=8000

该参数和max_connection相关的，例如1000个并发运行的连接，指定表缓存的数量至少是1000*N，要确保操作系统能处理指定打开文件描述符的数量，如果table_open_cache太高，MySQL可能会消耗完文件描述符从而拒绝连接或者查询失败。

back_log=1500

MySQL停止响应新请求最大允许暂存在堆栈里的请求数，有点类似于连接池阻塞队列的概念。如果MySQL的连接数达到max_connections时，新来的请求将会被存在堆栈中，以等待某一连接释放资源，如果等待连接的数量超过back_log，将不被授予连接资源，默认是80；

query_cache_type=0

关闭查询缓存功能，默认是关闭的。MySQL查询缓存其实就是把SQL语句的Hash值缓存起来，跟Mybatis的二级缓存是类似，缓存的是语句，本身缓存的命中率就很低，而且表的改动会触发缓存失效，并发情况下会有锁的竞争，多了不必要的开销。

table_open_cache_instances=8

表缓存实例数，减小会话间的争用以提高扩展性。表缓存会分区为table_open_cache/table_open_cache_instances。DML语句会话只需要锁定所在缓存实例，这样多个会话访问表缓存时就可提升性能（DDL语句仍会锁定整个缓存）。默认该值为1，当16核以上可设置为8或16。

innodb_log_file_size=1024M
innodb_log_files_in_group = 3

innodb_log_file_size：设置事务日志文件的大小，默认500M，推荐配置1024M。
如果设置得太小，当日式文件写满后，innodb会切换到另一个文件，同时触发数据库的checkpoint，会导致innodb缓存脏页的小批量刷新，会明显降低innodb的性能。如果设置得过大，虽然减少了checkpoint刷盘的次数，而且Redo log是顺序I/O，能极大提高了I/O性能，但是如果数据库意怠机，需要较长的时间去恢复。
innodb_log_files_in_group：设置事务日志文件数，默认是2，推荐配置3。

innodb_buffer_pool_size=20480M
innodb_buffer_pool_instances=8

innodb_buffer_pool_size：缓冲池的总大小，设置为系统内存的60%-80%
innodb_buffer_pool_instances：缓冲池的实例
将缓冲池划分为单独的实例可以通过减少不同线程对缓存页进行读写时的争用来提高并发性，可以动态调整innodb_buffer_pool_size和innodb_buffer_pool_instances的参数，但需要保证每个缓冲池实例至少为1GB。

innodb_log_buffer_size=64M

这个参数用来决定innodb的RedoLog缓存池的大小，默认是16MB。对于可能产生大量更新记录的大事务，增加innodb_log_buffer_size的大小，可以避免在事务提交前就执行不必要的日志写入磁盘操作。

innodb_flush_neighbors=0

禁止刷临近缓存页，减少要刷缓存页的数量，这样可以把刷缓存页的性能提升到最高，也尽可能降低每次刷缓存页对执行SQL语句的影响，防止MySQL频繁抖动。

innodb_flush_method=O_DIRECT

这个参数控制数据文件和日志文件的打开和输盘模式，默认是有三个值，fdatasync(默认)，O_DSYNC，O_DIRECT
fdatasync：innodb使用fsync()去刷数据文件与RedoLog
O_DSYNC：innodb使用O_SYNC方式打开和刷写RedoLog，使用fsync()刷写数据文件
O_DIRECT：innodb使用O_DIRECT打开数据文件，使用fsync()刷写数据文件跟RedoLog

在linux操作系统中，文件的打开方式为O_DIRECT会最小化缓冲对io的影响，文件的io是直接在用户空间的buffer上操作的，并且io操作是同步，因此不管是读还是写最终确保数据都是从磁盘上获取。O_SYNC表示同步打开文件，任何操作都阻塞到数据写入物理磁盘后才返回。

三、示例

最后给一份优化过后的配置my.cnf，对标的硬件资源（8核16G、硬盘500G），可以在其基础上进行调整。

# The MySQL server
[client]
port=3306
socket=/home/mysql/mysql_data/mysql.sock
default-character-set=utf8

[mysqld]
user=mysql
port=3306
#bind-address=0.0.0.0

socket=/home/mysql/mysql_data/mysql.sock
basedir=/usr/local/mysql
datadir=/home/mysql/mysql_data
log-error=/home/mysql/mysql_data/mysqld.log
pid-file=/home/mysql/mysql_data/mysqld.pid

#performance_schema_instrument='memory%=counted'

#tmpdir=/var/lib/mysql/tmp
# 禁止使用软链接
symbolic-links=0
# 表名是否区分大小写
lower_case_table_names=1
log_bin_trust_function_creators=1
# 禁止DNS查询
skip-name-resolve

# default-character-set=utf8
# 设置默认的字符集
character_set_server=utf8
# 设置默认的存储引擎
default-storage-engine=InnoDB

# 开启慢查询
slow_query_log_file = /home/mysql/slow_sql.log
slow_query_log=ON
long_query_time=20

# 最大连接数
max_connections=1000
# 设置表缓存实例数
table_open_cache=8000
# 设置堆栈数量大小
back_log=1500
# 关闭查询缓存
query_cache_type=0
# 设置数据库打开的表缓存
table_open_cache_instances=8

# files
# 打开独立表空间
innodb_file_per_table
# 设置事务日志文件大小
innodb_log_file_size=1024M
# 设置事务日志文件数
innodb_log_files_in_group=3
#innodb_open_files=6000

# buffers
# 设置数据和索引缓冲区大小
# 设置为系统内存的60-80%
innodb_buffer_pool_size=20480M
# 缓冲池划分的区域数
innodb_buffer_pool_instances=8
# Redolog缓存区的大小
innodb_log_buffer_size=64M
# 设置表连接申请的内存
join_buffer_size=1M
# 每个Connection分配的buffer大小
sort_buffer_size=1M

# innodb
# 关闭文件校验和
innodb_checksums=0
# 关闭innodb的两次写
innodb_doublewrite=0
# 关闭innodb的两阶段提交
innodb_support_xa=0
# 不限制innodb的并发线程数
innodb_thread_concurrency=0
# 设置文件刷盘策略
innodb_flush_log_at_trx_commit=2
# 设置脏页刷盘的百分比阈值
innodb_max_dirty_pages_pct=50
# 设置为异步IO
innodb_use_native_aio=1
# 设置统计数据为磁盘存储
innodb_stats_persistent=1
# 设置自旋锁等待的次数
innodb_spin_wait_delay=6

# perf special
# 开启Adaptive Flush特性
innodb_adaptive_flushing=1
# 关闭刷新临近脏页
innodb_flush_neighbors=0
# 设置IO回调的读线程
innodb_read_io_threads=8
# 设置IO回调的写线程
innodb_write_io_threads=8
# 设置刷新脏页的数量
innodb_io_capacity=3000
# 设置后台资源回收的线程数
innodb_purge_threads=1
# 关闭自适应hash查询
innodb_adaptive_hash_index=0
#设置刷盘方式
innodb_flush_method=O_DIRECT
sql_mode=STRICT_TRANS_TABLES,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
# 数据包传输的最大值
max_allowed_packet=128M

[mysqldump]
# 开启快速转存
quick
# 保证mysqldump的数据完整性
single-transaction
# mysqldump数据包传输的最大值
max_allowed_packet=128M