MySQL5.7生产环境推荐参数配置

隐藏在各项配置文件之后的,是MySQL的思想和设计方案。

查看当前所有配置
show variables;

一、表相关

表数据独立存储

表数据既可以存在共享表空间里,也可以是单独的文件。这个行为是由参数 innodb_file_per_table 控制的,从 MySQL 5.6.6 版本开始,它的默认值就是 ON 了。
  1. 这个参数设置为 OFF 表示的是,表的数据放在系统共享表空间,也就是跟数据字典放在一起;
  2. 这个参数设置为 ON 表示的是,每个 InnoDB 表数据存储在一个以 .ibd 为后缀的文件中。

我建议你不论使用 MySQL 的哪个版本,都将这个值设置为 ON。

因为,一个表单独存储为一个文件更容易管理,而且在你不需要这个表的时候,通过 drop/truncate table 命令,系统就会直接删除这个文件。而如果是放在共享表空间中,即使表删掉了,空间也是不会回收的。

innodb_file_per_table = ON

二、日志相关

bin log格式

如果是使用 delete 语句误删了数据行,可以用 Flashback 工具通过闪回把数据恢复回来。Flashback 恢复数据的原理,是修改 binlog 的内容,拿回原库重放。而能够使用这个方案的前提是,需要确保以下两个配置。
binlog_format=row
binlog_row_image=FULL

bin log文件体积限制/自动过期

row格式记录的数据更完整,在数据恢复方面有极大优势,但缺点是生成的日志量大。因此需要根据业务需求和服务器配置对binlog设置单个文件最大体积限制和保留时长的限制。
expire_logs_days = 30
max_binlog_size = 512M

双“1”配置

一个事务完整提交前,需要等待两次刷盘,一次是 redo log(prepare 阶段),一次是 binlog。

sync_binlog 这个参数设置成 1 的时候,表示每次事务的 binlog 都持久化到磁盘。这个参数我也建议你设置成 1,这样可以保证 MySQL 异常重启之后 binlog 不丢失。

redo log 用于保证 crash-safe 能力,innodb_flush_log_at_trx_commit 这个参数设置成 1 的时候,表示每次事务的 redo log 都直接持久化到磁盘。这个参数我建议你设置成 1,这样可以保证 MySQL 异常重启之后redo log数据不丢失。

sync_binlog = 1
innodb_flush_log_at_trx_commit = 1

 

redo log文件体积/个数

利用 WAL 技术,MySQL将随机写转换成了顺序写,大大提升了数据库的性能。但是,由此也带来了内存脏页的问题。脏页会被后台线程自动 flush,也会由于数据页淘汰而触发 flush,而刷脏页的过程由于会占用资源,可能会让你的更新和查询语句的响应时间长一些,造成性能的间歇性下降。

“redo log 写满了,要 flush 脏页”,这种情况是 InnoDB 要尽量避免的。因为出现这种情况的时候,整个系统就不能再接受更新了,所有的更新都必须堵住。如果你从监控上看,这时候更新数会跌为 0。

一个内存配置为 128GB、innodb_io_capacity 设置为 20000 的大规格实例,正常会建议你将 redo log 设置成 4 个 1GB 的文件。

这两个属性是只读的,需要通过修改配置文件并重启MySQL生效。

innodb_log_file_size=512M
innodb_log_files_in_group=2

 

三、buffer相关

缓冲池大小

缓冲池内存大小不够,意味着可用的数据页少,脏页比例容易接近75%,造成性能间歇性抖动。

innodb_buffer_pool_size=1G

join联表查询缓冲池大小

join_buffer 的大小是由参数 join_buffer_size 设定的,默认值是 256k。如果放不下表 t1 的所有数据话,策略很简单,就是分段放。如果join语句较慢,有可能是因为缓冲池过小。

join_buffer_size=32M

四、硬盘IO相关

IO读写能力

如果没能合理地设置 innodb_io_capacity 参数,会导致一些性能问题。比如MySQL 的写入速度很慢,TPS 很低,但是数据库主机的 IO 压力并不大。

如果主机磁盘用的是 SSD,但是 innodb_io_capacity 的值设置的是 300。于是,InnoDB 认为这个系统的能力就这么差,所以刷脏页刷得特别慢,甚至比脏页生成的速度还慢,这样就造成了脏页累积,影响了查询和更新性能。

测试完成后可以得到服务器硬盘的IOPS,根据这个值来设置:(200是默认值)

innodb_io_capacity = 200

centos安装fio,测试硬盘io
yum install -y libaio-devel fio
cd /;mkdir test;cd /test;touch file;fio -filename=/test/file -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=4k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest

windows可以使用AS SSD软件测试IOPS

 

脏页刷写连坐机制

一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时,这个查询就可能要比平时慢了。而 MySQL 中的一个机制,可能让你的查询会更慢:在准备刷一个脏页的时候,如果这个数据页旁边的数据页刚好是脏页,就会把这个“邻居”也带着一起刷掉;而且这个把“邻居”拖下水的逻辑还可以继续蔓延,也就是对于每个邻居数据页,如果跟它相邻的数据页也还是脏页的话,也会被放到一起刷。在 InnoDB 中,innodb_flush_neighbors 参数就是用来控制这个行为的,值为 1 的时候会有上述的“连坐”机制,值为 0 时表示不找邻居,自己刷自己的。

找“邻居”这个优化在机械硬盘时代是很有意义的,可以减少很多随机 IO。机械硬盘的随机 IOPS 一般只有几百,相同的逻辑操作减少随机 IO 就意味着系统性能的大幅度提升。

而如果使用的是 SSD 这类 IOPS 比较高的设备的话,建议把 innodb_flush_neighbors 的值设置成 0:

因为这时候 IOPS 往往不是瓶颈,而“只刷自己”,就能更快地执行完必要的刷脏页操作,减少 SQL 语句响应时间。在 MySQL 8.0 中,innodb_flush_neighbors 参数的默认值已经是 0 了。

innodb_flush_neighbors = 0

 

五、线程相关

并发线程上限数

通常情况下,我们建议把 innodb_thread_concurrency 设置为 64~128 之间的值。并发连接和并发查询并不是同一个概念,你在 show processlist 的结果里,看到的几千个连接,指的就是并发连接。而“当前正在执行”的语句,才是我们所说的并发查询。

并发连接数达到几千个影响并不大,就是多占一些内存而已。我们应该关注的是并发查询,因为并发查询太高才是 CPU 杀手。这也是为什么我们需要设置 innodb_thread_concurrency 参数的原因。

在线程进入锁等待以后,并发线程的计数会减一,也就是说等行锁(也包括间隙锁)的线程是不算在 128 里面的。MySQL 这样设计是非常有意义的。因为,进入锁等待的线程已经不吃 CPU 了;更重要的是,必须这么设计,才能避免整个系统锁死。

innodb_thread_concurrency=128

 

六、锁相关

自增锁模式

MySQL 5.1.22 版本引入了一个新策略,新增参数 innodb_autoinc_lock_mode,默认值是 1。

  1. 这个参数的值被设置为 0 时,表示采用之前 MySQL 5.0 版本的策略,即语句执行结束后才释放锁;
  2. 这个参数的值被设置为 1 时:
    • 普通 insert 语句,自增锁在申请之后就马上释放;
    • 类似 insert … select 这样的批量插入数据的语句,自增锁还是要等语句结束后才被释放;
  1. 这个参数的值被设置为 2 时,所有的申请自增主键的动作都是申请后就释放锁。

在生产上,尤其是有 insert … select 这种批量插入数据的场景时,从并发插入数据性能的角度考虑,我建议你这样设置:innodb_autoinc_lock_mode=2 ,并且 binlog_format=row. 这样做,既能提升并发性,又不会出现数据一致性问题。

innodb_autoinc_lock_mode=2

 

七、SQL部分

安全模式

为了防止忘记在 delete 或者 update 语句中写 where 条件、where 条件里面没有包含索引字段、没有加入limit限制引起的非安全SQL语句造成数据误删等,写入该配置后,这些非安全语句执行就会报错。

sql_safe_updates = ON

 

评论

  1. 健康管理
    Windows Firefox
    2周前
    2020-11-10 9:46:00

    文章写的很好啊,赞(ㆆᴗㆆ),每日打卡~~

    • keyboardman 博主
      Windows Chrome
      2周前
      2020-11-11 17:29:30

      感谢

发送评论 编辑评论


				
|´・ω・)ノ
ヾ(≧∇≦*)ゝ
(☆ω☆)
(╯‵□′)╯︵┴─┴
 ̄﹃ ̄
(/ω\)
∠( ᐛ 」∠)_
(๑•̀ㅁ•́ฅ)
→_→
୧(๑•̀⌄•́๑)૭
٩(ˊᗜˋ*)و
(ノ°ο°)ノ
(´இ皿இ`)
⌇●﹏●⌇
(ฅ´ω`ฅ)
(╯°A°)╯︵○○○
φ( ̄∇ ̄o)
ヾ(´・ ・`。)ノ"
( ง ᵒ̌皿ᵒ̌)ง⁼³₌₃
(ó﹏ò。)
Σ(っ °Д °;)っ
( ,,´・ω・)ノ"(´っω・`。)
╮(╯▽╰)╭
o(*////▽////*)q
>﹏<
( ๑´•ω•) "(ㆆᴗㆆ)
😂
😀
😅
😊
🙂
🙃
😌
😍
😘
😜
😝
😏
😒
🙄
😳
😡
😔
😫
😱
😭
💩
👻
🙌
🖕
👍
👫
👬
👭
🌚
🌝
🙈
💊
😶
🙏
🍦
🍉
😣
Source: github.com/k4yt3x/flowerhd
颜文字
Emoji
小恐龙
花!
上一篇
下一篇