Take the 2-minute tour ×
Server Fault is a question and answer site for system and network administrators. It's 100% free, no registration required.

We have a web application that uses apache and mysql. Generally (according to Munin) our MySQL thread count sits between 2 and 4 at all times. The other day, our server almost came to a halt. HTTP requests were slow or wouldn't go through at all, SSH would work, but would take 30+ seconds to register keystrokes, etc.. So we pull up Munin and the only thing that's out of normal boundaries is the Mysql thread count. CPU usage was under 1%, load was under 1.0, plenty of available RAM.

As mentioned before, the thread count floats around 2 to 4. At the time of our slow downs it had spiked to 14. So I start poking around the Internet and I see that in most cases, you'll start to see a higher thread count when you start running into slow queries. If I understand it correctly, the request comes in that takes a while to process, in the mean time other requests are coming in, so a new thread will be created to work on the request (yes?). But at the time of the slow down, we had 0 slow queries.

My question is: What else can cause mysql to create additional threads. And would this sudden spike in threads possibly cause the server to slow down? To fix the issue, we restarted apache and everything went back to it's beautiful, normal self. Considering the the Server Vitals (CPU, RAM, Network, etc) were all ideal, and the thread count was the only thing out of place, this seems like the most logical thing to pursue as the possible cause.

If it matters, we're on Mysql 5.1.40. Server is FreeBSD 7.2 and the server in question is inside a jail.

share|improve this question

2 Answers 2

up vote 2 down vote accepted

Depending on what Apache is doing, lots of simultaneous requests to Apache could cause this. For example a big gnarly CMS might open a MySQL connection early on, take a long time to generate a page, and not close the connection till it's finished.

FWIW, I've found that periodic polling of the current connections doesn't usually show the real picture. You should look at max_used_connections. Unfortunately, this value is difficult to manage: http://dba.stackexchange.com/questions/6040/how-can-i-get-the-maxium-number-of-concurrent-connections-to-mysql-over-a-specif

When this happens, have a look at the output of SHOW FULL PROCESSLIST; See if there are a lot of processes in the sleep state. There's at least one bug that can cause MySQL to hang on SHOW VARIABLES.

share|improve this answer
    
Great, thanks. This isn't the first time that this has happened. When we're not around to fix it (i.e. in the middle of the night..) it tends to fix itself after 10-15 minutes. So we generally have a large enough window to log in and poke around. I'll add these to a to-do list the next time it happens. –  Safado Nov 30 '11 at 18:31

Instead of using:

mysql -e "show global status"|grep -i threads_connected

or lsof

you may consider using the procfs itself with common GNU/Linux utilities:

find /proc/`pidof mysqld`/fd/ -follow -type s | wc -l
share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.