MySQL -- 数据可靠性

binlog的写入机制

1. 事务在执行过程中，先把日志写到binlog cache，事务提交时，再把binlog cache写到binlog file
2. 一个事务的binlog是不能被拆开的，不论事务多大，也要确保一次性写入
3. 系统会给每个线程分配一块内存binlog cache，由参数binlog_cache_size控制
   • 如果超过了binlog_cache_size，需要暂存到磁盘
4. 事务提交时，执行器把binlog cache里面的完整事务写入到binlog file，并清空binlog cache

-- 2097152 Bytes = 2 MB
mysql> SHOW VARIABLES LIKE '%binlog_cache_size%';
+-----------------------+----------------------+
| Variable_name         | Value                |
+-----------------------+----------------------+
| binlog_cache_size     | 2097152              |
| max_binlog_cache_size | 18446744073709547520 |
+-----------------------+----------------------+

写入过程

1. 每个线程都有自己的binlog cache，但共用一份binlog file
2. write：把日志写入到文件系统的page cache，但并没有将数据持久化到磁盘，速度比较快
3. fsync：将数据持久化到磁盘，fsync才会占用磁盘的IOPS

sync_binlog

mysql> SHOW VARIABLES LIKE '%sync_binlog%';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| sync_binlog   | 0     |
+---------------+-------+

1. sync_binlog=0，每次提交事务都只write，不fsync
2. sync_binlog=1，每次提交事务都会执行fsync
3. sync_binlog=N，每次提交事务都会write，但累计N个事务后才fsync
   • 一般为(100 ~ 1,000)，可以提高性能
   • 如果主机断电，会丢失最近的N个事务的binlog

redolog的写入机制

1. 事务在执行过程中，生成的redolog需要先写到redolog buffer
2. redolog buffer里面的内容，并不需要每次生成后都直接持久化到磁盘
   • 如果事务执行期间，MySQL异常重启，那么这部分日志丢失了
   • 由于事务没有提交，所以这时的日志丢失不会有什么影响
3. 在事务还未提交时，redolog buffer中的部分日志也是有可能持久化到磁盘的

redolog的状态

1. 红色部分：存在于redolog buffer中，物理上存在于MySQL进程的内存
2. 黄色部分：写到磁盘（write），但没有持久化（fsync），物理上存在于文件系统的page cache
3. 绿色部分：持久化到磁盘，物理上存在于hard disk
4. 日志写到redolog buffer是很快的，write到FS page cache也比较快，但fsync到磁盘的速度就会慢很多

redolog的写入策略

事务提交

mysql> show variables like '%innodb_flush_log_at_trx_commit%';
+--------------------------------+-------+
| Variable_name                  | Value |
+--------------------------------+-------+
| innodb_flush_log_at_trx_commit | 2     |
+--------------------------------+-------+

1. innodb_flush_log_at_trx_commit=0
   • 每次事务提交时都只是将redolog写入到redolog buffer（红色部分）
   • redolog只存在内存中，MySQL本身异常重启也会丢失数据，风险太大
2. innodb_flush_log_at_trx_commit=1
   • 每次事务提交时都将redolog持久化到磁盘（绿色部分）
   • 两阶段提交：redolog prepare -> 写binlog -> redolog commit
   • redolog prepare需要持久化一次，因为崩溃恢复依赖于prepare的redolog和binlog
   • redolog commit就不需要持久化（fsync）了，只需要write到FS page cache即可
   • 双1配置：一个事务完整提交前，需要2次刷盘：redolog prepare + binlog
     • 优化：组提交
3. innodb_flush_log_at_trx_commit=2
   • 每次事务提交时都将redolog写入到FS page cache（黄色部分）

后台刷新

1. 后台线程：每隔1秒，就会将redolog buffer中的日志，调用write写入到FS page cache，再调用fsync持久化到磁盘
2. 事务执行期间的redolog是直接写到redolog buffer，这些redolog也会被后台线程一起持久化到磁盘
   • 即一个未提交的事务的redolog也是有可能已经持久化到磁盘的

事务未提交

-- 16777216 Bytes = 16 MB
mysql> SHOW VARIABLES LIKE '%innodb_log_buffer_size%';
+------------------------+----------+
| Variable_name          | Value    |
+------------------------+----------+
| innodb_log_buffer_size | 16777216 |
+------------------------+----------+

1. 当redolog buffer占用的空间即将达到innodb_log_buffer_size的一半时，后台线程会主动写盘
   • 由于事务尚未提交，因此这个写盘动作是在write，不会调用fsync，停留在FS page cache
2. 在并行事务提交时，顺带将未提交事务的redolog buffer持久化到磁盘
   • 事务A执行到一半，有部分redolog在redolog buffer
   • 事务B提交，且innodb_flush_log_at_trx_commit=1，事务B要把redolog buffer里面的日志全部持久化到磁盘
   • 这时会带上事务A在redolog buffer里的日志一起持久化到磁盘

组提交

LSN

1. LSN：log sequence number
2. LSN是单调递增的，对应redolog的写入点
   • 每次写入长度为length的redolog，LSN就会加上length
3. LSN也会写入到数据页中，用来确保数据页不会被多次执行重复的redolog

样例

1. 三个并发事务处于prepare阶段：tx1、tx2、tx3
   • 都完成写入redolog buffer和持久化到磁盘的过程
   • 对应的LSN为50、120、160
2. tx1第一个到达，被选为组leader
3. 等trx1要开始写盘的时候，组内已经有3个事务，LSN变成了160
4. trx1带着LSN=160去写盘，等trx1返回时，所有LSN<160的redolog都已经持久化到磁盘
   • trx2和trx3可以直接返回

小结

1. 一次组提交里面，组员越多，节省磁盘的IOPS的效果越好
2. 在并发更新场景下，第1个事务写完redolog buffer后，接下来的fsync越晚调用，节省磁盘的IOPS的效果越好

binlog组提交

写binlog其实分两步：binlog cache -> (write) -> binlog file + binlog file -> (fsync) -> disk
MySQL为了让组提交效果更好，延后了fsync执行时机，两阶段提交细化如下

binlog也可以支持组提交了，但第3步执行很快，导致了binlog的组提交效果不如redolog的组提交效果

参数

mysql> SHOW VARIABLES LIKE '%binlog_group_commit_sync%';
+-----------------------------------------+-------+
| Variable_name                           | Value |
+-----------------------------------------+-------+
| binlog_group_commit_sync_delay          | 0     |
| binlog_group_commit_sync_no_delay_count | 0     |
+-----------------------------------------+-------+

1. binlog_group_commit_sync_delay：延迟多少微秒才调用fsync
2. binlog_group_commit_sync_no_delay_count：累计多少次之后才调用fsync
3. 两者关系：或，但当binlog_group_commit_sync_delay=0时，binlog_group_commit_sync_no_delay_count无效

WAL性能

1. redolog和binlog都是顺序写
2. 组提交机制：可以大幅降低磁盘的IOPS消耗

MySQL的IO瓶颈

1. 设置binlog_group_commit_sync_delay和binlog_group_commit_sync_no_delay_count
   • 故意等待，利用组提交减少IOPS消耗，同时可能会增加语句的响应时间，但没有丢数据风险
2. sync_binlog=N(100~1,000)
   • 主机断电会丢失binlog日志
3. innodb_flush_log_at_trx_commit=2
   • 主机断电会丢失数据
   • 2和0的性能接近，但设置为0（数据仅在redolog buffer），在MySQL异常重启时也会丢失数据

crash-safe的保证

1. 如果客户端收到事务成功的消息，事务就一定持久化了的
2. 如果客户端收到事务失败（主键冲突、回滚等）的消息，事务一定是失败的
3. 如果客户端收到执行异常的消息，应用需要重连后通过查询当前状态来继续后续的逻辑
   • 数据库只需要保证内部（数据与日志之间，主从之间）一致即可

参考资料

《MySQL实战45讲》