Eliminating InnoDB Adaptive Hash Index Lock Contention for Faster Queries

Background

InnoDB’s Adaptive Hash Index (AHI) is an auxiliary hash structure built on top of B‑tree leaf pages. By creating a hash entry for frequently accessed rows, AHI can bypass the root‑to‑leaf traversal, reducing CPU cycles compared to a pure B‑tree search.

Problem

During a sysbench run on TXSQL 5.7 (96‑core client and server, 200 GB buffer pool, 120 GB table) the QPS curve rose initially, dropped sharply after ~100 s, and recovered slowly after ~400 s. Perf profiling at the drop point showed massive CPU time spent on lock contention in the AHI hash table. The stack trace revealed many threads trying to build AHI for the same leaf page, each requiring an exclusive (X) lock on the hash table, creating a bottleneck.

Most pages had not yet built AHI, so many concurrent threads attempted to construct the hash entry for the same page, each waiting for the X‑lock on the hash table.

Optimization Idea

Only one thread can hold the X‑lock on the AHI hash table; blocking other threads on this lock is inefficient. The solution is to check the lock status before attempting to build AHI. If the hash table is already X‑locked, skip the construction for that page and fall back to the regular B‑tree lookup.

When the B‑tree search decides to build AHI for a page, first test whether the corresponding hash table is currently locked for writing.

If it is locked, cancel the AHI build for that page and use the normal B‑tree path.

Implementation

The change is made in btr_search_info_update_slow, which decides whether to invoke btr_search_build_page_hash_index. The call chain is:

btr_cur_search_to_nth_level → btr_search_info_update → btr_search_info_update_slow → btr_search_build_page_hash_index

A lock‑check is added before the final call. The modification consists of only a few lines, illustrated below.

The patch does not clear any AHI statistics; it merely postpones the build until the hash‑table lock is less contended.

Effect

Re‑running the benchmark with the optimization shows a stable QPS after a short warm‑up period, and the lock‑contention spike disappears.

Related Work / Inspiration

MySQL already includes a similar fallback during AHI lookup: if the hash table is X‑locked, the engine skips the AHI path and uses the B‑tree directly. The new optimization applies the same principle to AHI construction.

Reference: https://bugs.mysql.com/bug.php?id=100512

Conclusion

The lock‑avoidance patch has been merged into the latest TXSQL 5.7 release, effectively mitigating AHI‑construction lock contention in scenarios such as system startup, enabling AHI, or primary‑standby switchover. The same idea has been submitted to the MySQL community (bug #100512) and is under evaluation for upstream inclusion.