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 )