MySQL 偶尔抽风,性能突然下降

SQL 执行突然变慢的原因

当内存数据页(redo log)和磁盘数据页内容不一致时,将该内存也称为 “脏页”。将内存数据写入到磁盘后,数据一致,内存页称为 “干净页”。

在内存数据写入磁盘时,这个过程称为 flush 过程。SQL 突然执行变得很慢,性能下降。原因就可能和 flush 操作有关。

因为在进行 flush 操作时,更新操作会等待 redo log 的写入。

引起 flush 操作的原因

场景一:redo log 日志已经记满。这时系统会停止更新操作,将 check point 向前推进,让 redo log 留出空间可以继续写。
MySQL 偶尔抽风,性能突然下降
这里假设 CP 到 CP‘ 间隙已经写入到磁盘,这部分就变成了干净页,此时 write pos 就可以写入这部分区域了。

场景二:系统内存不足,需要新的内存页时,发现内存不够用了,就需要淘汰一些数据页。如果淘汰时,这时数据页时脏页,就要将脏页写到磁盘。

这时有个问题是,命名 redo log 中的内容已经被记录到日志中了,假如内存满了,直接删除不就可以吗?下次读入时,再把 redo log 日志中的内容应用到磁盘。

没有选择直接清空内存,是从性能考虑的,因为在查询数据时,有两种情况:

  • 首先数据页在内存中,内存是就是正确的结果,直接返回
  • 内存里没有数据,从数据文件上读入内存。

所以这样效率比较高。

场景三:MySQL 会在系统空闲时,进入 flush 操作。

场景四:在 MySQL 正常关闭时,会把内存脏页 flush 到磁盘上。

引起 flush 对性能的影响

对于第三,四场景来说,是比较正常的情况,不需要考虑性能问题。

对于第一种场景,InnoDB 会尽量避免,因为在这种情况下,整个系统不再接受更新。

但有时出现人为的配置错误,比如内存为 128 GB,innodb_io_capacity 设置为 20000 的实例。通常建议将 redo log 设置成 4 个 1GB 的文件。但由于配置错误,设置成 100M 的文件。

这里由于 redo log 设置的太小,很快就会被写满。write pos 一直追着 check point. 这时,系统只能停止所有更新,推进 checkpoint.

表现就是,磁盘 IO 很小,但是出现间歇性的性能下降。

对于第二种场景,内存不够用的情况,InnoDB 会用缓冲池(buffer pool)管理内存

内存页在缓冲池中会有三种状态:

  • 没用使用的数据页
  • 使用了,但是是干净页
  • 使用了,是脏页

每个数据页头部有LSN,8字节,每次修改都会变大。
对比这个 LSN 跟 checkpoint 的 LSN,比checkpoint小的一定是干净页

由于 InnoDB 的策略是尽可能使用内存,所以对于长时间运行的库来说,未被使用的页面很少。

当发现想读入的数据页没有在内存中时,必须到缓冲池申请数据页。并会把最久不用得数据页从内存中淘汰

  • 如果是干净页,直接释放使用
  • 如果是脏页,必须先刷盘,变成干净页才能复用

当时,如果在下面的情况进行刷脏页,会明显影响性能:

  • 要淘汰的脏页太多,导致查询响应时间较长。
  • 日志写满,更新被阻塞。

为了解决这个问题,InnoDB 使用控制脏页比例的机制,来避免上面的情况。

InooDB 控制刷脏页的策略

在 InnoDB 中,通过 innodb_io_capacity 参数,来告诉 InnoDB 目前主机的磁盘能力是多少,这个值建议设置成磁盘的 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 

由于 innodb_io_capacity 导致的性能问题很常见,比如有时系统吞吐量(TPS)很低,写入很慢,但是磁盘 IO 并不高。就有可能是该参数设置的不正确。例如,innodb_io_capacity 的值设置的很低,但是磁盘用的 SSD,导致 InooDB 认为系统能力很差,所以刷脏页特别慢。造成脏页累计,影响查询和更新性能。

InnoDB 在刷盘时主要考虑两个因素:

  1. 脏页的比例
  2. redo log 写盘速度

会通过这两个因素单独先算出两个数字。

innodb_max_dirty_pages_pct 脏页比例上限,默认 75%.

InnoDB 会根据脏页的比例(M),算出范围在 0 - 100 的数字。,过程称为 F1(M)

# M 脏页比例
F1(M)
{
  if M>=innodb_max_dirty_pages_pct then
      return 100;
  return 100*M/innodb_max_dirty_pages_pct;
}

除此之外,InnoDB 每次写入日志都会有一个序号 N. 然后根据 N 再算出一个 0 到 100 的数字,这个计算过程称为 F2(N)

N: 当前写入的序号和 checkpoint 对应序号之间的差值。

最后,根据 F1(M)和 F2(N)两个值,取其中较大的值为 R,之后引擎就可以按照 innodb_io_capacity * R 来控制刷脏页的速度。

所以无论是在查询,需要加载数据到内存数据页,而淘汰脏页。还是更新时,导致刷盘操作都有可能造成 MySQL 的性能下降。

为了避免这种情况,要合理的设置 innodb_io_capacity 的值,平时要多关注脏页比例,不让其接近 75%.

其中脏页比例可以通过下面的方式获取:

mysql> use performance_schema;
mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
select @a/@b;

初次之外,在一个查询操作进行时,如果需要 flush 脏页的话,如果这个该脏页的邻居也是脏页的话,就会把这个邻居一起刷掉,如果恰好旁边还是脏页的话,就会一直连坐。这时导致 flush 过慢的原因。

可以通过 innodb_flush_neighbors 来控制该行为,值为 1 打开上述机制,为 0 则关闭。

对于机械硬盘来说,是可以减少很多随机 IO ,因为机械硬盘 IOPS 一般就几百,减少随机 IO 就意味着性能提升。

但如果用 SSD 这类 IOPS 较高的设备,IOPS 往往不是瓶颈,关闭就好,减少 SQL 语句的响应时间。

在 8.0 中,已经默认是 0 了.

 

 

上一篇:设计模式的征途—23.解释器(Interpreter)模式


下一篇:c#实现单链表的尾插法和头插法