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)
---------------------- 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 Patch: innodb_io_pattern