1

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.

3
  • 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
    – Jeremy
    Jun 2, 2015 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, 2015 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, 2015 at 2:52

3 Answers 3

2

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.

1
  • 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, 2011 at 18:31
0

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
0

I'm late to the party but just solved a similar issue recently. If you happen to be using the MyISAM storage engine and that there's a write operation, all other read requests have to wait until the table-level lock is released. If the query spans multiple tables, there are two complications: 1. the joined results won't all fit in memory, and a file-sort is called, which means more disk I/O. and 2. more tables are locked, so the read queries are more likely to be queued up. In most cases MySQL will finish the write and the reads will clear up. If the OS fails to release the lock of a file (it happens more often than you'd expect), then the MySQL thread will be stuck.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .