Monitor slow MySQL queries with Scout
About a month ago I started using Scout to monitor Forumwarz from a performance standpoint.
Scout can be very useful for finding performance bottlenecks in your Rails apps! If you set it up with the Ruby on Rails Request Monitoring plugin, it will email you all of the requests that are running slowly. I found this invaluable when debugging performance problems on the site.
I was surprised to find out that selling items, for example, was by far the slowest thing you could do! I’d never received a single complaint about it, because the operation always finished in 5-6 seconds, but behind the scenes it was taxing the processor!
However, just knowing which requests are responding slowly is not always enough information to fix them. Often there can be many database queries being run, and any one of them could be your bottleneck.
MySQL conveniently has an option for a slow query log, where it will log all the queries that are operating slowly. You can usually enable it in your my.cnf file with a couple of lines like this:
I personally have MySQL logging every query that takes longer than 2 seconds to finish.
Once I had MySQL logging this information, I created a plugin for Scout to monitor the log file for slow queries. It’s based on the plugin code for the Request Monitoring. You can grab it here:
The plugin has two options, the path to your MySQL slow queries log and the threshold for a very slow query. Anything above this threshold will be emailed to you, including the SQL that caused your DB to do lots of work.
Using this information, you can then fine tune your queries and improve your site performance!