MySQL学习总结--WAL日志

WAL

• Write-Ahead Logging 技术，是指先写日志和内存，等不忙的时候再写磁盘，作用是将随机写变成顺序写，减少写盘消耗，还能用于实现 crash-safe。
• WAL 能减少磁盘写要得益于两个方面：
  • redo log 和 binlog 都是顺序写，磁盘的顺序写比随机写速度要快。
    • 随机写：本次写磁盘的扇区和上一次没啥关系，需要重新定位位置。
    • 顺序写：本次写磁盘的扇区就在上一次的下一个位置，不需要重新定位。
  • 组提交机制，可以大幅度降低磁盘的 IOPS 消耗。
• 术语
  • flush 一般是说刷脏页
  • purge 一般是指清除 undo log
  • merge 一般是指应用 change buffer

redo log VS binlog

• redo log 是 InnoDB 引擎特有的用于 crash-safe 的重做日志；binlog 是 MySQL 的 Server 层实现的归档日志，没有 crash-safe 的能力，所有引擎都可以使用。
• redo log 是循环写的，空间固定会用完，不能用做归档；binlog 是追加写入的，写到一定大小后会切换到下一个，并不会覆盖以前的日志，可以用做归档。
• redo log 是物理日志，记录的是“在某个数据页上做了什么修改”；binlog 是逻辑日志，记录的是这个语句的原始逻辑，例如 SQL 语句或数据行字段值的变更。

日志的两阶段提交

• 两阶段提交就是让 redo log 和 binlog 所记录的事务提交状态保持逻辑上的一致。否则，误删恢复时可能出现前后不一致，扩容从库时可能出现主从不一致。
• 核心：把写 redo log 拆分成两个阶段：prepare、commit 状态。
• 执行顺序：写 redo log(prepare 状态) -> 写 binlog -> 写 redo log(commit 状态)
• 崩溃恢复时，会按顺序扫描 redo log：
  • 如果碰到既有 prepare、又有 commit 的 redo log，就直接提交。
  • 如果碰到只有 prepare、而没有 commit 的 redo log，就拿两个日志的共同字段 XID 去 binlog 找对应的事务。
• 实现过程：写操作事务执行过程中，日志会分别写入 redo log buffer 和 binlog cache ，而执行 commit 命令 或 autocommit 时，才会发生日志的两阶段提交。
  • 更新内存数据页
  • 写入 redo log：把 redo log buffer 内容记为 prepare 状态，调用 write page cache。
  • 写入 binlog：把 binlog cache 内容，调用 write page cache。
  • 落盘 redo log：对 prepare 状态的 redo log 调用 fsync。为了利用 redo log 组提交机制节约磁盘 IOPS ，MySQL把落盘步骤往后放，让 fsync 尽量晚调用。
  • 落盘 binlog：对 binlog 调用 fsync。为了利用 binlog 组提交机制节约磁盘 IOPS ，MySQL把落盘步骤往后放，让 fsync 尽量晚调用。
  • 改写 redo log 状态：把 redo log 改为 commit 状态，调用 write page cache。
• 写入 VS 落盘
  • write 指的就是指把日志写入到文件系统的 page cache（文件系统的内存），此时数据还没有持久化到磁盘，所以速度比较快。
  • fsync 才是持久化到磁盘，一般认为 fsync 才占磁盘的 IOPS。

组提交机制（group commit）

• 一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。为了让一次 fsync 带的组员更多，MySQL 的优化是：拖时间。
• 日志逻辑序列号LSN（log sequence number）：是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。LSN 也会写到 InnoDB 的数据页中，来确保数据页不会被多次执行重复的 redo log。
  • 假设事务 trx1（LSN 50）是第一个提交的，会被选为这组的 leader。
  • 等事务 trx1 要开始写盘的时候，这个组里面已经有了三个事务，其中 trx3（LSN=160）。
  • 事务 trx1 去写盘的时候，带的是组里最后一个事务的 LSN=160，因此等 trx1 返回时，所有 LSN <= 160 的 redo log 都一并完成落盘了。
  • 等到事务 trx2 和 trx3 要去写盘时，就可以直接返回了。

redo log（重做日志）

• InnoDB 通过 redo log 实现 crash-safe 能力，即使数据库发生异常重启，已提交的事务都不会丢失。

  • 执行写操作语句时，先把变更内容记录到 redo log 中，并更新内存，语句就执行完成了。
  • 在系统比较空闲的时候，或者 redo log 快写满的时候，再将变更内容应用到磁盘数据页上。

• 内部实现

  • redo log 是固定大小的，从头开始写，写到末尾就又回到开头循环写。一般配置为一组 4 个文件，每个 1GB，文件名是ib_logfile+ 数字。
  • write pos 是当前记录的位置，一边写一边后移，写到第 3 号文件末尾后就回到 0 号文件开头。
  • checkpoint 是当前要擦除的位置，也是往后推移并且循环的，擦除记录前要把记录更新到数据文件。
  • write pos 和 checkpoint 之间的是剩余可用空间，用来记录新的操作。
  • 如果 write pos 追上 checkpoint，表示 redo log 已经满了，这时候不能再执行新的写操作，得停下来先擦掉一些记录（应用到磁盘数据页上），把 checkpoint 推进一下。

• redo log 的写入机制

  • 事务在执行过程中，生成的 redo log 是要先写到 redo log buffer 的。当事务中有多个写操作时，每执行一条语句，就改一次内存数据页，写一次 redo log buffer ，因为此时事务尚未提交 ，不能直接写到 redo log 文件里。最后提交事务时，才真正把日志写到 redo log 文件。
  • 由于 redo log 记录的是数据页的变化，因此线程可以共享 redo log buffer。
  • redo log buffer 里面的内容，可能会在事务还没提交时就先搭便车持久化了，但不是必然每次生成后都直接持久化。
  • redo log buffer 即使搭便车持久化了，也没有 prepare/ commit 状态的概念，只有该事务提交时写入 redo log 文件时才有状态的概念。
  • 日志写到 redo log buffer 是很快的，wirte 到 page cache 也差不多，但是 fsync 持久化到磁盘的速度就慢多了。

• write 和 fsync 的时机由参数 innodb_flush_log_at_trx_commit 控制

  • innodb_flush_log_at_trx_commit=0 ：每次事务提交时都只是把 redo log 留在 redo log buffer 中，由后台线程自动持久化。
  • innodb_flush_log_at_trx_commit=1 ：每次事务提交时都将 redo log 直接持久化到磁盘，真正实现 crash-safe，最安全。
  • innodb_flush_log_at_trx_commit=2 ：每次事务提交时都只是把 redo log 写入 page cache，而不 fsync 落盘。

• 后台线程自动持久化 redo log buffer 的场景

  • 后台线程每秒一次的轮询操作：把 redo log buffer 中的日志，调用 write 写到文件系统的 page cache，然后调用 fsync 持久化到磁盘。
  • redo log buffer 占用的空间即将达到innodb_log_buffer_size 一半的时候，后台线程会主动写盘。
  • 并行的事务提交的时候，顺带将这个事务的 redo log buffer 搭便车持久化到磁盘。

binlog（归档日志）

• 应用场景

  • 主从复制
  • 误删恢复：让数据库恢复到半个月内任意一秒的状态（定期做整库备份 + 保存最近半个月的所有 binlog）
    • 找到最近的一次全量备份，用这个备份恢复出来一个临时库。
    • 从备份的时间点开始，把 binlog 依次重放到误删表之前的那个时刻。
    • 把表数据从临时库取出来，按需要恢复到线上库去。

• binlog 的写入机制

  • 事务执行过程中，先把日志写到 binlog cache，事务提交的时候，再把 binlog cache 写到 binlog 文件中，并清空 binlog cache。
  • 一个事务的 binlog 不能被拆开，不论这个事务多大，也要确保一次性写入，否则在备库执行时就会被当做多个事务分段自行，破坏了原子性。而一个线程只能同时有一个事务在执行，因此系统给每个线程的 binlog cache 分配了一片内存 ，但是所有线程共用同一份 binlog 文件。
  • 参数binlog_cache_size 用于控制单个线程内 binlog cache 所占内存的大小，超出则需暂存到磁盘。
  • 一个事务的 binlog 日志不会被拆到两个 binlog 文件，即使当前文件写入这条 binlog 之后会超过设置的max_binlog_size值，也会等到这个事务的日志写完再rotate，所以会出现超过配置大小上限的binlog 文件。

• write 和 fsync 的时机由参数 sync_binlog 控制

  • sync_binlog=0：每次提交事务都只 write 就返回，由操作系统决定何时落盘，风险最大。
  • sync_binlog=1：每次提交事务都会执行 fsync，从而保证 binlog 不会丢失事务，最安全。
  • sync_binlog=N(100～1000) ：每次提交事务都只 write 就返回，但累积 N 个（组提交）事务时后台会一起 fsync。在出现 IO 瓶颈的场景里，可以提升性能。风险是，如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。

• binlog 组提交

  • binlog 的 write 和 fsync 的间隔时间短，导致能集合到一起持久化的 binlog 比较少，因此 binlog 的组提交的效果通常不如 redo log 的效果那么好。
  • 想提升 binlog 组提交的效果，可以设置两个参数，从而减少提交一组事务的总时间，因为减少了 fsync 的总次数。但是会增加个别事务响应客户端的时长，并且在高并发时，延迟有可能增加竞争，从而降低吞吐量。通常，设置延迟的好处大于坏处，但应始终进行调优以确定最佳设置。
    • binlog_group_commit_sync_delay：延迟多少微秒后才调用 fsync。
    • binlog_group_commit_sync_no_delay_count：在延迟到期之前，累积到多少次时，就立即调用 fsync。如果延迟指定为 0，则此选项不生效。
  • MySQL 官方文档–binlog 组提交

• sync_binlog 和 binlog_group_commit_sync_no_delay_count 的区别

  • sync_binlog=N：每个事务 write 后就响应客户端了。落盘是达到 N 次事务时后台 fsync。N 次事务之间主机掉电，数据丢失。
  • binlog_group_commit_sync_no_delay_count=N：当前事务 write 后，必须等凑够 N 个事务一起 fsync 后才能返回，会增加响应客户端的时间。但是一旦响应了，那么数据就一定持久化了。主机掉电时，数据是不会丢失的。

• 查看 binlog 相关命令

 # 查看 binlog 内容的 2 种方法
 mysql> show binlog events in 'master.000001';
 mysqlbinlog  -vv data/master.000001 --start-position=8900;
 # 查看binlog_format:
 mysql> show session variables like 'binlog_format';
 # 修改日志格式：
 mysql> set session binlog_format=statement;
 mysql> set session binlog_format=row;
 # 查看日志文件列表：
 mysql> show binary logs;
 # 根据查看到的日志文件使用显示日志事件的命令:
 mysql> show binlog events in 'XXX';

• 双 1 配置日志落盘参数
  • innodb_flush_log_at_trx_commit=1 ：每次事务提交时都将 redo log 直接持久化到磁盘，真正实现 crash-safe，最安全。
  • sync_binlog=1：每次提交事务都会执行 fsync，从而保证 binlog 不会丢失事务，最安全。
  • 双 1 配置时，一个事务完整提交前，需要等待两次 fsync 刷盘，一次是 redo log（prepare 状态），一次是 binlog。
  • 而 redo log 在改写为 commit 状态时不需要 fsync 刷盘 ，只要 write 到文件系统的 page cache 即可。
  • 原因一方面是 redo log 本身就有每秒一次的后台轮询刷盘，另一方面是崩溃恢复的逻辑定义为事务只要有 prepare 状态的 redo log + binlog 就视为已提交。
• 把线上生产库设置成【非双 1】的场景
  • innodb_flush_log_at_trx_commit=2 ：每次事务提交时都只是把 redo log 写入 page cache，而不 fsync 落盘。
  • sync_binlog=N(100～1000) ：每次提交事务都只 write 就返回，但累积 N 个（组提交）事务时后台会一起 fsync。在出现 IO 瓶颈的场景里，可以提升性能。风险是，如果主机发生异常重启，会丢失最近 N 个事务的 binlog 日志。
  • 业务高峰期。一般如果有预知的高峰期，DBA 会有预案，把主库设置成“非双 1”。
  • 备库延迟，为了让备库尽快赶上主库。
  • 用备份恢复主库的副本，应用 binlog 的过程，与备库延迟的场景类似。
  • 批量导入数据时。

binlog 的三种格式对比

• statement：binlog 记录的是 SQL 语句的原文

  • statment 格式节省空间和 IO 资源
  • statement 格式 + 某些 unsafe 语句 = 可能导致主备数据不一致
    • 例如 delete + limit 语句：在主库执行时用的是索引 a；而在备库执行时却使用了索引 t_modified。
    • RC 级别需要解决 binlog 日志顺序和语句实际执行顺序不一致的问题，因此只能把 binlog 格式设置为 row。
    • show warnings能看到相应的风险提示。

• row：binlog 记录的是数据行的内容变化

  • row 格式更耗费空间和 IO 资源（eg: delete 语句删掉 10 万行数据）
  • row 格式记录了真实删除行的主键 id，不存在主备数据不一致问题
  • row 格式 + binlog_row_image FULL = 可以用来恢复数据
    • delete、insert 或者 update 语句导致的数据操作错误，通过把 row 格式 binlog 内容转换一下，就可以恢复到操作之前的状态。MariaDB 的 Flashback工具就是基于此原理来回滚数据的。
    • 使用 binlog 恢复数据的方法
      • 错误方法：mysqlbinlog 解析日志，把里面的 statement 语句直接拷贝出来执行。其风险在于有些语句的执行结果是依赖于上下文命令的。
      • 标准方法：mysqlbinlog 解析日志+ 管道符发给 MySQL 执行 mysqlbinlog master.000001 --start-position=2738 --stop-position=2973 | mysql -h127.0.0.1 -P13000 -u$user -p$pwd;

• mixed：对 safe 语句记录的是 SQL，对 unsafe 语句记录的是 row

  • MySQL 自己会判断这条 SQL 语句是否可能引起主备不一致，如果有可能（eg: delete + limit），就用 row 格式，否则就用 statement 格式（eg: delete）。
  • 从而既能利用 statment 格式节省空间和 IO 资源的优点（eg: delete 语句删掉 10 万行数据），同时又避免了数据不一致的风险。
  • 因此，线上至少应该把 binlog 的格式设置为 mixed。
  • now()函数会被 MySQL 判断为 statement 格式，因为 binlog 会先记录SET TIMESTAMP=1546103491指定上下文时间，从而是安全的。

WAL 导致了内存脏页

• 脏页：当内存数据页跟磁盘数据页内容不一致的时候，称这个内存页为“脏页”。
• 干净页：当内存数据写入到磁盘后，内存和磁盘上的数据页的内容就一致了，称这个内存页为“干净页”。
• MySQL 偶尔很慢就是在刷脏页
  • MySQL 平时执行很快的更新操作，其实就是在写内存和日志（WAL），偶尔很慢一下就是在刷脏页。
  • 把内存脏页刷到磁盘就是数据最终的落盘操作，即落盘是从 buffer pool 更新过来的，而不是从 redo log，因为它并没有记录数据页的完整数据。
  • 在崩溃恢复场景中，InnoDB 如果判断到一个数据页可能丢失了更新，就会将它读到内存，然后让 redo log 更新内存内容。更新完成后，内存页变成脏页，通过刷脏页落盘。
• 刷脏页的四个场景
  • redo log 满了：这种情况是 InnoDB 要尽量避免的。因为写 redo log 是所有写操作的必经过程，一旦满了，所有的写操作都会阻塞，监控上的更新数会跌为 0。
  • 内存满了，要将淘汰的脏页写到磁盘：这种情况其实是常态，但如果一个查询要淘汰的脏页个数太多，也会导致查询的响应时间明显变长。
  • MySQL 空闲的时候
  • MySQL 正常关闭的时候
• 如何避免平时刷脏页太慢，导致内存脏页太多，或 redo log 写满
  • 需要合理地设置 innodb_io_capacity 的值，设置成磁盘的IOPS，避免过低（默认值为200，如果SSD建议改为 20000）。
  • 平时要多关注脏页比例，不要让它经常接近 75%。
  • SSD 关闭 innodb_flush_neighbors（MySQL 8.0 默认），机械硬盘可以开启。
• 高配机器 redo log 文件大小设置过低，会经常写满强刷脏页，导致数据库性能间歇性下降，但是主机磁盘压力很小
  • redo log 设置过低，每过一段时间就会写满，触发强制 flush 刷脏页，导致数据库性能间歇性下降，但是主机磁盘压力很小。正常情况应该是主机磁盘压力大，才导致数据库性能下降。
  • 此时连 change buffer 的优化也失效了。因为redo log 的 checkpoint 一直要往前推，会触发 merge 操作，然后又进一步地触发刷脏页操作。

InnoDB刷脏页的控制策略

• 控制全力刷脏页的速度上限，即告诉 InnoDB 主机的 IO 能力。参数 innodb_io_capacity，设置成磁盘的IOPS，默认值为200，如果主机磁盘用的是SSD，建议改为 20000。

  • 如果遇到 MySQL 的写入速度很慢，TPS 很低，但是数据库主机的 IO 压力并不大的问题，可以排查此参数是否过低。
  • mysql> show global variables like 'innodb_io_capacity';
  • 磁盘的 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

• 控制按全力速度的百分之几来刷脏页。为了避免平时刷太慢，导致内存脏页太多，或 redo log 写满，InnoDB 就根据这两个因素决定平时刷脏页速度百分比。R % = max{ func1(当前脏页比例)、func2(redo log 写盘的速度) }。参数 innodb_max_dirty_pages_pct 控制脏页比例上限，默认值是 75%。平时要多关注脏页比例，不要让它经常接近 75%。

  • 查看当前脏页比例
  • mysql> select VARIABLE_VALUE into @a from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_dirty';
  • mysql> select VARIABLE_VALUE into @b from global_status where VARIABLE_NAME = 'Innodb_buffer_pool_pages_total';
  • mysql> select @a/@b;

• 刷新相邻页面策略：连坐刷邻居可以减少很多随机 IO，对机械硬盘（几百 IOPS ）能大幅提升性能，对 SSD 建议关闭（MySQL 8.0 中默认是 0）。因为 IOPS 往往不是瓶颈，而“只刷自己”，就能更快地执行完必要的刷脏页操作，减少 SQL 语句响应时间。

  • innodb_flush_neighbors=1 连坐机制
  • innodb_flush_neighbors=0 不找邻居，自己刷自己

slow log（慢查询日志）

• 如何定位慢sql
  • 慢sql排名分析，排名靠前的sql并不一定是真正需要优化的。
  • 慢查询日志里面关注“变更以后新出现的”慢查询。
  • 一般先关注总扫描行数最多的语句。
  • 从show processlist看线索。
• 查看慢查询日志文件位置
  sql Show variables like "output";
• 扫描行数 Rows_examined
  Rows_examined：表示语句执行过程中扫描了多少行
  set long_query_time=0，将慢查询日志的时间阈值设置为 0，能把所有语句记录到 slow log 里。

general log（全量查询日志）

• general log 是关于 mysqld 正在做什么的通用记录。当客户端连接或断开连接时，服务器将信息写入日志，并记录从客户端接收的每个 SQL 语句。当您怀疑客户机中出现错误并希望准确地知道客户机向 mysqld 发送了什么内容时，general log 非常有用。
• mysql 官方文档—general log
• 其他参考文档