Show Hashed Memory

InnoDB allocates memory dynamically. If the allocated memory in 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 feature shows InnoDB internal hash table sizes (in bytes) in SHOW INNODB STATUS, in the 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)

The feature depends on Data Page I/O Activity (for display in versions based on MySQL 5.0 only).

  ----------------------
  BUFFER POOL AND MEMORY
  ----------------------
  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 Data Page I/O Activity