Insufficient Disk Bandwidth

Scenario

The SQL statement execution of an RDS for MySQL instance slows down (for more than 5 seconds), and an error is reported due to a timeout.

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.
   Figure 1 Slow SQL queries
   
2. Check the CPU usage. The CPU usage was 88% at the time and was not a performance bottleneck.
   Figure 2 CPU usage
   
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.
   Figure 3 QPS
   
4. Check the disk read and write throughput. The disk throughput reached 350 MB/s, making it a performance bottleneck.
   For details about storage performance, see DB Instance Storage Types.

   Figure 4 Disk throughput
   

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.