InnoDB Buffer Pool Sizing

Introduction¶

The InnoDB buffer pool caches table and index pages (and the change buffer, adaptive hash index, locks, and more) in RAM. On a database server it's usually the largest single memory consumer and the biggest lever on performance.

What it caches¶

• Data pages — 16 KB pages of table rows.
• Index pages — 16 KB pages of B-tree nodes.
• Change buffer — buffered modifications to secondary index pages not yet in the pool.
• Adaptive hash index — a hash of hot pages for faster lookups.

A query that finds every page it needs in the pool never touches disk. A query that doesn't triggers reads, and if the pool is full, evicts other pages (LRU).

Sizing¶

# my.cnf
innodb_buffer_pool_size = 24G

Guidance:

• Dedicated DB server: 60–75% of RAM. Leave room for the OS, connections (each has buffers), temp tables, and other MySQL memory. On a 32 GB box, ~22–24 GB.
• Shared server (app + DB): much less — size to the working set, not a percentage.
• Working set smaller than data: you don't need the pool to fit all data, just the frequently accessed pages. A 500 GB database with a 40 GB hot set runs fine with a 48 GB pool.
• Don't exceed what leaves the OS comfortable. Swapping the buffer pool is catastrophic — worse than a smaller pool.

MySQL 8 lets you resize online (SET GLOBAL innodb_buffer_pool_size = ...), in 128 MB chunks; it takes time and I/O.

Instances¶

innodb_buffer_pool_instances = 8    # only matters if pool >= 1G; default 8 in 8.0

Splits the pool into N independent regions to reduce mutex contention on many-core boxes. Default is fine; only tune on very high concurrency.

Warmup: dump and restore¶

A cold pool after restart means minutes of slow, disk-bound queries. InnoDB can save and reload the list of cached pages:

innodb_buffer_pool_dump_at_shutdown = ON     # default ON in 8.0
innodb_buffer_pool_load_at_startup   = ON
innodb_buffer_pool_dump_pct          = 75    # dump the 75% most-recently-used

Force a dump/load without restarting:

SET GLOBAL innodb_buffer_pool_dump_now = ON;
SET GLOBAL innodb_buffer_pool_load_now = ON;

Monitoring¶

SHOW ENGINE INNODB STATUS\G      -- the BUFFER POOL AND MEMORY section

SELECT * FROM performance_schema.global_status
WHERE VARIABLE_NAME IN (
  'Innodb_buffer_pool_read_requests',   -- logical reads (from pool)
  'Innodb_buffer_pool_reads',           -- physical reads (from disk) -- want this LOW
  'Innodb_buffer_pool_pages_free',
  'Innodb_buffer_pool_pages_dirty',
  'Innodb_buffer_pool_wait_free');      -- >0 means the pool is under pressure

Hit ratio ≈ 1 - (Innodb_buffer_pool_reads / Innodb_buffer_pool_read_requests). Above ~99% is healthy for OLTP; a falling ratio under steady load means the working set outgrew the pool.

The sys schema:

SELECT * FROM sys.innodb_buffer_stats_by_table ORDER BY allocated DESC LIMIT 10;

Verification and troubleshooting¶

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS gb;

free -g
grep -i swap /proc/meminfo
vmstat 1 5           # si/so columns — any swap in/out is bad

• Innodb_buffer_pool_reads climbing steadily under constant workload — pool too small for the working set, or a new query/report scanning cold data. Identify with the slow log (see MySQL slow query log analysis).
• Box is swapping — pool + connections + OS exceed RAM. Shrink the pool, or reduce max_connections / per-connection buffers (sort_buffer_size, join_buffer_size are per connection).
• Innodb_buffer_pool_wait_free > 0 — the flush rate can't keep up; check innodb_io_capacity, disk latency, and innodb_buffer_pool_pages_dirty %.
• Slow for minutes after every restart — dump/load disabled, or the dump file (ib_buffer_pool) isn't being written (permissions, shutdown killed before dump).
• Resize online "hangs" — it's working, in chunks, doing I/O. Watch Innodb_buffer_pool_resize_status.
• Huge pool, still slow — the bottleneck is elsewhere: bad queries, missing indexes, lock contention, redo log too small, slow disk for writes. The pool helps reads, not everything.

Related tools and reading¶

• On-site: Timestamp converter.
• Related posts: MySQL slow query log analysis, Reading a MySQL EXPLAIN plan.