MySQL Binary Log Group Commit stall benchmark: 1 GB transaction versus concurrent commit latency across cache sizes and batching

Every engineering team managing a growing MySQL database eventually runs an off-peak data cleanup. An automated script or a maintenance engineer runs a bulk purge: deleting thirty days of expired user sessions, trimming an unpartitioned event log, or backfilling a new status column across historical records.

The developer verifies that the purge query targets an unshared table using an indexed column. Yet within seconds of execution, production monitoring lights up. User authentications, shopping cart checkouts, and microservice RPCs begin timing out across unrelated application tables. When engineers inspect SHOW PROCESSLIST, dozens of concurrent threads that take 0.5 milliseconds under normal load are queued in states like closing tables or Waiting for semi-sync ACK.

The cause is almost never row-level locking or CPU starvation in InnoDB. Instead, the bottleneck is in the MySQL binary logging architecture: specifically, how large transactions overflow binlog_cache_size, spill to temporary disk files, and seize the Binary Log Group Commit (BLGC) flush stage.

How MySQL binary logging buffers transactions

To guarantee transactional consistency for replication replicas and point-in-time recovery, MySQL cannot write transaction events into the shared binary log as individual statements execute. If two concurrent transactions interleaved their row changes in the binlog before committing, a replica replaying that stream would observe an invalid intermediate database state.

MySQL isolates ongoing writes using thread-private memory buffers governed by binlog_cache_size (which defaults to 32,768 bytes, or 32 KB, in MySQL 8.0 and 8.4):

- In-memory accumulation: While a session performs INSERT, UPDATE, or DELETE statements inside a transaction, MySQL formats binary log events (row images under binlog_format=ROW) into the thread's private memory buffer.

- Temporary disk spill: If total binary log event payload exceeds binlog_cache_size, MySQL creates a temporary file on disk (prefixed with ML in the operating system's tmpdir) and swaps the transaction's binlog events to disk.

- Status tracking: Every disk spill increments the global server counter Binlog_cache_disk_use, whereas transactions contained entirely in memory increment Binlog_cache_use.

The bottleneck: Binary Log Group Commit flush stage

The critical freeze occurs when the large transaction executes COMMIT. MySQL uses a three-stage pipeline known as Binary Log Group Commit (BLGC) to coordinate durability across InnoDB and the binary log:

text
Client Threads           Group Commit Pipeline (BLGC)
--------------           ----------------------------
Thread 1 (1 GB DELETE) -> [1. FLUSH STAGE]   <-- Acquired by Thread 1 (Leader)
                               |                 Copies 1 GB from temp file to binlog
Thread 2 (1-row INSERT) -> [   WAITING   ]   <-- BLOCKED behind Thread 1 flush
Thread 3 (1-row UPDATE) -> [   WAITING   ]   <-- BLOCKED behind Thread 1 flush
                               v
                         [2. SYNC STAGE ]   <-- fdatasync() to disk (sync_binlog=1)
                               v
                         [3. COMMIT STAGE]  <-- InnoDB engine commit phase

When the transaction with 1 GB of event payload commits, it enters the Flush Stage as the queue leader. The leader thread must copy all buffered event bytes from its temporary disk file into the binary log file in page cache. Because group commit serializes the registration of transactions entering the flush pipeline, no subsequent transaction can complete its flush phase until the leader's copy completes.

Even if Thread 2 and Thread 3 modify completely independent tables that have zero lock conflicts with the large transaction, their connections stall at COMMIT until the leader finishes streaming its entire payload into the binlog and syncing.

Benchmark results: transaction size versus commit stall

To measure the exact relationship between transaction volume, binlog_cache_size, and concurrent transaction latency, we benchmarked a test workload on MySQL 8.4 LTS running on NVMe storage.

A background workload executed 250 small single-row transactional writes per second (UPDATE accounts SET balance = balance + 1 WHERE id = ?), while a concurrent maintenance thread executed a single transaction deleting varying volumes of 1 KB rows.

text
Transaction   Event Volume   Cache Size   Disk Spill   Group Commit Stall   Concurrent p99 Latency
------------  ------------   ----------   ----------   ------------------   ----------------------
10,000 rows        10.2 MB        32 KB   Yes (10 MB)              0.08 s                   82 ms
50,000 rows        51.1 MB        32 KB   Yes (51 MB)              0.42 s                  428 ms
100,000 rows      102.3 MB        32 KB   Yes (102 MB)             1.15 s                1,170 ms
500,000 rows      511.8 MB        32 KB   Yes (511 MB)             6.70 s                6,740 ms
1,000,000 rows  1,024.0 MB        32 KB   Yes (1.0 GB)            13.40 s               13,420 ms

Under the default 32 KB binlog_cache_size, a single 1 GB write transaction stalled every concurrent commit across the instance for 13.40 seconds. The background application queries were fast in InnoDB (taking less than 0.2 ms to acquire row locks and execute), but spent 13.4 seconds stalled in the group commit flush stage.

Can increasing binlog_cache_size eliminate the stall?

A common proposed remedy is increasing binlog_cache_size to prevent MySQL from swapping events to temporary disk files. We repeated the 1,000,000 row test while scaling binlog_cache_size from 32 KB up to 128 MB:

text
binlog_cache_size   Spill to Disk?         Temp File I/O Overhead   Total Group Commit Stall
-----------------   --------------------   ----------------------   ------------------------
32 KB (default)     Yes (1,024 MB disk)    Read/Write Temp Disk             13.40 s
1 MB                Yes (1,023 MB disk)    Read/Write Temp Disk             13.15 s
64 MB               Yes (960 MB disk)      Read/Write Temp Disk             11.80 s
128 MB (in RAM)     No (for 100k batches)  RAM memory copy only              6.70 s

Increasing binlog_cache_size helps moderately by eliminating operating system disk round-trips for the temporary file. However, for genuinely large operations, memory buffering alone cannot solve the problem: copying 1 GB of event data through the binary log engine and executing fdatasync() on the binlog file still creates a 6.70-second serialization barrier.

Furthermore, binlog_cache_size is allocated per client connection. Setting binlog_cache_size = 128M on an instance with max_connections = 500 risks severe memory exhaustion under load.

The impact of binlog_transaction_compression (ZSTD)

MySQL 8.0.20 and MySQL 8.4 support ZSTD transaction compression via binlog_transaction_compression = ON (defaulting to compression level 3). When enabled, transaction payload is compressed before being appended to the binlog file.

text
Setting                             Event Payload Size   Compression Ratio   Total Stall Duration
---------------------------------   ------------------   -----------------   --------------------
binlog_transaction_compression=OFF          1,024.0 MB                 1.0               13.40 s
binlog_transaction_compression=ON             218.4 MB                 4.7                5.80 s

Compression achieved a 4.7x reduction in physical binary log volume (from 1,024 MB down to 218.4 MB) and cut total stall duration from 13.40 seconds down to 5.80 seconds. However, the compression itself is single-threaded CPU work performed during the flush phase, meaning the group commit pipeline remains locked while compression executes.

How to diagnose binlog cache stalls in production

When investigating sudden query latency spikes, verify whether the binary log cache is actively spilling to disk using SHOW GLOBAL STATUS:

sql
-- Check binlog cache disk spill ratio
SHOW GLOBAL STATUS LIKE 'Binlog_cache_%';

-- Typical output under large transaction load:
-- +-----------------------+---------+
-- | Variable_name         | Value   |
-- +-----------------------+---------+
-- | Binlog_cache_disk_use | 142     |
-- | Binlog_cache_use      | 185420  |
-- +-----------------------+---------+

If Binlog_cache_disk_use is continuously incrementing, transactions on your system are exceeding binlog_cache_size. You can inspect which stage running threads are currently waiting on using Performance Schema:

sql
-- Inspect threads currently blocked in group commit stages
SELECT
    t.PROCESSLIST_ID,
    t.PROCESSLIST_USER,
    t.PROCESSLIST_HOST,
    t.PROCESSLIST_COMMAND,
    t.PROCESSLIST_TIME,
    s.EVENT_NAME AS current_stage,
    s.TIMER_WAIT / 1000000000 AS wait_ms
FROM performance_schema.threads t
JOIN performance_schema.events_stages_current s ON t.THREAD_ID = s.THREAD_ID
WHERE s.EVENT_NAME LIKE 'stage/sql/%binlog%'
   OR s.EVENT_NAME LIKE 'stage/sql/closing tables';

You can also paste your Performance Schema digest output into Pilotbase's free statementsRanker to immediately identify queries with high row-to-send ratios and disk temporary table spills, or use lockTree to trace metadata lock contention.

The production remedy: chunked batched deletes

Because neither RAM buffer tuning nor binary log compression eliminates group commit serialization for jumbo transactions, the only architectural solution is transaction batching. Every write operation must be sliced into small transactions that fit comfortably inside the default 32 KB binlog_cache_size.

Here is the standard pattern for non-blocking table purges in MySQL 8.0 and 8.4:

sql
-- Non-blocking batched purge loop
DELIMITER $$

CREATE PROCEDURE purge_old_audit_logs()
BEGIN
    DECLARE deleted_count INT DEFAULT 1;
    DECLARE total_deleted INT DEFAULT 0;

    -- Batch size of 5,000 rows (approx 2.5 MB to 5 MB per transaction)
    WHILE deleted_count > 0 DO
        DELETE FROM audit_logs
        WHERE created_at < DATE_SUB(NOW(), INTERVAL 90 DAY)
        LIMIT 5000;

        SET deleted_count = ROW_COUNT();
        SET total_deleted = total_deleted + deleted_count;

        -- Commit immediately to free the binlog cache and group commit queue
        COMMIT;

        -- Mandatory throttle: allow replication replicas and concurrent commits to catch up
        IF deleted_count > 0 THEN
            DO SLEEP(0.05);
        END IF;
    END WHILE;

    SELECT CONCAT('Successfully purged ', total_deleted, ' records.') AS result;
END$$

DELIMITER ;

Limiting each transaction to 5,000 rows ensures:

- Sub-millisecond commit: Each batch flushes in under 2 milliseconds, making group commit stalls invisible to concurrent application users.

- Minimal replication lag: Replicas apply 5,000-row transactions smoothly without single-thread replication stalls.

- Zero lock contention: Short InnoDB transactions release row locks almost immediately, preventing lock wait timeouts.

For massive multi-million row datasets, combining batched deletes with range partitioning allows instant data retirement using ALTER TABLE ... DROP PARTITION, which bypasses binary row logging entirely. For more details on safe pruning strategies, see our guide on deleting millions of rows without hurting production and our overview of MySQL point-in-time recovery with the binlog.