jmhobbs

Automatic MySQL Slow Query Log Emails

Something we try to do regularly at Pack is to check for slow queries.

We do this when introducing new features and schema changes, but we also try to do it occasionally to look for anything that may have slipped through, or become more of an issue as usage patterns change.

To make this a more regular occurrence, I decided to automate it.

The first thing that needed to be handled was enabling and disabling the slow query log. I don't want it to run all the time, because eventually it will eat up too much disk, and there has to be overhead to calculating and saving that data.

To turn it on and off, I created a limited privilege user on the server called "slow_log". The commands needed to turn on the slow query log are SET GLOBAL and FLUSH SLOW LOGS. Looking at the MySQL documentation, the privileges needed for those commands are RELOAD and SUPER.

GRANT RELOAD,SUPER ON *.* TO slow_log@localhost IDENTIFIED BY 'password';

Once that user was in place, I created two shell scripts. The first just logs into MySQL and turns on slow query logging.

#!/bin/bash

MYSQL_SLOW_LOG_USER="slow_log"
MYSQL_SLOW_LOG_PASSWORD='password'

SLOW_LOG="/var/log/mysql/mysql-slow.log"

rm -f $SLOW_LOG

cat <

The second script turns slow query logging off, then it processes the slow query log with request-log-analyzer and pt-query-digest. Lastly it emails the output of those tools to me.

#!/bin/bash

MYSQL_SLOW_LOG_USER="slow_log"
MYSQL_SLOW_LOG_PASSWORD='password'

SLOW_LOG="/var/log/mysql/mysql-slow.log"

cat < /tmp/report.txt

recipient='[email protected]'

(
cat - <

Finally, I added a cron job to run the first script at the beginning of the day once a month, and another to run the second at the end of the day once a month. That way, once a month, I get an email with slow query logs to look over and try to improve.

As a note, using a subshell to generate the body of the command is something I hadn't seen before and came across while looking for uuencode usage. It's a nice trick.

So. What did I screw up horribly?