InnoDB Buffer Pool memory growth during write-heavy workloads

Answer to the two questions

First, the direct answers:

1. Does performance_schema.memory_summary_global_by_event_name capture adaptive hash index allocations? – Yes, when the memory instrument memory/innodb/adaptive_hash_index is enabled, the table records those allocations.
2. How does innodb_adaptive_flushing interaction affect reported memory usage? – The algorithm changes the dirty‑page ratio inside the fixed‑size buffer pool but does not alter innodb_buffer_pool_size. Reported memory usage from performance_schema is the sum of buffer pool, adaptive hash index, change buffer, and other tracked InnoDB components, so total memory stays stable while the dirty‑page fraction rises.

Confirmed facts (from the research brief)

• The performance_schema.memory_summary_global_by_event_name table includes instruments for memory/innodb/adaptive_hash_index, memory/innodb_buffer_pool, and memory/innodb_change_buffer.
• With innodb_adaptive_flushing enabled (default), MySQL 8.0 dynamically adjusts the flush rate based on redo log usage; when flushing lags, the proportion of dirty pages increases but the buffer pool size remains unchanged.
• Adaptive hash index memory is accounted for separately from buffer pool memory; total memory usage shown by performance_schema is the sum of these components.
• Enabling the relevant memory instruments adds minimal overhead (<1% on typical workloads).

Likely explanation

During sustained write‑heavy workloads, the adaptive hash index may grow modestly as more page lookups benefit from hashing, but this growth is independent of the flushing algorithm. If the flushing mechanism cannot keep pace with redo log generation, dirty pages accumulate inside the buffer pool, raising the dirty‑page percentage while the overall buffer pool footprint stays at innodb_buffer_pool_size. Consequently, any observed memory increase is most likely due to the adaptive hash index (or change buffer) rather than a failure of the buffer pool to release memory.

Steps needed for this case

1. Verify that the InnoDB memory instruments are enabled:

SELECT NAME, ENABLED
FROM setup_instruments
WHERE NAME LIKE 'memory/innodb/%';

1. If any instrument is disabled, enable it (requires appropriate privileges):

UPDATE setup_instruments SET ENABLED = 'YES'
WHERE NAME LIKE 'memory/innodb/%';
UPDATE setup_consumers SET ENABLED = 'YES'
WHERE NAME LIKE 'events_statements_current' OR NAME LIKE 'events_waits_current';

1. Sample memory usage over time:

SELECT EVENT_NAME,
       SUM_CURRENT_NUMBER_OF_BYTES/1024/1024 AS MB_USED
FROM performance_schema.memory_summary_global_by_event_name
WHERE EVENT_NAME LIKE 'memory/innodb/%'
GROUP BY EVENT_NAME;

1. Check adaptive flushing status and dirty‑page ratio:

SHOW GLOBAL VARIABLES LIKE 'innodb_adaptive_flushing';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty',
                        'Innodb_buffer_pool_pages_total';

1. Correlate spikes in memory/innodb/adaptive_hash_index with changes in dirty‑page percentage and redo log write rate (Innodb_os_log_written) during a benchmark.

Missing diagnostic detail

To confirm whether the observed memory growth is due to the adaptive hash index or simply a higher dirty‑page ratio, we need to know if the InnoDB memory instruments are currently enabled. If they are not, enabling them (step 1‑2) is required before the measurements in steps 3‑5 can be trusted.