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. During a shrink, nested transactions started after the resize begins 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. 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 on Linux is fsync - 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

SignalWhy it mattersWarning sign
Buffer pool hit ratioReveals whether the working set fits in RAMSustained below 99% for OLTP
Innodb_buffer_pool_wait_freeCount of queries blocked waiting for clean pagesAny nonzero sustained rate
Innodb_buffer_pool_pages_dirty / totalDirty page accumulation relative to capacitySustained approach toward innodb_max_dirty_pages_pct (90% default in 8.0+)
Checkpoint age / redo log capacityProximity to synchronous flush stallSustained above 75% of capacity
Threads_runningQuery pile-up when reads slowSustained above CPU core count with flat QPS
OS swap in/out (vmstat)Evidence of memory pressure outside MySQLsi 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.
  • How MySQL actually works in production: a mental model for operators: /guides/mysql/how-mysql-works-in-production/
  • MySQL Aborted_connects and Aborted_clients climbing: diagnosis: /guides/mysql/mysql-aborted-connections/
  • MySQL InnoDB checkpoint age: the redo log capacity signal nobody watches: /guides/mysql/mysql-checkpoint-age-monitoring/
  • MySQL connection exhaustion: detection, diagnosis, and prevention: /guides/mysql/mysql-connection-exhaustion/
  • MySQL innodb_deadlock_detect=OFF: when deadlock detection becomes the bottleneck: /guides/mysql/mysql-deadlock-detect-off-high-concurrency/
  • MySQL ERROR 1213: Deadlock found when trying to get lock; try restarting transaction: /guides/mysql/mysql-deadlock-found/
  • MySQL FLUSH TABLES WITH READ LOCK stall: backups that freeze the server: /guides/mysql/mysql-flush-tables-with-read-lock-stall/
  • MySQL gap locks and next-key locks: surprising deadlocks under REPEATABLE READ: /guides/mysql/mysql-gap-locks-next-key-locks/
  • MySQL ERROR 1205: Lock wait timeout exceeded; try restarting transaction: /guides/mysql/mysql-lock-wait-timeout-exceeded/
  • MySQL Innodb_log_waits > 0: the log buffer is too small (not a checkpoint stall): /guides/mysql/mysql-log-buffer-waits/
  • MySQL long-running transactions: detecting and killing the silent blocker: /guides/mysql/mysql-long-running-transactions/
  • MySQL: got a packet bigger than ‘max_allowed_packet’ bytes - causes and fixes: /guides/mysql/mysql-max-allowed-packet-errors/