The Buffer Pool Is Everything
In MySQL, the InnoDB buffer pool is the single most important memory structure. It caches data pages, index pages, undo logs, and change buffer pages. Getting it right can mean the difference between a fast database and a slow one.
This guide dives deep into the internals — how pages are organized, how chunks work, how the LRU algorithm decides what to evict, and how to tune it for your workload.
Buffer Pool Architecture
The buffer pool is divided into chunks (default 128 MB each). Each chunk contains a series of pages (default 16 KB). Pages are the unit of I/O between disk and memory.
# Key buffer pool variables
SHOW VARIABLES LIKE 'innodb_buffer_pool%';
innodb_buffer_pool_size = 8G -- Total size
innodb_buffer_pool_instances = 8 -- Parallel instances
innodb_buffer_pool_chunk_size = 134217728 -- 128MB chunks
innodb_old_blocks_pct = 37 -- % for old sublist
innodb_old_blocks_time = 1000 -- ms before promotionThe LRU List: Young and Old Sublists
InnoDB uses a modified LRU algorithm with two sublists:
- Young (new) sublist: Hot pages accessed recently — top 63% of the list
- Old sublist: Pages inserted but not yet confirmed hot — bottom 37%
When a page is first read into the buffer pool, it enters the head of the old sublist. Only after it's accessed again (and innodb_old_blocks_time ms have elapsed) does it get promoted to the young sublist. This prevents full table scans from thrashing hot pages.
-- Check buffer pool hit ratio
SELECT
FORMAT(((1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)) * 100), 2) AS hit_ratio_pct
FROM (
SELECT
(SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_reads') AS Innodb_buffer_pool_reads,
(SELECT variable_value FROM performance_schema.global_status WHERE variable_name = 'Innodb_buffer_pool_read_requests') AS Innodb_buffer_pool_read_requests
) AS stats;Target: > 99% hit ratio. Below 95% means you need more buffer pool or better query patterns.
Page Types in the Buffer Pool
| Page Type | Description |
|---|---|
| Data pages | B-tree leaf pages containing row data |
| Index pages | B-tree non-leaf pages for index traversal |
| Undo pages | Rollback segments for MVCC |
| System pages | Data dictionary, rollback segment headers |
| Change buffer pages | Buffered secondary index changes |
| Insert buffer bitmap | Tracks free space for change buffer |
Monitoring Buffer Pool State
-- Detailed buffer pool stats
SHOW ENGINE INNODB STATUS\G
-- Or via information schema
SELECT
pool_id,
pool_size,
free_buffers,
database_pages,
old_database_pages,
modified_database_pages,
pages_made_young,
pages_not_made_young,
pages_read,
pages_created,
pages_written
FROM information_schema.INNODB_BUFFER_POOL_STATS;Buffer Pool Instances
For buffer pools larger than 1 GB, use multiple instances to reduce mutex contention:
[mysqld]
innodb_buffer_pool_size = 16G
innodb_buffer_pool_instances = 16 # 1 per GB, max 64
innodb_buffer_pool_chunk_size = 134217728 # 128MBRule: Each instance should be at least 1 GB. With 16 GB pool and 16 instances, each instance gets exactly 1 GB.
Warmup After Restart
By default, MySQL dumps and restores the buffer pool on restart:
[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25 # Save top 25% most-used pages-- Monitor warmup progress
SELECT variable_name, variable_value
FROM performance_schema.global_status
WHERE variable_name LIKE 'Innodb_buffer_pool_load%';Tuning innodb_old_blocks_time
If full table scans are evicting hot pages, increase this value:
-- Protect hot pages during ETL/backup scans
SET GLOBAL innodb_old_blocks_time = 1000; -- 1 second (default)
-- For heavy batch workloads, increase to 2-5 seconds
SET GLOBAL innodb_old_blocks_time = 5000;Memory Sizing Formula
A practical formula for allocating buffer pool on a dedicated MySQL server:
buffer_pool_size = total_RAM * 0.70 # 70% of RAM
# Leave room for:
# - OS file cache: 10-15%
# - InnoDB log buffer: 256MB-1GB
# - Per-thread buffers: sort_buffer, join_buffer, etc.
# - Connection overhead: 2-4 MB per connection
# - Temp tables, binlog cacheKey Takeaways
- Buffer pool hit ratio should be > 99%; below 95% is a red flag
- Use multiple instances (1 per GB) for large buffer pools
- Enable dump/load at shutdown/startup for fast warmup
- Tune
innodb_old_blocks_timeif batch scans evict hot data - Monitor
pages_made_youngvspages_not_made_youngfor LRU behavior