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:

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!