Working with our users at Bonanza earlier this week, we saw their team demonstrate a great example of how monitoring insights can lead to a relatively simple — but impactful — MySQL system tweak. In this case, the adjustment Bonanza made resulted in huge improvements to their total query time.
By looking at the mysql.innodb.queued_queries metric in VividCortex, it became clear to Bonanza's team there was an issue within InnoDB that was preventing otherwise runnable threads from executing. Often, when queries begin to queue, it's indicative of a problem; it's a good idea to regularly look for states like queuing, pending, or waiting as signs of potential issues. In this case, the innodb_thread_concurrency parameter had been configured to 8. Once VividCortex revealed the mysql.innodb.queued_queries metric, the parameter was changed to 0 (self governing).
The fix was implemented at about 5:35 pm on 3/28/2017. In the VividCortex chart below, you can see where queries cease queuing (because they've started executing faster and more efficiently). Note how the orange line drops off almost immediately.
This second chart shows how in the hour after the fix, SELECT total time dropped by over 50% compared to the hour previous. That is 9.59 fewer hours that the system spent executing queries — or 9.59 hours of extra CPU time available. Average latency went from 1.36 ms all the way down to 664.6 μs.
This is a view of the query itself, from 4:35 pm to 6:35 pm. Note the overall decrease at 5:35 pm.
Great work by the Bonanza team, and thank you for sharing!