A short time ago in a galaxy nearby, Domas Mituzas wrote about contention profiling with GDB stack traces. Mark Callaghan found the technique useful, and contributed an awk script (in the comments) to aggregate stack traces and identify which things are blocking most threads. I’ve used it myself a time or five. But I’ve found myself wanting it to be fancier, for various reasons. So I wrote a little utility that can aggregate and pretty-print backtraces. It can handle unresolved symbols, and aggregate by only the first N lines of the stack trace. Here’s an example of a mysqld instance that’s really, really frozen up:

bt-aggregate -4 samples/backtrace.txt | head -n12
2396 threads with the following stack trace:
        #0  0x00000035e7c0a4b6 in …

Do you test your application systematically when you upgrade or reconfigure your database server? You should! Here’s a real (anonymized) story of what happens if you don’t.

When we upgraded to 5.0.62 (from 5.0.27 and 5.0.45), our code broke for queries like this:

SELECT SUM(amt) FROM daily_amt WHERE day = FROM_UNIXTIME(1222889772);

The problem here was a wrong DATE/DATETIME comparison and other bug fixes in MySQL 5.0.62; it was stricter in enforcing the comparison.

This resulted in an outage and revenue loss to the company.

Daniel and I (mostly Daniel) continue to improve mk-upgrade to make it easy and inexpensive to find these kinds of scenarios before they bite you. Don’t get caught with your pants down — next time you make …

Only the other day I was talking with someone who does a lot of work on the shell command line, but hadn’t used the GNU screen tool, so I’d better scribble a post about it as I regard it as an absolute must-have for any remote work, for multiple reasons.

First of all, what screen does. You start screen inside a terminal session (local or SSH remote), and then you can create additional sessions though Ctrl-A C. The initial screen is number 0, the next one 1, and so on. You can switch between screens with Ctrl-A # where # is the screen number. This way, you can have multiple things going within a single ssh connection, very handy. But that’s not all!

If you get disconnected (it happens and you reconnect, your screen sessions will still be there, and running too. You can reattach with screen -r. To do a nice disconnect, you can do Ctrl-A D (detach) before …

I was actually exploring inotify-tools for something else, but they can also be handy for seeing what goes on below a mysqld process. inotify hooks into the filesystem handlers, and sees which files are accessed. You can then set triggers, or just display a tally over a certain period.

It has been a standard Linux kernel module since 2.6.13 (2005, wow that’s a long time ago already) and can be used through calls or the inotify-tools (commandline). So with the instrumentation already in the kernel, apt-get install inotify-tools is all you need to get started.

# inotifywatch -v -t 20 -r /var/lib/mysql/* /var/lib/mysql/zabbix/*
Establishing watches...
Setting up watch(es) on /var/lib/mysql/mysql/user.frm
OK, /var/lib/mysql/mysql/user.frm is now being watched.
[...]
Total of 212 watches.
Finished establishing watches, now collecting …

It seems pretty common to find customers install DRBD for the wrong reasons. There are many pros/cons to compare DRBD to replication, but I've managed to cut down my spiel I give to customers to these two points:

• DRBD's aim (assuming replication mode C) is to provide 100% consistency, and then as much uptime as possible.
• MySQL Replication (with a manager such as MMM) aims to have 100% availability, at the potential loss of some data surrounding a failure.

So if you are installing DRBD with the aim of purely "availability", and are not worried about losing that last write on the crash to your master database that (hopefully) happens only once every few years, you may be using the wrong technology.

While the prized "1 minute failover" is possible in DRBD, it doesn't really explain the full picture. The typical crash …

Today, I would like to introduce “skyload“, a small project that I’ve been working on for the last couple of weeks. In brief, skyload is a libdrizzle based load emulation tool that is capable of running concurrent load tests against database instances that can speak Drizzle (and/or) the MySQL protocol.

• http://code.google.com/p/skyload/

Something I’d like to emphasize here is that, skyload is not a replacement for mysqlslap or drizzleslap since it only provides a subset of what they can do. As I’ve stated on the project description, skyload is designed to do a good …

For the last couple of months, we've been quietly developing a MySQL protocol parser for Maatkit. It isn't an implementation of the protocol: it's an observer of the protocol. This lets us gather queries from servers that don't have a slow query log enabled, at very high time resolution.

With this new functionality, it becomes possible for mk-query-digest to stand on the sidelines and watch queries fly by over TCP. It is only an observer on the sidelines: it is NOT a man in the middle like mysql-proxy, so it has basically zero impact on the running server (tcpdump is very efficient) and zero impact on the query latency. There are some unique challenges to watching an entire server's traffic, but we've found ways to solve those. Some of them are harder than others, such as making sense of a conversation when you start …

A post by Ronald reminded me I wanted to write about watch. The Linux watch command is a fab tool, it allows you to run a program at a set interval, such as every 5 seconds. Handy for keeping an eye on stuff!

Update 2009-06-30: yesterday I wrote “… watch does not re-load and run the program, but goes in and resets the program counter.” (and was un-recommending it for use on production systems for that reason). I was blindly quoting an otherwise trusted source, and it was wholly wrong. Numerous people had a peek at the source code (all the way back to 1997) and found the popen(). So, I stand corrected and my apologies for not doing extra homework in the first place before posting! (I’m leaving the comments as they are)

I’m decently familiar with grep so I can usually make it do what I want. I frequently need to search for instance MySQL source code for certain pattern strings, and this makes life so much easier. But Akash pointed out ack to me, which has the specific tagline “better than grep” (has the domain even) and I reckon it does live up to that. Win! It’s written in pure Perl, very easy to install (doesn’t even use CPAN if you don’t want).

It recurses into subdirs by default, while ignoring stuff like revision control and binary files. You can search specific types of files through a symbolic name rather than by specifying regexes. And it has colour highlighting, and simply uses the familiar Perl regexes for its pattern matching rather than funky subsets of which there are many distinct ones…