Bonanza Cuts Load in Half with VividCortex

Posted by Ewen Fortune on Mar 31, 2017 1:06:08 PM

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.

Bonanza InnoDB Operations-1.jpg

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.

Bonanza Time Compare.jpg

This is a view of the query itself, from 4:35 pm to 6:35 pm. Note the overall decrease at 5:35 pm.

Bonanza Query Time Compare.jpg

Great work by the Bonanza team, and thank you for sharing!

Read the Free eBook
Estimating CPU Per Query with Weighted Linear Regression

Recent Posts

Posts by Topic

see all