The worst CPU saturation I ever chased on a MySQL box had no slow queries attached to it. Every statement was a single-row lookup by primary key, the kind of query you stop seeing because it never appears in the slow log, and yet the server was pinned above ninety percent user CPU with throughput going nowhere. No lock waits, no I/O stall, no plan changes. The culprit turned out to be a feature that exists specifically to make those queries faster: the InnoDB adaptive hash index. On that workload, a handful of celebrity rows hammered by thousands of connections, the hash index's latch had quietly become the hottest lock on the machine. This article is how the adaptive hash index works, how to tell when it is helping, and how to tell when it has turned on you.
What the adaptive hash index actually is
InnoDB watches how your queries reach your data. When it notices the same B-tree index pages being found over and over by the same search pattern, it builds an in-memory hash index over those pages, keyed on a prefix of the search value. A qualifying point lookup can then jump straight to the record with one hash computation instead of descending the B-tree from root to leaf. That saves a handful of page touches per query, which sounds small until you multiply it by tens of thousands of queries per second; on the right workload it is a meaningful reduction in CPU that you never had to write an index for.
The details that matter operationally: the hash index is built automatically, with no schema changes and no DDL. It lives in memory carved out of the buffer pool, so every hashed page carries a memory cost on top of the page itself. Entries exist per index page, and InnoDB maintains them as the underlying index changes, which means writes to a hashed page pay a maintenance tax. Only equality lookups on the exact key prefix qualify; range scans, ORDER BY, and anything that walks the tree never touch it. And InnoDB constantly re-evaluates which pages deserve entries, so the hash index's shape follows your workload in real time.
When it earns its keep
The adaptive hash index loves boring OLTP: read-heavy traffic dominated by primary-key and unique-key point lookups, hot rows spread evenly across the key space, working set resident in the buffer pool. That is the classic web workload, and it is why the feature defaulted to on through MySQL 8.0; 8.4 flips the default to off, which tells you how the project now scores the latch cost on modern hardware. The way to see it working is the INSERT BUFFER AND ADAPTIVE HASH INDEX section of SHOW ENGINE INNODB STATUS, which prints hash searches per second next to non-hash searches per second. When the hash number is a healthy fraction of the total, the feature is intercepting a real share of your lookups and saving tree descents on every one. When nearly everything shows up as non-hash, the feature is carrying memory and maintenance cost for nothing, which is the first hint that your workload does not suit it.
The latch problem
The hash index is not one big structure. It is split into parts, innodb_adaptive_hash_index_parts of them, eight by default, and each part is guarded by its own read-write latch. A lookup that wants the hash path takes the part's latch in shared mode. Any change to a hashed page, an insert, the delete-mark left by an update or delete, purge physically removing a row, takes it in exclusive mode. In the old days there was a single latch for the whole hash index, and partitioning plus rw-locks made things dramatically better. But partitioning does not repeal arithmetic: a latch is still a point of serialization, and eight parts is still a finite number.
Now picture a skewed workload. A status table everyone polls. A hot counter row. A tenant whose rows absorb half your traffic. A huge share of all lookups lands on the same few index pages, and those pages live in the same one or two parts. Thousands of threads serialize on one latch. The failure signature is distinctive once you have seen it: user CPU climbs toward saturation while completed queries per second flatlines or falls. The slow log stays empty because every individual query is fast. Row lock waits stay empty because nobody is waiting on data, only on a latch. Writers make it sharper, because every update to a hot hashed page needs the exclusive latch, so even a read-mostly workload with a modest write stream can melt if the writes hit the same pages the reads love.
Confirming it is really the adaptive hash index
Suspicion is cheap; proof takes two checks. First, the SEMAPHORES section of SHOW ENGINE INNODB STATUS: watch spin rounds and OS waits climbing fast while the server is sick. Second, and more precise, the wait instrumentation in performance_schema. The adaptive hash index latch shows up as an rwlock wait whose event name contains btr_search_latch:
SELECT event_name, count_star,
round(sum_timer_wait / 1000000000000, 1) AS total_wait_seconds,
round(avg_timer_wait / 1000000, 2) AS avg_wait_microseconds
FROM performance_schema.events_waits_summary_global_by_event_name
WHERE event_name LIKE '%btr_search%'
ORDER BY sum_timer_wait DESC;Enable the rwlock instruments first if your setup has them off; the overhead notes in my performance_schema setup guide apply here. The triangulation is what matters: high and climbing latch waits, flat throughput, a point-lookup workload, no I/O bottleneck. Any one of those alone has other explanations; all four together is the adaptive hash index. If you have perf on the host, a perf top during the incident showing spin loops inside InnoDB code is the final confirmation.
Deciding: more parts, or off
You have two levers, and they solve different problems. If the contention is spread across many hot indexes, raising the part count helps: 32 or 64 on a big many-core box spreads those indexes across more latches. That variable needs a restart, so plan the change. Every page of a given index is bound to a single part, so if the contention is concentrated on one hot index, more parts accomplish nothing; its pages sit on the same latch no matter how many parts you add. That case calls for SET GLOBAL innodb_adaptive_hash_index to OFF, which is dynamic and takes effect immediately.
Two caveats from experience. Turning the feature off empties the hash table right away, and point lookups fall back to plain B-tree descent while that teardown happens; on a busy box expect a short burst of work as the structure is dismantled, not a gentle fade over minutes. And judge the experiment by throughput and p99 latency during a real peak, not by how the server feels at four in the morning. I run thirty-minute windows, on and off, and keep whichever delivers more completed queries per second at lower CPU. My standing defaults after years of this: leave it on for uniform CRUD workloads, where it is genuinely faster; turn it off for queue-like tables, celebrity-row workloads, and heavy update churn on hot secondary index pages, where maintenance cost and latch traffic dominate. One historical note worth knowing: dropping a large hashed table used to scan the buffer pool LRU to evict hash entries and could visibly stall a busy server; MySQL 8.0.23 removed that scan, which is one more small reason to run a current release.
What I watch continuously
Four cheap signals. The hash-to-non-hash search ratio from the engine status output, trended so you notice when a workload drifts away from the feature. OS waits from the SEMAPHORES section. Wait time on the btr_search latch from performance_schema. And the efficiency ratio that settles every argument: user CPU per completed query. When that last one regresses without a plan change or a data change, a latch somewhere is eating your margin, and on point-lookup workloads the adaptive hash index is the first latch I check.
What I want tooling to see here
Everything in this article I diagnosed by hand, and that is the gap good tooling should close. I work on MonPG, which monitors PostgreSQL today; the MySQL edition is being built, and btr_search latch waits next to the hash-to-non-hash search ratio are exactly the signals it has to surface, because the incident in this article is invisible to any tool that only looks at queries and locks. The MySQL monitoring (coming soon) page tracks where that work stands. If PostgreSQL is also in your fleet, the same philosophy, engine internals first and queries second, already exists there; you can read about it on the PostgreSQL monitoring page or browse the blog for the PostgreSQL side of these war stories.