MySQL偶尔的停顿

12 MySQL为什么会突然抖动一下

• 有时候一条SQL在正常执行的时候很快，但有时候会特别慢。

1. SQL语句为什么变慢

• InnoDB的WAL机制，InnoDB在处理更新语句的时候，只做了写日志这一个磁盘操作。
• 这个日志叫做redo log(重做日志)，在更新内存写完redo log之后，就返回给客户端，本次更新成功。
• 即在更新操作时，有数据文件，日志文件，以及内存，数据文件即为底层文件，日志文件就是操作记录，在MySQL将数据文件更新就是要内存数据写入磁盘，即flush。
• 在flush之前，数据文件和内存的数据是不一致的。
  • 当内存数据也跟磁盘数据页内容不一致时，则成内存页为“脏页”
  • 内存数据写入磁盘后，内存和磁盘上的数据页内容一致，则称为“干净页”
• 
• 因此，平时很快的更新操作，突然变的很慢，就是在做flush操作。

2. 什么情况下会引发数据库的flush过程

1. InnoDB的redo log写满了，这时候系统会停止所有更新操作，把checkpoint往前推进，redo log留出空间可以继续写。

   1. 

   2. checkpoint 不是随便向前修改位置。如上如，把checkpoint位置从CP推进到cp’，就需要将两个点之间的日志(浅绿色)，对应的所有脏页都flush到磁盘上。

   3. 图中write pos 到CP’之间就是可以再写入的redo log区域

2. 系统内存不足 ，当需要新的内存页，而内存页不够用的时候，就要淘汰一些数据页，空出内存给别的数据页使用。如果淘汰的是“脏页”，就要把脏页先写道磁盘。如果刷脏页一定会写盘，就保证了每个数据页有两种状态：

   1. 一种是再内存里存在，内存就肯定是正确的结果，直接返回
   2. 内存没有数据，就可以肯定数据文件上是正确的结果，读入内存后返回。

3. MySQL认为系统空闲时候，会刷脏页。

4. MySQL正常关闭的时候，MySQL会把内存的脏页都flush到磁盘上。

上面四种场景对性能的影响

• 第三、第四中情况不需要太考虑性能问题。主要分析前两种性能问题

• 第一种：“redo log 写满，要flush脏页”，这种情况是InnoDB尽量避免的，因为出现这种状况的时候，整个系统不再接收更新，所有的更新都必须堵住。

• 第二种：“内存不够用了，要先将脏页写到磁盘”，这种情况是常态，InnoDB用缓冲池（buffer pool）管理内存，缓冲池中的内存页有三种状态：

  • 还没有使用的

  • 使用了并且是干净页

  • 使用了并且是脏页

    ---

  • InnoDB的策略是尽量使用内存，因此对于一个长时间运行的库来说，未被使用的页面很少。

  • 当读入的数据没有在内存的时候，就必须到缓冲池中申请一个数据页。这是胡只能把最久不使用的数据页从内存淘汰。

    • 如果要淘汰的是一个干净页，就直接释放出来服用；
    • 如果是脏页，就要编程干净页后复用

    ---

  • 刷脏页会出现两种情况，影响性能：

    • 一个查询要淘汰的脏页个数太多，会导致查询的响应时间明显变长。
    • 日志写满，更新全部堵住，写性能跌为0，对敏感业务来说不饿能接收。

InnoDB刷脏页的控制策略

1. 要知道InnoDB所在主机的IO能力，这样InnoDB才能知道需要全力刷脏页的时候，可以刷多快。

   1. 可以使用innodb_io_capacity这个参数，它会告诉InnoDB主机的磁盘能力，这个值一般可以设置成磁盘的IOPS。

   2. 主机的IOPS可以用fio来测试

      fio -filename=$filename -direct=1 -iodepth 1 -thread -rw=randrw -ioengine=psync -bs=16k -size=500M -numjobs=10 -runtime=10 -group_reporting -name=mytest 
      

2. InnoDB控制引擎按照“全力”的百分比来刷脏页。

   1. InnoDB的刷谈判速度会参考两个因素：
      1. 脏页比例
      2. redo log 写盘速度。
   2. InnoDB会根据两个因素先单独算出两个数字
      1. 参数innodb_max_dirty_pages_pct是脏页比例上限，默认值是75%。
      2. InnoDB会根据当前的脏页比例（假设为M），算出脏页比例M，算出一个范围在0~100的数字，即F1(M)
      3. InnoDB每次写入的日志都有一个序号，当前写入的序号跟checkPoint对应的序号之间的查询，假设为N，InnoDB会根据N算出一个0~100的数字，F2（N）,当N越大，算出来的值越大。
   3. 根据算出的F1(M)，F2(N)，取其中最大的值为R，之后引擎就以按照innodb_io_capacity定义的能力诚意R%来控制刷脏页的速度。
   4. 即刷脏页速度适合脏页比例以及redo log没有被flush的长度（或者比例）相关的，而且，脏页比例越大或者没有被flush的长度越大，刷脏页速度也就越大

3. 总结

• InnoDB在后台刷脏页，将内存页写入磁盘，因此在查询语句需要内存的时候，可能要淘汰一个脏页，或者刷脏页的逻辑会占用IO资源并可能影响到更新语句，都可能造成MySQL突然卡一下的原因。
• 要避免这种情况，就要合理设置innodb_io_capacity的值，尽量不要让脏页比例接近75%。
• 一旦一个查询请求需要在执行过程中先 flush 掉一个脏页时，这个查询就可能要比平时慢了。而 MySQL 中的一个机制，可能让你的查询会更慢：在准备刷一个脏页的时候，如果这个数据页旁边的数据页刚好是脏页，就会把这个“邻居”也带着一起刷掉；而且这个把“邻居”拖下水的逻辑还可以继续蔓延，也就是对于每个邻居数据页，如果跟它相邻的数据页也还是脏页的话，也会被放到一起刷。
  • 在InnoDB中，innodb_flush_neighbors参数就是控制这个行为的，值为1的时候就会上述行动，当为0是就自己刷自己。
  • 机械硬盘一般IOPS只有几百，相同的逻辑操作减少随机IO就意味着系统性能的提升。使用SSD的话，就将参数设置为0，因为这时候IOPS往往不是瓶颈，而自己刷自己，就能够更快执行刷脏页的操作，减少SQL响应的时间。