How to Optimize MySQL InnoDB on SSDs: Practical Tuning Steps and Insights

Background

The A project experienced regular, severe I/O fluctuations every 15 minutes, with peaks in innodb_buffer_pool_pages_flushed and disk I/O reaching 100%.

After eliminating triggers, events, stored procedures, application timers, and crontab jobs, the root cause was traced to InnoDB log switching.

Initial Mitigation on Primary Server

Disable Query Cache.

Set InnoDB log file size to 1280 M.

Set innodb_max_dirty_pages_pct to 30 and keep innodb_io_capacity at 200.

These changes stabilized I/O.

SSD Characteristics

Excellent random read performance; sequential read is average.

No seek latency; random and sequential write latency are similar.

Erase‑before‑write causes write amplification.

Wear‑leveling introduces write‑wear and can affect read speed.

Read latency is much lower than write latency.

Sequential writes outperform random writes (e.g., 1 MiB sequential > 128 × 8 KiB random).

SSD‑Based Database Optimizations

Key ideas:

Reduce repeated writes to the same location (Redo Log).

Convert scattered writes into append or batch writes.

Increase sequential write volume.

Specific Parameter Adjustments

System I/O scheduler

Change scheduler to noop (e.g., echo "noop" > /sys/block/sda/queue/scheduler).

Persist by adding elevator=noop to /etc/grub.conf.

Initial InnoDB configuration

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 200
innodb_max_dirty_pages_pct 30
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

Subsequent tuning steps (each applied with SET GLOBAL):

Increase innodb_io_capacity to 4000, then to 2000, 1500, and finally 1000 to match SSD capability.

Adjust innodb_max_dirty_pages_pct from 30 % down to 25 %, 20 %, and up to 40 % depending on observed dirty‑page pressure.

Toggle innodb_adaptive_flushing ON/OFF to evaluate its impact.

Each change was followed by monitoring innodb_buffer_pool_pages_flushed, innodb_buffer_pool_pages_dirty, and I/O utilization (until). The observations were:

Setting innodb_io_capacity to 4000 caused excessive I/O pressure without noticeable benefit.

Reducing it to 1000 stabilized I/O at roughly 10 % utilization.

Lowering innodb_max_dirty_pages_pct reduced dirty‑page accumulation but had limited effect when the buffer pool was not fully utilized.

Disabling innodb_adaptive_flushing did not change the flushing pattern.

Post‑Switch Optimization

After the master‑slave switch (new primary 39), the same parameter set ( innodb_io_capacity = 1500, innodb_max_dirty_pages_pct = 30) was applied, yielding stable I/O and balanced dirty‑page flushing.

Final Configuration

innodb_buffer_pool_size 42949672960
innodb_log_file_size 1342177280
innodb_io_capacity 2000
innodb_max_dirty_pages_pct 40
innodb_adaptive_flushing ON
innodb_write_io_threads 4
innodb_read_io_threads 4

Key Takeaways

Switch the Linux I/O scheduler to noop for SSDs.

Continuously monitor I/O metrics and dynamically adjust innodb_io_capacity and innodb_max_dirty_pages_pct.

Experiment with innodb_adaptive_flushing, but it may not provide noticeable gains.

Parameter tuning must be driven by real‑world data, not by generic recommendations.