Controlling Adaptive Hash Indexing
If a table fits almost entirely in main memory, the fastest way to perform queries on it is to use hash indexes rather than B-tree lookups. MariaDB monitors searches on each index defined for an InnoDB table. If it notices that certain index values are being accessed frequently, it automatically builds an in-memory hash table for that index. Based on the observed pattern of searches, it builds a hash index using a prefix of the index key. The prefix of the key can be any length, and it may be that only some of the values in the B-tree appear in the hash index. Hash indexes are built on demand for those pages of the index that are often accessed.
This adaptive hash index mechanism allows InnoDB to take advantage of large amounts of memory, something typically done only by database systems specifically designed for databases that reside entirely in memory. Normally, the automatic building and use of adaptive hash indexes improves performance. However, sometimes, the read/write lock that guards access to the adaptive hash index may become a source of contention under heavy workloads, such as multiple concurrent joins.
You can monitor the use of the adaptive hash index and the contention for its use in the "SEMAPHORES" section of the output of the SHOW ENGINE INNODB STATUS command. If you see many threads waiting on an RW-latch created in btr0sea.c, then it might be useful to disable adaptive hash indexing.
The configuration parameter innodb_adaptive_hash_index can be set to disable or enable the adaptive hash index. See , "Dynamically Changing innodb_adaptive_hash_index" for details.
For more information about the performance characteristics of hash indexes, see , "Comparison of B-Tree and Hash Indexes".