MySQL-23：redo log和binlog的写入机制

淡淡的烟草味﹌ 2022-12-27 14:21 136阅读 0赞

#### 23.1 binlong 的写入机制 ####

**binlog的写入逻辑：** 事务执行过程中，先把日志写到binlog cache中，事务提交的时候，再把binlog cache写到binlog文件中，并清空binlog cache。

同一个事务的binlog无法进行拆分，必须保证能一次性写入磁盘文件。

系统给binlog cache分配了内存，每个线程一个，binlog cache的大小由参数`binlog_cache_size`控制，如果超过设定参数大小，就暂存到磁盘中。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]

每个线程都有自己的binlog cache，但是共用同一份binlog文件。

上述示意图中有`write`和`fsync`步骤，它们的时机由参数`sync_binlog`控制：

1.  sync\_binlog = 0时， 表示每次提交事务都只 write，不 fsync；
2.  sync\_binlog = 1时， 表示每次提交事务都会执行write和 fsync；
3.  sync\_binlog = N时，表示每次提交事务都 write，但累积 N 个事务后才 fsync。

**write：** 将binlog cache中的日志写入binlog files(也就是文件系统的page cache)，这一步还是在内存中的。

**fsync：** 将page cache中的日志持久化到磁盘中。

将参数`sync_binlog`设为较大的值，可以提升性能，但是能考虑到日志丢失的问题，一般参数的设置值为100~1000之间的某个数值。在sync\_binlog = N的情况下，如果主机发生异常重启，会丢失最近的N个事务的binlog。

#### 23.2 redo log的写入机制 ####

redo log是先写入redo log buffer，再持久化到磁盘的。

redo log与binlog过程相似，看下面示意图：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]

从redo log buffer write到page cache中，再fsync到磁盘中。

redo log持久化到磁盘的时机由参数`innodb_flush_log_at_trx_commit`控制：

*  设置为0时： 表示每次事务提交时都会把 redo log 留在 redo log buffer 中 ;
 *  设置为1时： 表示每次事务提交时都将 redo log 直接持久化到磁盘；
 *  设置为2时： 表示每次事务提交时都只是把 redo log 写到 page cache。

InnoDB有一个后台线程，每隔1秒，就会把redo log buffer中日志，调用write写到文件系统的page cache，再调用fsync持久化到磁盘。

事务执行过程中的redo log也是直接写在redo log buffer中的，这些redo log也会被后台线程一起持久化磁盘。也就是说，一个没有提交的事务的redo log也会被持久化磁盘中。

下面两种场景也会将没有提交的事务的redo log写入磁盘：

1.  当 redo log buffer 占用的空间即将达到 innodb\_log\_buffer\_size 一半的时候，后台线程会主动写盘。 不过是从redo log buffer写入page cache，并不会调用fsync。
2.  并行的事务提交的时候，顺带将这个事务的 redo log buffer 持久化到磁盘。

如果把 innodb\_flush\_log\_at\_trx\_commit 设置成 1，那么 redo log 在 prepare 阶段就要持久化一次，因为有一个崩溃恢复逻辑是要依赖于 prepare 的 redo log，再加上 binlog 来恢复的。

每秒一次后台轮询刷盘，再加上崩溃恢复这个逻辑，InnoDB 就认为 redo log 在 commit 的时候就不需要 fsync 了，只会 write 到文件系统的 page cache 中就够了。

通常我们说 MySQL 的“双 1”配置，指的就是 sync\_binlog 和 innodb\_flush\_log\_at\_trx\_commit 都设置成 1。也就是说，一个事务完整提交前，需要等待两次刷盘，一次是 redo log（prepare 阶段），一次是 binlog。

**组提交机制：**

LSN ( 日志逻辑序列号 )是单调递增的，用来对应 redo log 的一个个写入点。每次写入长度为 length 的 redo log， LSN 的值就会加上 length。

如下图所示，是三个并发事务 (trx1, trx2, trx3) 在 prepare 阶段，都写完 redo log buffer，持久化到磁盘的过程，对应的 LSN 分别是 50、120 和 160。

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]

1.  trx1 是第一个到达的，会被选为这组的 leader；
2.  等 trx1 要开始写盘的时候，这个组里面已经有了三个事务，这时候 LSN 也变成了 160；
3.  trx1 去写盘的时候，带的就是 LSN=160，因此等 trx1 返回时，所有 LSN 小于等于 160 的 redo log，都已经被持久化到磁盘；
4.  这时候 trx2 和 trx3 就可以直接返回了。

一次组提交里面，组员越多，节约磁盘 IOPS 的效果越好。

在并发更新场景下，第一个事务写完 redo log buffer 以后，接下来这个 fsync 越晚调用，组员可能越多，节约 IOPS 的效果就越好。

MySQL中对这种机制有一个优化，将redo log prepare的fsync拖到binlog的write的后面，如下图所示：

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]

步骤3的执行速度很快，binlog的组提交机制并不能很好地发挥，可以通过设置 binlog\_group\_commit\_sync\_delay 和 binlog\_group\_commit\_sync\_no\_delay\_count 来实现。，两个参数之间是或的关系。

*  binlog\_group\_commit\_sync\_delay： 表示延迟多少微秒后才调用 fsync;
 *  binlog\_group\_commit\_sync\_no\_delay\_count： 表示累积多少次以后才调用 fsync。

**IO性能问题解决方法：**

1.  设置 binlog\_group\_commit\_sync\_delay 和 binlog\_group\_commit\_sync\_no\_delay\_count 参数，减少 binlog 的写盘次数。 方法原理就是故意的额外等待，提高组提交机制的效率。缺点是可能会增加语句响应时间。
2.  sync\_binlog 设置为大于 1 的值 ，缺点是可能会丢数据。
3.  将 innodb\_flush\_log\_at\_trx\_commit 设置为 2 ，缺点也是可能会丢数据。

**练习问题：**

1.  binlog的写入机制
2.  参数 binlog\_cache\_size
3.  binlog的写入策略：参数 sync\_binlog
4.  redo log的写入机制
5.  参数 innodb\_log\_buffer\_size
6.  redo log的写入策略：参数innodb\_flush\_log\_at\_trx\_commit
7.  没有提交的事务的redo log日志会持久化到磁盘的三种场景
8.  组提交机制
9.  MySQL对于组提交机制的优化
10. binlog 对于组提交机制的控制：参数binlog\_group\_commit\_sync\_delay 和binlog\_group\_commit\_sync\_no\_delay\_count
11. 常见的IO性能解决方法

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/db701213c6ec49d488f328e021597a1c.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/c495f700b06646fa8c257e63521b7e78.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 2]: /images/20221120/4a35fea121c54fbc9362b7ba75d927ed.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQwMDA4NTM1_size_16_color_FFFFFF_t_70_pic_center 3]: /images/20221120/64e0a019d4c54ed19b7c0c3a3803cca4.png