Buffer Pool Architecture, Change Buffer, Adaptive Hashing, and Caching Behavior

Learning outcomes

When an application reads an InnoDB row, it usually benefits from several cache layers: application caches, operating-system caches depending on I/O configuration, and most importantly InnoDB’s own buffer pool. Tuning mistakes happen when people collapse those layers into “RAM” and then read one hit ratio as proof that more memory—or less memory—will fix everything.

The buffer pool is InnoDB’s working set

The buffer pool is memory managed by InnoDB to cache table and index pages. Reading a page already in the pool avoids an InnoDB data-file read. Modifying a cached page makes it dirty: memory now contains a newer page image than the durable tablespace copy. Background flushing eventually writes dirty pages to durable storage, coordinated with redo and checkpoint progress.

SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','innodb_buffer_pool_instances',  'innodb_old_blocks_pct','innodb_read_ahead_threshold',  'innodb_random_read_ahead','innodb_adaptive_hash_index',  'innodb_change_buffering');SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_pages_data',  'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_pages_free',  'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_buffer_pool_read_ahead','Innodb_buffer_pool_pages_flushed');

Innodb_buffer_pool_read_requests counts logical read requests; Innodb_buffer_pool_reads counts reads InnoDB could not satisfy from the buffer pool and therefore had to read from storage. The relationship is useful over a defined workload interval, but a high cache-hit ratio can coexist with a single business-critical query doing expensive physical I/O.

LRU-style aging: hot pages are not immortal

InnoDB maintains an LRU-like list with “young” and “old” regions rather than a naive exact least-recently-used queue. The design helps prevent a one-time large scan from immediately evicting every frequently used page. Read-ahead can prefetch pages when access patterns indicate sequential behavior.

Do not set innodb_buffer_pool_size to a memorized percentage without considering other MySQL memory, per-connection buffers, the operating system, monitoring agents, containers/cgroups, and co-located services.

Warm versus cold-ish: a local observation, not a benchmark claim

We cannot safely promise a truly cold buffer pool without restarting or explicitly manipulating cache state, which would itself change the experiment. Instead, measure a first run and repeated runs on the same dataset and label them honestly as cold-ish versus warm on this local instance.

USE servicehub_innodb_lab;SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';SELECT COUNT(*), SUM(OCTET_LENGTH(note_text))FROM work_order_notesWHERE work_order_id BETWEEN 1 AND 100;SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_reads';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read_requests';-- Run the SELECT again, then capture the counters again.

Run the same SELECT again and compare the InnoDB counters before and after each execution. Current MySQL has no old-style query cache to disable here; the observation is about InnoDB page-cache reuse, not a query-result cache. The point is to measure your instance, not manufacture a perfect benchmark.

Use buffer-pool statistics carefully

SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES,       MODIFIED_DATABASE_PAGES, PENDING_READS,       PENDING_FLUSH_LRU, PENDING_FLUSH_LIST,       PAGES_READ, PAGES_WRITTENFROM information_schema.INNODB_BUFFER_POOL_STATSORDER BY POOL_ID;

These are current or cumulative counters, not service-level objectives. Capture timestamps and deltas around a known workload. Some lower-level views such as INNODB_BUFFER_PAGE can be expensive to query on large production systems; use them on a test instance unless you have evaluated the overhead.

Change buffer: secondary-index work deferred—when enabled

The change buffer can cache changes to eligible secondary-index pages when those pages are not currently in the buffer pool, merging them later when pages are read. It does not apply to the clustered index, and eligibility has further restrictions. In MySQL 8.4 the default innodb_change_buffering is none, so a default 8.4 server does not actively buffer new secondary-index changes.

SHOW VARIABLES LIKE 'innodb_change_buffering';SELECT NAME, COUNT, STATUS, COMMENTFROM information_schema.INNODB_METRICSWHERE NAME LIKE '%ibuf%'ORDER BY NAME;

Do not enable it because an old MySQL 5.7/8.0 article says it helps write-heavy workloads. The 8.4 default changed deliberately. Any change should be tested with representative storage, working-set size, and secondary-index DML.

Adaptive hash index: acceleration is workload-dependent

The adaptive hash index (AHI) can build hash-based shortcuts for frequently accessed B-tree pages. It is an internal optimization, not a user-defined HASH index. MySQL 8.4 defaults innodb_adaptive_hash_index to OFF. On older/current customized systems it may be on, so verify the variable.

SHOW VARIABLES LIKE 'innodb_adaptive_hash_index';SHOW GLOBAL STATUS LIKE 'Innodb_adaptive_hash%';

Turning AHI on or off should be an evidence-driven experiment. Some workloads can benefit; others can see contention or no meaningful gain. The wrong lesson is “AHI is faster.” The right lesson is “AHI is an optional internal shortcut whose value depends on access patterns and concurrency.”

Failure drill: reading one hit ratio as a tuning verdict

A common shortcut computes 1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests since startup and declares the server healthy if the result is near 1.0. That aggregate can hide bursty physical reads, one damaging report query, write-flush pressure, or a completely different latency source.

SELECT NOW(3) AS captured_at;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_pages_flushed');-- Run the controlled workload here.SELECT NOW(3) AS captured_at;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_pages_flushed');

Hands-on lab and checks

Run a controlled SELECT loop, then a bounded UPDATE loop, and capture counters before/after. Do not flush operating-system caches or restart a shared MySQL service just to create a prettier graph.

START TRANSACTION;UPDATE work_ordersSET priority = CASE priority WHEN 3 THEN 2 ELSE priority + 1 ENDWHERE work_order_id IN (1,2,3);SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';ROLLBACK;SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';

Rollback undoes the logical changes, but background engine work and counters do not necessarily “rewind” to a previous number. Metrics are observations of engine activity, not transactionally rolled-back business data.

Separate three cache questions before changing memory

When a database host is slow, “the cache” can mean at least three different things. An application may cache business objects or query results. The operating system may cache filesystem blocks depending on InnoDB’s configured I/O method. InnoDB itself caches logical database pages in the buffer pool. Enlarging one layer does not automatically help another, and duplicate caching can increase memory pressure.

A production tuning session should therefore correlate MySQL counters with host/container memory limits and device I/O. If the process is swapping, a larger buffer pool can make things worse even when the database working set would happily use more cache. If the storage device is saturated by dirty-page flushing, a read-cache hit ratio alone misses the real constraint.

Dirty pages connect memory to the redo/checkpoint subsystem

A dirty page is not “unsafe memory.” Its durability is coordinated with redo. InnoDB can commit a transaction while the final data page remains dirty in the buffer pool because the redo subsystem records what recovery needs. Background flushing gradually makes data files catch up. That is why dirty-page count, pages flushed, redo generation, checkpoint distance, and storage latency form one system rather than separate tuning topics.

Read-ahead should be observed in context

Sequential scans can make prefetching useful; random point lookups can make prefetched pages less useful. The existence of Innodb_buffer_pool_read_ahead does not mean every prefetched page was helpful. MySQL also exposes counters for read-ahead behavior that can be studied over a controlled scan. Before changing read-ahead settings, identify a workload whose I/O pattern you can reproduce and compare against a baseline.

Production judgment and bridge

Buffer-pool tuning is capacity engineering: working set, access paths, write rate, dirty-page pressure, storage latency, and total host memory all matter. Old “set it to 80%” rules are not substitutes for measurement, especially in containers or shared hosts. Similarly, do not revive change buffering or adaptive hashing just because they sound like caches.

Lesson 4 follows one update from this cached state into durability: redo logging, undo history, doublewrite protection, checkpoint advancement, and crash recovery.

Authoritative references

• MySQL 8.4 Reference Manual — Buffer Pool
• MySQL 8.4 Reference Manual — Change Buffer
• MySQL 8.4 Reference Manual — Adaptive Hash Index
• MySQL 8.4 Reference Manual — INNODB_BUFFER_POOL_STATS
• MySQL 8.4 Reference Manual — Changes in MySQL 8.4 defaults