Tag: MySQL

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?

Pushing a row to the end of a sort with MySQL

November 11, 2011 » Geek

Sometimes you have weird requirements to meet. It’s a fact of life.

One of these requirements I came across recently was outputting a result set in alpha order, but with a specific tuple at the end, instead of where it belongs in the sort.

I decided I’d figure out how to do this in SQL instead of gross rendering or a re-sort.

Please note there are other ways to solve this that are less brittle, but this was in a fairly static data set for a one off solution.

Consider this table:

Now, pull them out in alpha order:

But what if we always want “Other” at the end?

Well, our unique indices allow us to guarantee a conditional sort on the slug (or name).

To do this, we will use the CASE statement.

Here we have two ORDER BY clauses. We first use CASE to return 1 whenever the slug is “other”, and sort on that. Then we sort the sub-groups (“other” and “non-other”) by slug.

CASE is handy for other stuff too, and can handle complex expressions.

Do you ever use CASE?

You Don’t Know What You Don’t Know – GROUP_CONCAT

September 4, 2010 » Geek

I’ve been trying to get Sphinx search running at work and I’ve been stymied by needing to get data out of a many-to-many table. This has to be a possibility, why can’t I find a reference to it, and I’m not much of a DBA to begin with.

Turns out it’s not that hard, it just takes an SQL construct that I’d never seen before, GROUP_CONCAT. Once you have that building block, it becomes pretty easy, but without it, it seemed insurmountable.

This highlights a flaw in my autodidact education. If I just glom together from the internet, and something isn’t mentioned (or is only mentioned tangentially) then I don’t even know I don’t know something. Interesting problem to have to work around.