X-Original-To: pgsql-php-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 48902DA84D for ; Mon, 21 Nov 2005 20:14:01 -0400 (AST) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (av.hub.org [200.46.204.144]) (amavisd-new, port 10024) with ESMTP id 00513-04 for ; Tue, 22 Nov 2005 00:14:02 +0000 (GMT) X-Greylist: delayed 03:00:34.267183 by SQLgrey- Received: from svr2.postgresql.org (svr2.postgresql.org [65.19.161.25]) by svr1.postgresql.org (Postfix) with ESMTP id 02192DA872 for ; Mon, 21 Nov 2005 20:13:53 -0400 (AST) Received: from wolf.pjkh.com (wolf.pjkh.com [66.228.196.74]) by svr2.postgresql.org (Postfix) with ESMTP id 72907F0AC6 for ; Mon, 21 Nov 2005 21:13:24 +0000 (GMT) Received: from localhost (localhost [127.0.0.1]) by wolf.pjkh.com (Postfix) with ESMTP id E1498172AC; Mon, 21 Nov 2005 13:13:18 -0800 (PST) Received: from wolf.pjkh.com ([127.0.0.1]) by localhost (wolf.pjkh.com [127.0.0.1]) (amavisd-new, port 10024) with ESMTP id 29076-05; Mon, 21 Nov 2005 13:13:15 -0800 (PST) Received: by wolf.pjkh.com (Postfix, from userid 1000) id A4719172AB; Mon, 21 Nov 2005 13:13:15 -0800 (PST) Received: from localhost (localhost [127.0.0.1]) by wolf.pjkh.com (Postfix) with ESMTP id 9EFA1172A9; Mon, 21 Nov 2005 13:13:15 -0800 (PST) Date: Mon, 21 Nov 2005 13:13:15 -0800 (PST) From: Philip Hallstrom To: Andrew McMillan Cc: Gary Hoffman , pgsql-php@postgresql.org Subject: Re: Unwanted persisting connections In-Reply-To: <1132563520.5795.4.camel@ubu.mcmillan.net.nz> Message-ID: <20051121131201.C29386@wolf.pjkh.com> References: <3DF94EB5-4391-4709-9695-B544F77C5596@myrealbox.com> <1132563520.5795.4.camel@ubu.mcmillan.net.nz> MIME-Version: 1.0 Content-Type: TEXT/PLAIN; charset=US-ASCII; format=flowed X-Virus-Scanned: amavisd-new at pjkh.com X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0 required=5 tests=[none] X-Spam-Score: 0 X-Spam-Level: X-Archive-Number: 200511/20 X-Sequence-Number: 3337 On Mon, 21 Nov 2005, Andrew McMillan wrote: > On Sun, 2005-11-20 at 20:06 -0800, Gary Hoffman wrote: >> I've run PHP and PostgreSQL for quite a while and never seen this problem >> before. I think it's new, but maybe I never paid attention to it before. >> >> I am frequently running out of connections to the database. Here's a >> snippet of my ps -ax >> >> 1882 ?? S 0:00.07 postgres: postgres cgssd_members [local] idle >> >> 1883 ?? S 0:00.09 postgres: postgres cgssd_members [local] idle >> >> 1884 ?? S 0:00.07 postgres: postgres cgssd_members [local] idle >> >> 1886 ?? S 0:00.14 postgres: postgres cgssd_members [local] idle >> >> 1887 ?? S 0:00.06 postgres: postgres cgssd_members [local] idle >> >> 500 std- S 0:01.14 /usr/local/bin/postmaster -D >> /usr/local/pgsql/data >> 501 std- S 0:00.02 postgres: stats buffer process >> >> 502 std- S 0:00.21 postgres: stats collector process >> >> >> As you can see, there are some idle connections that have come in from PHP >> on my web pages. I've never seen these before and eventually they build up >> and I get an error connecting from the PHP page to the database. >> >> I'm running Apache 1.3.33 on Darwin Kernal 7.9.0 (MacOS X 10.3.9) with PHP >> 4.3.6 and PostgreSQL 7.4.2. > > You will have as many of these as you have apache processes. That's > what persistent connections means! You can end up with a *lot* more than apache's MaxClients if you open persistent connections with different connection paramaters... so keep that in mind if you're connecting to more than one database. You might look at pgpool. Never used it, but it comes up frequently as a solution to this... > > If you have MaxClients set to (e.g.) 150 in your Apache configuration, > and you actually get to something like that, then the default PostgreSQL > configuration of 100 clients simply won't be enough. You should > increase it to at leas 20-50 more than the MaxClients setting that > Apache has. > > You'll be pleased to know that each extra PostgreSQL connection > typically uses less than each additional Apache connection (especially > when you're running PHP et al.) so if you find yourself running out of > RAM you will need to lower the MaxClients setting first. > > Regards, > Andrew McMillan. > > ------------------------------------------------------------------------- > Andrew @ Catalyst .Net .NZ Ltd, PO Box 11-053, Manners St, Wellington > WEB: http://catalyst.net.nz/ PHYS: Level 2, 150-154 Willis St > DDI: +64(4)803-2201 MOB: +64(272)DEBIAN OFFICE: +64(4)499-2267 > You will be a winner today. Pick a fight with a four-year-old. > ------------------------------------------------------------------------- > >