If you’ve used relational databases for more than ten minutes, I hope you’ve heard of slow queries. Those are those pesky little gremlins that are slowing down your startup, and preventing scalability you so desperately need.
Luckily there’s a solution. What I’ve found is if I send a report to developers every week, it keeps these issues front and center, for folks that are very busy indeed.
The script below is for RDS, but you can surely modify it if you have a physical server or roll-your-own MySQL box on Amazon. Take a look & enjoy!
Join 26,000 others and follow Sean Hull on twitter @hullsean.
1. install percona tools
Percona as many probably already know, are a wildly successful services firm that support MySQL and related technologies. They also have a very popular & scalable MySQL distribution by the same name.
Even if you’re not using Percona MySQL, you definitely want to get ahold of the percona toolkit. It provides all sorts of useful tools, including the one this article is based on, query-digest.
This tool takes your stock MySQL slow query logfile as input, and summarizes it into a very useful and readable report. Formerly mk-query-digest, it’s not called pt-query-digest. See below.
You can install the percona tools easily by grabbing the repository file and installing that with rpm. From there you can just use yum or apt-get depending on your distribution.
2. install aws command line tool
Amazon has consolidated all it’s command line tools into a single one called just “aws”. The options can be a little arcane, and the error messages misleading besides. What’s good though is it is slightly easier to install & configure.
Do you already use Python? Install it this way:
$ pip install awscli
If not, you’ll need to dig into the aws cli installation instructions further.
3. edit .aws/config
After you get the tool installed, you need to setup your environment. I edited a file named /home/shull/.aws/config as follows:
region = us-east-1
aws_access_key_id = BLIBJZMKLWIL5UTNRBMQ
aws_secret_access_key = MF5J/2z7HmN92lQUrV12ZO/FBXNjDVjL52TNRWsG
Those access_key_id and secret_access_key you can find on your amazon dashboard. Click upper right hand corner under your name, select the menu item “Security Credentials”.
Check out: Are SQL Databases Dead?
4. edit send_query_report.sh
I wrote the script below so you can fairly easily edit it.
# get the rds db instanceID from command line (or crontab) entry
# here's where we'll store the latest slowquery.log
#SLOWLOG=`/bin/ls -tr /home/shull/*.log | /usr/bin/tail -1`
# fetch slow query log from rds box
# here I always grab the latest one.
/usr/local/bin/aws rds download-db-log-file-portion --db-instance-identifier $AWS_INSTANCE --output text --log-file-name slowquery/mysql-slowquery.log > $SLOWLOG
# query report output
# pt-query-digest location
# run the tool to get analysis report
$MKQD $SLOWLOG > $SLOWREPORT
# today's date in a variable
#YESTERDAY=`/bin/date -d "1 day ago" +\%m/\%d/\%Y-\%H:\%S`
# report subject
SUBJECT="Sean Query Report -- $TODAY "
# send an email using /bin/mail
/usr/bin/mailx -s "$SUBJECT" "$EMAIL" < $SLOWREPORT
Note, if you don't have mailx installed, it should be available in your repository. Use apt-get or yum as necessary to get it installed.
5. Add to crontab
After you've tested the above script from command line, you will want to add it to a weekly cron job. Voila, automation! Don't forget to chmod +x to make it executable.
00 09 * * 5 /home/shull/send_query_report.sh seandb