MySQL学习总结--WAL日志
- WAL
- redo log VS binlog
- 日志的两阶段提交
- 组提交机制(group commit)
- redo log(重做日志)
- binlog(归档日志)
- binlog 的三种格式对比
- WAL 导致了内存脏页
- InnoDB刷脏页的控制策略
- slow log(慢查询日志)
- general log(全量查询日志)
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,则此选项不生效。
- 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 非常有用。