Official Forumwarz Blog

The official blog for the web game, Forumwarz

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:


set-variable=long_query_time=2
log-slow-queries=/var/log/mysql/mysql-slow.log

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:

Slow MySQL Queries Plugin for Scout

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!

About these ads

Written by eviltrout

May 27, 2008 at 5:07 PM

Posted in Home

Tagged with , , , ,

5 Responses

Subscribe to comments with RSS.

  1. That’s pretty sweet if you have a large rails site.

    Echuu

    May 27, 2008 at 5:07 PM

  2. interesting stuff, even to someone who knows next to nothing about this kinda thing

    FAIL

    May 27, 2008 at 5:07 PM

  3. set-variable=long_query_time=2
    log-slow-queries=/var/log/mysql/mysql-slow.log

    ^^^^^ what is that?

    set-variable=lo…

    log.slowquery.seconds=2
    log.slowquery.file=/var/log/mysql/mysql-slow.log
    log.lolcats.interval=daily
    log.lolcats.lols=over9000!!!

    WHO the heck writes code to parse property files like that?

    INSANITY

    WHAT THE HECK

    May 27, 2008 at 5:07 PM

  4. Have you checked the result of unequip/equipping items. It seems to take as long as the selling items did.

    Noche

    May 27, 2008 at 5:07 PM

  5. @WHAT THE HECK yes I know it’s a weird looking syntax but that’s what it needs. Don’t ask me!

    Evil Trout

    May 27, 2008 at 5:07 PM


Comments are closed.

Follow

Get every new post delivered to your Inbox.

%d bloggers like this: