Ted Naleid

I’m currently working at a startup that’s small enough that we don’t have a dedicated DBA and I’ve been doing a lot of mysql maintenance work recently. I wanted a quick dashboard for which commands were currently running and how long they’ve been running for. Sort of like top but for mysql.

Combining the unix “watch” command with the mysql “show processlist” command gives me what I’m looking for. A quick, self-updating status of the current state of the database.

watch -n 5 --differences "mysql -u username -psekrit -e 'show processlist'"

Shows something like this:

Every 5.0s: mysql -n 5 -u username -psekrit -e 'show processlist'                                   Thu Jun 18 05:25:14 2009
 
Id      User    Host       db      Command Time    State         Info
3141    admin   localhost  mydb    Query   34978   freeing items SELECT id, type, active, email FROM user WHERE email
3146    admin   localhost  mydb    Sleep   0                     NULL       
24876   root    localhost  NULL    Query   0       NULL          show processlist

Where the “time” column is the number of seconds the query has been running, and Info holds the actual query (you can use “show full processlist” to see the full query).

Watch is a nice little linux util that runs a command every “n” seconds (it defaults to 2 seconds). If you use the –differences switch, it will highlight the differences between one update and another. I use watch for all kinds of monitoring activities, such as watching a directory to see a file grow in size as it gets transfered.

Here’s a quick shell function that you can add to your .bashrc/.bash_profile/.zshrc to use on arbitrary hosts:

function mysqltop() {
    MYSQL_OPTS=$@
    watch -n 5 --differences "mysql $MYSQL_OPTS -e 'show processlist'"
}

Then just pass in any creds/host info you need like a normal mysql command:

mysqltop -u ted -psekrit1 -h example.com -P 3307

If you’re on linux, you probably already have “watch” installed. If you’re on OSX, you probably don’t, but you can get it quickly through macports. Install macports, make sure “port” is in your path and run:

sudo port install watch

There is also a command called mytop that you can get which looks like the same thing, but prints out the processlist details with some nicer formatting and a little extra information. It’s in macports, but it has a number of dependencies including mysql so if you didn’t install mysql through macports, you might want to stick with what I have above or get it another way.

(EDIT: updated with bash function)