I spent a lot of time investigating PHP and MySQL last night and learned a lot. I wanted to share some of it with everyone because I think some of the things were doing to limit connections to the database might put even more load on the server. I'd like to recommend that folks not change their reading habits a lot until we figure out exactly what's causing the excess load.

I now have a tiny little Phoenix board running on my Mac at home for testing. I've been digging into the source code of PHP and MySQL (gotta love open source) and I'm starting to understand how this all fits together.

I now suspect that the problem isn't so much too many connections, as too many MySQL threads running at the same time. Each thread uses exactly one connection, but we're allowed to have up 128 of them cached. Recently ita checked and we had 58 threads in the cache, only three of them running, but 26 of them holding a connection. I don't think the cached threads are every deleted, so eventually we'll max out the cache.

Now this new server has a 5 minute idle timeout (as opposed to the 8 hour default), so that might explain why only 26 of the cached threads have a connection. But the threads themselves consume a lot of resources, so they might actually be what's causing the server problem, more than the connections themselves.

I'm looking to see if they can limit our use of threads without affecting other MySQL users on the server. I think there might be a way to tell Apache to limit the buffista.org domain to fewer concurrent threads, which would limit the MySQL threads as well.

I give up.

Noo, don't give up, Kristen.

Hang in there. Rob's in geek Sherlock Holmes mode, which is all for the good.

I now suspect that the problem isn't so much too many connections, as too many MySQL threads running at the same time.

Just to be clear to the non-techies (and even some of us techies), Rob, you're not talking about threads in the sense of Natter, Bitches, etc. You're talking about threads of instructions to the database, or something similar, right?

Yes, I'm talking about threads of instructions to the database. Boy I wish I'd thought of that potential area of confusion up front.

Yeah, he mean threads as in the technical term, which is like one instance of a program running -- the details are a) not so important and b) going to be badly explained by me, so never mind.

However, the tech guy says it's connections, not threads, so we need to focus on addressing what he sees as the problem. He calls the shots.

edit: Or what Rob said.

Is it total number of connections over a period, or max concurrent connections that they are concerned about?

I think it's the # of connections open at that "moment", but I'll double check.

Yup, as I recall it was max concurrent connections that are the deal.

Do we know when they changed the wait_timeout from the default to five minutes? Was that after the last time the server fell over and caught fire?