Re: To many connections Error - Mailing list pgsql-general
From | scott.marlowe |
---|---|
Subject | Re: To many connections Error |
Date | |
Msg-id | Pine.LNX.4.33.0212181303260.3807-100000@css120.ihs.com Whole thread Raw |
In response to | Re: To many connections Error (Alvaro Herrera <alvherre@dcc.uchile.cl>) |
List | pgsql-general |
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. :-)
pgsql-general by date: