--- title: "Why Your Benchmark Measured the Cache" description: "A query run five times in a row reports the speed of memory, not of the system. Which layer answered the request is the first thing to establish." url: https://sqlpress.com/blog/benchmark-measured-the-cache/ category: SQL Fundamentals tags: ["benchmarking", "performance", "query-optimization", "execution-plans"] author: "Elias Rowe" published: 2026-10-25T00:00:00.000Z measured: false --- # Why Your Benchmark Measured the Cache ## Key takeaways - Repeating a query on a small dataset measures the buffer pool, not the query: after the first run every page it touches is already in memory. - At least three caches sit under a query — the database buffer pool, the operating system page cache, and the storage device — and a miss in the first is not a disk read. - A warm cache is a legitimate thing to measure, because production is usually warm. Not stating which regime the number came from is what makes it useless. - The ratio of working-set size to memory has to resemble production; scaling the machine down without scaling the data down changes the regime being tested. ## Test environment - System: PostgreSQL 18 - System: MySQL 8.4 - Workload: Illustrative (examples verified, nothing timed) - Notes: No timings are reported here. The example numbers in the opening are illustrative of the shape of the problem, not measurements from a specific run. A query that takes 340 ms once and 12 ms on the next four runs has not been measured. The first number includes moving pages into memory; the rest report the cost of finding them already there. Which of the two an application experiences depends on facts about production that the benchmark has not reproduced. ## Three caches sit under every query The database keeps its own buffer pool — `shared_buffers` in PostgreSQL, `innodb_buffer_pool_size` in MySQL. Underneath it, the operating system keeps a page cache of file contents. Underneath that, the storage device has a cache of its own. Each layer is invisible from the one above. A page absent from the buffer pool is requested from the operating system, which may return it from the page cache without the device being involved at all. That is why "buffer pool miss" and "disk read" are not synonyms, and why a benchmark can report I/O that never happened. ## What the BUFFERS counters can and cannot tell you In PostgreSQL, `shared hit` means the page was found in the buffer pool and `read` means it was not. [BUFFERS is what separates a cold cache from a genuinely expensive plan](/blog/explain-analyze-buffers/), and that holds — but it bounds the answer from one side only. `read` counts pages PostgreSQL fetched, not pages the device served. Separating the second layer from the third needs timing rather than counters. `track_io_timing` is off by default and turning it on costs something — it queries the operating system clock repeatedly — but with it enabled the plan reports the wall time spent in those reads. An average far below the read latency the storage can deliver is the page cache; compare it against the device's published figures rather than a fixed threshold. ## The dataset decides the regime more than the query does A 2 GB table under 8 GB of buffers is fully resident after one pass, whatever the query does. Every comparison run against it is a comparison of CPU and access-path efficiency with the I/O term fixed at zero. That is a real measurement. It is not the one that predicts behavior on a 400 GB table with the same 8 GB of buffers, where physical correlation, read-ahead, and the number of random reads an access path implies start to dominate — the regime in which [a row estimate, rather than the index itself, decides the plan](/blog/index-not-used-estimate-not-index/). The constraint that follows is about ratios: the working set relative to memory in the benchmark has to resemble the ratio in production. Renting a smaller machine and keeping the full dataset preserves it. Keeping the machine and shrinking the dataset does not. ## Warm is a legitimate thing to measure; unstated is not A steady-state OLTP service runs warm nearly all the time. Benchmarking it cold measures a state it occupies for a few minutes a week, usually right after a restart. Warm is the correct choice there. Two questions decide whether the result means anything: was the cache warmed before timing, and was the same warming applied to every variant. The second produces more false results than the first, because variant B looks faster for having run after variant A left the pages resident. ## Forcing the regime you intend to test :::warning Both steps need root, and neither is available on a managed service. On a shared host, dropping the page cache slows every other process on the machine until it refills. ::: ```bash title="cold-run.sh" # Cold: the buffer pool goes with the process, the page cache needs its own step. sudo systemctl stop postgresql sync && echo 3 | sudo tee /proc/sys/vm/drop_caches sudo systemctl start postgresql ``` ```sql title="warm-run.sql" -- Warm and repeatable: put the same pages in the pool before every variant. CREATE EXTENSION IF NOT EXISTS pg_prewarm; SELECT pg_prewarm('orders'); SELECT pg_prewarm('orders_customer_id_idx'); ``` Deliberate prewarming beats "run it five times and take the last one" because it gives every variant an identical starting state instead of whatever state its predecessor left behind. ## Limits Cache regime is one way a benchmark can be confidently wrong, not the only one. A single query repeated in a loop still has no concurrency, no lock contention, and no competing writes, and none of the above detects that. Dropping the page cache also requires root, so on a managed service the only lever left is dataset size — which is the more honest lever anyway. ## FAQ ### Why is a database query faster the second time I run it? The first execution reads pages from storage into memory. The second finds them already resident in the database's buffer pool, or failing that in the operating system page cache, so the I/O term of the cost is close to zero. The difference between the two runs is the cost of the reads, which means a timing taken after several warm-up runs reports how fast the machine can process cached pages rather than how fast the query is. ### Does a read in EXPLAIN (ANALYZE, BUFFERS) mean the page came from disk? No. The counters describe PostgreSQL's own buffer pool: shared hit means the page was found there, and read means it was not and had to be requested from the operating system. That request is frequently served from the OS page cache without the storage device being touched. To separate the two, enable track_io_timing and look at the time attributed to those reads; an average far below what the storage can deliver is the page cache, not a device. ### How do I clear the database cache before a benchmark? On a self-managed Linux host, stopping the database clears its buffer pool and dropping the OS caches clears the page cache; both are needed, and both require root. On a managed service neither is available, so the practical alternative is to make the working set far larger than the memory available, which puts the system into the cold regime without needing to clear anything. ### Should a database benchmark run on a warm or a cold cache? It depends on which regime the production system spends its time in. A steady-state OLTP service is warm almost always, so a cold measurement describes a state it occupies for minutes per week. A reporting workload that scans data nobody has touched in a month is genuinely cold. The error is not choosing one; it is reporting a number without saying which one it is, or warming one variant and not the other. ## Sources - [PostgreSQL 18 documentation — Resource Consumption](https://www.postgresql.org/docs/18/runtime-config-resource.html) (docs) — shared_buffers and its relationship to the operating system cache. - [PostgreSQL 18 documentation — EXPLAIN](https://www.postgresql.org/docs/18/sql-explain.html) (docs) — Defines what the BUFFERS counters do and do not report. - [PostgreSQL 18 documentation — pg_prewarm](https://www.postgresql.org/docs/18/pgprewarm.html) (docs) - [PostgreSQL 18 documentation — Run-time Statistics](https://www.postgresql.org/docs/18/runtime-config-statistics.html) (docs) — track_io_timing is off by default and repeatedly queries the OS clock. - [MySQL 8.4 Reference Manual — The InnoDB Buffer Pool](https://dev.mysql.com/doc/refman/8.4/en/innodb-buffer-pool.html) (docs)