Buffer Pool Sizing, Working Sets, Dirty Pages, Flushing, and Memory Pressure

Learning outcomes

ServiceHub is now running a meaningful workload, and an operator proposes the familiar rule “give 80% of RAM to InnoDB.” The rule is attractive because it is simple, but it ignores the operating system, connection memory, Performance Schema, temporary work, backup agents, monitoring, and any other process sharing the host. This lesson replaces that slogan with a measurable memory budget.

Mental model: the buffer pool is a cache, not a claim on all RAM

The InnoDB buffer pool is MySQL’s primary cache for InnoDB data and index pages. A working set is the subset of those pages the current workload repeatedly touches. If the active working set fits comfortably, repeated reads can be served from memory. If it does not fit, old pages are evicted and later reread from storage. Neither observation tells you that the buffer pool should consume every byte the host can provide.

A dirty page is a buffer-pool page changed in memory but not yet reflected in its final tablespace location on storage. Redo logging protects durability independently of immediate page flushing. Background flushing, adaptive flushing, checkpoints, and eviction all interact with the dirty-page population. The operating system still needs memory for the kernel, process stacks, filesystem metadata, networking, executable pages, monitoring, and other services. If the combined demand exceeds physical memory, the host may page or swap; under harder pressure, an operating-system out-of-memory mechanism can terminate a process.

Inspect the configured pool and effective evidence

SELECT @@GLOBAL.innodb_buffer_pool_size AS pool_bytes,       @@GLOBAL.innodb_buffer_pool_instances AS pool_instances,       @@GLOBAL.innodb_page_size AS page_bytes;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_pages_flushed',  'Innodb_buffer_pool_wait_free',  'Innodb_buffer_pool_resize_status');

Innodb_buffer_pool_read_requests counts logical read requests, while Innodb_buffer_pool_reads records reads InnoDB could not satisfy from the buffer pool and therefore requested from storage. A low physical-read rate during a stable workload can indicate a warm useful cache, but a lifetime ratio alone is not a capacity model: a restart, batch scan, changing dataset, or idle period can distort it.

Build the reproducible ServiceHub dataset

The lab creates 50,000 moderate-width rows—large enough to create visible work on many laptops without pretending to be production scale. On a very small machine, reduce the final seq < 50000 predicate. Record the row count you actually used so later benchmark results remain reproducible.

DROP DATABASE IF EXISTS servicehub_perf_lab;CREATE DATABASE servicehub_perf_lab  CHARACTER SET utf8mb4 COLLATE utf8mb4_0900_ai_ci;USE servicehub_perf_lab;CREATE TABLE technicians (  technician_id INT PRIMARY KEY,  technician_name VARCHAR(80) NOT NULL) ENGINE=InnoDB;INSERT INTO technicians VALUES(1,'Ava'),(2,'Omar'),(3,'Lina'),(4,'Noah'),(5,'Mina'),(6,'Ravi'),(7,'Sara'),(8,'Leo'),(9,'Nora'),(10,'Kai');CREATE TABLE region_rules (  region_name VARCHAR(20) PRIMARY KEY,  multiplier DECIMAL(5,2) NOT NULL) ENGINE=InnoDB;INSERT INTO region_rules VALUES('north',1.10),('south',1.05),('east',1.00),('west',1.15),('central',1.08);CREATE TABLE work_orders (  work_order_id BIGINT UNSIGNED NOT NULL AUTO_INCREMENT,  technician_id INT NOT NULL,  status VARCHAR(16) NOT NULL,  region VARCHAR(20) NOT NULL,  scheduled_at DATETIME(6) NOT NULL,  labor_minutes INT NOT NULL,  parts_cost DECIMAL(10,2) NOT NULL,  payload VARCHAR(300) NOT NULL,  PRIMARY KEY (work_order_id),  KEY idx_status_schedule (status, scheduled_at, work_order_id),  CONSTRAINT fk_perf_technician FOREIGN KEY (technician_id)    REFERENCES technicians(technician_id),  CONSTRAINT chk_perf_status CHECK (status IN ('open','assigned','closed','cancelled'))) ENGINE=InnoDB;CREATE TABLE d10 (n TINYINT PRIMARY KEY);INSERT INTO d10 VALUES (0),(1),(2),(3),(4),(5),(6),(7),(8),(9);INSERT INTO work_orders(technician_id,status,region,scheduled_at,labor_minutes,parts_cost,payload)SELECT 1 + MOD(seq,10),       ELT(1+MOD(seq,4),'open','assigned','closed','cancelled'),       ELT(1+MOD(seq,5),'north','south','east','west','central'),       TIMESTAMP('2026-01-01 00:00:00')         + INTERVAL MOD(seq,180) DAY         + INTERVAL MOD(seq,86400) SECOND,       15 + MOD(seq,220),       ROUND(5 + MOD(seq,8000)/10,2),       RPAD(CONCAT('WO-',seq,' service payload '),240,'x')FROM (  SELECT a.n + 10*b.n + 100*c.n + 1000*d.n + 10000*e.n AS seq  FROM d10 a CROSS JOIN d10 b CROSS JOIN d10 c CROSS JOIN d10 d CROSS JOIN d10 e) AS numbersWHERE seq < 50000;ANALYZE TABLE work_orders;SELECT COUNT(*) AS work_orders,       MIN(scheduled_at) AS first_time,       MAX(scheduled_at) AS last_timeFROM work_orders;

Observe warm-up instead of declaring a hit ratio target

SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads', 'Innodb_data_reads','Innodb_data_read');

SELECT SUM(labor_minutes) AS minutes,       SUM(parts_cost) AS partsFROM servicehub_perf_lab.work_ordersWHERE scheduled_at >= '2026-02-01'  AND scheduled_at <  '2026-05-01';SELECT SUM(labor_minutes) AS minutes,       SUM(parts_cost) AS partsFROM servicehub_perf_lab.work_ordersWHERE scheduled_at >= '2026-02-01'  AND scheduled_at <  '2026-05-01';SHOW GLOBAL STATUS WHERE Variable_name IN ( 'Innodb_buffer_pool_read_requests','Innodb_buffer_pool_reads', 'Innodb_data_reads','Innodb_data_read');

Compute the before/after deltas. On a pool large enough for this working set, the second run often requires fewer new physical reads. That is an observation to verify locally, not a guaranteed ratio. OS filesystem cache, concurrent activity, prefetch/read-ahead, and a working set larger than the pool can all change the result.

Create dirty pages, then watch background work

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_flushed';START TRANSACTION;UPDATE servicehub_perf_lab.work_ordersSET labor_minutes = labor_minutes + 1WHERE work_order_id BETWEEN 1 AND 5000;COMMIT;SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_flushed';-- Re-run after 5–15 seconds and compare. Background flushing may change both values.SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_dirty';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_pages_flushed';

The dirty-page count may rise during the write burst and later fall as page cleaners flush. Timing varies by storage speed, redo/checkpoint pressure, existing dirty pages, and adaptive flushing. The lesson is not “dirty pages are bad”; it is to correlate dirty-page state with workload and storage behavior.

Correlate MySQL with the host

Database counters cannot tell you whether the host is swapping aggressively or whether another process is consuming memory. Capture host evidence at the same time as the SQL workload.

Get-CimInstance Win32_OperatingSystem |  Select-Object TotalVisibleMemorySize,FreePhysicalMemory,TotalVirtualMemorySize,FreeVirtualMemoryGet-Counter '\Memory\Available MBytes','\Memory\Pages/sec' -SampleInterval 1 -MaxSamples 5

free -hvmstat 1 5# If available, inspect mysqld RSS without changing the system:ps -o pid,rss,vsz,cmd -C mysqld

Available memory trending toward zero while paging rises is a different diagnosis from a stable host with spare memory and occasional buffer-pool reads. On Linux, an OOM kill is normally visible in kernel/system logs; on Windows, memory-commit pressure and application/service termination require OS event evidence. Do not induce host OOM on a workstation just to “prove” the concept.

Wrong approach: allocate by slogan

A safer sizing workflow is: measure the active dataset/working set and physical-read behavior; inventory non-buffer-pool mysqld memory; model concurrent per-session demand; reserve OS/tooling headroom; choose a conservative pool size; change it on a disposable/staged instance; then verify service latency, physical-read rate, host paging, dirty-page behavior, and total process/host memory.

SELECT VARIABLE_NAME, VARIABLE_VALUE, VARIABLE_SOURCEFROM performance_schema.variables_infoWHERE VARIABLE_NAME='innodb_buffer_pool_size';SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_resize_status';

innodb_buffer_pool_size is dynamically resizable in MySQL 8.4, but a dynamic knob is not automatically a safe knob. The server adjusts sizes to buffer-pool chunk/instance constraints, and resizing can temporarily consume resources. Test changes with workload evidence and rollback criteria.

Production judgment

Increase the pool when repeated production evidence shows a useful working set is being reread from storage and the host has durable memory headroom. Decrease it when the host is under memory pressure or when a smaller pool meets the workload while releasing memory needed elsewhere. On containerized systems, plan against the memory limit visible to the workload rather than physical host RAM. On shared hosts, coordinate with the OS/platform owner.

Lesson 2 extends the budget from the large global cache to memory that multiplies with active sessions and query operations—where one “small” buffer setting can become a concurrency problem.

Authoritative references

• MySQL 8.4 Reference Manual
• MySQL Community Server 8.4 downloads
• Configuring InnoDB Buffer Pool Size
• InnoDB Buffer Pool
• Configuring Buffer Pool Flushing
• Server Status Variables