I believe in automation as much as possible, and I'm always working to make the day to day tasks of operations as smooth as possible.  Also I try not to be afraid to take good tools and make them better.

Here in Database Ops at Box, we use pt-kill running as a service to constantly monitor our servers and help protect against long running queries.  But our thresholds are pretty generous, and in some cases it's possible for unforeseen circumstances to cause enough queries to storm the database such that we can have problems before any of them hit the threshold for "busy time."  Ditto for idle connections.

The response is that someone has to be available to manually run another copy of pt-kill with much lower thresholds to clear out these thundering herds.  But what if we could let pt-kill handle both the "normal" mode and still protect us from herds?

That's what we've done by adding a …

Sequel Pro

I often use Sequel Pro when I'm getting up to speed on the data model for a project or when I just want to debug in a more visual way than with the mysql command-line client. It's a free OS X application that lets you inspect and manage MySQL databases. I also find it very useful for making small changes to the data while I develop and test web apps.

Quickly Copy Data Between Databases

I recently needed a way to copy a few dozen records from one camp to another. I tried using the "SELECT...INTO OUTFILE" method but ran into a permissions issue with that approach. Using mysqldump was another option but that seemed like overkill in …

Lefred and I spent a bit of time making innotop 1.9.1.
We’ve released a new version mainly to include MySQL 5.6 support as well as including some bugs fixed by Baron Schwartz and Frédéric Descamps.

You can download the .tar.gz and rpm’s (new!) at
http://code.google.com/p/innotop/downloads/list

Bugs fixed:

• Issue 71: DBI prints to STDERR instead of just throwing an inactive statement error
• Issue …

Original images from Flickr user jenniferwilliams

One of our clients, for various historical reasons, runs both MySQL and PostgreSQL to support their website. Information for user login lives in one database, but their customer activity lives in the other. The eventual plan is to consolidate these databases, but thus far, other concerns have been more pressing. So when they needed a report combining user account information and customer activity, the involvement of two separate databases became a significant complicating factor.

In similar situations in the past, using earlier versions of PostgreSQL, we've written scripts to pull data from MySQL and dump it into PostgreSQL. This works well enough, but we've updated …

Nagios Check For Calculating Based on Mysql Server Variables

Recently we needed to make a change for a client to one of our mysql monitoring tools so I thought it would be a good opportunity to highlight the tool and discuss some of the changes that I made.

You can access the tool on Github from our public repository here.

Before this change if you were using either the "varcomp" or "lastrun-varcomp" modes, it would only return a WARNING if your criteria for comparison were exceeded. In the new version, both WARNING and CRITICAL states can return to nagios. Here is an example: Let's say you want to alert on maximum connections, but the number of maximum connections is different for different hosts. Instead of writing distinct per/host checks, you can use this check to do a simple calculation …

Nagios Check For Calculating Based on Mysql Server Variables

Recently we needed to make a change for a client to one of our mysql monitoring tools so I thought it would be a good opportunity to highlight the tool and discuss some of the changes that I made.

You can access the tool on Github from our public repository here.

Before this change if you were using either the "varcomp" or "lastrun-varcomp" modes, it would only return a WARNING if your criteria for comparison were exceeded. In the new version, both WARNING and CRITICAL states can return to nagios. Here is an example: Let's say you want to alert on maximum connections, but the number of maximum connections is different for different hosts. Instead of writing distinct per/host checks, you can use this check to do a simple calculation …

pt-stalk recipes: Gather forensic data about MySQL when a server problem occurs

It happens to us all from time to time: a server issue arises that leaves you scratching your head. That’s when Percona Toolkit’s pt-stalk comes into play, helping you diagnose the problem by capturing diagnostic data that helps you pinpoint what’s causing the havoc hitting your database.

From the documentation (http://www.percona.com/doc/percona-toolkit/pt-stalk.html):

pt-stalk watches for a trigger condition to become true, and then collects data to help in diagnosing problems. It is designed to run as a daemon with root privileges, so that you can diagnose intermittent problems that you cannot observe directly. You can also use it to execute a custom command, or to gather the data on demand without waiting for the trigger to happen. …

One of my favorite tools in the Percona Toolkit is pt-query-digest.  This tool is indispensable for identifying your top SQL queries, and analyzing which queries are accounting for your database load.

But the report you get from pt-query-digest is only as good as the log of queries you give it as input.  You need a large enough sample of query logs, collected over a period of time when you have representative traffic on your database.

You also need the log to include all the queries, not just those that take more than N seconds.  The reason is that some queries are individually quick, and would not be logged if you set the long_query_time configuration variable to 1 or more seconds. …

Do you ever find yourself wanting to open several terminal windows and send the same commands to all of them? I’ve had this need many times, and I’ve never found a completely satisfactory solution. I’ve also known a lot of people who’ve written various sets of scripts to help them accomplish such tasks.

In no particular order, here are a few ways I’ve done this in the past:

1. Facebook’s pmysql client
2. The dsh tool
3. Several screen windows named remoteXXX, followed by a bash for-loop: while read cmd; do screen -X at remote# stuff "$cmd"; done
4. Using many PuTTY windows and the puttycs tool
5. Opening many tabs in KDE’s Kterm tool and selecting the options to send input to all tabs

Here …

If you ever had a replication slave that is severely behind, you probably noticed that it’s not catching up with a busy master at a steady pace. Instead, the “Seconds behind master” is going up and down so you can’t really tell whether the replica is catching up or not by looking at just few samples, unless these are spread apart. And even then you can’t tell at a glance when it is going to catch up.

Normally, the “severely behind” thing should not happen, but it does often happen in our consulting practice:

• sometimes replication would break and then it needs to catch up after it is fixed,
• other times new replication slave is built from a backup which is normally hours behind,
• or, it could be that replication slave became too slow to catch up due to missing index

Whatever the case is, single question I am being asked by the customer every time this happens is this: …