1

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.

  • Please clarify for those of us who are not familiar with the graphs -- When were the indexes added? What do green, red, blue indicate? – Rick James May 1 '18 at 4:07
2

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.
  • 1) The staging and production volumes are the same size (20GB), which means that they both get 100 / 3000 IOPS on the gp2 SSD drive. 2) I have already enabled enhanced monitoring, but only on the 60 second granularity; will the additional detail give me that much extra insight? 3) Slow query logging is something I'm adding today. 4) Will do! – Siyfion Apr 17 '18 at 10:34
  • @Siyfion - What did you learn from the SlowLog? – Rick James May 1 '18 at 4:10
  • @RickJames Not a whole lot if I'm honest. The spikes died down almost immediately after I posted this. I'm pretty sure that they were related to adding the indexes, perhaps MySQL was doing some hygiene equivalent to Postgres' "VACUUM" function? :/ – Siyfion May 2 '18 at 9:42

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.