Parameters (InnoDB buffer pool)
Example query this animation executes
-- Act 1: Your OLTP workload keeps 8 hot pages in the pool\nSELECT * FROM users WHERE id = 42; -- repeated point lookups\n\n-- Act 2: A reporting query runs a full table scan\nSELECT SUM(amount) FROM events WHERE event_date >= '2026-01-01';\n\n-- Act 3: OLTP workload returns — same 8 pages\nSELECT * 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
- Act 1 — 'The hot set': 8 blue OLTP pages fill both pools. These are your frequently-accessed users, orders, products rows.
- Act 2 — 'The scan arrives': orange scan pages stream in. In the textbook LRU (right), they push the blue pages out. In InnoDB (left), scan pages only enter the OLD sublist — the blue young pages don't move.
- Act 3 — 'Hot queries return': the same 8 blue pages are re-accessed. Classic LRU: all 8 are cache MISSES (they were evicted during the scan). InnoDB: all 8 are cache HITS (they never left the young sublist).
- A counter at the bottom tracks misses vs hits. After act 3, the difference is the whole argument for InnoDB's LRU design.
InnoDB midpoint-insertion LRU
Textbook single-list LRU
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. Should be 8/8 after a scan.
—
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. Should be 0/8 after a scan — 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 (head of old), NOT the head of the list.
- On a hit in old, the page only promotes to young if
now - first_access ≥ innodb_old_blocks_time(default 1000 ms). A one-pass scan never triggers this — so scan pages cycle through old and never pollute young.
Sources: MySQL 8.4 Reference Manual §17.5.1 "Buffer Pool" and §17.8.3.3 "Making the Buffer Pool Scan Resistant".