稳如狗的MySQL 配置参数,让你的数据库飞起 - 技术分享 - 五行资源分享网

稳如狗的MySQL 配置参数,让你的数据库飞起

作者头像图片

作者: 五行

网络资源搬砖的爱好者。

文章二维码手机扫码查看

标签:

MySQL

特别声明:文章多为网络转载,资源使用一般不提供任何帮助,如有侵权请联系!

为了提高 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 #建议打开

 

 

分享到:
打赏
未经允许不得转载:

作者: 五行, 转载或复制请以 超链接形式 并注明出处 五行资源分享网
原文地址: 《稳如狗的MySQL 配置参数,让你的数据库飞起》 发布于2024-11-14 23:41
帖子声明: 本站对文章进行整理、排版、编辑,是出于传递信息之目的, 并不意味着赞同其观点或证实其内容的真实性,不拥有所有权,不承担相关法律责任。

评论

在线链接输入图片 打卡按钮图片 私密评论按钮图片 99 + 90 =
乱评论、多次评论者一律禁言帐号或封禁IP
本站已经全面开启缓存,评论查看隐藏内容需等待1分钟后再刷新本页!

 

觉得文章有用就打赏一下文章作者

微信扫一扫打赏

打赏图片