group commit有什么好处?使用时需要注意什么?

若事务为非只读事务， 则每次事务提交时需要进行一次fsync操作， 以此保证重做日志都已经写入磁盘 。 当数据库发 生宕机时， 可以通过重做日志进行恢复 。 虽然固态硬盘的出现提高了磁盘的性能， 然而磁盘的fsync性能是有限的。 为了提高磁盘fsync的效率， 当前数据库都提供了group commit的功能， 即一次fsync可以刷新确保多个事务日志被 写入文件 。对于InnoDB存储引擎来说， 事务提交时会进行两个阶段的操作：

1） 修改内存中事务对应的信息， 并且将日志写入重做日志缓冲。

2） 调用fsync将确保日志都从重做日志缓冲写入磁盘。

步骤2） 相对步骤1） 是一个较慢的过程， 这是因为存储引擎需要与磁盘打交道 。但当有事务进行这个过程时， 其他 事务可以进行步骤1） 的操作， 正在提交的事物完成提交操作后， 再次进行步骤2） 时， 可以将多个事务的重做日志 通过一次fsync刷新到磁盘， 这样就大大地减少了磁盘的压力， 从而提高了数据库的整体性能 。对于写入或更新较为 频繁的操作， group commit的效果尤为明显。

然而在InnoDB1.2版本之前， 在开启二进制日志后， InnoDB存储引擎的group commit功能会失效， 从而导致性能 的下降 。 并且在线环境多使用replication环境， 因此二进制日志的选项基本都为开启状态， 因此这个问题尤为显著。

导致这个问题的原因是在开启二进制日志后， 为了保证存储引擎层中的事务和二进制日志的一致性， 二者之间使用 了两阶段事务， 其步骤如下：

【mysql我能讲两小时035】group commit有什么好处?使用时需要注意什么?_MySQL

为了保证MySQL数据库上层二进 制日志的写入顺序和InnoDB层的 事务提交顺序一致， MySQL数据 库内部使用了prepare_commit_mutex这个锁。 但是在启用这个锁之后， 步骤3） 中的步骤a） 步不可以在其他事务 执行步骤b） 时进行， 从而导致了 group commit失效。

  1. 当事务提交时InnoDB存储引擎进行prepare操作。
  2. MySQL数据库上层写入二进制日志。
  3. InnoDB存储引擎层将日志写入重做日志文件。
  4. 修改内存中事务对应的信息， 并且将日志写入重做日志缓冲。
  5. 调用fsync将确保日志都从重做日志缓冲写入磁盘。

一旦步骤2） 中的操作完成， 就确保了事务的提交， 即使在执行步骤3） 时数据库发生了宕机 。此外需要注意的是 ， 每个步骤都需要进行一次fsync操作才能保证上下两层数据的一致性 。 步骤2） 的fsync由参数sync_binlog控制， 步骤3） 的fsync由参数innodb_flush_log_at_trx_commit控制 。 因此上述整个过程如下图所示。

这个问题最早在2010年的MySQL数据库大会中提出， Facebook MySQL技术组， Percona公司都提出过解决方案 。 最后由MariaDB数据库 的开发人员Kristian Nielsen完成了最终的“完美”解决方案 。在这种情况下， 不但MySQL数据库上层的二进制日志写入是group commit的， InnoDB存储引擎层也是group commit的 。 此外还移除了原先的锁prepare_commit_mutex， 从而大大提高了数据库的整体性 。 MySQL 5.6采用了类似的实现方式， 并将其称为Binary Log Group Commit （BLGC） 。

MySQL 5.6 BLGC的实现方式是将事务提交的过程分为几个步骤来完成， 如下图所示。

【mysql我能讲两小时035】group commit有什么好处?使用时需要注意什么?_mysql_02

在MySQL数据库上层进行提交时首先按顺序将其放入一个队列中， 队列中的第一个事务称为leader， 其他事务称为follower， leader控 制着follower的行为 。 BLGC的步骤分为以下三个阶段：

  • Flush阶段， 将每个事务的二进制日志写入内存中。
  • Sync阶段， 将内存中的二进制日志刷新到磁盘， 若队列中有多个事务， 那么仅一次fsync操作就完成了二进制日志的写入， 这就是 BLGC。
  • Commit阶段， leader根据顺序调用存储引擎层事务的提交， InnoDB存储引擎本就支持group commit， 因此修复了原先由于锁 prepare_commit_mutex导致group commit失效的问题。

参数binlog_max_flush_queue_time用来控制Flush阶段中等待的时间， 即使之前的一组事务完成提交， 当前一组的事务也不马上进入 Sync阶段， 而是至少需要等待一段时间 。 这样做的好处是group commit的事务数量更多， 然而这也可能会导致事务的响应时间变慢 。 该参数的默认值为0， 且推荐设置依然为0 。 除非用户的MySQL数据库系统中有着大量的连接 （如100个连接） ， 并且不断地在进行事务 的写入或更新操作。