Re: Persistent connections in PHP - Mailing list pgsql-general

On 8/13/07, Scott Marlowe <scott.marlowe@gmail.com> wrote:
> On 8/13/07, Josh Trutwin <josh@trutwins.homeip.net> wrote:
> > On Mon, 13 Aug 2007 09:44:26 -0500
> > Erik Jones <erik@myemma.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.

Oh, one other thing that contributes to the problem is that the
php.ini file has an entry for max persistent connections.  Sadly, most
people think this is max persistent connections for apache / php as a
whole.  it's not.  It's for each apache / php child process.  This
number should generally be set to 1, 2 at the absolute most when using
persistent connections.