Zach suggested it last week, but it’s only now that I’ve gotten around to setting up MySQL’s slow query log.
It’s easy enough, you’ve just got to put a couple lines like this in your my.cnf (which is in /etc on my server):
log-slow-queries = /var/log/mysql/mysql-slow.log<br /> long_query_time = 10
This should get most people running, but this story in Database Journal offers a few more details. Potentially more useful is this guide to query and index optimization (though it’s probably a little out of date).