Re: Persistent connections in PHP

On 8/13/07, Josh Trutwin <josh(at)trutwins(dot)homeip(dot)net> wrote:
> On Mon, 13 Aug 2007 09:44:26 -0500
> Erik Jones <erik(at)myemma(dot)com> 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.