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
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 email@example.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?