InnoDB's scan resistance leaks through the free list

InnoDB is supposed to survive a big table scan without losing its hot pages. New pages enter the buffer pool's LRU list at a midpoint, in an "old" sublist, and innodb_old_blocks_time (default 1000 ms) keeps a page there unless it's touched again at least a second after its first access. A scan reads each page for a few milliseconds and moves on, so its pages should age out of the old sublist without disturbing the rest. I tested that with a 256 MB pool, a hot set of point reads, and a 746 MB table scanned in a loop. With the scans back to back, the largest hot set lost 59% of its pages even with the default protection, against 93% with it off. Scan pages still get into the young sublist, about 35 a second. They don't get there by being reused. Whenever the page cleaner refills the free list and reads drain it again, the boundary between the two sublists slides. Once a scan page is young, every pass of the loop touches it again and moves it back to the head.

The test

Each run used a fresh mysql:8.4 container (8.4.11, 2 CPUs) with a 256 MB buffer pool and default settings otherwise. Two tables: h, 300,000 rows with a 200-byte char column (74 MB, 4,485 pages), and b, 3 million rows (746 MB, three times the pool). One connection did random primary-key lookups on the first 15,000, 60,000 or 300,000 rows of h, as fast as it could. That hot set fills 224, 897 or 4,485 pages. After a 60 second warm-up, a second connection ran select sum(v) from b in a loop for 150 seconds, either back to back or with a 0.5 or 2 second pause between passes. A back-to-back pass took between half a second and two seconds, about one on average. Every 20 seconds I counted the hot table's pages in information_schema.innodb_buffer_page. "Kept" is the average over the last 110 seconds, as a share of the pages the hot rows fill. Each cell is three runs; without a scan, all three hot sets stayed 100% resident.

The setting does something: at every size and pause it keeps more than turning it off does. But a scan that loops without pausing still takes most of a hot set that's read about once every second or two per page. The loss feeds on itself, too. Once hot pages had to come from disk, the lookup rate fell from about 5,000 a second to 1,500, so each surviving hot page was touched less often.

How scan pages get into the young sublist

I logged which table each young and old page belonged to (innodb_buffer_page_lru has an IS_OLD column). With the default setting and back-to-back scans, the scanned table's young page count rose by about 35 pages a second for the whole run. To rule out leftovers from loading the table, I restarted MySQL and filled the pool from a third table before the warm-up, so the scanned table started with no young pages at all. It climbed at the same rate.

The way in is buf_LRU_old_adjust_len. The old sublist is held at innodb_old_blocks_pct of the LRU list's length (default 37%, give or take 20 pages), and a newly read page goes in at the head of the old sublist, right behind the boundary. When the list grows, because a read took its page from the free list instead of evicting one, the old sublist is suddenly too long. The boundary steps toward the tail one page at a time, clearing the old flag on each page it passes:

} else if (old_len > new_len + BUF_LRU_OLD_TOLERANCE) {
  buf_pool->LRU_old = UT_LIST_GET_NEXT(LRU, LRU_old);
  old_len = --buf_pool->LRU_old_len;
  buf_page_set_old(LRU_old, false);

The pages just behind the boundary are the newest midpoint inserts, which during a scan are scan pages. They become young after a single access. The free list is what makes the list length move. I sampled Innodb_buffer_pool_pages_free 20 times a second: during the warm-up it held at about 1,000 pages, just under innodb_lru_scan_depth (default 1024), the number of pages the page cleaner checks at the LRU tail each second. Once the scan started it sat at 0, except when the cleaner refilled it. In a 35-second sample I caught three refills of several hundred pages, the largest about 900, and the scan drained each one within a fraction of a second. Each refill-and-drain moves the boundary toward the tail by about 63% of the refill, so a 900-page refill turns about 550 scan pages young. Three of those in 35 seconds is roughly in line with the 35 a second I counted.

Getting in is half of it. A young page that's touched again goes back to the head if it has drifted into the older three quarters of the young sublist (buf_page_peek_if_young), and with a pass every second that's always true by the next pass. So the young sublist becomes an LRU contest. Scan pages come back about every second. In the 4,485-page runs, each hot page came back every 1.5 to 3 seconds on average, depending on the lookup rate. The scan wins. A pause between passes stretches the scan's return time, and the hot set does much better. The 224-page hot set comes back every tenth of a second or so, and nothing displaced it. The 897-page set doesn't fit this picture neatly: its pages came back every third of a second on average and it still lost more than half, so return time isn't the whole story. Pages get demoted in bulk whenever the boundary moves the other way, and I haven't traced that part.

Loading the table had made about 4,800 of its pages young already. That's a big head start, and the warm and cold starts diverged. With those leftovers, the hot set was down to about 80% 40 seconds into the scan, and after 140 seconds only 7–15% of it was still cached, almost all of that in the old sublist, in all three runs. From the cold start, the filler table's young pages were pushed out first, and the hot set was still fully cached at the end of 150 seconds in all three runs. By then the filler was gone from the young sublist, and the scanned table held about 5,700 young pages.

The cold start only delays it. I ran the same thing for 10 minutes of scanning: twice from the cold start, once warm. In both cold runs the filler's young pages ran out about two minutes in. The hot set then fell from 4,485 cached pages to under 400 within the next three minutes and stayed between about 100 and 1,300 for the rest of the run, the same as the warm run. The scanned table settled at about 10,150 young pages, which is nearly the whole young sublist.

The free-list target sets the leak rate

If refills are the way in, the size of the refills should set the rate. The manual describes innodb_lru_scan_depth as how far the page cleaner scans down the LRU list each second, per buffer pool instance. At the default, the free list in my runs never went above 1,024 pages, so it looks like the cap on refill size too. I reran the 4,485-page, back-to-back, default-old_blocks_time case with the warm start at three depths, two runs each:

Same table, same scan, same setting for the old sublist; only the free-list target changed, and the leak and the loss moved with it. At 128 the hot set was still shrinking slowly at the end (3,830 pages cached after 140 seconds of scanning), so a lower depth slows the leak but doesn't plug it.

What to do about it

If a hot working set shares a pool with a job that reads a big table over and over, the default old_blocks_time won't keep it safe on its own. Two things helped about equally in these runs. At 4,485 pages, a 2 second pause between passes raised the hot set kept from 41% to 89%. Dropping innodb_lru_scan_depth from 1024 to 128 raised it to 91–93%. A pause needs to be longer than the hot pages' reuse gap, and a lower depth only slows the leak. The MySQL manual already says "a setting smaller than the default is generally suitable for most workloads," with the catch that a write-heavy server needs the page cleaner to keep up, so a depth too low makes queries wait on flushing. My runs were read-only, so they say nothing about that cost. Finally, the leak follows innodb_lru_scan_depth, which is per pool instance, not the pool size. In a pool of many gigabytes, the same few hundred pages per refill are a much smaller share of the young sublist than in my 256 MB pool, so I'd expect big pools to hold up much longer. I didn't test that.

Caveats: one pool size, one machine, small containers. Lookup rates varied between runs (1,500 to 5,000 a second), since the scan and the lookups shared two CPUs, and that changes how often a hot page is touched. A real server's scan rarely loops this tightly, but a batch job that reads a big table in chunks back to back is close. The mechanism doesn't depend on the loop. Every free-list refill turns a few hundred of whatever was read last into young pages. The loop is what keeps them there.