MySQL Performance Optimizations and Feature Enhancements: Group Commit, DDL Fast‑Fail, GTID, InnoDB Auto‑Increment Persistence, Replication, and Key Partition Algorithms

Editor’s Note

Taobao has been using MySQL at massive scale since 2010, replacing earlier IOE‑based databases and collaborating with Oracle, Percona, and MariaDB to contribute dozens of patches. The internal database team focuses on single‑node performance, clustering, and I/O storage research.

MySQL – Performance Optimization – Group Commit

Prior to MySQL 5.6, Binlog and InnoDB commit were serialized, causing a bottleneck when sync_binlog=1 . MariaDB introduced a Binlog Group Commit queue, and Percona adopted it. Oracle MySQL 5.6 added a three‑stage Group Commit (flush, sync, commit) that can run concurrently.

By moving the InnoDB redo‑log write to the first stage and delaying it until after the flush stage, the implementation reduces log_sys->mutex contention. The change was upstreamed to MySQL 5.7.6 (bug #73202).

Performance test (sysbench, 100 tables, 100 k rows each, innodb_flush_log_at_trx_commit=2 , sync_binlog=1000 ) showed notable throughput gains across thread counts.

MySQL – New Feature – DDL Fast Fail

DDL operations often block all queries due to MDL (metadata lock) contention. The “DDL fast fail” feature adds NO_WAIT / WAIT 1 syntax to DDL statements, returning an error immediately if the exclusive MDL lock cannot be obtained.

This avoids the classic “snow‑avalanche” where a long‑running transaction blocks the entire workload. The approach is similar to Oracle’s DDL_LOCK_TIMEOUT parameter.

MySQL – Performance Optimization – GTID Scenarios

GTID allocation in automatic mode holds a global lock during the commit phase, limiting scalability. The optimization removes the need to maintain the gtid_owned set for automatic allocation, inserting the GTID directly into gtid_executed and thus eliminating the lock in the final cleanup step.

Benchmarks (sysbench, up to 1024 threads) demonstrate stable TPS after the change compared with a decline in the original implementation.

MySQL – Bug Fix – InnoDB Auto‑Increment Duplicate Values

InnoDB stores the next auto‑increment value only in memory; after a server restart it recomputes the value via SELECT MAX(id)+1 , which can reuse previously deleted IDs. The fix persists the value in the clustered index root page’s trx_id field, adding a tiny redo‑log write without undo overhead.

Enabling innodb_autoinc_persistent=ON incurs less than 1 % performance loss. An additional variable innodb_autoinc_persistent_interval controls persistence frequency.

MySQL – Replication Performance Improvements

From MySQL 5.5 to 5.7 the replication architecture evolved from a single SQL thread to multi‑threaded parallel replication (coordinator + workers) and finally to “sequenced” parallelism using a common seqno tag, greatly reducing lag on high‑concurrency workloads.

Implementation details include memory management of relay logs, database‑to‑worker mapping, and handling of temporary tables.

MySQL – Historical Analysis – Key Partition Hash Algorithm

MySQL 5.1 used a simple hash function for non‑NULL key values, while 5.5/5.6 switched to a charset‑aware algorithm, causing data‑loss‑like query failures after upgrades. Compatibility is achieved by specifying ALGORITHM = 1 in the partition definition or by running CHECK TABLE … FOR UPGRADE which suggests the necessary ALTER statement.