The worst stall I ever chased on MySQL lasted four minutes and twenty seconds, and it was caused by an index nobody had ever created. The system was a session-token service: one InnoDB table, about 380 million rows, serving 52,000 primary-key lookups a second at peak, with a background job deleting roughly 1,500 expired sessions a second and new logins inserting maybe 2,000 more. Most of the day, p99 latency sat at 2 milliseconds. Then, every few minutes and always under load, p99 would jump to 40, 60, sometimes 200 milliseconds, and during the bad one it flatlined completely while sixty application threads waited on the database. The box was a 32-core 5.7 primary with a 96GB buffer pool, CPU at 45%, disk nearly idle. Everything pointed at a latch.
SHOW ENGINE INNODB STATUS confirmed it. The SEMAPHORES section was a wall of threads waiting on one thing: btr_search_latch, the lock guarding InnoDB's adaptive hash index. At 22:40 I ran SET GLOBAL innodb_adaptive_hash_index = OFF, and within about a minute the waiters drained, p99 dropped under 3 milliseconds, and throughput actually climbed by about 15%. Nothing about the queries changed. What changed was that InnoDB stopped maintaining an in-memory hash index that had been costing far more than it saved. This piece is what that index is, how to tell when it is hurting you, and how to make the on/off decision with evidence instead of folklore.
What does InnoDB's adaptive hash index actually build?
An in-memory hash index, built automatically on top of hot B-tree pages, keyed by the prefixes of the index values InnoDB sees queried repeatedly — you never declare it, and you cannot control which entries exist. InnoDB watches the search patterns hitting each index. When it observes the same kind of lookup — same leading columns, equality predicates, point selects — served from the same B-tree pages over and over, it builds hash entries that map those key prefixes directly to the page, skipping the B-tree descent. The hash table lives in memory alongside the buffer pool, it is rebuilt from scratch on every restart, and nothing about it is durable or queryable as data. The promise is real: a B-tree lookup walks a few levels of pages and does comparisons at each level, while a hash lookup is one probe. For a workload that is mostly repeated point lookups against the same keys, the adaptive hash index shaves that traversal off every query, and published benchmarks have shown double-digit throughput gains on exactly that shape of workload. The "adaptive" part matters for the failure modes: the index is a function of observed access patterns, so when the patterns change, the hash entries get invalidated and rebuilt, and every modification to a page that has hash entries must update those entries too.
When does AHI help, and when does it hurt?
It helps on read-heavy workloads dominated by repeated point lookups over a stable working set, and it hurts when writes churn the pages that carry hash entries, when queries scan ranges or use wildcard patterns, or when concurrency on the hash table's latch gets high enough that the maintenance cost dwarfs the saved traversals. The helpful case is real and I have measured it: a pure key-value read workload on a read-only replica lost about 12% throughput when I disabled AHI there, so on that box I left it on. The hurtful cases all share a shape. Writes to hot pages force hash-entry maintenance under the same latch reads need, so a table mixing heavy point reads with inserts, updates, or deletes — like my sessions table — serializes readers behind writers on one synchronization point. LIKE patterns with leading wildcards and range scans produce no reusable hash entries but still churn the heuristics. And there is a second-order cost people forget: dropping or truncating a large table, or rebuilding an index, must purge the table's hash entries from the buffer pool, which is why MariaDB's own documentation notes AHI slows down DROP TABLE, TRUNCATE TABLE, ALTER TABLE, and DROP INDEX — a big enough factor that MariaDB flipped the default to OFF in 10.5, and MySQL followed by defaulting innodb_adaptive_hash_index to OFF in 8.4. When two vendors independently change a twenty-year-old default in the same direction, that is not a superstition, it is a signal.
How does AHI contention show up in SHOW ENGINE INNODB STATUS?
As threads stacked up in the SEMAPHORES section waiting on the btr_search latch, often with long wait times and high spin counts, while the OS WAIT ARRAY INFO counters for that latch climb steadily. The SEMAPHORES section lists every thread currently waiting more than a threshold on an InnoDB synchronization object, the file and line where the object was created, and how long it has waited. On 5.7 the line to hunt for is btr0sea.cc and the object is an RW-latch; on 8.0 the same structure is an SX-lock and the file is still btr0sea.cc — the source file that implements the adaptive hash index. The night of the four-minute stall, the section looked like this, with dozens more just like it:
--Thread 140472836867840 has waited at btr0sea.cc line 198 for 242.00 seconds the semaphore:
X-lock (wait_ex) on RW-latch at 0x7fbfd81230c0 created in file btr0sea.cc line 198
a writer (thread id 140472121841408) has reserved it in mode wait exclusive
number of readers 1, waiters flag 1, lock_word: fffffffff0000000
OS WAIT ARRAY INFO: reservation count 8894128, signal count 12603417Two corroborating signals live elsewhere in the same output. The INSERT BUFFER AND ADAPTIVE HASH INDEX section prints hash searches per second against non-hash searches per second — if the non-hash number is large relative to the hash one, your lookups are not even using the index you are paying to maintain. And the SEMAPHORES spin-wait counters keep a running tally per mutex: a btr_search entry with rounds and OS waits climbing fast between two samples taken thirty seconds apart is contention made numeric. One caveat on sampling: SEMAPHORES is a snapshot of the moment, so catch it during a stall or take repeated samples; the standby trick of watching sys schema and friends does not cover this one, because this latch lives below the query layer entirely.
How do you measure AHI contention and usage with performance_schema?
Through the synchronization wait instruments: on 5.7 the event is wait/synch/rwlock/innodb/btr_search_latch, and on 8.0 it is wait/synch/sxlock/innodb/btr_search_lock, and both roll up in events_waits_summary_global_by_event_name with counts and total wait time. The instruments are usually enabled already, but the summary table is cumulative since startup, so the honest measurement is a delta over a busy window — sample, wait through your peak, sample again, subtract:
-- is the instrument armed? (8.0 spelling; 5.7 says rwlock/btr_search_latch)
SELECT NAME, ENABLED, TIMED
FROM performance_schema.setup_instruments
WHERE NAME LIKE '%btr_search%';
-- cumulative contention so far; sample this twice across a peak window
SELECT EVENT_NAME, COUNT_STAR, SUM_TIMER_WAIT
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'wait/synch/%innodb/btr_search%';
-- who is waiting right now, during a stall
SELECT THREAD_ID, EVENT_NAME, TIMER_WAIT
FROM performance_schema.events_waits_current
WHERE EVENT_NAME LIKE '%btr_search%';
-- how much of your lookup traffic AHI is actually serving
SHOW GLOBAL STATUS LIKE 'Innodb_adaptive_hash%';
-- and the setting itself, dynamic since long ago, no restart needed
SHOW VARIABLES LIKE 'innodb_adaptive_hash_index%';The last query is the one that settles the argument. Innodb_adaptive_hash_searches counts lookups served through the hash index, and Innodb_adaptive_hash_searches_btree counts lookups that had to walk the B-tree. On my sessions box the ratio was terrible — the point selects did hash-hit, but every delete and insert forced maintenance, so the latch wait time in the summary delta was growing at nearly a millisecond per second of wall clock while the hash-hit count justified nothing close to that. When the contention counters are high and the hit ratio is low, the index is a tax. When the ratio is high and contention counters are flat, leave it alone and go fix something else.
Should you disable AHI, partition it, or leave it alone?
Test disabling it first, because innodb_adaptive_hash_index is dynamic and a reversible SET GLOBAL is the cheapest experiment in all of MySQL tuning; reach for innodb_adaptive_hash_index_parts only when the test shows the feature helps but the latch still hurts. The honest decision procedure is the one I ran. First, confirm the contention is really btr_search with the evidence above — do not disable a read optimization because a blog post said to. Second, run SET GLOBAL innodb_adaptive_hash_index = OFF during the next predictable peak and watch p99 latency, throughput, and the SEMAPHORES section for an hour. Off is off immediately: the hash table stops being consulted and being maintained, and existing entries drain as pages age out of the buffer pool. Third, if throughput dropped instead of rising, turn it back on — that is a legitimate result, and it is what I got on the read-only replica. If off won, persist it with SET PERSIST so the setting survives a restart, because a 3 a.m. crash loop that silently re-enables the thing you disabled is a special kind of déjà vu. The middle option, innodb_adaptive_hash_index_parts, partitions the hash table into up to 512 separately latched segments — the default is 8 on 5.7.8 and later, and raising it spreads the contention across more latches. It is a real mitigation for the "AHI helps but the latch is hot" middle case, but it is a startup-only variable, so testing it costs you planned restarts instead of one dynamic SET, and it multiplies the memory the hash table can consume. My order of preference after living through it: measure, try off, persist off if it wins, and only then consider parts if some node in the fleet genuinely misses the feature. And budget the buffer pool implications either way — hash entries live in memory that competes with your pages, which folds into the same sizing exercise as buffer pool sizing.
Where MonPG stands on MySQL
I build MonPG, so the honest line: MonPG monitors PostgreSQL today, and MySQL support is in active development, not shipped. The signals this piece leans on — latch-level wait events trending over time, the adaptive-hash hit ratio next to non-hash lookups, p99 latency annotated with the moment a dynamic SET GLOBAL landed — are exactly the kind of evidence the MySQL work is designed to surface, so a contention fix shows up as a graph with a before and an after rather than a four-minute stall and a war story. The MySQL monitoring (coming soon) page tracks that work as it lands. Until it ships, the same evidence-first approach runs on the PostgreSQL side, and the rest of these MySQL field notes live on the blog.