How MySQL 5.7 Optimizes Metadata Locks and Boosts Replication Performance

MySQL·5.7 Optimization·Metadata Lock Subsystem

Background

The MDL lock was introduced to fix bug #989, which caused replication breakage in MySQL 5.1 and earlier because transactions did not hold metadata locks for all involved tables.

Session 1: BEGIN;

Session 1: INSERT INTO t1 VALUES (1);

Session 2: DROP TABLE t1; —‑ SQL writes to BINLOG

Session 1: COMMIT; —‑ transaction writes to BINLOG

During binlog replay on a replica, the DROP TABLE is executed before the INSERT, breaking replication.

MySQL 5.5 added MDL so that all tables used in a transaction acquire MDL locks that are released only at commit, preventing the above race.

However, MDL also caused a new hotspot: all MDL lock objects are stored in a single hash. Facebook and Percona introduced alternatives to FTWRL (FLUSH TABLES WITH READ LOCK) that create a read view instead of a global MDL lock.

MDL introduced contention on metadata_locks_hash_instances (added in MySQL 5.6.8) to partition the hash, but early implementations suffered from poor hash distribution. Later revisions switched to MurmurHash3 for better key distribution.

MySQL 5.7 MDL Optimizations

MySQL 5.7 made several substantial changes:

1. Lock‑free hash (LF_HASH) replaces the partitioned MDL hash with a lock‑free extensible hash table based on the "Split‑Ordered Lists" algorithm, eliminating the need for hash partitioning.

2. Fast‑path for DML/SELECT introduces a FAST‑PATH lock mode that avoids mutexes for common DML and SELECT operations; if FAST‑PATH fails, the lock falls back to a SLOW‑PATH that uses a traditional rwlock.

Each MDL lock object now holds a long long state field ( MDL_lock::m_fast_path_state) indicating its fast‑path status.

Session 1: BEGIN; Session 1: SELECT ... // fast‑path state set Session 2: BEGIN; Session 2: SELECT ... // also fast‑path Session 3: ALTER TABLE ... // DDL forces slow‑path Session 4: SELECT ... // checks for obtrusive lock

3. Redundant THR_LOCK removal – InnoDB no longer needs the old THR_LOCK for table‑level concurrency; only a few lines of code were added to handle the new MDL types.

4. User locks via GET_LOCK() are now implemented with MDL, enabling dead‑lock detection and imposing a 64‑byte name limit.

Hash‑Scan Replication Optimization

When a row‑based binlog event deletes many rows from a table without an index, the replica performs a full table scan for each row, leading to O(N²) work and replication lag.

MySQL 5.6.6 introduced the slave_rows_search_algorithms variable with three options: TABLE_SCAN, INDEX_SCAN, and HASH_SCAN. HASH_SCAN caches changed rows in a hash ( m_hash) and, if an index exists, also in m_distinct_key_list.

--source include/master-slave.inc
--source include/have_binlog_format_row.inc
connection slave;
set global slave_rows_search_algorithms='TABLE_SCAN';
connection master;
create table t1(id int, name varchar(20));
insert into t1 values(1,'a');
... 
delete from t1;
---source include/rpl_end.inc

During Rows_log_event::do_hash_scan_and_update, rows are first cached in m_hash. If an index exists, keys are also cached, allowing a single pass over the table (O(1) per lookup) instead of repeated full scans.

Bug #72788 caused duplicate deletions because m_distinct_key_list could contain non‑unique keys; it was fixed in revision 8494.

TokuDB 7.5.0 Optimizations

a) Faster shutdown – Parallel compression of dirty nodes reduces shutdown time.

b) Internal node read acceleration – The OMT structure (key → FIFO offset) is now persisted, eliminating the need to sort the FIFO on every cache miss.

c) Sequential write acceleration – A heuristic seqinsert_score detects sequential writes; when the score exceeds 100, writes are directed to the right‑most pivot, avoiding extra comparisons.

MariaDB Filesort with Small LIMIT Optimization

Since MySQL 5.6.2 / MariaDB 10.0.0, the optimizer uses a priority‑queue (heap) of size n for queries of the form ORDER BY … LIMIT n when n is small, reducing the sort complexity from m·log(m) to m·log(n) (where m is the number of rows examined).

MariaDB 10.0.13 exposes the counter Sort_priority_queue_sorts and logs the usage in the slow‑query log when log_slow_verbosity=query_plan is enabled:

# Time: 140714 18:30:39
# User@Host: root[root] @ localhost []
# Thread_id: 3  Schema: test  QC_hit: No
# Query_time: 0.053857  Lock_time: 0.000188  Rows_sent: 11  Rows_examined: 100011
# Filesort: Yes  Filesort_on_disk: No  Priority_queue: Yes
select * from t1 where col1 between 10 and 20 order by col2 limit 100;

This indicates that the priority‑queue execution plan was used.