Loading...

How to Debug Sleeping MySQL Processes

A server in our environment was not working properly. This was a webserver running apache and a db server.

Everything was running well, and then we experienced lots of random slowdowns and seemingly queries that do not seem to run and execute properly. Another problem was that we experienced lots of sleeping processes when we checked the db server.

Restarting the db server didn’t work, nor did restarting the webserver. Checking the disk space on the db server showed that free disk space was more than adequate. So how do you debug sleeping mysql processes?

In the SHOW PROCESSLIST command, one of the fields returned is the Host field. For example:

xxx.xxx.xxx.xxx:39769

The most important part here is the port number (39769). The ip address here in my example showed that the originating ip was one of our webservers.

We now log into our webserver and type in the command:
netstat -ntp | grep 39769

This would now show (this is simulated output below):
tcp 0 0 192.168.1.20:39769 192.168.1.1:3306

We can filter out the pid (process id) using the netstat command and using the pid do a strace command:

strace -p [pid]

In my case here, it was found out that the webserver’s /var partition was full.

Hope these instructions help you guys when you have some weird stuff going on with mysql and your webserver.

 
 

2 comments
Leave a Reply

Your email address will not be published. Required fields are marked *