Received: from localhost (maia-4.hub.org [200.46.204.183]) by postgresql.org (Postfix) with ESMTP id 197C79FB92F for ; Mon, 19 Feb 2007 14:24:23 -0400 (AST) Received: from postgresql.org ([200.46.204.71]) by localhost (mx1.hub.org [200.46.204.183]) (amavisd-new, port 10024) with ESMTP id 04008-07 for ; Mon, 19 Feb 2007 14:24:20 -0400 (AST) X-Greylist: from auto-whitelisted by SQLgrey-1.7.4 Received: from exchange.g2switchworks.com (mail.g2switchworks.com [63.87.162.25]) by postgresql.org (Postfix) with ESMTP id CF25F9FB91B for ; Mon, 19 Feb 2007 14:24:19 -0400 (AST) Received: from 10.10.1.37 ([10.10.1.37]) by exchange.g2switchworks.com ([10.10.1.2]) with Microsoft Exchange Server HTTP-DAV ; Mon, 19 Feb 2007 18:24:18 +0000 Received: from state.g2switchworks.com by mail.g2switchworks.com; 19 Feb 2007 12:24:18 -0600 Subject: Re: How to debug performance problems From: Scott Marlowe To: Jeff Davis Cc: Andreas Tille , pgsql-performance@postgresql.org In-Reply-To: <1171909107.10824.185.camel@dogma.v10.wvs> References: <1171909107.10824.185.camel@dogma.v10.wvs> Content-Type: text/plain Content-Transfer-Encoding: 7bit Message-Id: <1171909458.25338.19.camel@state.g2switchworks.com> Mime-Version: 1.0 X-Mailer: Ximian Evolution 1.4.6 (1.4.6-2) Date: Mon, 19 Feb 2007 12:24:18 -0600 X-Virus-Scanned: Maia Mailguard 1.0.1 X-Spam-Status: No, hits=0.028 tagged_above=0 required=5 tests=AWL, BAYES_50, SPF_PASS, UNPARSEABLE_RELAY X-Spam-Level: X-Archive-Number: 200702/196 X-Sequence-Number: 23250 On Mon, 2007-02-19 at 12:18, Jeff Davis wrote: > On Mon, 2007-02-19 at 11:50 +0100, Andreas Tille wrote: > > Hi, > > > > I'm running a web application using Zope that obtains all data > > from a PostgreSQL 7.4 database (Debian Sarge system with package > > 7.4.7-6sarge4 on an "older" Sparc machine, equipped with 2GB > > Upgrade to 8.2.3 if possible, or at least to 7.4.16. What Jeff said ++ > > This is a basic question, but do you VACUUM ANALYZE regularly? 7.4 is > before autovacuum was integrated in the core. If you don't do this you > could have a lot of wasted space in your tables causing unneeded I/O, > and the planner might be making bad plans. Look into vacuum full followed by reindex to fix the bloat. Then schedule regular vacuums (regular, not full). > > memory and two processors E250 server). Once I did some performance > > tuning and found out that > > > > max_connections = 256 > > shared_buffers = 131072 > > sort_mem = 65536 > > > > You're allocating 50% of the physical memory to shared buffers. That's > not necessarily too much, but that's on the high side of the normal > range. For 7.4 that's far too much. Very few installations running 7.4 will be faster as you go past 10000 to 20000 buffers. 131072 is probably slowing the machine down instead of speeding it up, as the buffer cache algo in 7.4 was not that good with large amounts of memory.