Adaptive hash index memory is incorrectly calculated in SHOW ENGINE INNODB STATUS and I_S

[Alexey Kopytov]

XtraDB exposes the amount of memory allocated by the adaptive hash index structures in SHOW ENGINE INNODB STATUS (PS 5.5), the status variable Innodb_mem_adaptive_hash (PS 5.5 and 5.6), or the INFORMATION_SCHEMA.XTRADB_INTERNAL_HASH_TABLES table (PS 5.6).

In all cases the value is calculated as follows (with simplifications):

1. Get the number of cells in the first hash table and multiply it by the size of a cell.
2. Multiple value #1 by the number of adaptive hash index partitions.
3. Get the amount of heap memory allocated in the first hash table.
4. Multiple value #3 by the number of adaptive hash index partitions.

SHOW ENGINE INNODB STATUS in 5.5 and I_S table in 5.6 then show values #2 and #4 separately. And the status variable is always the sum of values #2 and #4.

The problem with #4 is that assumes all hash tables are uniformly filled. Which is not necessarily the case. Trivial example: multiple AHI partitions, but just one index using AHI. So depending on whether the hash table for that index happens to be in partition #0, we may get either much higher AHI memory than actually allocated, or much lower value than actually allocated.

[Alexey Kopytov (akopytov)]

Does not technically apply to 5.6, but will be soon once AHI partitioning is ported there.

[Shahriyar Rzayev (rzayev-sehriyar)]

Percona now uses JIRA for bug reports so this bug report is migrated to: https://jira.percona.com/browse/PS-1412