Skip to content

InnoDB buffer pool internals#

2024-06-08

Quick notes from re-reading MySQL 8.0's buf/buf0lru.cc and storage/innobase/buf/buf0buf.cc. Old material but worth writing down because I keep forgetting the details.

LRU list structure#

InnoDB's buffer pool LRU is actually a midpoint-insertion LRU, sometimes called a 2Q approximation. The list is split into "young" and "old" sublists by a midpoint that defaults to 3/8 of the way from the tail.

  • New pages get inserted at the midpoint, not the head. This is what protects the hot working set from a single scan that touches millions of new pages.
  • Pages get moved to the head of the young list only if they're accessed at least innodb_old_blocks_time milliseconds after being inserted (default 1000 ms). This filters out one-off touches.

Free list and flush list#

There are two other lists you care about:

  • Free list: buffer frames that don't currently hold a page. When the free list runs low, the page cleaner background thread flushes dirty pages from the LRU tail to disk.
  • Flush list: dirty pages, ordered by oldest modification LSN. The page cleaner walks this list to decide what to write out.

The flush list is doubly linked and ordered by LSN, which is why sudden massive dirty-page storms cause the "furious flushing" pattern InnoDB is infamous for.

Practical tuning#

  • innodb_buffer_pool_size — the obvious one. Aim for ~75% of RAM on a dedicated MySQL box.
  • innodb_lru_scan_depth — how many pages the cleaner scans per second per buffer pool instance. Default 1024 is fine for most workloads.
  • innodb_buffer_pool_instances — splitting the pool into 8+ instances is purely for lock contention; doesn't change the overall size.
  • innodb_old_blocks_time — the anti-scan-pollution knob. Rarely worth tuning.

Reference#

  • Peter Zaitsev's classic InnoDB Performance Optimization talks.
  • Mark Callaghan's blog posts on flushing (small-datum.blogspot.com).