How Group Commit, DDL Fast Fail, and GTID Optimizations Boost MySQL Performance

MySQL Performance Optimization – Group Commit

Before MySQL 5.6, binlog writing and InnoDB commit were serialized, making sync_binlog=1 a bottleneck because the second step held a global lock.

MariaDB introduced a binlog group‑commit scheme that queues threads, letting a leader write the binlog for the whole group; Percona adopted this. Oracle MySQL 5.6 added a similar three‑stage process: flush (write binlog cache to file), sync (fsync), and commit (engine‑level transaction commit), with only one thread per stage.

Tip: After enabling group commit, sync_binlog represents the number of transaction groups, not individual transactions.

1. Scan the last binlog file to extract XIDs. 2. InnoDB compares prepared XIDs with binlog XIDs; if present, commit, otherwise roll back.

Problem: each transaction must ensure its redo log is written and synced before binlog write, causing log_sys‑>mutex contention.

Optimization: Modify the first stage of group commit to write the redo log after the leader has collected the maximum LSN, reducing mutex competition.

Step1. InnoDB Prepare, record current LSN in thd;<br/>Step2. Enter flush stage, leader gathers queue and max LSN;<br/>Step3. Write/sync InnoDB redo log to that LSN;<br/>Step4. Write binlog and perform subsequent work (sync binlog, InnoDB commit, etc.)

Benchmark (sysbench, 100 tables × 100k rows, innodb_flush_log_at_trx_commit=2, sync_binlog=1000) shows throughput improvement from 25k‑30k to 27k‑38k TPS as concurrency increases.

DDL Fast Fail

Online services suffer when a DDL statement cannot acquire an exclusive MDL lock, blocking all reads and writes. The solution adds NO_WAIT / WAIT 1 syntax to ALTER TABLE, causing immediate error or a 1‑second timeout if the lock cannot be obtained.

If NO_WAIT fails, client receives

ERROR: Lock wait timeout exceeded; try restarting transaction

.

If WAIT 1 fails, the same error is returned after one second.

This approach prevents DDL from silently blocking the entire workload, though it does not eliminate the inherent lock contention of the DDL itself.

GTID Allocation Optimization

MySQL 5.6+ supports GTID. Automatic GTID allocation occurs during the first stage of group commit, but maintaining gtid_owned requires a global lock, hurting scalability.

Optimization removes the gtid_owned set: after allocating a GTID, it is directly added to gtid_executed, eliminating the need to clean up gtid_owned on commit.

ha_commit_trans → MySQL_BIN_LOG::commit → MySQL_BIN_LOG::ordered_commit → MySQL_BIN_LOG::finish_commit → Gtid_state::update_owned_gtids_impl → lock_sidno

Benchmarks (sysbench, 100 tables × 100k rows) show stable TPS across high concurrency after the change.

InnoDB Auto‑Increment Duplicate Issue

After a server restart, InnoDB may reuse auto‑increment values because the counter is kept only in memory; on restart MySQL computes the next value with SELECT MAX(id)+1, which can reuse freed IDs, leading to duplicate primary keys.

Solution: persist the auto‑increment counter in the clustered index root page’s trx_id field, writing a tiny redo log entry each time the counter changes. A new global variable innodb_autoinc_persistent (ON/OFF) controls this behavior, with an optional innodb_autoinc_persistent_interval to batch persistence. innodb_autoinc_persistent=ON Performance impact is less than 1%.

Replication Performance Improvements

MySQL 5.5 used a single SQL thread to apply relay‑log events, causing lag under load. MySQL 5.6 introduced multi‑threaded replication: a coordinator thread distributes events to worker threads based on database name, allowing parallel execution when possible.

sql_thread: exec_relay_log_event → apply_event_and_update_pos → apply_event → rows_log_event::apply_event → storage_engine operation → update_pos

MySQL 5.7 added a seqno tag to binlog events, enabling true parallel execution of transactions with the same timestamp, simplifying the parallel model and removing the database‑binding limitation.

Overall, these enhancements dramatically increase replication throughput, though 5.6 introduced some bugs that were later fixed.