This page has been moved, the new location is Show Hashed Memory.

InnoDB allocates memory dynamically. If allocated memory of InnoDB is growing, we have to consider why the memory is used. In InnoDB, most of such dynamic memory is used in internal hash tables.

This patch shows InnoDB internal hash table sizes (in bytes) in SHOW INNODB STATUS in BUFFER POOL AND MEMORY section. It also adds Buffer pool size in bytes information. (constant factor: size of pointer array for each hash value, variable factor: size of the dynamically allocated memory)

Patch depends on Patch: innodb_io_pattern. (For display only)

  Total memory allocated 328830416; in additional pool allocated 2117120
+ Internal hash tables (constant factor + variable factor)
+     Adaptive hash index 4839388         (4425628 + 413760)
+     Page hash           138716
+     Dictionary cache    3383508         (3320220 + 63288)
+     File system         41848   (41336 + 512)
+     Lock system         332788  (332468 + 320)
+     Recovery system     0       (0 + 0)
+     Threads             41900   (41348 + 552)
+     innodb_io_pattern   0       (0 + 0)
  Buffer pool size        16384
+ Buffer pool size, bytes 268435456
  Free buffers            12396
  Database pages          3963
  Modified db pages       1510
  Pending reads 0

Possible heavy consumers are…

  • Dictionary cache : do you use too many tables?
  • Lock system : do you use too many record locks?
  • innodb_io_pattern : do you have too big innodb_io_pattern_size_limit?

Supplementation of what each hash tables contain

  • Adaptive hash index: All records cached in the buffer pool. This is always created and maintained even if “innodb_adaptive_hash_index = false”.
  • Page hash: All data pages cached in the buffer pool. The each entry is blocks of buffer pool.
  • Dictionary cache: Metadata of opened tables. Generally, InnoDB doesn't remove the cached entries except when the table is removed.
  • File system: All files (datafile / transaction log file) used by InnoDB.
  • Lock system: All table-level/record-level locks.
  • Recovery system: Which page is operated by which log records. It is constructed only during crash recovery. It must be 0 for ordinary use.
  • Threads: Each threads' private data related to InnoDB.
  • innodb_io_pattern: It is not original component of InnoDB. Please see Patch: innodb_io_pattern
patches/innodb_show_hashed_memory.txt · Last modified: 2015/08/05 15:37 (external edit)
Except where otherwise noted, content on this wiki is licensed under the following license:CC Attribution-Noncommercial-Share Alike 3.0 Unported
Contact Us 24 Hours A Day
SupportContact us 24×7
Emergency? Contact us for help now!
Sales North America(888) 316-9775 or
(208) 473-2904
+44-208-133-0309 (UK)
0-800-051-8984 (UK)
0-800-181-0665 (GER)
Training(855) 55TRAIN or
(925) 271-5054


Share This
]]> ]]>