Parameters (InnoDB buffer pool)
About this exampleInnoDB midpoint-insertion LRU
Textbook single-list LRU
About this example
Example query this animation executes
-- Act 1: Your OLTP workload keeps 8 hot pages in the pool SELECT * FROM users WHERE id = 42; -- repeated point lookups -- Act 2: A reporting query runs a full table scan SELECT SUM(amount) FROM events WHERE event_date >= '2026-01-01'; -- Act 3: OLTP workload returns — same 8 pages SELECT * FROM users WHERE id = 42; -- hit or miss?
Watch what happens to the 8 blue pages during the scan.
What you'll see in the animation: a 3-act story
- Each cell is one 16 KiB page, labelled table:page (users:42, events:1001). Blue = your hot OLTP pages, orange = pages the reporting scan reads, green = a hit in act 3, red = a miss read from disk again.
- Act 1, 'The hot set': 8 blue OLTP pages fill both pools. Each was read again more than innodb_old_blocks_time (1,000 ms) after its first read, so InnoDB keeps it in the young sublist.
- Act 2, 'The scan arrives': orange events pages stream in. In the textbook LRU (right) every new page goes to the head and pushes the blue pages toward eviction. In InnoDB (left) a page read from disk enters the old sublist at the midpoint, and the scan's quick re-reads of the same page leave it there.
- Act 3, 'Hot queries return': the same 8 blue pages are read again. With the default settings the textbook LRU misses all 8 (they were evicted) and InnoDB hits all 8. Raise innodb_old_blocks_pct and the young sublist gets too small for the hot set.
- The drawing keeps both sublists at fixed sizes, as in a full pool: once the old sublist has no empty cell, no free page is left, so each read from disk evicts the page at the tail. The line under each pool says what the last step did; the counters track evictions and hits.
- Next step plays one step at a time; the simplified code beside the drawing highlights the InnoDB lines each step runs.
Simulation results
InnoDB: young pages ?Pages in the hot (MRU) half. These survive a full scan because new pages only enter the old sublist.
—
InnoDB: evictions ?Pages kicked out of the old sublist tail. During a scan these are scan pages, not your hot pages.
—
Act 3 — InnoDB hits ?When the hot queries return in Act 3, how many find their page still in the pool. 8/8 with the default settings: the scan never reached the young sublist.
—
Classic LRU: evictions ?Pages kicked out during the scan. In a textbook LRU, these are your hot OLTP pages — the scan pushed them all out.
—
Act 3 — Classic hits ?When the hot queries return, how many find their page. 0/8 after a scan larger than the pool: they were all evicted.
—
Learn more — why is a full table scan a problem for plain LRU?
Imagine a 1 GB buffer pool and a 10 GB table. Under textbook LRU, reading the whole table once visits every page exactly once — and each visit bumps that page to the head of the list. By the end of the scan, every single page that used to be hot has been evicted. Your OLTP working set just got destroyed by a reporting query.
InnoDB's answer is midpoint-insertion LRU:
- The linked list is split into a young sublist (MRU end,
~5/8) and an old sublist (LRU end, ~3/8). The split is
innodb_old_blocks_pct(default 37). - On a cache miss, the new page enters at the
midpoint, NOT the head of the list: InnoDB links it just
behind the first page of the old sublist (
LRU_old); the drawing simplifies that to the head of the old sublist. (A list shorter than 512 pages, 8 MiB of 16 KiB pages, has no old sublist yet, so there new pages go to the head; real pools are far larger.) - On a hit in old, the page only promotes to young if
now - first_access ≥ innodb_old_blocks_time(default 1000 ms). A scan reads all the rows of a page within milliseconds, so its re-reads never trigger this: scan pages cycle through old and never pollute young.
MySQL and MariaDB share this design: both read a page into the old
sublist (buf_LRU_add_block with old set),
promote an old page only after innodb_old_blocks_time
(buf_page_peek_if_too_old), and take the page for a read
from the free list, evicting from the tail of the LRU list only when no
free page is left (buf_LRU_get_free_block).
Sources: MySQL 8.4 Reference Manual §17.5.1 "Buffer Pool" and
§17.8.3.3 "Making the Buffer Pool Scan Resistant"; InnoDB source
storage/innobase/buf/buf0lru.cc in both servers.