MySQL InnoDB Buffer Pool Tuning

The InnoDB buffer pool is the most important memory structure in MySQL. It caches data pages, index pages, undo logs, and change buffers, trading RAM for disk I/O. Almost every performance problem in an I/O-bound MySQL workload traces back to an undersized or misconfigured buffer pool.

Impact

An undersized buffer pool means frequently accessed pages are evicted to make room for new reads — each eviction requires a disk read when the page is next needed. On spinning disk, that is a 5–10 ms penalty per page miss. On NVMe, it is 0.1–0.3 ms. For a workload that makes millions of page reads per second, the cumulative impact is enormous: CPU waits for I/O instead of executing queries, query latency grows, and throughput collapses.

Common Causes of Buffer Pool Problems

innodb_buffer_pool_size set to the MySQL default (128 MB), which is appropriate only for development, not production
Buffer pool too large: consuming > 90% of RAM leaves no room for OS page cache, per-thread buffers, or connection overhead — causing OOM kills
Multiple buffer pool instances not configured for high-concurrency workloads (the default of 8 is usually fine)
Cold buffer pool after a restart causing a "warm-up period" of degraded performance
Large table scans or batch jobs flooding the buffer pool with pages that evict hot OLTP data (LRU pollution)
Fragmentation in the buffer pool from frequent small allocations and frees

Checking Buffer Pool Status

SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool%';

Key status variables:

Innodb_buffer_pool_read_requests — total logical reads (served from pool or disk)
Innodb_buffer_pool_reads — physical reads (not found in pool, fetched from disk)
Innodb_buffer_pool_pages_total — total pages the pool can hold
Innodb_buffer_pool_pages_free — pages currently unused

Calculate cache hit ratio:

SELECT
    ROUND(
        (1 - (
            SUM(CASE VARIABLE_NAME WHEN 'Innodb_buffer_pool_reads' THEN VARIABLE_VALUE ELSE 0 END)
            /
            SUM(CASE VARIABLE_NAME WHEN 'Innodb_buffer_pool_read_requests' THEN VARIABLE_VALUE ELSE 0 END)
        )) * 100,
        2
    ) AS buffer_pool_hit_pct
FROM performance_schema.global_status
WHERE VARIABLE_NAME IN ('Innodb_buffer_pool_reads', 'Innodb_buffer_pool_read_requests');

A healthy OLTP workload targets > 99% hit ratio. Below 95% usually indicates the buffer pool is too small for the working data set.

Sizing the Buffer Pool

Start with 70–80% of available RAM for a dedicated MySQL host:

-- On a 32 GB dedicated MySQL server
SET GLOBAL innodb_buffer_pool_size = 24696061952;  -- 23 GB (72%)
SET PERSIST innodb_buffer_pool_size = 24696061952;

In my.cnf:

[mysqld]
innodb_buffer_pool_size = 23G

On a shared host (app + MySQL on the same server), use 50% or less:
```
innodb_buffer_pool_size = 4G   # on an 8 GB shared host
```

Use the working data set size to determine the ideal ceiling. If your frequently accessed data fits in 8 GB but you set 24 GB, the extra 16 GB is wasted:

-- Estimate working set size from table sizes
SELECT
    ROUND(SUM(data_length + index_length) / 1024 / 1024 / 1024, 2) AS total_gb
FROM information_schema.tables
WHERE table_schema = 'mydb';

-- More useful: tables accessed most frequently (via performance_schema)
SELECT
    object_schema,
    object_name,
    ROUND(SUM_TIMER_READ + SUM_TIMER_WRITE, 0) AS total_io_wait_ps
FROM performance_schema.table_io_waits_summary_by_table
WHERE object_schema NOT IN ('mysql', 'performance_schema', 'sys', 'information_schema')
ORDER BY total_io_wait_ps DESC
LIMIT 20;

Resize the buffer pool online (MySQL 5.7.5+) without a restart:

-- Can be raised or lowered online; takes effect in chunks
SET GLOBAL innodb_buffer_pool_size = 17179869184;  -- 16 GB

-- Monitor the resize progress
SELECT POOL_ID, POOL_SIZE, FREE_BUFFERS, DATABASE_PAGES, HIT_RATE
FROM information_schema.INNODB_BUFFER_POOL_STATS;

Online resize is asynchronous — the buffer pool migrates pages gradually. Check SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status' for progress.

Buffer Pool Instances

Set innodb_buffer_pool_instances to reduce mutex contention on large pools:
```
# Recommended: one instance per GB, up to 16
innodb_buffer_pool_instances = 8   # for a pool up to 16 GB
innodb_buffer_pool_instances = 16  # for a pool > 16 GB
```
Each instance has its own LRU list and mutex. More instances reduce contention at the cost of slightly lower hit ratio per instance (smaller LRU lists). The default of 8 is fine for pools up to 8 GB; raise for larger pools.

Note: innodb_buffer_pool_instances cannot be changed at runtime — requires a restart.

Warming Up the Buffer Pool After Restart

Enable buffer pool dump and load for fast warm-up:
```
[mysqld]
innodb_buffer_pool_dump_at_shutdown = ON   # dump page list on shutdown
innodb_buffer_pool_load_at_startup = ON    # restore page list on startup
innodb_buffer_pool_dump_pct = 25           # dump the top 25% of LRU (hot pages)
```
At shutdown, InnoDB writes a compact list of page IDs to ib_buffer_pool. On restart, it reads those pages back from disk before accepting connections. This dramatically shortens the cold-start period from hours to minutes.

Trigger a manual dump (for planned restarts):

-- Dump the current hot page list
SET GLOBAL innodb_buffer_pool_dump_now = ON;

-- Check dump progress
SHOW STATUS LIKE 'Innodb_buffer_pool_dump_status';

-- On the next restart, load automatically (if innodb_buffer_pool_load_at_startup = ON)
-- Or trigger manually after restart:
SET GLOBAL innodb_buffer_pool_load_now = ON;
SHOW STATUS LIKE 'Innodb_buffer_pool_load_status';

Preventing LRU Pollution from Full Scans

Tune innodb_old_blocks_time to protect the hot region from large scans:

SHOW VARIABLES LIKE 'innodb_old_blocks_time';
-- default: 1000 ms

-- Pages fetched by a full scan are inserted into the "old" region of the LRU
-- and promoted to the "young" region only if accessed again within old_blocks_time.
-- Raising this value prevents scan pages from evicting hot pages:
SET GLOBAL innodb_old_blocks_time = 1000;   -- already 1 s; raise to 2000–5000 for heavy scans

The buffer pool uses a midpoint insertion strategy: new pages start in the old (bottom 37%) region by default. Only pages accessed twice within innodb_old_blocks_time move to the young (hot) region.

Use innodb_old_blocks_pct to control the young/old split:

SHOW VARIABLES LIKE 'innodb_old_blocks_pct';
-- default: 37 (%)

-- Raise to protect more of the pool as "young" (hot) region:
SET GLOBAL innodb_old_blocks_pct = 50;

Monitoring the Buffer Pool Over Time

-- Per-pool hit ratio (when multiple instances)
SELECT
    POOL_ID,
    POOL_SIZE,
    PAGES_MADE_YOUNG,
    PAGES_NOT_MADE_YOUNG,
    HIT_RATE,
    YOUNG_MAKE_PER_THOUSAND_GETS,
    NOT_YOUNG_MAKE_PER_THOUSAND_GETS
FROM information_schema.INNODB_BUFFER_POOL_STATS;

-- Buffer pool pages by type
SELECT PAGE_TYPE, COUNT(*) AS pages
FROM information_schema.INNODB_BUFFER_PAGE
GROUP BY PAGE_TYPE
ORDER BY pages DESC;
-- Note: INNODB_BUFFER_PAGE can be slow on large pools — run during low traffic

Additional Information

innodb_buffer_pool_size is the single most impactful MySQL configuration parameter for I/O-bound workloads. All other buffer and cache settings are secondary.
The change buffer (formerly the insert buffer) is a portion of the buffer pool that caches changes to non-unique secondary indexes when the target page is not in memory. It is flushed lazily. Its size is controlled by innodb_change_buffer_max_size (default 25% of buffer pool).
In containerized deployments, set innodb_buffer_pool_size explicitly rather than relying on automatic detection — MySQL's auto-sizing reads total host memory, not the container's memory limit, and will overallocate.
Amazon Aurora decouples storage from compute and has its own buffer pool equivalent (the Aurora buffer cache) that survives instance restarts. The sizing guidance still applies, but the warm-up concern is less severe.

Frequently Asked Questions

Q: My hit ratio is 99.5% but the server is still slow. What else should I check? A: A high hit ratio means pages are found in the pool, but it says nothing about lock contention, query plan efficiency, or CPU usage. Check Threads_running, the slow query log, and SHOW ENGINE INNODB STATUS for lock contention.

Q: Can I set innodb_buffer_pool_size larger than the physical RAM? A: MySQL will start and accept the value, but pages that do not fit in physical RAM will be swapped to disk — which is slower than InnoDB reading from its own data files. Never set the buffer pool larger than available physical RAM minus OS and other process overhead.

Q: After raising innodb_buffer_pool_size, I get OOM kills. Why? A: The buffer pool itself is not the only memory consumer. Per-thread buffers, connection overhead, performance_schema, and the OS need RAM too. Back off by 2–4 GB and re-evaluate.

Q: Does the buffer pool persist across MySQL restarts? A: The data does not — pages are only in RAM. The page list (which pages were hot) can be persisted with innodb_buffer_pool_dump_at_shutdown = ON, allowing fast reload on startup. Without this, a restart causes a cold-start period while hot pages are re-read from disk.

Q: I have 8 buffer pool instances but HIT_RATE varies significantly across them. Is that a problem? A: It indicates uneven data access distribution across the hash buckets that map pages to instances. This is normal for workloads with hot keys that concentrate in a few tables. Changing the number of instances (requires restart) may rebalance, but the effect is usually minor.