X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id 25054D1B285 for ; Wed, 4 Aug 2004 10:01:23 -0300 (ADT) 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 32305-01 for ; Wed, 4 Aug 2004 13:01:28 +0000 (GMT) Received: from rwcrmhc12.comcast.net (rwcrmhc12.comcast.net [216.148.227.85]) by svr1.postgresql.org (Postfix) with ESMTP id 96138D1B257 for ; Wed, 4 Aug 2004 10:01:15 -0300 (ADT) Received: from jefftrout.com ([24.128.241.68]) by comcast.net (rwcrmhc12) with SMTP id <2004080413012301400s92rve>; Wed, 4 Aug 2004 13:01:24 +0000 Received: (qmail 52506 invoked from network); 4 Aug 2004 13:01:23 -0000 Received: from unknown (HELO ?192.168.0.101?) (10.10.10.20) by 10.10.10.10 with SMTP; 4 Aug 2004 13:01:23 -0000 In-Reply-To: <4110DA83.7020505@clockltd.com> References: <4110DA83.7020505@clockltd.com> Mime-Version: 1.0 (Apple Message framework v618) Content-Type: text/plain; charset=US-ASCII; format=flowed Message-Id: <85562EA8-E616-11D8-A77F-000393D1F76E@torgo.978.org> Content-Transfer-Encoding: 7bit Cc: pgsql-performance@postgresql.org From: Jeff Subject: Re: The black art of postgresql.conf tweaking Date: Wed, 4 Aug 2004 09:02:20 -0400 To: Paul Serby X-Mailer: Apple Mail (2.618) X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.1 tagged_above=0.0 required=5.0 tests=RCVD_IN_SORBS X-Spam-Level: X-Archive-Number: 200408/46 X-Sequence-Number: 7682 On Aug 4, 2004, at 8:45 AM, Paul Serby wrote: > > Apache on the Web server can take up to 300 connections and PHP is > using pg_pconnect > > Postgres is set with the following. > > max_connections = 300 > shared_buffers = 38400 > sort_mem = 12000 > > But Apache is still maxing out the non-super user connection limit. > Did you restart PG after making that change? (you need to restart, reload won't change max_connections) Also, you're sort_mem is likely too high (That is the amount of memory that can be used PER SORT) and you s hould back down on shared_buffers. (General consensus is don't go over 10k shared buffers) Another thing you may want to try is using pgpool and regular pg_connect - this way you only have a pool of say, 32 connections to the DB that are shared among all apache instances. This gets rid of the need to have hundreds of idle postgres' sitting around. Connecting to pgpool is very fast. We use it in production here and it works wonderfully. And it is 100% transparent to your application. -- Jeff Trout http://www.jefftrout.com/ http://www.stuarthamm.net/