Recently I’ve had to do some heavy-duty maintenance work on a MySQL database that’s still in heavy use, can’t take more than about 30 seconds of downtime, is having serious problems due to a 50 million row table that really needs trimmed down, but is missing the crucial indexes that would allow that to happen easily. Without the indexes, even deleting the complete data set for obsolete accounts can take several hours, never mind the time to perform the more complex purges for active accounts (which should be happening daily, but as they take too long, haven’t been happening for a long time, thus making the problem worse every day!) Of course, adding the indexes that would make this all much easier would also lock the table up even longer.

I have a plan to solve this by temporarily replicating the table to a slave version that has the correct indexes and then swapping the tables (I’ll blog the complete details later if I can get it to work), but whilst I’ve been in investigation mode, I’ve been relying heavily on mytop. This is a wonderful little utility for watching what’s happening in a mysql database, similar to the unix ‘top’ command.

Because the data is in a MyISAM table, and thus has table level locking, it’s prone to the old problem of a long select causing an insert or update to block, which in turn causes all other selects behind that to block as well. So I need to be very careful that none of my queries are causing a big queue. For this, mytop is almost perfect, with one small caveat: out of the box it doesn’t show which threads are locked. I don’t care if my select takes too long if it isn’t blocking anything, but once a queue forms, I need to be ready to kill my thread. Mostly I can work this out from what else is executing, but I prefer letting the computer do that sort of work for me. So I made a simple one line addition to the code.

At around line 1000 there’s a an ‘if ($HAS_COLOR)’ block to print the output in different colours depending on the type of command being executed at the database. At the end of that I added:

print RED()    if $thread->{State} && $thread->{State} eq 'Locked';

Now any locked thread is instantly recognizable, and I can react much quicker to any problem. It’s also quite interesting to watch what’s happening even when I’m not meddling, and see how many locks are naturally arising anyway!

There hasn’t been a release of mytop for a few years now, the mailing list has vanished, and I’m not sure whether it’s even actively maintained any more. So I’m not expecting to see this show up in the live code any time soon. But this post will remind me to add this on any future installations where it might come in useful. The beauty of Free Software!