Flapping Head

I recently had to troubleshoot a busy mysql database server for a client. Here’s some basic tips.

SHOW PROCESSLIST is your friend

Inside mysql you can run show processlist to find out what the server is doing. From the list you can see how many connections there are, which database, whether they are causing a lock, waiting for a lock, and even the query being run. Very helpful.

You can get the same info from the mysqladmin program. A simple way to monitor what the server is doing is to have cron email you every hour (or whatever interval) with the process list:

crontab -e

MAILTO="you@example.com"
31 * * * * mysqladmin -u root -psecret --verbose processlist

Sleeping connections need closing

If you have a lot of connections in a sleep state, it means they’re not doing anything but are still held open. The server can run out of connections if they’re all busy sleeping.

Assuming the database is being accessed by a website (e.g. php code), you could look for ways to close the database connection sooner.

If you’re using Smarty, instead of:

// open mysql connection
// do stuff
$smarty->display('template.tpl');
// close mysql connection

you might try:

// open mysql connection
// do stuff
$output = $smarty->fetch('template.tpl');
// close mysql connection
print $output;

The difference is, you’re closing the mysql connection before returning data to the client. If you’ve got slow clients (and assuming no reverse-proxy or load balancer to spoon-feed) it could release the database connection much sooner.

EXPLAIN those queries

If you have slow queries, you can find out why using explain. See how many rows they need to read (perhaps there’s no index the query can use) and if they need to be optimised.