Home / Databases / MySQL

DatabasesHow-toIntermediate

Optimization: sizing the MySQL InnoDB buffer pool, redo log and I/O settings from evidence

Most MySQL performance comes from a well-sized buffer pool, enough redo log capacity and I/O settings that match the storage. Measure the buffer pool hit rate and working set, size redo capacity for peak write rates, set I/O capacity for SSD/cloud disks, and change settings online where MySQL allows.

Deepak Bhardwaj2 min read

Applies toMySQL 8.0 and 8.4 LTS (self-managed); many settings are also exposed in parameter groups on Amazon RDS/Aurora MySQL and Azure Database for MySQL

Buffer pool

SELECT @@innodb_buffer_pool_size / 1024 / 1024 / 1024 AS bp_gb;

SHOW GLOBAL STATUS LIKE 'Innodb_buffer_pool_read%';
-- Innodb_buffer_pool_read_requests (logical) vs Innodb_buffer_pool_reads (from disk)

Miss ratio = reads / read_requests. Track it over time; a rising miss ratio under normal load means the working set no longer fits.

-- How much of each table is in the buffer pool (sys schema; can be heavy on big pools)
SELECT object_schema, object_name, allocated, data
FROM sys.innodb_buffer_stats_by_table
ORDER BY allocated DESC LIMIT 20;
Server role Buffer pool starting point
Dedicated database server Large share of RAM (commonly 60–75%), leaving room for connections, OS and other buffers
Shared server Much smaller; account for other services

The buffer pool can be resized online:

SET GLOBAL innodb_buffer_pool_size = 48 * 1024 * 1024 * 1024;

Redo log capacity (8.0.30+)

SELECT @@innodb_redo_log_capacity / 1024 / 1024 AS redo_mb;
SHOW GLOBAL STATUS LIKE 'Innodb_redo_log_%';

Too little redo forces aggressive flushing at peak writes (stalls, "checkpoint age" pressure). A common approach is enough capacity for roughly an hour of peak redo writes; measure LSN growth over a busy period:

SELECT VARIABLE_VALUE FROM performance_schema.global_status WHERE VARIABLE_NAME = 'Innodb_redo_log_current_lsn';
-- wait 60 seconds at peak, run again, difference = bytes per minute

innodb_redo_log_capacity is dynamic in 8.0.30+.

I/O settings for SSD and cloud disks

Setting Guidance
innodb_io_capacity Roughly the sustained IOPS you want background flushing to use
innodb_io_capacity_max Upper bound for bursts
innodb_flush_neighbors 0 on SSD
innodb_flush_method O_DIRECT on Linux to avoid double buffering

Match these to the provisioned IOPS of your cloud disk, not to the default values.

Other memory consumers

  • Per-connection buffers (sort_buffer_size, join_buffer_size) multiply by connection count; keep them modest.
  • Temporary tables (tmp_table_size, max_heap_table_size, TempTable engine limits).

Measure after changes

  • Buffer pool miss ratio, disk reads/sec.
  • Query latency from Performance Schema digests (see the slow queries post).
  • Checkpoint/flush stalls during peak.

Tip

Check innodb_dedicated_server. On a dedicated host it sizes buffer pool and redo automatically from available memory, which is a good baseline before manual tuning (don't combine it with manual settings for the same variables).

Checklist

  • Buffer pool miss ratio and working set measured
  • Buffer pool sized with memory headroom
  • Redo capacity sized for peak writes
  • I/O capacity matched to storage
  • Per-connection buffers kept modest
  • Latency and flushing re-measured

Related fixes