Senior LevelSQL & MySQL
How does InnoDB Buffer Pool LRU Scan Resistance prevent full table scans from flushing hot cache pages?
❓ Interview Prompt: Explain InnoDB Buffer Pool LRU List sub-blocks (Young vs Old sub-lists), `innodb_old_blocks_pct`, `innodb_old_blocks_time`, and scan resistance.
A massive full table scan reading 10GB of cold data pages can flush out all hot cached pages from RAM! InnoDB prevents this using LRU Scan Resistance:
Two-Buffer LRU Sub-Lists:
•
The LRU list is split into Young Sub-list ($5/8$) and Old Sub-list ($3/8$).
•
New pages read during a table scan enter the Old Sub-list.
•
A page MUST remain in the Old Sub-list for at least 'innodb_old_blocks_time' ($1000\text{ms}$) before it can move to the Young Sub-list. Batch table scans finish faster than 1s, so cold pages are discarded quickly without evicting hot young pages!
Code Implementation Example
sql
-- Checking InnoDB LRU scan resistance configuration
SHOW VARIABLES LIKE 'innodb_old_blocks%';💡Inspecting InnoDB Buffer Pool LRU scan resistance parameters.
🎯 Interview Key Takeaway
InnoDB LRU Scan Resistance prevents full table scans from flushing frequently accessed hot pages out of memory.
#InnoDB#Buffer Pool#LRU#Scan Resistance#Senior
Back to all SQL & MySQL Questions→