All of this assumes LAMP (specifically CentOS 6, MySQL server 5.5, Apache 2+ with mod-status enabled)
On mysql server:
watch -n 1 "mysql -e 'show processlist';"
Look for sleeping connections that are staying open for a while. Watch for a trend. When you see a connection that you think will stay open for long enough, note it’s port number. Open second shell. Do the following:
netstat -lnap | grep 12345
– where the 12345 is the port number from the mysql process list. If/when you get a result, note the number to the right, next to “ESTABLISHED”. Next – open a web browser. Navigate to http://yourserver.com/server-status and do a search for that number (that’s the PID (process id). The highlighted line is now the process that caused the long-running MySQL thread.
Repeat this process a bunch of times until you have a good overview of what’s causing problems. Viola! Debug success, and it’s time for beers!
( This week, we’re drinking Sam Adams Boston Lager for our friends in Boston )