Buffer Pool Sizing, Dirty Pages, Flushing, Read-Ahead, and Cache Hit Interpretation

Learning outcomes

ServiceHub has grown until the database host shows periodic storage reads and write stalls. The tempting response is to set innodb_buffer_pool_size to a familiar percentage of RAM. That is not performance engineering: it ignores the active working set, container or cgroup limits, other MariaDB memory, the operating-system cache, dirty-page pressure, and whether the workload is read- or write-bound. This lesson builds an evidence chain before any resize.

1. Mental model: pages move through memory, not “queries through a cache”

InnoDB stores table and index content in fixed-size pages. A logical query can touch many pages; some may already be resident in the buffer pool, while others require a physical read. A modified resident page becomes a dirty page: its newer version exists in memory and must eventually be written to the tablespace. A page can therefore be cached yet still create future write pressure.

A useful hit estimate must use a defined observation window: 1 - delta(reads)/delta(read_requests). Using lifetime counters after weeks of uptime can hide a severe five-minute regression. Chapter 17’s observability discipline applies directly here: record the counter start values, run a named workload phase, then take end values.

2. Build a small workload and capture a before-state

DROP DATABASE IF EXISTS servicehub18;CREATE DATABASE servicehub18 CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;USE servicehub18;CREATE TABLE tickets (  ticket_id BIGINT PRIMARY KEY AUTO_INCREMENT,  customer_id BIGINT NOT NULL,  status ENUM('open','waiting','closed') NOT NULL,  priority TINYINT NOT NULL,  opened_at DATETIME(6) NOT NULL,  updated_at DATETIME(6) NOT NULL,  summary VARCHAR(240) NOT NULL,  INDEX ix_status_opened(status, opened_at),  INDEX ix_customer_updated(customer_id, updated_at)) ENGINE=InnoDB;INSERT INTO tickets(customer_id,status,priority,opened_at,updated_at,summary)WITH RECURSIVE seq AS (  SELECT 1 AS n  UNION ALL SELECT n+1 FROM seq WHERE n < 1000)SELECT MOD(n,125)+1,       ELT(MOD(n,3)+1,'open','waiting','closed'),       MOD(n,5)+1,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       TIMESTAMP('2026-08-01 00:00:00') + INTERVAL n MINUTE,       CONCAT('ServiceHub ticket ',n)FROM seq;SELECT VERSION() AS server_version, @@version_comment AS build_comment,       @@innodb_buffer_pool_size AS buffer_pool_bytes;SELECT COUNT(*) AS seeded_rows FROM tickets;

SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads', 'Innodb_buffer_pool_pages_total','Innodb_buffer_pool_pages_free', 'Innodb_buffer_pool_pages_dirty','Innodb_buffer_pool_read_ahead', 'Innodb_buffer_pool_read_ahead_evicted','Innodb_pages_written');SHOW GLOBAL VARIABLES WHERE Variable_name IN ( 'innodb_buffer_pool_size','innodb_buffer_pool_size_max', 'innodb_buffer_pool_size_auto_min','innodb_page_size', 'innodb_old_blocks_time','innodb_read_ahead_threshold');SHOW ENGINE INNODB STATUS\G

Record the timestamp and uptime with the snapshot. On current MariaDB lines, buffer-pool resize behavior changed in maintenance releases including 11.8.2: innodb_buffer_pool_chunk_size is deprecated/ignored and sizing can use one-megabyte increments, while innodb_buffer_pool_size_max establishes the startup ceiling for manual upward resizing. Do not copy a resize procedure from an older MySQL or MariaDB release without checking the target release.

3. Observe warmup and working-set behavior

-- Snapshot A in your notes first.SELECT SQL_NO_CACHE COUNT(*)FROM servicehub18.ticketsWHERE status='open' AND opened_at >= '2026-08-01';SELECT ticket_id, customer_id, summaryFROM servicehub18.ticketsWHERE customer_id BETWEEN 20 AND 80ORDER BY updated_at DESCLIMIT 200;-- Repeat the same two statements several times, then snapshot B.SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_ahead','Innodb_buffer_pool_read_ahead_evicted');

The first pass can legitimately read from storage because the working set is cold. Later passes should usually require fewer physical reads if the needed pages remain resident. If physical reads continue while the buffer pool has almost no free pages, investigate whether the active working set exceeds the available cache, whether large scans are displacing hotter pages, and whether the container or host is itself under memory pressure.

4. Dirty pages connect memory tuning to write I/O

Make a controlled write burst, then inspect dirty-page and flushing evidence. A larger buffer pool can absorb more modified pages, but that only postpones writes; durability still requires data-file flushing and redo persistence. If storage cannot sustain the long-term write rate, cache size alone cannot solve the problem.

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';SHOW GLOBAL STATUS LIKE 'Innodb_pages_written';UPDATE servicehub18.ticketsSET priority = IF(priority=5,1,priority+1), updated_at=NOW(6)WHERE ticket_id BETWEEN 1 AND 800;SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';SHOW GLOBAL STATUS LIKE 'Innodb_pages_written';SHOW ENGINE INNODB STATUS\G

Interpret the result together with storage latency and the redo/checkpoint state introduced in Lesson 4. A dirty-page percentage has no universal “correct” value. What matters is whether the system has enough sustained flushing headroom to keep checkpoint age and latency controlled through expected write bursts.

5. The wrong approach: maximize the pool until the OS nearly has nothing

A learner may reason that disk reads are slow, so assigning nearly all RAM to InnoDB must be optimal. In a container, that can exceed the memory limit after connection buffers, Performance Schema, thread stacks, temporary tables, binaries, and page-cache needs are included. The resulting symptom may be swap pressure, cgroup throttling, or an out-of-memory kill—not a neat MariaDB error.

SELECT @@GLOBAL.innodb_buffer_pool_size,       @@GLOBAL.innodb_buffer_pool_size_max;-- BAD HABIT: choosing 90% from a blog without a memory budget.-- SET GLOBAL innodb_buffer_pool_size = 15*1024*1024*1024;-- Safer lab pattern: make a small reversible change only if it is-- below innodb_buffer_pool_size_max and the host/container has headroom.SET @old_pool := @@GLOBAL.innodb_buffer_pool_size;SELECT @old_pool AS old_pool_bytes;

For production, calculate a budget first and resize one step at a time. Verify SELECT @@GLOBAL.innodb_buffer_pool_size, watch error logs, RSS/cgroup memory, swap, latency percentiles, physical-read deltas, and dirty-page behavior. If the pool does not change to the requested value, check the target release’s block-size/ceiling rules rather than assuming the command succeeded exactly.

6. Reproducible lab: prove whether the pool is the limiting resource

Prerequisites: MariaDB Community Server 12.3.2 for the current lab baseline (or the curriculum’s 11.8 LTS line with version differences noted), InnoDB, a local account allowed to read global status/variables, and host/container memory visibility. No paid tooling or multi-node topology is required.

1. Create the disposable schema and record server version, uptime, buffer-pool variables, host/container memory limit, and baseline counters.
2. Run the cold read phase once; record physical-read and logical-read deltas plus elapsed time.
3. Repeat the same phase; record the new deltas and explain the warm-cache difference.
4. Run the bounded update phase; observe dirty pages and page writes rather than changing settings immediately.
5. If your lab has safe headroom and the target version permits the resize, make one modest change; rerun the identical workload and compare. Otherwise, perform the reasoning exercise without changing the server.
6. Restore the original buffer-pool value if changed and DROP DATABASE servicehub18;.

Production judgment and bridge

Buffer-pool tuning is appropriate when the working set and physical-read evidence show cache pressure and the host has proven memory headroom. It is not a substitute for index/query design, storage capacity, or write-path tuning. Monitor workload-window read misses, dirty pages, page writes, storage latency, process/container memory, swap/OOM events, and restart warmup behavior. The next lesson completes the memory budget by adding the less obvious multiplier: per-session and per-operation memory.

Authoritative references

Use the target-version tab or release notes when a variable or default differs from the course baseline. These lessons intentionally avoid treating old tuning folklore as current MariaDB behavior.

• InnoDB Buffer Pool
• InnoDB system variables
• MariaDB memory allocation