Automatic MySQL Slow Query Log Emails

October 10, 2014

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
#!/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 <<EOF | mysql -u $MYSQL_SLOW_LOG_USER -p$MYSQL_SLOW_LOG_PASSWORD
SET GLOBAL log_queries_not_using_indexes = 'ON';
SET GLOBAL long_query_time = 1;
SET GLOBAL slow_query_log = 'ON';
FLUSH SLOW LOGS;
EOF

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.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
#!/bin/bash
 
MYSQL_SLOW_LOG_USER="slow_log"
MYSQL_SLOW_LOG_PASSWORD='password'
 
SLOW_LOG="/var/log/mysql/mysql-slow.log"
 
cat <<EOF | mysql -u $MYSQL_SLOW_LOG_USER -p$MYSQL_SLOW_LOG_PASSWORD
SET GLOBAL log_queries_not_using_indexes = 'OFF';
SET GLOBAL slow_query_log = 'OFF';
FLUSH SLOW LOGS;
EOF
 
request-log-analyzer /var/log/mysql/mysql-slow.log --no-progress --reject query /SQL_NO_CACHE/ --output HTML --file /tmp/report.html
pt-query-digest $SLOW_LOG > /tmp/report.txt
 
recipient='jmhobbs@packdog.com'
 
(
cat - <<END
Subject: MySQL Slow Query Log Report
Content-Type: multipart/mixed; boundary="-omgemailslikewhoa"
 
---omgemailslikewhoa
Content-Type: text/plain
Content-Disposition: inline
 
GET ON IT.
 
---omgemailslikewhoa
Content-Type: application; name="report.html"
Content-Transfer-Encoding: base64
Content-Disposition: attachment; filename="report.html"
END
 
uuencode --base64 /tmp/report.html report.html
 
cat <<END
---omgemailslikewhoa
Content-Type: text/plain; name="report.txt"
Content-Disposition: attachment; filename="report.txt"
END
 
cat /tmp/report.txt
 
cat <<END
---omgemailslikewhoa--
 
END
) | /usr/sbin/sendmail -i -- $recipient
 
rm -f $SLOW_LOG
rm -f /tmp/report.html
rm -f /tmp/report.txt

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?

Recent Posts