$ guides / mysql / mysql-buffer-pool-sizing ▌

Operations Guides

MySQL innodb_buffer_pool_size tuning: 60-80% of RAM and when that breaks

The 60-80% rule works for a bare-metal host running only MySQL. In containers, shared hardware, and high-connection-count environments, it is a hazard. Size the pool against aligned allocation, cgroup limits, per-connection memory, and OS headroom. Do not size it as a percentage of total RAM.

What the buffer pool costs

InnoDB caches data and index pages in a fixed-size pool. Every read and write touches it. When the working set fits entirely in memory, queries avoid disk. When it does not, InnoDB evicts pages and disk reads dominate latency.

The LRU split into young and old lists protects hot data from full-table scans, but it cannot fix an undersized pool. Hit-ratio degradation is non-linear: on many OLTP workloads, sustained drops below 99% are a warning, drops toward 95% accelerate latency, and below 95% disk I/O often saturates. Because the pool is allocated at startup and dominates mysqld’s RSS, its size dictates headroom for the OS, thread buffers, adaptive hash index, temporary tables, and the redo log buffer.

Sizing mechanics and constraints

The default 128 MB is unusable for production. Set innodb_buffer_pool_size explicitly, or rely on auto-configuration only on dedicated bare-metal hosts.

Since MySQL 5.7.5 you can resize online:

SET GLOBAL innodb_buffer_pool_size = <bytes>;

The resize proceeds in chunks and blocks operations that access the buffer pool. New transactions can stall until it finishes. Nested transactions started after the resize begins can fail. Monitor progress with:

SHOW STATUS LIKE 'Innodb_buffer_pool_resize_status';

InnoDB rounds innodb_buffer_pool_size up to a multiple of innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances. If you request 9 GB with 16 instances and a 128 MB chunk, the allocated size is 10 GB. Budget memory against the aligned value:

aligned_bytes = CEILING(requested / (chunk_size * instances)) * chunk_size * instances

innodb_buffer_pool_chunk_size is only settable at startup.

In MySQL 8.4, innodb_buffer_pool_instances defaults dynamically: if the pool is greater than 1 GB, the default is min((bp_size / chunk_size) / 2, 1/4 logical processors), capped at 64. Earlier versions default to 8 for pools larger than 1 GB.

innodb_page_cleaners flush dirty pages in parallel. In MySQL 8.4, the default is innodb_buffer_pool_instances; earlier releases default to 4. Setting more cleaners than instances wastes threads.

Dirty-page limits also shifted. In MySQL 8.0+, innodb_max_dirty_pages_pct defaults to 90 (it was 75 in 5.7). Background pre-flushing starts at innodb_max_dirty_pages_pct_lwm. Treating 75% as the modern hard ceiling is stale advice.

Where the 60-80% heuristic breaks

Containers and cgroup limits. Inside Docker or Kubernetes, MySQL typically sees host total RAM via /proc/meminfo, not the container’s cgroup memory limit. If the host has 64 GB but the container is capped at 8 GB, auto-sizing can exceed the limit and trigger an immediate OOM kill. Always set innodb_buffer_pool_size explicitly in containers and validate against the cgroup limit.

Check the real limit:

cat /sys/fs/cgroup/memory.max 2>/dev/null || cat /sys/fs/cgroup/memory/memory.limit_in_bytes

Some runtimes also set memory.high for throttling. Size against memory.max and leave headroom for runtime overhead and bursts.

Per-connection memory overhead. Each thread can allocate sort, join, read, and temporary buffers. A few large queries can spike memory even when Threads_connected is modest. On a host with max_connections = 1000, worst-case thread memory can run into multiple gigabytes. A quick upper-bound estimate:

SELECT @@max_connections *
  (@@sort_buffer_size + @@read_buffer_size + @@join_buffer_size + @@read_rnd_buffer_size)
  AS worst_case_thread_bytes;

Real usage is usually lower because buffers are allocated on demand, but spikes matter. The safe rule is: aligned buffer pool + realistic peak connection memory + OS headroom < available RAM.

Shared hosts and multi-tenant platforms. When mysqld runs alongside application services, message queues, or other databases, 80% of total RAM starves co-resident workloads. Size the pool against the host’s remaining capacity after those neighbors.

Silent rounding inflation. A pool budgeted at exactly 80% of RAM can become 85% or more after alignment. On a memory-constrained host that margin is the difference between stability and swap.

Dynamic resize blocking. Online shrinking stalls queries and can break nested transactions. Do not shrink the pool during peak traffic to free memory for another process.

Tradeoffs and common misuses

Oversizing the pool beyond the working set wastes RAM, increases shutdown time as dirty pages flush, and lengthens warm-up after restart. If innodb_flush_method is not O_DIRECT - the default was fsync before MySQL 8.4 changed it to O_DIRECT - InnoDB data also occupies the OS page cache, so an oversized buffer pool crowds out the file system cache.

Undersizing is usually more visible. Once the working set exceeds the pool, the hit ratio collapses, disk reads saturate, Threads_running climbs as queries pile up, and the connection pool backs up. The fix is more RAM or removing large scans.

Auto-sizing heuristics are only safe on dedicated bare-metal MySQL hosts. They ignore cgroup boundaries, VM reservations, and co-resident workloads.

Signals to watch

Signal	Why it matters	Warning sign
Buffer pool hit ratio	Reveals whether the working set fits in RAM	Sustained below 99% for OLTP
`Innodb_buffer_pool_wait_free`	Count of queries blocked waiting for clean pages	Any nonzero sustained rate
`Innodb_buffer_pool_pages_dirty` / total	Dirty page accumulation relative to capacity	Sustained approach toward `innodb_max_dirty_pages_pct` (90% default in 8.0+)
Checkpoint age / redo log capacity	Proximity to synchronous flush stall	Sustained above 75% of capacity
`Threads_running`	Query pile-up when reads slow	Sustained above CPU core count with flat QPS
OS swap in/out (`vmstat`)	Evidence of memory pressure outside MySQL	`si` or `so` nonzero

Quick checks

Check configured vs aligned allocation:

SHOW VARIABLES LIKE 'innodb_buffer_pool%';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';

Actual allocated bytes = Innodb_buffer_pool_pages_total * 16384.

Check dirty-page pressure:

SHOW VARIABLES LIKE 'innodb_max_dirty_pages_pct%';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_dirty';
SHOW STATUS LIKE 'Innodb_buffer_pool_pages_total';

Check hit ratio and wait pressure:

SHOW STATUS LIKE 'Innodb_buffer_pool_read_requests';
SHOW STATUS LIKE 'Innodb_buffer_pool_reads';
SHOW STATUS LIKE 'Innodb_buffer_pool_wait_free';

Hit ratio = 1 - (reads / read_requests) over an interval.

Check connection load:

SHOW STATUS LIKE 'Threads_connected';
SHOW STATUS LIKE 'Threads_running';
SHOW VARIABLES LIKE 'max_connections';

Check OS memory pressure:

vmstat 1 5

Watch si and so. Non-zero swap activity usually means the buffer pool or connection memory is too large for the container or host.

How Netdata helps

Correlates buffer pool hit ratio with disk utilization to separate working-set misses from one-off scans.
Alerts on Innodb_buffer_pool_wait_free to catch pressure before the latency cliff.
Plots cgroup memory limit against MySQL RSS to expose container sizing mismatches.
Overlays dirty page ratio and checkpoint age to spot flush stalls driven by memory pressure.
Compares Threads_running to CPU core count to reveal query pile-up from thrashing.

The Netdata solution

MySQL monitoring with Netdata

Netdata monitors MySQL and MariaDB with per-second metrics and ML anomaly detection. Track connection usage, query throughput, slow queries, redo-log pressure, and replication lag alongside the host and storage signals that explain them.

See MySQL monitoring → Start monitoring free