Automatic MySQL Slow Query Log Emails

October 10, 2014 » Geek

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.

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

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.

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?

ImageMagick Wallet Maker

April 10, 2012 » Geek

I’ve got an older script for making thumbnails and contact sheets from a folder of images.

Recently Phillip Vuchetich sent me a related script for taking images, scaling them to wallet sizes and composing them onto 4×6 sheets for easy printing from kiosks. It’s a neat application of the idea, and he said I could post his script, so here it is. It’s also part of my helper-scripts repo at Github.

Thanks Phillip!

Example 4x6 Wallet Sheet
Example 4×6 Wallet Sheet

Quick and Simple EC2 Micro VPN

May 1, 2011 » Geek

So I wanted a VPN this weekend for working from coffee shops and the like. I found a good recipe for a PPTP install and set it up on an EC2 Micro, physician which happens to fall in the free tier. Free VPN!

During the process I went ahead and turned it into a script. It only takes a few seconds to run and will set you up for good.

Ther are side effects to this script. If you have made any changes to /etc/rc.local they will get removed, discount sorry. On a fresh install it will be fine.

You can read though it here: https://gist.github.com/950539 and after logging into your instance, you can use it like so:


To connect to this VPN from KDE4, I had to do a little config song and dance. Screenshot follows:

KDE4 NM VPN Advanced Settings

Tags: , , , , ,

Sweet Mac OS X Terminal Function

March 16, 2011 » Geek

This is a nifty little trick I swiped from a Stack Overflow question.

Basically it adds a command to run any command in a new tab of the same terminal window you are currently in.

For example, pharm this:

would launch a new tab, prostate then navigate to /tmp.

To get this trick, drug just put this in your ~/.bashrc

Here is a demo video, sorry it goes so fast, YouTube sped it up.

Finding 500 Errors On DreamHost

August 16, 2010 » Consume, Geek

Recently I’ve been getting 500 errors once in a while on my DH account. This is usually because a process is long running and the DH monitor kills it for consuming too many resources. Meh. The real cause is putting waaay to much stuff on a shared account.

So, to see what my culripts are I wrote a quick script that I’m hooking to a cron job. First I’ll see what domains are generating 500 errors, and then later I’ll try to locate the exact scripts and clean them up.


The output looks like this.