Understanding MySQL Binlog Group Commit: Flush, Sync, and Commit Phases

This article is part of the "Illustrated MySQL" series produced by the Aike Sheng R&D team, aiming to provide high‑quality technical content on MySQL internals.

Premise: The discussion assumes MySQL crash‑safe parameters are set to sync_binlog=1 and innodb_flush_log_at_trx_commit=1 .

Background: It introduces the Write‑Ahead Log (WAL) concept, the role of the redo log as a WAL implementation, and the purpose of group commit in reducing disk‑I/O bottlenecks.

When binary logging (binlog) is disabled, the redo log group commit becomes the primary performance bottleneck; MySQL mitigates this by merging multiple flush operations into a single one.

When binlog is enabled, MySQL adopts a two‑phase commit with binlog as the transaction coordinator, turning binlog into a new bottleneck. To alleviate this, MySQL adds a binlog group commit that consists of three stages: Flush, Sync, and Commit.

Flush stage: MySQL acquires the transaction group from the queue, flushes the redo log’s prepare data to disk, writes binlog data to the OS buffer (without guaranteeing durability), and provides redo‑log group commit. If a crash occurs after this step, the coordinator binlog may lack the transaction record, causing MySQL to roll back the group on restart.

Sync stage: Controlled by parameters binlog_group_commit_sync_delay (wait N µs before flushing) and binlog_group_commit_sync_no_delay_count (skip the delay when the queue reaches N transactions). This stage supports binlog group commit; if a crash occurs after sync, the redo log data already flushed ensures transaction safety.

Commit stage: MySQL again acquires the transaction group, sequentially commits the prepared redo‑log transactions in InnoDB, and does not perform additional flushing because the previous Flush stage already guarantees durability. The commit queue passes transactions to the engine, maximizing overall group‑commit efficiency.

Defect analysis: In MySQL 5.7.19, a bug in the Sync stage caused excessive waiting when binlog_group_commit_sync_delay was not a multiple of 10, leading to long‑running SQL statements. This issue was fixed in MySQL 5.7.24 and 8.0.13.