最后更新: 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 发挥不出作用.