Sign up ×
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
Can you share what was your solution to this ? I am curious to know about it as i have the same exact problem as yours, myself. Thx – username55 Jun 2 at 13:55
Gosh, this was so long ago I can't remember. But reviewing, I'm guessing it was an issue with slow queries. One way you can tell if it's slow queries is to set the long_query_time to something a little lower than the default (we have it set to 3 seconds). Most normally queries for our app shouldn't take longer than a second, so 3 is a good measure for something that's slow. You can then check the slow query log and see if you have a higher than normal amount of queries taking too long, and from there you can optimize them. Additionally, you can do show processlist... – Safado Jun 4 at 2:47
this will give you an immediate view of current processes and how long they've been running. If you have a specific query thats killing your server, chances are you'll see multiple instances of that query and you'll see that the processes have all been running longer than normal. A good monitoring program is a must. I've grown to love a tool called MONyog. Check it out. – Safado Jun 4 at 2:52

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:

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


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.