On 8/13/07, Josh Trutwin <josh@xxxxxxxxxxxxxxxxxxx> wrote: > On Mon, 13 Aug 2007 09:44:26 -0500 > Erik Jones <erik@xxxxxxxxxx> wrote: > > > I'll agree with Scott on this one. (Not that I can recall > > specifically ever disagreeing with him before...). Unless you > > know all of the potential caveats associated with php's persisent > > postgres connections and have a use case that fits them, don't use > > them. If you need something to pool connections, look at pgpool. > > Could elaborate a little on the problems with using php's persistent > connections? > > Personally I use ADODB php abstraction library (adodb.sf.net) for my > database stuff and I think there's a way to enable persistent > connections though I just use the default connection. > > I've heard before that php's persistent connections are to be > avoided, was just curious as to why though? OK, there are a few things that gather together to make php's persistant connections a problem. 1: Each apache / php process maintains its own connections, not sharing with others. So it's NOT connection pooling, but people tend to think it is. 2: Each unique connection creates another persistent connection for an apache/php child process. If you routinely connect to multiple servers / databases or as > 1 user, then each one of those combinations that is unique makes another persistent connection. 3: There's no facility in PHP to clean an old connection out and make sure it's in some kind of consistent state when you get it. It's in exactly the same state it was when the previous php script finished with it. Half completed transactions, partial sql statements, sequence functions like currval() may have values that don't apply to you. 4: pg_close can't close a persistent connection. Once it's open, it stays open until the child process is harvested. 5: Apache, by default, is configured for 150 child processes. Postgresql, and many other databases for that matter, are configured for 100 or less. Even if apache only opens one connection to one database with one user account, it will eventually try to open the 101st connection to postgresql and fail. So, the default configuration of apache / postgresql for number of connections is unsafe for pconnect. 6: The reason for connection pooling is primarily to twofold. One is to allow very fast connections to your database when doing lots of small things where connection time will cost too much. The other is to prevent your database from having lots of stale / idle connections that cause it to waste memory and to be slower since each backend needs to communicate with every other backend some amount of data some times. pconnect takes care of the first problem, but exacerbates the second. P.s. dont' think I'm dogging PHP, cause I'm not. I use it all the time, and it's really great for simple small scripts that need to be done NOW and need to be lightweight. I even use pconnect a bit. But my machine is set for 50 or fewer apache children and 150 postgresql connects, and I only use pconnect on small, lightweight things that need to zoom. Everything else gets regular old connect. ---------------------------(end of broadcast)--------------------------- TIP 2: Don't 'kill -9' the postmaster
