Boost MySQL Performance: Group Commit, DDL Fast‑Fail, GTID & Auto‑Increment Fixes

MySQL Group Commit Optimization

Before MySQL 5.6, Binlog writing and InnoDB commit were fully serialized, causing a bottleneck when sync_binlog=1. The sequence was:

InnoDB prepare (holds prepare_commit_mutex);
write/sync Binlog;
InnoDB commit (writes COMMIT marker and releases prepare_commit_mutex).

MariaDB introduced a Binlog Group Commit that queues threads, letting a leader write the Binlog for the whole group, reducing the global lock. Oracle MySQL 5.6 adopted a similar idea but split the process into three concurrent stages: flush, sync, and commit, each allowing only one thread at a time.

After enabling Group Commit, the meaning of sync_binlog changes: a value of 1000 now means “fsync after 1000 transaction groups” rather than 1000 individual transactions.

XA Recovery Logic

With binary logging enabled, MySQL uses MySQL_BIN_LOG as the XA coordinator. The recovery steps are:

Scan the last Binlog file and extract XIDs.

InnoDB maintains a list of prepared transactions; each XID is compared with the Binlog list. If present, the transaction is committed; otherwise it is rolled back.

This ensures that any transaction that has reached the InnoDB PREPARE state and whose XID appears in the Binlog can be recovered after a crash without an explicit redo‑log write at commit time.

Optimization of the First Stage

To avoid the mutex contention of log_sys->mutex, the first stage of Group Commit was modified:

InnoDB PREPARE records the current LSN in the thread context.

The leader enters the flush stage, gathers the queue, and determines the maximum LSN.

The redo log is written and synced up to that LSN.

Binlog is written and the subsequent sync/commit steps are performed.

This delayed redo‑log write groups multiple writes together, reducing lock contention. The change was back‑ported to MySQL 5.7.6 (bug #73202) with the release note stating that concurrent transactions are now grouped before disk synchronization when innodb_flush_log_at_trx_commit=1, improving performance.

Performance Data for Group Commit

Using sysbench (update_non_index.lua) on 100 tables with 100 k rows each, innodb_flush_log_at_trx_commit=2 and sync_binlog=1000, the following throughput was observed (TPS):

Threads   Native   Modified
32        25600    27000
64        30000    35000
128       33000    39000
256       29800    38000

DDL Fast‑Fail Feature

Online services often suffer when a DDL statement blocks because it cannot acquire an exclusive MDL lock, causing all other sessions to wait. Alibaba Cloud RDS adds a NO_WAIT/WAIT 1 clause to DDL statements. If the lock cannot be obtained, the client receives an error after either immediate failure or a one‑second wait.

The implementation simply attempts to acquire the exclusive MDL lock; on failure it returns

ERROR: Lock wait timeout exceeded; try restarting transaction

. The feature also supports RENAME, TRUNCATE, DROP, OPTIMIZE, and CREATE INDEX operations.

Comparison with Oracle

Oracle 10g would block indefinitely, leading to application “avalanche” effects. Oracle 11g introduced the DDL_LOCK_TIMEOUT parameter, allowing configurable wait times similar to MySQL’s fast‑fail approach.

GTID Performance Issue and Fix

MySQL 5.6 introduced GTID. In the automatic allocation path, GTID assignment occurs during the first Group Commit stage (flush). After commit, the GTID is moved from gtid_owned to gtid_executed, requiring a global lock that becomes a bottleneck at high concurrency.

The optimization removes the need to maintain gtid_owned for automatic allocation: the GTID is directly added to gtid_executed, eliminating the lock‑heavy cleanup step. If Binlog writing fails, the GTID is removed from gtid_executed, a rare case.

GTID Performance Data

Using the same sysbench workload, the throughput before and after the change was:

Threads   Native   Modified
32        24500    25000
64        27900    29000
128       30800    31500
256       29700    32000
512       29300    31700
1024      27000    31000

The optimized version maintains stable TPS as concurrency grows.

InnoDB Auto‑Increment Duplicate‑Value Bug

When MySQL restarts, InnoDB’s in‑memory auto‑increment counter is lost. The server recomputes the next value using SELECT MAX(id)+1 FROM t1, which can reuse a previously generated value, leading to duplicate primary keys after a restart.

MyISAM stores the counter persistently in the .MYI file, avoiding the issue.

Fix Implemented in InnoDB

The solution persists the maximum auto‑increment value in the clustered index root page’s trx_id field (an unused 8‑byte area). Each time the counter changes, the new value is written there, generating a tiny redo log entry without undo logging. This makes the persistence cost negligible (<1% performance impact).

A new system variable innodb_autoinc_persistent (ON/OFF) controls this behavior, and innodb_autoinc_persistent_interval controls how often the value is flushed (e.g., every 100 inserts).

Performance Impact of Persistence

Benchmarks with sysbench insert tests show:

set global innodb_autoinc_persistent=off;  tps: 22199
set global innodb_autoinc_persistent=on;   tps: 22003

The overhead is below 1%.

Limitations

If innodb_autoinc_persistent=on and innodb_autoinc_persistent_interval>1, the persisted value may be larger than the actual maximum after a restart, causing gaps but no duplicates.

Frequent changes to auto_increment_increment can make the persisted value inaccurate because the interval calculation does not account for the increment size.

To enable persistence permanently, add innodb_autoinc_persistent=on to the MySQL configuration file.