Optimizing MySQL on SSD: Real‑World InnoDB Tuning Strategies

Background

Before reading this article, note that the MySQL server IP is anonymized and some private information is omitted. Project A experienced regular, severe I/O fluctuations every 15 minutes, with innodb_buffer_pool_pages_flushed and disk I/O alternating peaks and valleys, and util reaching 100%. After eliminating triggers, events, stored procedures, front‑end timers, and system crontab, the issue was traced to InnoDB log switching, though further analysis was needed.

Initial adjustments on the primary server (24) included disabling the Query Cache, setting the InnoDB log size to 1280 MiB, and keeping innodb_max_dirty_pages_pct at 30 while leaving innodb_io_capacity at 200. These changes stabilized I/O.

To be safe, the project migrated the primary to an SSD‑equipped machine and also moved replica 27. After migration, further SSD‑specific MySQL InnoDB tuning was planned before and after the program switch.

SSD Characteristics

Excellent random read performance; sequential read is average but better than SAS.

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

Erase‑before‑write causes write amplification, affecting write speed.

Wear‑leveling extends lifespan but can impact read performance.

Read latency is much lower than write latency, unlike traditional disks.

Sequential writes outperform random writes due to reduced erase overhead.

In summary, SSDs favor random reads over sequential reads, sequential writes over random writes, and suffer from write amplification and wear‑leveling effects.

Database Optimization on SSD

Reduce repeated writes to the same location, especially for InnoDB redo logs.

Convert scattered writes into append or batch writes for data files.

Increase sequential write volume.

Specific adjustments:

Change the system I/O scheduler to NOOP.

Increase each log file size to 1280 MiB (innodb_log_file_size).

Continuously tune innodb_io_capacity and innodb_max_dirty_pages_pct to balance flushing and I/O.

Disable innodb_adaptive_flushing to observe effects.

Adjust innodb_write_io_threads and innodb_read_io_threads.

The four Linux I/O schedulers are CFQ, NOOP, Deadline, and Anticipatory. For SSDs, NOOP often performs best because other algorithms aim to reduce seek time, which SSDs lack.

I/O Type            NOOP   Anticipatory   Deadline   CFQ
Sequential Read    22256  7955           22467      8652
Sequential Write   4090   2560           1370       1996
Sequential RW Read 6355   760            567        1149
Sequential RW Write6355   760            565        1149
Random Read        17905  20847          20930      20671
Random Write       7423   8086           8113       8072
Random RW Read     4994   5221           5316       5275
Random RW Write    4991   5222           5321       5278

The results show NOOP slightly outperforms the other schedulers.

InnoDB Parameter Details

innodb_log_file_size – size of InnoDB log files.

innodb_io_capacity – number of dirty pages flushed per second.

innodb_max_dirty_pages_pct – percentage of dirty pages allowed in the buffer pool.

innodb_adaptive_flushing – adaptive dirty‑page flushing.

innodb_write_io_threads – number of background threads for write I/O.

innodb_read_io_threads – number of background threads for read I/O.

A Project MySQL Master‑Slave Diagram

Pre‑Switch Tuning (Section 6)

6.1 Modify System I/O Scheduler

echo "noop" > /sys/block/sda/queue/scheduler

To make it permanent, add elevator=noop to the kernel boot parameters in /etc/grub.conf.

6.2 Set innodb_io_capacity = 4000

SET GLOBAL innodb_io_capacity = 4000;

Increasing to 4000 did not improve performance due to heavy UPDATE workload; I/O pressure became too high.

6.3 Set innodb_max_dirty_pages_pct = 25

SET GLOBAL innodb_max_dirty_pages_pct = 25;

This lowered the dirty‑page threshold but showed little effect on the low‑pressure replica.

6.4–6.5 Adjust innodb_io_capacity to 2000 then 1500

Reducing the capacity lowered I/O peaks and smoothed fluctuations.

6.6 Disable innodb_adaptive_flushing

SET GLOBAL innodb_adaptive_flushing = OFF;

No noticeable impact; dirty pages still flushed based on I/O pressure.

6.7 Re‑enable innodb_adaptive_flushing

SET GLOBAL innodb_adaptive_flushing = ON;

Leaving it enabled is advisable.

6.8 Set innodb_max_dirty_pages_pct = 20

SET GLOBAL innodb_max_dirty_pages_pct = 20;

With a 40 GiB buffer pool, 20% (8 GiB) was not reached, so no effect.

6.9 Set innodb_io_capacity = 1000

SET GLOBAL innodb_io_capacity = 1000;

This achieved a balanced I/O load, keeping utilization around 10%.

Post‑Switch Tuning (Section 7)

7.1 Set innodb_max_dirty_pages_pct = 30, innodb_io_capacity = 1500

SET GLOBAL innodb_max_dirty_pages_pct = 30;
SET GLOBAL innodb_io_capacity = 1500;

This stabilized dirty‑page count and I/O utilization.

7.2 Set innodb_max_dirty_pages_pct = 40, innodb_io_capacity = 2000

SET GLOBAL innodb_max_dirty_pages_pct = 40;
SET GLOBAL innodb_io_capacity = 2000;

Further adjustment for higher I/O load.

7.3 Analysis

Monitoring showed that when dirty pages approached the max threshold, flushing increased, producing the observed curves.

Conclusion

Change the system I/O scheduler to NOOP.

Continuously analyze I/O and dynamically adjust innodb_io_capacity and innodb_max_dirty_pages_pct.

Experiment with innodb_adaptive_flushing to assess its impact.

Further tuning of innodb_write_io_threads and innodb_read_io_threads (e.g., increasing to 8 or 16) could improve performance.

Remember that external articles may have limited applicability; always prioritize business stability over raw performance gains.