Insufficient Disk Bandwidth

Scenario

Troubleshooting

1. Check for slow SQL queries. In this example, slow SQL queries of the instance started to increase at 18:03 and reached up to 700 per second.
2. Check the CPU usage. The CPU usage was 88% at the time and was not a performance bottleneck.
3. Check the QPS. The QPS increased by more than three times from 18:03 to 18:05, indicating that the service was being provided during peak hours.
4. Check the disk read and write throughput. The disk throughput reached 350 MB/s, making it a performance bottleneck.

Solution

1. Adjust the value of innodb_io_capacity or innodb_io_capacity_max to prevent high I/O throughput because underlying data reads and writes will generate physical I/Os if the requested data page cannot be hit in the buffer pool.
2. Purchase a DB instance with high-performance extreme SSD as the storage type or upgrade the instance memory specifications to cache more data to the buffer pool.