Jimbo

I'm writing a site in PHP+MySQL, and I've been using mysql_pconnect for everything... logically this would result in the entire system using a single persistent connection to the database (I'm trying to avoid overhead from opening and closing connections all the time). The site is intended to have multiple (and hopefully lots of) users doing stuff simultaneously, so I got to thinking about it and I was wondering if having the single connection would end up as a bottleneck if the server had any sort of load on it. Might it become necessary to implement a server pool if the traffic becomes high enough?

I've never done a site expecting any sort of load before, so I'm learning these things as I go, and I figured it's easiest to get it right the first time... :o

DaWei

Have you seen this, Jim?

__________________
Abstraction doesn't make it impossible to write bad code; it makes it possible to write superior code.
Contributor's Corner: Grumpy on C++ Exceptions DaWei on Pointers

Dameon

According to the PHP documentation, a persistent DB connection is not shared between child processes. If you are using Apache, more child processes will spawn as more connections are simultaneously served, according to the settings for the MPM (Multi-Processing Module). I believe that that module is in common use, so chances are, lots of requests will end up being split among a relatively small pool of DB connections. I don't know how much of a bottleneck a persistent connection can be, but I doubt it will be a problem. I would anticipate the load on the DB server to be the issue if you reach that point.

__________________
MD5(sig) = bcef75433db02e9ad9bf81d6f7c5c270

Mocker

If you are using persistant connections and expect any number of users at the same time, make sure you have your allowed mysql connections set a lot higher than normal. The majority of the time I've seen scripts use them, they rapidly fill up the allowed user connections and often the total connections allowed, which then breaks the site for anyone else. Also be sure to close them upon exiting. Leftover connections will also trash the number of connection slots you have, even though they are no longer in use. Really I cannot see very many uses of persisten connections, probably mostly for a smaller amount of users performing a heavy amount of mysql calls in a single visit (which only helps if you have php run as a module and not as cgi).

__________________
#programmingforums relay - http://thegupstudio.com/cgi-bin/pforelay.cgi
freelance scripts - http://ryanguthrie.com/index.html

Jimbo

I had seen that DaWei, but thanks for pointing it out anyways. I guess I kinda scanned over the part about the child processes a little fast, but reading though it again helped clear things up a bit.

I'm still slightly confused about how it works with the child processes. According to Dameon, the child processes can't share a persistent connection between them; however the link DaWei posted says:
So does this mean that each child process will have it's own connection to the database, and then in the eventuality that UserRequest(N+1) is on a different child process than UserRequest(N) the session will actually end up using different DB connections, just without the overhead of closing and opening in between?

And Mocker brought up closing the connections. Is that necessary if the different child processes will be using the same database? If so, how do you close a persistent connection? Or does it automagically close if the child process opens a persistent (or regular) connection to the database with different parameters?

Dameon

Each child process is going to serve a number of requests in its lifetime. The first time a script executed under that process calls pconnect, that child has a DB connection. You can't assume (since HTTP is fairly stateless) that subsequent requests from the same client will be handled by the same thread or process, but it doesn't matter;if another client requests a script that uses pconnect on the same database with the same credentials, then the connection is already open, saving a bit of time but giving the same result.

As load increases, so do the benefits of pconnect. If you have 10 child processes with 10 server threads each and all threads are serving requests (high load indeed), then you end up with 10 long-lived database connections instead of 100 very short ones. Short being in the range of a fraction of a second, long being the child process's lifetime.

__________________
MD5(sig) = bcef75433db02e9ad9bf81d6f7c5c270

Jimbo

Ok, I think I've got that part down now. Thanks for the explanation.

What about closing them? Should I be worried about that?

Dameon

pconnect acts as a drop-in replacement for a normal connect. Normal connections terminate when the script does (or when explicitly told to do so), but not persistent ones. Attempts to close them with the <database>_close function result in no action. Instead, cleanup is up to the child process, perhaps when it terminates, perhaps after a timeout.

__________________
MD5(sig) = bcef75433db02e9ad9bf81d6f7c5c270

Jimbo

Ok. Thanks.