One of the difficulties found in running a shared hosting environment, or even a dedicated hosting area, is finding out who is using resources and why. Apache running Suexec or some variant thereof can let you know who owns the scripts that are pounding the box. MySQL process are a bit more difficult to analyze. MyTop is definitely an essential tool for seeing who is pounding it RIGHT NOW, but isn’t as accurate or at least helpful in finding routine abusers. Luckily MySQL provides for the logging of slow queries. This is their story.

The logging of slow queries is fairly easy to setup in MySQL. There are even several ways to do so. You can add the “–log-slow-queries=/var/log/mysqlslowqueries.log” parameter in the command line. However, most of the time, you will have mysql starting up as a service/daemon meaning that in order to get that done, you need to edit the /etc/init.d/mysql file (not good) and adding the line in there if you can find where it is supposed to go. The easier way is to add the following line to your /etc/my.cnf file under the [mysqld] group:

log-slow-queries=/var/log/mysqlslowqueries.log

Once that is done, you will need to create the log file since mysql doesn’t seem to be able to do that on its own, at least from what I have seen:

touch /var/log/mysqlslowqueries.log

You will also need to chmod it to be the mysqld’s user so he can write in it.

chown mysql:mysql /var/log/mysqlslowqueries.log

Then restart the mysqld service, generally like so:

service mysqld restart

With that done, you will get this in the /var/log/mysqlslowqueries.log file:

/usr/sbin/mysqld, Version: 5.0.45-community-log (MySQL Community Edition (GPL)). started with:
Tcp port: 0  Unix socket: (null)
Time                 Id Command    Argument

Once mysql starts hitting some slow queries, you will get entries that look something like this:

# Time: 080505 14:03:40
# User@Host: dbusername[dbname] @ localhost []
# Query_time: 17  Lock_time: 0  Rows_sent: 0  Rows_examined: 0
use seantubbs_stdemo;
SELECT option_value FROM wp_options WHERE option_name = 'siteurl';

That’s pretty useful but if you are getting thousands of these entries, and in a shared environment it is quite likely you will, it can be nearly impossible to search through. Here is a command sequence you can use to parse through, find the users with the most entries, and display them:

grep "User@Host" /var/log/mysqlslowqueries.log | awk '{ print $3 }' | sort | uniq -c | sort -nr | head

This will give you output similar to :

    763 crappiec[crappiec]
    646 root[root]
    492 barrowwight_barr[barrowwight_barr]
    289 crappiec_joom1[crappiec_joom1]
     81 panthers_planet[panthers_planet]
     40 horde[horde]
     29 papapedro_ucmadm[papapedro_ucmadm]
     24 bourbon_bourbon[bourbon_bourbon]
     20 kshdesign_03admi[kshdesign_03admi]
     19 toasterlogic_sur[toasterlogic_sur]

That tells you the the users and how many entries they have in the slow query log. At that point you can look for queries for specific users in the log file and see what exactly is taking those queries so long.

There you go, simple, fast, and dirty way of doing mysql slow query monitoring.

Tags: Databases, Linux, tutorials // 2 Comments »