mysql>  SELECT (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE WHERE PAGE_TYPE LIKE ’IBUF%’) AS change_buffer_pages, (SELECT COUNT(*) FROM INFORMATION_SCHEMA.INNODB_BUFFER_PAGE) AS total_pages, (SELECT ((change_buffer_pages/total_pages)*100)) AS change_buffer_page_percentage; +---------------------+-------------+-------------------------------+ | change_buffer_pages | total_pages | change_buffer_page_percentage | +---------------------+-------------+-------------------------------+ | 25 | 8192 | 0.3052 | +---------------------+-------------+-------------------------------+ mysql>  SELECT * FROM performance_schema.setup_instruments WHERE NAME LIKE ’%wait/synch/mutex/innodb/ibuf%’; +-------------------------------------------------------+---------+-------+ | NAME | ENABLED | TIMED | +-------------------------------------------------------+---------+-------+ | wait/synch/mutex/innodb/ibuf_bitmap_mutex | YES | YES | | wait/synch/mutex/innodb/ibuf_mutex | YES | YES | | wait/synch/mutex/innodb/ibuf_pessimistic_insert_mutex | YES | YES | +-------------------------------------------------------+---------+-------+ 3015 With some workloads, the speedup from hash index lookups greatly outweighs the extra work to monitor index lookups and maintain the hash index structure. Access to the adaptive hash index can sometimes become a source of contention under heavy workloads, such as multiple concurrent joins. Queries with If a table fits almost entirely in main memory, a hash index speeds up queries by enabling direct lookup of any element, turning the index value into a sort of pointer.  InnoDB  has a mechanism that monitors index searches. If  InnoDB  notices that queries could benefit from building a hash index, it does so automatically. Based on the observed pattern of searches, a hash index is built using a prefix of the index key. The prefix can be any length, and it may be that only some values in the B-tree appear in the hash index. Hash indexes are built on demand for the pages of the index that are accessed often. The adaptive hash index enables  InnoDB  to perform more like an in-memory database on systems with appropriate combinations of workload and sufficient memory for the buffer pool without sacrificing transactional features or reliability. The adaptive hash index is enabled by the innodb_adaptive_hash_index  variable, or turned off at server startup by  --skip-innodb- adaptive-hash-index . For information about monitoring  InnoDB  mutex waits, see Section 15.16.2, “Monitoring InnoDB Mutex Waits Using Performance Schema” . For information about other data provided by the  INNODB_BUFFER_PAGE  table, see Section 26.4.2, “The INFORMATION_SCHEMA INNODB_BUFFER_PAGE Table” . For related usage information, see Section 15.15.5, “InnoDB INFORMATION_SCHEMA Buffer Pool Tables” . •Performance Schema provides change buffer mutex wait instrumentation for advanced performance monitoring. To view change buffer instrumentation, issue the following query: 15.5.3 Adaptive Hash Index Warning Adaptive Hash Index Querying the  INNODB_BUFFER_PAGE  table can introduce significant performance overhead. To avoid impacting performance, reproduce the issue you want to investigate on a test instance and run your queries on the test instance. For example, you can query the  INNODB_BUFFER_PAGE  table to determine the approximate number of IBUF_INDEX  and  IBUF_BITMAP  pages as a percentage of total buffer pool pages.