The InnoDB buffer pool is the single most important MySQL performance parameter. It caches table data and indexes in memory, avoiding disk I/O. Getting its size and configuration right is the highest-ROI MySQL tuning action.
How the Buffer Pool Works
InnoDB uses an LRU (Least Recently Used) algorithm with a midpoint insertion strategy. New pages enter at the midpoint (default 37% from the tail) rather than the head, preventing large table scans from evicting frequently-accessed data.
Size the Buffer Pool Correctly
sql
-- Check current buffer pool hit ratio
SELECT
variable_name,
variable_value
FROM performance_schema.global_status
WHERE variable_name IN (
'Innodb_buffer_pool_reads',
'Innodb_buffer_pool_read_requests'
);
-- Hit ratio = 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests)
-- Target: > 99%. Below 95% means buffer pool is too small.
ini
[mysqld]
# Rule of thumb: 70-80% of available RAM for dedicated DB servers
innodb_buffer_pool_size = 12G # for 16 GB RAM server
# Multiple instances reduce mutex contention (1 per 1 GB)
innodb_buffer_pool_instances = 12Dynamic Resize (MySQL 5.7.5+)
sql
-- Resize without restart
SET GLOBAL innodb_buffer_pool_size = 12884901888; -- 12 GB in bytes
-- Monitor resize progress
SELECT event_name, work_completed, work_estimated
FROM performance_schema.events_stages_current
WHERE event_name LIKE '%buffer%pool%resize%';Warm-Up on Restart
ini
[mysqld]
# Save buffer pool state on shutdown, reload on startup
innodb_buffer_pool_dump_at_shutdown = ON
innodb_buffer_pool_load_at_startup = ON
innodb_buffer_pool_dump_pct = 25 # Save top 25% of pagesMonitor Buffer Pool Usage
sql
SHOW ENGINE INNODB STATUS\G
-- In BUFFER POOL AND MEMORY section:
-- Buffer pool size: total pages allocated
-- Free buffers: available pages (should be > 0)
-- Database pages: pages with data
-- Modified db pages: dirty pages not yet flushed
-- Pages by type
SELECT pool_id, pool_size, free_buffers, database_pages,
old_database_pages, modified_database_pages
FROM information_schema.innodb_buffer_pool_stats;Avoid Buffer Pool Pollution
sql
-- Large table scans evict hot pages
-- Use SQL_NO_CACHE for analytics queries that should not pollute the pool
SELECT SQL_NO_CACHE * FROM large_table WHERE ...
-- Or adjust the old_blocks_time to reduce scan impact
SET GLOBAL innodb_old_blocks_time = 1000; -- pages must stay in old sublist 1s before promotionKey Takeaways
- Target a buffer pool hit ratio > 99% — below 95% means reads are going to disk
- Set
innodb_buffer_pool_sizeto 70-80% of RAM on dedicated database servers - Enable dump/load at shutdown/startup to avoid cold-start performance degradation after restarts
- Set
innodb_old_blocks_time = 1000to protect hot pages from full-table-scan eviction
JusDB Can Help
InnoDB buffer pool tuning is the single highest-impact MySQL configuration change. JusDB engineers can analyze your buffer pool metrics and right-size it for your workload.