Mysql – After adding indexes to an AWS RDS MySQL DB getting huge/crippling spikes in IOPS

amazon-rdsawsMySQLperformance

So after looking at the production database, I realised that several queries were running utilizing no indexes at all, and as such, were row-scanning close to 4 million rows. :/

I added the required indexes to both the production db instance and the staging read-replica instance.

The result of adding the indexes on the staging environment has been huge:

staging environment

You can clearly see when the indexes were added, leading to a massive reduction in IOPS and as-such keeping the burst balance at it's maximum level.

However, on our production instance, all is not quite so perfect:

production environment

Again, in general the amount of IOPS has gone down dramatically. However, we are now seeing huge spikes in both read and write IOPS every day or so (no particular schedule) which then completely exhausts our burst balance and cripples the db instance until whatever it is doing is finished, at which point the burst balance goes back up again.

Does anyone have any idea's as to what might cause this sort of behaviour, especially in regards to adding indexes, as these spikes were not occuring before we added them.

Best Answer

Few things you can look forward to this issue.

  1. IOPS limit is based on your volume size if you are using General Purpose SSD.

  2. And IOPS are your reserved IOPS.

    • In staging its fine. Check both volumes are in the same size.
    • Sometimes you are getting a sudden spike, in this case, enable Enhanced monitoring to track every 1sec of MySQL activities.
    • Enable slow query log and set the long-running query time to 0. So it'll capture all the queries.
    • Compare the enhanced monitoring IOPS graph with the slow query log table, then you can get an idea for the bad queries.
Related Question