为了提高 MySQL 服务器的性能和稳定性，我们需要对其配置参数进行调整，主要包含 OS 配置参数和 MySQL 数据库配置参数，需要的小伙伴可以参考一下。
OS配置部分
（1）在BIOS及内核层面关闭NUMA
（2）在BIOS层面将CPU、内存均设置最大性能模式
（3）在BIOS层面关闭CPU节能模式
（4）修改 IO Scheduler 为 deadline 或 noop，机械盘设置为 deadline，ssd 设置为 noop
grep deadline /sys/block/sd*/queue/scheduler
（5）使用xfs文件系统，挂载选项 noatime、nodiratime、nobarrier
（6）在内核层面设置 vm.swappiness<=5，vm.dirty_ratio<=10， vm.dirty_background_ratio<=5
fs.file_max=65536 指定能够打开的文件句柄数
vm.dirty_background_ratio 指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如5%）就会触发pdflush/flush/kdmflush等后台 回写进程运行，将一定缓存的脏页异步地刷入外存;
vm.dirty_ratio 指定了当文件系统缓存脏页数量达到系统内存百分之多少时（如10%），系统不得不开始处理缓存脏页（因为此时脏页数量已经比较多，为了避免数据丢失需要将一定脏页刷入外存）；在此过程中很多应用进程可能会因为系统转而处理文件IO而阻塞。
net.core.somaxconn=65536 指定socket监听的TCP协议连接数的上限
net.core.netdev_max_backlog=65536
net.ipv4.tcp_max_sync_backlog=65536
net.ipv4.tcp_fin_timeout=10
net.ipv4.tcp_tw_reuse=0 此参数表示开启重用，允许将 TIME_WAIT 套接字重新用于新的TCP连接 建议关闭
net.ipv4.tcp_tw_recycle=0 此参数表示开启TCP连接中 TIME_WAIT 的快速回收，建议关闭
（7）在内核层面修改用户可最大打开文件数和线程数为 65535
vi /etc/security/limits.conf
# add for mysql
* - nofile 65535
* - nproc 65535
MySQL配置部分
（1）sort/join/read/read rnd buffer 设置
--一般4M或者8M，最多到16M
sort_buffer_size = 4M
join_buffer_size = 4M
read_buffer_size = 8M
read_rnd_buffer_size = 4M
（2）tmp/heap table
--一般16M或者32M，如果sql性能差，需要经常产生临时表，可以设到96M。可以在session级别设置
tmp_table_size = 32M
max_heap_table_size = 32M
（3）双一保证
--保证主库环境、主从数据一致性
sync_binlog = 1
innodb_flush_log_at_trx_commit = 1
（4）long_query_time
--建议设为0.01-0.1，如果设为0，就会把所有的sql记录下来，需要定期去清理
long_query_time = 0.1
（5）log_queries_not_using_indexes & log_throttle_queries_not_using_indexes
--把所有没有使用索引的sql都记录下来
log_queries_not_using_indexes =1
log_throttle_queries_not_using_indexes = 60
（6）interactive_timeout & wait_timeout
--一般建议设置为300s 或者600s
interactive_timeout = 600
wait_timeout = 600
（7）lock_wait_timeout
--持有锁的时间，一般设置为1800或者3600
lock_wait_timeout = 3600
（8）default_time_zone
--可能造成cpu使用高，要设置一个固定值
default_time_zone = "+8:00"
（9）thread_handling
--企业版或者percona版本才有的参数，如果业务是大量短连接，可以设置。如果是长连接或者连接池，没必要打开
（10）innodb_buffer_pool_size
--一般设置为内存50%-75%
innodb_buffer_pool_size=2G
innodb_buffer_pool_instances 必须在 innodb_buffer_pool_size 大于等于1G 时才生效。
当 innodb_buffer_pool_size 值低于 1GB时，没必要也不能设置innodb_buffer_pool_instances 值大于等于 2。
一般而言，当 innodb_buffer_pool_size 值不高于 8GB时，没必要设置innodb_buffer_pool_instances 值大于 1。
通常，当 innodb_buffer_pool_size 较大时（大于64GB），innodb_buffer_pool_instances设置为 8 是个比较合理的值。
（11）innodb_max_dirty_pages_pct
--默认75%，IO比较快的可设置为50%
innodb_max_dirty_pages_pct = 50
（12）innodb_thread_concurrency
--建议设置为0
innodb_thread_concurrency = 0
（13）innodb_lock_wait_timeout
--行锁等待时间，设为5-10s
innodb_lock_wait_timeout = 10
（14）innodb_log_file_size & innodb_log_files_in_group
innodb_log_file_size = 2G
innodb_log_files_in_group = 3
（15）
# 根据您的服务器IOPS能力适当调整
# 一般配普通SSD盘的话，可以调整到 10000 - 20000，普通机械磁盘其随机IO的IOPS最多也就是300
# 配置高端PCIe SSD卡的话，则可以调整的更高，比如 50000 - 80000
innodb_io_capacity = 4000
innodb_io_capacity_max = 8000
（16）
innodb_status_output innodb 状态监控信息开关，on为开启，off为关闭，默认为off
innodb_status_output_locks 为锁监控信息开关，on为开启，off为关闭，默认也为off
一般建议将innodb_status_output innodb 参数关闭，如测试需要可以临时打开，测试完成再关闭。
SET GLOBAL innodb_status_output=OFF;
改为还需要同步修改配置文件，否则下次重启又打开了。改完后，错误日志不再有innodb状态信息输出了。
innodb_status_output_locks 设置为打开，监控锁信息。这样需要查看锁信息时，可以通过 show engine innodb status G; 进行查看。
（17）
skip_name_resolve：默认值为OFF，内网生产建议设为ON，禁用dns解析
（18）设置保存binlog时间
expire_logs_days=7
或者
binlog_expire_logs_seconds=604800
（19）sql_mode
ONLY_FULL_GROUP_BY,STRICT_TRANS_TABLES,NO_ZERO_IN_DATE,NO_ZERO_DATE,ERROR_FOR_DIVISION_BY_ZERO,NO_AUTO_CREATE_USER,NO_ENGINE_SUBSTITUTION
（20）max_allowed_packet
max_allowed_packet = 64M
（21）innodb_print_all_deadlocks
innodb_print_all_deadlocks=1 #建议打开
[... 
评论