I'm debugging a memory problem with MySQL 5.6 (I'm using the Percona distribution), but I can't figure out, searching around the web, if the Adaptive Hash Index amount is part of the Total memory allocated, or if it's separated.

Current output:

Total memory allocated 109890764800; in additional pool allocated 0
Total memory allocated by read views 15776
Internal hash tables (constant factor + variable factor)
    Adaptive hash index 10365924496   (1699980328 + 8665944168)
    Page hash           13281976 (buffer pool 0 only)
    Dictionary cache    427147426   (424996592 + 2150834)
    File system         84040   (82672 + 1368)
    Lock system         295705056   (295674296 + 30760)
    Recovery system     0   (0 + 0)

Just going by the MySQL Documentation, I would say yes.

According to the MySQL 5.6 Documentation on innodb_adaptive_hash_index

Disabling the adaptive hash index empties the hash table immediately. Normal operations can continue while the hash table is emptied, and executing queries that were using the hash table access the index B-trees directly instead. When the adaptive hash index is re-enabled, the hash table is populated again during normal operation.

If you really want to know for sure, login as root@localhost, run this

SET GLOBAL innodb_adaptive_hash_index = 'OFF';

and look at the memory stats again. They should be zero and memory counts should reflect that.

  • I've tried disabling it (unfortunately I have to work on production, so I can't always directly try things), and the server memory occupation stayed the same, so yes, the AHI is allocated inside the buffer pool. As interesting note though, the AHI occupation does not go to zero after disabling it. In my case, it's measure as 8412492944 when ON, and 1699984528 when OFF. – Marcus Apr 20 '16 at 7:02
  • Perhaps it was not being populated to start with due to your workload : percona.com/blog/2016/04/12/… – RolandoMySQLDBA Apr 21 '16 at 2:36
  • I've switched on and off multiple times, and the value changes through same/similar numbers. I'm not sure about the internals, anyway, your answer is correct - the AHI lives inside the buffer pool! – Marcus Apr 25 '16 at 17:37

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.