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.
