Re: PHP Connections - Mailing list pgsql-php

On Tue, 5 Aug 2003, Lynna Landstreet wrote:

> On my site I have one global include file with the db connection in it, and
> just call it in the head section of each page. The code just references the
> $db handle where it needs it, rather than connecting again. Even with that,
> I keep wondering if it might be better to use a persistent connection that
> would last from page to page rather than each page connecting over again.
> But the problem there is that I don't necessarily know which page visitors
> are going to come in on, so I need to make sure they always get a
> connection, and connecting once per page seems like a reasonable compromise.
>
> But hey, come to think of it, maybe I could turn the pg_connect into a
> pg_pconnect and just have it check to see if $db already exists before
> connecting - that way there'd be only one connection per site visit! Cool,
> your question gave me a new idea... :-)

Be VERY wary of persistant connections.  They are fraught with danger and
can cause your server to present the "too many connections" error message
after being up for a while.

The reason is the dirt simple persistant connection technology in
PHP/Apache.  Each persistant connection stays open until the child process
dies or the connection errors or times out.  Since Postgresql, by default,
doesn't time out idle connections, and apache, by default doesn't kill
it's children every so often, only if there's too many, and postgresql, by
experience never seems to crash (backend or especially postmaster for me)
this means that each persistant connection functioanlly lasts forever.

So, if apache is configured for 150 children (the default) and postgresql
is configured for 32 children (again, the default) then after the server's
been up and under load, it is quite possible for all the connections to
postgresql to get exhausted and for the 33rd connection to get a too many
connection errors.

Easy fix, just crank up max backends, right?  Well, sorta.  The rule of
TANSTAAFL (there ain't no such thing as a free lunch.)  If Postgresql has
150+ connections sitting open and idle, it's likely not gonna run as fast
as if it had only 3 or 4 children open.

So, it's generally better to turn down the number of apache children to
control this problem.

But that brings up apache's keep alive mechanism, a standard part of the
http/1.1 protocol, which basically means that whichever request came from
ip w.x.y.z last time will likely get serviced by the same child, which
will stay assigned to that client as long as he clicks every X seconds
(usually 15 or so).

If apache has 30 keep alive connects going, and a max children setting of
150, when a new request from a new client comes along, it will NOT reuse
one of the pre-existing children unless its keep alive has timed out.  So,
if you've got 100 distinct users clicking every 7 to 15 seconds, you may
find apache spawning LOTS of children if you haven't turned it down.

Now, if the max children are set to 30, and apache has 30 keep alive
connections going, it will reuse one of the pre-existing child processes.

That means that whatever page cache was being kept for that client likely
is lost (but still probably in kernel buffers, so no huge loss.)

Now, things get real ugly when you start connecting to different
databases.

Say I have 64 databases, and my scripts have equal chances of using any
one of them depending on which customer site whatever they are hitting.

Now we look at pgsql.max_persistent.  this setting is PER CHILD.  Not for
the whole apache/php server.  If you have this set to unlimited, and your
child processes are hopping all over due to keep alive timeout/out of
children mentioned above, then you could theoretically have 64 persistant
connections open, per child.  That's 1920 connections.   ugh.

It's important to understand that php persistant connects are NOT
connection pooling in the classic java sense, and if not configured
properly, can bring an apache/php/postgresql server to it's knees under
fairly light load.

Put another way, whereas non-persistant connections have a slow degrade
under increasing of load, persistant connections, if improperly
configured, fail catastrophically under increasing load.

Configured properly:

pgsql.max_persistent set to 1 to 4
apache max_children set to 30 to 100
max_connections set to the two numbers above multiplied by each other,
plus an extra dozen or so for fudge factor

they can work quite well and help a busy web site.  But they aren't always
a gain, since having too many connections open at once can cause a
performance issue too.