最后更新: 2019 年 10 月 28 日 13:35:41

本篇文章属于个人备忘录, 主要内容来自: 极客时间《MySQL 实战 45 讲》的第 12 讲 – 为什么我的 MySQL 会“抖”一下

WAL(Write-Ahead Loggin)

WAL 是预写式日志, 关键点在于先写日志再写磁盘.

在对数据页进行修改时, 通过将 ” 修改了什么 ” 这个操作记录在日志中, 而不必马上将更改内容刷新到磁盘上, 从而 将随机写转换为顺序写, 提高了性能.

但由此带来的问题是, 内存中的数据页会和磁盘上的数据页内容不一致, 此时将内存中的这种数据页称为 脏页

Redo Log(重做日志)

这里的日志指的是 Redo Log(重做日志), 这个日志是循环写入的.

它记录的是在某个数据页上做了什么修改, 这个日志会携带一个 LSN, 同时每个数据页上也会记录一个 LSN(日志序列号).

这个日志序列号 (LSN) 可以用于数据页是否是脏页的判断, 比如说 write pos 对应的 LSN 比某个数据页的 LSN 大, 则这个数据页肯定是干净页, 同时当脏页提前刷到磁盘时, 在应用 Redo Log 可以识别是否刷过并跳过.

这里有两个关键位置点:

• write pos 当前记录的位置, 一边写以便后移.
• checkpoint 是当前要擦除的位置, 擦除记录前要把记录更新到数据文件.

脏页

当内存数据页和磁盘数据页内容不一致的时候, 将内存页称为 ” 脏页 ”.
内存数据页写入磁盘后, 两边内容一致, 此时称为 ” 干净页 ”.
将内存数据页写入磁盘的这个操作叫做 ” 刷脏页 ”(flush).

InnoDB 是以缓冲池 (Buffer Pool) 来管理内存的, 缓冲池中的内存页有 3 种状态:

• 未被使用
• 已被使用, 并且是干净页
• 已被使用, 并且是脏页

由于 InnoDB 的策略通常是尽量使用内存, 因此长时间运行的数据库中的内存页基本都是被使用的, 未被使用的内存页很少.

刷脏页(flush)

时机

刷脏页的时机:

1. Redo Log 写满了, 需要将 checkpoint 向前推进, 以便继续写入日志

   checkpoint 向前推进时, 需要将推进区间涉及的所有脏页刷新到磁盘.

2. 内存不足, 需要淘汰一些内存页 (最久未使用的) 给别的数据页使用.

   此时如果是干净页, 则直接拿来复用.

   如果是脏页, 则需要先刷新到磁盘(直接写入磁盘, 不用管 Redo Log, 后续 Redo Log 刷脏页时会判断对应数据页是否已刷新到磁盘), 使之成为干净页再拿来使用.

3. 数据库系统空闲时

   当然平时忙的时候也会尽量刷脏页.

4. 数据库正常关闭

   此时需要将所有脏页刷新到磁盘.

InnoDB 需要控制脏页比例来避免 Redo Log 写满以及单次淘汰过多脏页过多的情况.

Redo Log 写满

这种情况尽量避免, 因此此时系统就不接受更新, 所有更新语句都会被堵住, 此时更新数为 0.

对于敏感业务来说, 这是不能接受的.

此时需要将 write pos 向前推进, 推进范围内 Redo Log 涉及的所有脏页都需要 flush 到磁盘中.

Redo Log 设置过小或写太慢的问题: 此时由于 Redo Log 频繁写满, 会导致频繁触发 flush 脏页, 影响 tps.

内存不足

这种情况其实是常态.

当从磁盘读取的数据页在内存中没有内存时, 就需要到缓冲池中申请一个内存页, 这时候根据 LRU(最久不使用)就需要淘汰掉一个内存页来使用.

此时淘汰的是脏页, 则需要将脏页刷新到磁盘, 变成干净页后才能复用.

注意, 这个过程 Write Pos 位置是不会向前推进的.

当一个查询要淘汰的脏页数太多, 会导致查询的响应时间明显变长.

策略

InnoDB 控制刷脏页的策略主要参考:

• 脏页比例

  当脏页比例接近或超过参数 innodb_max_dirty_pages_pct 时, 则会全力, 否则按照百分比.

• redo log 写盘速度

  N = (write pos 位置的日志序号 – checkpoint 对应序号), 当 N 越大, 则刷盘速度越快.

最终刷盘速度取上述两者中最快的.

参数 innodb_io_capacity

InnoDB 有一个关键参数: innodb_io_capacity, 该参数是用于告知 InnoDB 你的磁盘能力, 该值通常建议设置为磁盘的写 IOPS.

该参数在 MySQL 5.5 及后续版本才可以调整.

测试磁盘的 IOPS:

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

注意, 上面的 -filename 要指定具体的文件名, 千万不要指定分区, 否则会导致分区不可用, 需要重新格式化.

innodb_io_capacity 一般参考 写能力的 IOPS

innodb_io_capacity 设置过低导致的性能问题案例:

MySQL 写入速度很慢, TPS 很低, 但是数据库主机的 IO 压力并不大.

当innodb_io_capacity 设置过小时, InnoDB 会认为磁盘性能差, 导致刷脏页很慢, 甚至比脏页生成速度还慢, 就会造成脏页累积, 影响查询和更新性能.

innodb_io_capacity 大小设置:

• 配置小, 此时由于 InnoDB 认为你的磁盘性能差, 因此刷脏页频率会更高, 以此来确保内存中的脏页比例较少.
• 配置大, InnoDB 认为磁盘性能好, 因此刷脏页频率会降低, 抖动的频率也会降低.

参数innodb_max_dirty_pages_pct

innodb_max_dirty_pages_pct 指的是脏页比例上限(默认值是 75%), 内存中的脏页比例越是接近该值, 则 InnoDB 刷盘速度会越接近全力.

如何计算内存中的脏页比例:

show global status like 'Innodb_buffer_pool_pages%';

脏页比例 = 100 * Innodb_buffer_pool_pages_dirty / Innodb_buffer_pool_pages_total 的值

参数 innodb_flush_neighbors

当刷脏页时, 若脏页旁边的数据页也是脏页, 则会连带刷新, 注意这个机制是会蔓延的.

当 innodb_flush_neighbors=1 时开启该机制, 默认是 1, 但在 MySQL 8.0 中默认值是 0.

由于机械硬盘时代的 IOPS 一般只有几百, 该机制可以有效减少很多随机 IO, 提高系统性能.

但在固态硬盘时代, 此时 IOPS 高达几千, 此时 IOPS 往往不是瓶颈, “ 只刷自己 ” 可以更快执行完查询操作, 减少 SQL 语句的响应时间.

如果 Redo Log 设置太小

这里有一个案例:

测试在做压力测试时, 刚开始 insert, update 很快, 但是一会就变慢且响应延迟很高.

↑ 出现这种情况大部分是因为 Redo Log 设置太小引起的.

因为此时 Redo Log 写满后需要将 checkpoint 前推, 此时需要刷脏页, 可能还会连坐(innodb_flush_neighbors=1), 数据库 ” 抖 ” 的频率变高.

其实此时内存的脏页比例可能还很低, 并没有充分利用到大内存优势, 此时需要频繁 flush, 性能会变差.

同时, 如果 Redo Log 中存在 change buffer, 同样需要做相应的 merge 操作, 导致 change buffer 发挥不出作用.