InnoDB Buffer Pool – Are Pages Evicted When Exceeding Buffer Size?

buffer-pooldata-pagesdatabase-internalsinnodbMySQL

If I have some (hot) pages in the InnoDB buffer, and I perform a query which reads an amount of data larger than the buffer itself, will the hot pages be necessarily evicted?

Best Answer

According to MySQL 5.7 Reference Manual - The InnoDB Buffer Pool

InnoDB manages the buffer pool as a list, using a variation of the least recently used (LRU) algorithm. When room is needed to add a new page to the pool, InnoDB evicts the least recently used page and adds the new page to the middle of the list. This “midpoint insertion strategy” treats the list as two sublists:

  • At the head, a sublist of “new” (or “young”) pages that were accessed recently.

  • At the tail, a sublist of “old” pages that were accessed less recently.

This algorithm keeps pages that are heavily used by queries in the new sublist. The old sublist contains less-used pages; these pages are candidates for eviction.

(emphasis mine)

I think this is the answer you're looking for. Not all pages are evicted, they're evicted one at at a time, and only of the old ones will be evicted each time. Obviously, if queries need twice (or more) of the available pool, eventually all pages might be evicted, depending on how frequently they're used.

I'd say that all pages near the root of B+trees (used for indices and the way tables are clustered) will be used more frequently than the rest, and so, they're unlikely to ever be evicted. However, some situations where there are just lots of full table scans, that (probably) pass through pages just once, might end up having all your hot pages overwhelmed and evicted. I don't think this is likely, but theoretically possible.

Related Question