Buffer Pool, Flushing, Dirty Pages, Read-Ahead, and Cache Efficiency

Learning outcomes

ServiceHub’s database server shows a 99.8% buffer-pool hit ratio, yet p95 request latency spikes during reporting scans. A team member concludes that memory cannot be the problem because “almost every read is cached.” That conclusion confuses a ratio with a latency model. InnoDB’s buffer pool caches data/index pages, but query latency can still be dominated by CPU, lock waits, dirty-page flushing, storage stalls, poor access paths, or a scan that churns useful pages out of cache.

A page in the buffer pool is clean when its in-memory contents match the durable page version; it is dirty after modification until background flushing writes an appropriate version to storage. InnoDB uses an LRU-like old/new list policy and read-ahead mechanisms to manage page residency. The useful observability model therefore tracks page population, physical misses, read-ahead usefulness, dirty-page pressure and I/O over time—not one static “hit percentage.”

1. Reset and warm a measurable working set

DROP DATABASE IF EXISTS servicehub_innodb_lab;CREATE DATABASE servicehub_innodb_lab CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub_innodb_lab;CREATE TABLE digits (n TINYINT NOT NULL PRIMARY KEY) ENGINE=InnoDB;INSERT INTO digits VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);CREATE TABLE work_orders (  work_order_id BIGINT NOT NULL AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status VARCHAR(20) NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  summary VARCHAR(180) NOT NULL,  notes LONGTEXT NULL,  PRIMARY KEY (work_order_id),  KEY ix_work_orders_status (status, priority, work_order_id),  KEY ix_work_orders_customer (customer_id, opened_at)) ENGINE=InnoDB ROW_FORMAT=DYNAMIC;INSERT INTO work_orders(customer_id,status,priority,opened_at,summary,notes)SELECT 1 + (x.n % 250),       ELT(1 + (x.n % 4),'queued','open','closed','cancelled'),       1 + (x.n % 5),       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL x.n SECOND,       CONCAT('ServiceHub order ', x.n),       RPAD(CONCAT('diagnostic-note-',x.n,' '), 1200, 'x')FROM (  SELECT a.n + 10*b.n + 100*c.n + 1000*d.n AS n  FROM digits a CROSS JOIN digits b CROSS JOIN digits c CROSS JOIN digits d) AS xWHERE x.n BETWEEN 1 AND 8000;

SHOW VARIABLES WHERE Variable_name IN ('innodb_buffer_pool_size','innodb_buffer_pool_size_max',  'innodb_page_size','innodb_old_blocks_time');SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_pages_total','Innodb_buffer_pool_pages_free',  'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_read_requests',  'Innodb_buffer_pool_reads','Innodb_buffer_pool_read_ahead',  'Innodb_buffer_pool_read_ahead_evicted','Innodb_data_reads','Innodb_data_writes');

Copy these values with a timestamp. Global counters are cumulative since server start or reset, so the absolute value is usually less useful than the change during a controlled interval. A production dashboard should compute rates/deltas and correlate them with request latency, workload volume and storage telemetry.

2. Logical read requests are not physical reads

Innodb_buffer_pool_read_requests counts logical page-read requests satisfied through the buffer-pool interface; Innodb_buffer_pool_reads counts requests that could not be satisfied from the pool and required a page read from storage. A rough interval hit ratio can be calculated from deltas, but that ratio says nothing about how expensive the misses were or whether the query plan read too many pages in the first place.

SELECT SQL_NO_CACHE work_order_id,status,priorityFROM work_ordersWHERE work_order_id BETWEEN 500 AND 900ORDER BY work_order_id;SELECT status, COUNT(*)FROM work_ordersGROUP BY status;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads',  'Innodb_buffer_pool_pages_dirty','Innodb_data_reads');

Run the statements twice and compare deltas. The second pass may generate fewer physical reads because pages are warm, but do not treat that as a production guarantee. Your real working set competes with other tables, indexes, maintenance tasks and large scans.

3. LRU-like old/new lists protect hot pages imperfectly

MariaDB describes the buffer pool as an old/new LRU-like list. Newly read pages first enter the old portion; pages that prove useful can be promoted. innodb_old_blocks_time helps prevent a one-time scan from immediately promoting every scanned page into the hot portion. This is a mechanism for reducing cache pollution, not a substitute for fixing an accidental full scan or provisioning enough memory for the real working set.

If a reporting query touches millions of cold pages, it can still consume I/O bandwidth and buffer-pool space even when promotion rules protect some hot pages. Therefore diagnose the plan and rows/pages examined alongside cache counters. A high hit ratio can be generated by a huge volume of cheap repeated buffer accesses while a small number of expensive misses dominate tail latency.

4. Dirty pages connect memory to background write pressure

A successful UPDATE usually modifies an in-memory InnoDB page first. That page becomes dirty; it does not need to be synchronously written to its final tablespace location on every commit because redo logging provides the crash-recovery mechanism. Background page flushing later writes dirty pages. If dirty-page accumulation, checkpoint pressure or storage latency becomes unfavorable, foreground work can feel the consequences even when reads are well cached.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';START TRANSACTION;UPDATE work_ordersSET priority = CASE WHEN priority=5 THEN 1 ELSE priority+1 ENDWHERE work_order_id BETWEEN 1 AND 1500;COMMIT;SHOW GLOBAL STATUS WHERE Variable_name IN ('Innodb_buffer_pool_pages_dirty','Innodb_data_writes','Innodb_data_written');SHOW ENGINE INNODB STATUS\G

The exact dirty-page count at the second snapshot is nondeterministic because page cleaners work concurrently. That is the point: observe a moving system, not a textbook still image. Use time-series behavior and write latency to decide whether flushing is keeping up.

5. Read-ahead is speculation, so measure usefulness

When InnoDB detects sequential access, it can prefetch nearby pages before the query explicitly requests them. Innodb_buffer_pool_read_ahead counts prefetched pages, while Innodb_buffer_pool_read_ahead_evicted identifies pages read ahead and later evicted without query use. A large evicted fraction during a workload can be evidence that prefetching did not match the access pattern—but counter interpretation still needs a time window and plan context.

SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read_ahead%';SELECT SUM(priority), COUNT(*)FROM work_ordersWHERE work_order_id BETWEEN 1 AND 8000;SHOW GLOBAL STATUS WHERE Variable_name LIKE 'Innodb_buffer_pool_read_ahead%';

Do not tune read-ahead variables from a single 8,000-row laptop scan. The lab teaches how to gather evidence. Production decisions require representative table size, storage latency, concurrency and query plans.

6. Deliberately wrong approach: “allocate 80% of RAM to InnoDB”

Percentage rules are attractive because they turn capacity planning into one number. They are unsafe because the same host may also need memory for the operating system, filesystem metadata, connection/session buffers, query sorts, Galera/replication, monitoring agents, backup processes, containers and other services. The correct buffer-pool size is a budget decision: reserve headroom, estimate active InnoDB working set, observe misses/evictions and latency, then change one variable at a time.

On a disposable local server you can experiment with a modest dynamic resize, but first record innodb_buffer_pool_size_max and leave host headroom. In production, resize only with an explicit rollback value and OS memory monitoring; do not use a tutorial to justify memory pressure.

7. Lab checklist and production bridge

1. Record buffer-pool/page-size configuration and baseline counters.
2. Run the point/range workload twice and compute delta logical requests versus physical reads.
3. Create a controlled update burst and observe dirty-page/write counters.
4. Run an ordered scan and compare read-ahead/read-ahead-evicted deltas.
5. Explain one reason a high hit ratio can coexist with bad p95 latency.
6. Write a memory budget that includes non-buffer-pool consumers before proposing any resize.

The next lesson explains why dirty pages do not have to be written to their final location at COMMIT. Redo, undo, checkpoints and the doublewrite mechanism separate transaction durability from background page flushing and make crash recovery possible.