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.
Best Answer
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: https://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.