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:
MAILTO="firstname.lastname@example.org" 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.