【MySQL 45讲-12】第12讲mysql时不时抽风的原因

12 | 为什么我的MySQL会抖一下？

前言

• 一条SQL偶尔特别慢，且场景难复现，原因？

• 做个比喻

  • 粉板：redo log
  • 掌柜的记忆： 内存
  • 账本： 数据文件

• 脏页：内存数据页与磁盘数据页内容不一致

• 刷脏页： 将内存页写入磁盘

• 抖一下：可能实在刷脏页，平常执行很快的更新操作实在写内存和日志

什么情况会引发数据库的flush过程？

• 1. 粉板满了，记不下了
     （redo log写满了，系统停止所有更新操作）
  • 尽量避免这种情况，全阻塞了，更新数跌为0
• 2. 生意太好，掌柜要记不住了
     （内存不足，要将脏页写到磁盘）

  • 常态，要有控制脏页比例的机制来避免下面两种情况

    • 1. 要淘汰的脏页个数过多，导致查询的响应时间变长
    • 2. 日志写满，更新全堵了
• 3. 生意不忙时更新账本
     （MySQL认为系统空闲时，进行刷脏页）
  • 系统空闲，无压力
• 4. 年底清账本
     （MySQL正常关闭时进行刷脏页）
  • 系统空闲，无压力

InnoDB刷脏页的控制策略

• 1. 告诉InnoDB所在主机的IO能力

  • innodb_io_capacity

  • 刷脏页速度的影响因素

    • 脏页比例
    • redo log写盘速度
• 2. 刷脏页的连坐机制
  • innodb_flush_neighbors
  • 当刷到的脏页的邻居也是脏页时，会一起刷了
  • 将参数设置为0，关闭连坐机制

举个栗子

• 一个内存配置为128GB，innodb_io_capacity=20000情况下，正常建议你将redo log设置成4个1GB文件。如果在配置时将redo log设置成一个100MB文件，会发生什么情况？（高配机器，redo log设置太小发生的情况。）

  • 每次事务提交都要写redo log，设置太小很快就会被写满，这时系统被迫停止所有更新，出现的现象：磁盘压力很小，但数据库出现间歇性的性能下跌。