MENU
InnoDB Buffer Pool and Caching
The InnoDB buffer pool is the single most important cache in MySQL: it holds table and index pages in memory, so a page already resident avoids a disk read entirely. Since the query cache no longer exists, buffer pool sizing and page residency are the primary levers for making a workload memory-bound instead of disk-bound.Sizing innodb_buffer_pool_size
innodb_buffer_pool_size sets how much memory InnoDB reserves for cached pages. On a dedicated database server, a common starting point is 70–80% of physical RAM, leaving headroom for connection memory (sort_buffer_size, join_buffer_size, etc. – see Server Variable Tuning), the OS, and other processes. On a shared or containerized host, size it against the container's memory limit, not the host's total RAM.| SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024; |
Resizable online since MySQL 5.7 – InnoDB adds/removes chunks (innodb_buffer_pool_chunk_size, default 128MB) in the background without a restart. Set the persistent value in my.cnf too, or the change is lost on restart.
Undersizing forces InnoDB to evict and re-read hot pages constantly (visible as high Innodb_buffer_pool_reads relative to Innodb_buffer_pool_read_requests); oversizing beyond working-set size plus OS/connection needs wastes memory the OS could otherwise use, or worse, causes swapping.
| SELECT (1 - Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests) * 100 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 ) t; |
A hit ratio consistently below roughly 99% on an OLTP workload usually means the buffer pool is too small for the working set.
Buffer Pool Instances
innodb_buffer_pool_instances splits the buffer pool into independent regions, each with its own mutexes and free/LRU/flush lists, to reduce internal contention under high concurrency. It only matters once the buffer pool is large: MySQL ignores it (uses a single instance) when innodb_buffer_pool_size is below 1GB. For larger pools, 8 instances is a reasonable default; more instances only pay off with enough total buffer pool size that each instance is still comfortably large (innodb_buffer_pool_size / innodb_buffer_pool_instances should stay well above innodb_buffer_pool_chunk_size). This is a startup-only variable – changing it requires a restart.The Removed Query Cache
MySQL's statement-level query cache (query_cache_type, query_cache_size) was deprecated in 5.7 and removed entirely in 8.0. It cached whole result sets keyed by exact statement text, but a single global mutex serialized every cache read and invalidation, so it scaled badly under write-heavy or highly concurrent workloads – often making performance worse than having no cache at all, which is why it was removed rather than fixed.There is no direct built-in replacement; the buffer pool itself absorbs most of what mattered, since a hot table's pages simply stay resident in memory and repeated reads never touch disk – it just doesn't cache pre-computed result sets. For workloads that specifically need whole-result caching, the standard MySQL 8.0+ approaches are:
- Application-level or ORM-level caching against Redis or Memcached, with explicit invalidation on write.
- A caching proxy such as ProxySQL, which can cache query results in front of MySQL.
- Materialized summary tables refreshed on a schedule or by triggers, for expensive aggregate queries that don't need to be perfectly current.
Warm-up Strategies
After a restart, the buffer pool starts empty, and performance is disk-bound until the working set is reloaded – a cold-cache dip that can be significant on a busy server. InnoDB can dump the set of pages resident at shutdown and reload the same pages automatically at the next startup:| innodb_buffer_pool_dump_at_shutdown = ON innodb_buffer_pool_load_at_startup = ON innodb_buffer_pool_dump_pct = 25 |
Set in my.cnf (both default ON since MySQL 5.6/8.0). The dump only records page identifiers, not the pages themselves, so it's fast and small; innodb_buffer_pool_dump_pct limits it to the most recently used N% of pages per instance to keep the dump file and reload time bounded.
A dump/load cycle can also be triggered manually – useful before a planned restart:
| SET GLOBAL innodb_buffer_pool_dump_now = ON; -- ... restart ... SET GLOBAL innodb_buffer_pool_load_now = ON; SELECT * FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_buffer_pool_load_status'; |
Progress and completion of a load can be polled via Innodb_buffer_pool_load_status.
Where no dump exists (a fresh instance, or a host migration), warm-up means deliberately running representative read queries against the hot tables/indexes before exposing the server to full production traffic, so the first real users don't pay the cold-cache cost.
For diagnosing whether a slow workload is buffer-pool-bound or query-shape-bound in the first place, see Performance Schema and Sys Schema.
-- Resize online (also persist the value in my.cnf)
SET GLOBAL innodb_buffer_pool_size = 8 * 1024 * 1024 * 1024;
SHOW VARIABLES LIKE 'innodb_buffer_pool_size';
-- Buffer pool hit ratio
SELECT (1 - r.reads / q.requests) * 100 AS hit_ratio_pct
FROM
(SELECT VARIABLE_VALUE AS reads FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_reads') r,
(SELECT VARIABLE_VALUE AS requests FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_read_requests') q;
-- Trigger a manual dump before a planned restart, then reload after
SET GLOBAL innodb_buffer_pool_dump_now = ON;
-- ... restart mysqld ...
SET GLOBAL innodb_buffer_pool_load_now = ON;
SELECT VARIABLE_VALUE FROM performance_schema.global_status
WHERE VARIABLE_NAME = 'Innodb_buffer_pool_load_status';