Re: To many connections Error

On Wed, 18 Dec 2002, Alvaro Herrera wrote:

> On Wed, Dec 18, 2002 at 09:07:19AM -0700, scott.marlowe wrote:
>
> I've seen this several times and I don't know why should one
>
> > 1:  Discontinue use of pg_pconnect.  Performance gains from pg_pconnect
> > aren't that great on a lightly loaded system and the inherent problems
> > that pg_pconnect can produce are much worse than any small performance
> > gain you might see.
>
> What are the problems with pg_pconnect?  Is there some bug on PHP?

There's no bug in it.  It's an architecture issue.  Basically, with
xx_pconnect for ANY database in PHP, PHP assumes that each apache child
process that it is attached to will have it's own persistant connection.
In other words, while Java and other interfaces support connection
pooling, PHP supports persistant connections for EACH child process on the
server, and the child processes do NOT share these connections, like they
would in real pooling.

This means that if you have your apache box configured for 100 max
children, then postgresql will need to hand at least that many connections
in order to handle all those persistant connections.

Note that in the php.ini file, the max_persistant_connections (not sure
that's the right name) setting is PER APACHE CHILD.  I.e. a
max_persistant_connections setting of 4 on a box setup for 100 max apache
child processes could theoretically have 400 connections open to the
postgresql server at the same time.

The good news is that postgresql can handle 200 to 1000 connections quite
well on a large enough server.  Even a lowly Pentium 100 with 64 Megs ram
running ONLY postgresql can handle 100 connections just fine.

The other good news is that you don't need 150 max child processes for
apache to handle load well.  Most apache servers can work just fine with a
max child processes setting of 25 to 100, and the child processes will
just server requests kind of "round robin" fashion instead of each client
getting their own child processes, they will get whichever one is ready,
since the one that served their request last may well be serving someone
else right now.

This is why you should never try to re-use properties / transactions
across persistant connects, because you can never be sure with apache that
you'll get the same last child process / connection as last time.  Even
with keep alive turned on, and the time out way up, there's always a
chance that your child process from the last page isn't the same.

Making matters worse, this kind of problem will only show up under load
(both the "I didn't get my same child process as last time" and the "out
of connections" problems).  So, everything seems fine in initial testing
with a dozen people, but roll out an app to 500 people and watch it
collapse in sheets of flame.

Well, sorry for being long winded, just wanted to be thorough.

:-)