MySQL 8.4 changed the InnoDB adaptive hash index (innodb_adaptive_hash_index) default from ON to OFF, a major shift after years of it being enabled by default. Note that the MySQL adaptive hash index (AHI) feature remains fully available and configurable.
This blog is me going down the rabbit hole so you don’t have to and present you what you actually need to know. I am sure you’re a great MySQLer know-it-all and you might want to skip this but DON’T, participate in bonus task towards the end.
Note that MariaDB already made this change in 10.5.4 (see MDEV-20487), so MySQL is doing nothing new! But why? Let me start with What(?) first!
What is Adaptive Hash Index in MySQL (AHI)
This has been discussed so many times, I’ll keep it short.
We know InnoDB uses B-trees for all indexes. A typical lookup requires traversing 3 – 4 levels: root > internal nodes > leaf page. For millions of rows, this is efficient but not instant.
AHI is an in-memory hash table that sits on top of your B-tree indexes. It monitors access patterns in real-time, and when it detects frequent lookups with the same search keys, it builds hash entries that map those keys directly to buffer pool pages.
So when next time the same search key is hit, instead of a multi-level B-tree traversal, you get a single hash lookup from the AHI memory section and direct jump to the buffer pool page giving you immediate data access.
FYI, AHI is part of InnoDB bufferpool.
What is “adaptive” in the “hash index”
InnoDB watches your workload and decides what to cache adaptively based on access patterns and lookup frequency. You don’t configure which indexes or keys to hash, InnoDB figures it out automatically. High-frequency lookups? AHI builds entries. Access patterns changes? AHI rebuilds the hash. It’s a self tuning optimization that adjusts to your actual runtime behavior and query patterns. That’s the adaptive-ness.
Sounds perfect, right? What’s the problem then?
The Problem(s) with AHI
– Overhead of AHI
AHI is optimal for frequently accessed pages but for non-frequent? The look-up path for such query is:
– Check AHI
– Check bufferpool
– Read from disk
For infrequent or random access patterns the AHI lookup isn’t useful, only to fall through to the regular B-tree path anyway. It causes you to spend memory search, comparison and burn CPU cycles.
– There is a latch on the AHI door
AHI is a shared data structure, though partitioned (innodb_adaptive_hash_index_parts), it has mutexes for controlled access. Thus when the concurrency increases, AHI may cause those threads blocking each other.
– The unpredictability of AHI
This appears to be the main reason for disabling the Adaptive Hash Index in MySQL 8.4. The optimizer needs to predict costs BEFORE the query runs. It has to decide: “Should I use index A or index B?”. AHI is dynamically built and is access (more frequently or less) dependent thus optimizer cannot predict a consistent query path.
The comments in this IndexLookupCost function section of cost_model.h explains it better, and I quote:
“With AHI enabled the cost of random lookups does not appear to be predictable using standard explanatory variables such as index height or the logarithm of the number of rows in the index.”
I encourage you to admire the explanation in the comments here: https://dev.mysql.com/doc/dev/mysql-server/latest/cost__model_8h_source.html
Why AHI Disabled in MySQL 8.4
I’d word it like this… the default change of InnoDB Adaptive Hash Index in MySQL 8.4 was driven by,
One: the realization that “favoring predictability” is more important than potential gains in specific scenarios and
Two: End users have the feature available and they can Enable it if they know/think it’d help them.
In my production experience, AHI frequently becomes a contention bottleneck under certain workloads, like write-heavy, highly concurrent or when active dataset is more than the buffer pool size. Disabling AHI ensures consistent response times and eliminates a common source of performance unpredictability”.
That comes to our next segment, what is that YOU need to do? and importantly, HOW?
The bottom line: MySQL 8.4 defaults to innodb_adaptive_hash_index=OFF. Before upgrading, verify whether AHI is actually helping your workload or quietly hurting it.
How to track MySQL AHI usage
Using the MySQL CLI
Use ENGINE INNODB STATUS command and look for the section that says “INSERT BUFFER AND ADAPTIVE HASH INDEX”:
SHOW ENGINE INNODB STATUS\G8582.85 hash searches/s, 8518.85 non-hash searches/sHere:
hash searches: Lookups served by AHI
non-hash searches: Regular B-tree lookups (after AHI search fails)
If your hash search rate is significantly higher, AHI is actively helping.
If the numbers for AHI are similar or lower, AHI isn’t providing much benefit.
Is AHI causing contention in MySQL?
In SHOW ENGINE INNODB STATUS look for wait events in SEMAPHORE section:
-Thread X has waited at btr0sea.ic line … seconds the semaphore:
S-lock on RW-latch at … created in file btr0sea.cc line …If ENGINE INNODB STATUS shows many threads waiting on rw-latches created in btr0sea.c, it is the signs of Adaptive Hash index locking contention. That’s a sign for disabling it.
Refer: https://dev.mysql.com/doc/dev/mysql-server/latest/btr0sea_8cc.html
Monitoring AHI for MySQL
How about watching a chart that shows AHI efficiency? Percona Monitoring and Management makes visualization easy to decide on if that’s better for current workload. Here are 1000 words for you:
Bonus Task
Think you’ve got it about MySQL AHI here? Let’s do this task:
- Open pmmdemo.percona.com
- Go to Dashboards > MySQL > MySQL InnoDB Details
- Scroll down to “Innodb Adaptive Hash Index” section
- Answer this question in comments section: Which MySQL instances are better off without AHI?
Conclusion
AHI is a great idea and it works until it doesn’t. You’ve gotta do the homework, track usage, measure impact, then decide. Make sure you be ready for your upgrade.
If your monitoring shows consistently high hash search rates with minimal contention, you’re in the sweet spot, AHI should remain enabled. If not, innodb_adaptive_hash_index is good to remain OFF.
I recall a recent song verse that suits well on MySQL AHI: “I’m a king but I’m far from a saint” “It’s a blessing and a curse” (IUKUK)
Have you seen AHI help or hurt in your systems? What’s your plan for MySQL 8.4? I’d love to hear real-world experiences… the database community learns best when we share our war stories.
PS
Open source is beautiful, you can actually read the code (and comments) and understand the “why” behind decisions.
Planet for the MySQL Community