InnoDB’s Adaptive Hash

Posted in: Technical Track

MySQL’s manual page for InnoDB’s adaptive hash states:

If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes. InnoDB has a mechanism that monitors index searches made to the indexes defined for a table. If InnoDB notices that queries could benefit building a hash index, it does so automatically.

Note that the hash index is always built based on an existing B-tree index on the table. InnoDB can build a hash index on a prefix of any length of the key defined for the B-tree, depending on the pattern of searches that InnoDB observes for the B-tree index. A hash index can be partial: It is not required that the whole B-tree index is cached in the buffer pool. InnoDB builds hash indexes on demand for those pages of the index that are often accessed.

In a sense, InnoDB tailors itself through the adaptive hash index mechanism to ample main memory, coming closer to the architecture of main-memory databases.

This sounds like a good idea. However, with memory capacities increasing, InnoDB can create more and more hash indexes. While these hash indexes are fast, they also require the same maintenance as regular indexes. This means that the hash index needs to be written to, and therefore locked, whenever data is changed.

In addition, there’s a bug in InnoDB, detailed in Bug Report 20358 and Bug Report 29560. There’s a workaround in MySQL 5.0.52 (released Nov. 30 2007) and fixed in MySQL 5.0.54 (not yet released).

The first referenced bug report contains the following description:

[The read lock within row_search_for_mysql()] is a SELECT query done as a consistent read, and the read view has already been allocated. . . . [description of algorithm elided] Our algorithm is NOT prepared to inserts interleaved with the SELECT, and if we try that, we can deadlock on the adaptive hash index semaphore!

With multi-core machines (and innodb_thread_concurrency) rapidly on the rise, more and more of these adaptive hash index semaphore deadlocks are seen. You can see this bug implemented if you have many transactions that “holds adaptive hash latch” — see the “Transactions” section of the innodb_lock_monitor or in SHOW ENGINE INNODB STATUS

I guess there finally is a benefit to having MySQL Enterprise — the ability to disable InnoDB’s Adaptive Hash right now, with MySQL 5.0.52. Recall that even numbers are Enterprise, and odd ones are Community. The next edition of the Community server does not have a release date, but as the previous revision was October 19th, it could be a wait as long as April for a source build — with a build promised “once per quarter”, that could be March 31st. For a binary build, it may be longer — we were promised a binary build at least twice a year, though I believe MySQL’s intentions are approximately once every 6 months. With luck, by the MySQL Conference & Expo mere community users will be able to turn off adaptive hashing.

I am highly annoyed that I cannot find a reference to this bugfix (or is it a feature addition, the addition of a new flag?) in the Release Notes:

However, I do know what version it is in because 1) MySQL Support said so and 2) The bug report says so (see 18 Oct. 23:27). On that note, there is no reference to the new flag in the 5.1.23 release notes either. At least that is consistent! Although the release notes for MySQL 5.0.54 do contain the fix (the flag is just the workaround).

If you want, you can hack the source code and compile your own version of MySQL. Only 4 file changes are needed to make the workaround happen, and the changes are detailed here:

As a postscript to this — if you are a developer, whether or not it’s for Oracle (this was an InnoDB bug, after all…) if you put a “smart” feature into your software, make sure you have the means to easily disable it. What is smart today is not always smart tomorrow. In this particular case, the adaptive hashing is not causing the issue, but the locking that the index requires does cause the issue. However, having the flag earlier on would have saved a lot of grief, at least while the developers could figure out what was going on.

Interested in working with Sheeri? Schedule a tech call.

2 Comments. Leave new

Thanks for sharing this Sheeri. Interesting feature and it also shows that self managing/tuning trend didn’t pass MySQL. Btw, do you know or have any reference on how this hash index is working?

Regarding disabling new feature. Oracle has tradition to introduce a new feature silently in the previous release as disabled with possibility to enable using a hidden parameter. I think that’s a good practice. Unfortunately, Oracle doesn’t always make corresponding hidden parameter public and supported when the feature is finally released.


I don’t have any information other than what’s listed in the manual, which is at the top of the post. I haven’t had time to dig into the internals.


Leave a Reply

Your email address will not be published. Required fields are marked *