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 51A608B9CCC for ; Wed, 9 Feb 2005 22:18:16 +0000 (GMT) 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 46848-07 for ; Wed, 9 Feb 2005 22:18:02 +0000 (GMT) Received: from greywolf.vistashare.net (www.vistashare.net [65.207.67.162]) by svr1.postgresql.org (Postfix) with ESMTP id 30EBC8B9FD6 for ; Wed, 9 Feb 2005 22:18:03 +0000 (GMT) Received: from [10.100.2.210] (phoenix.vistashare.net [10.100.2.210]) by greywolf.vistashare.net (Postfix) with ESMTP id 08B5A1EE5E; Wed, 9 Feb 2005 17:18:00 -0500 (EST) From: Chris Kratz Organization: VistaShare To: Greg Stark Subject: Re: Performance Tuning Date: Wed, 9 Feb 2005 17:17:59 -0500 User-Agent: KMail/1.7.1 Cc: pgsql-performance@postgresql.org References: <200502091501.31447.chris.kratz@vistashare.com> <87wtthcuux.fsf@stark.xeocode.com> In-Reply-To: <87wtthcuux.fsf@stark.xeocode.com> MIME-Version: 1.0 Content-Type: text/plain; charset="iso-8859-1" Content-Transfer-Encoding: 7bit Content-Disposition: inline Message-Id: <200502091718.00014.chris.kratz@vistashare.com> X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0 tagged_above=0 required=5 tests= X-Spam-Level: X-Archive-Number: 200502/158 X-Sequence-Number: 10487 On Wednesday 09 February 2005 03:59 pm, Greg Stark wrote: > Chris Kratz writes: > > We continue to tune our individual queries where we can, but it seems we > > still are waiting on the db a lot in our app. When we run most queries, > > top shows the postmaster running at 90%+ constantly during the duration > > of the request. The disks get touched occasionally, but not often. Our > > database on disk is around 2.6G and most of the working set remains > > cached in memory, hence the few disk accesses. All this seems to point > > to the need for faster processors. > > I would suggest looking at the top few queries that are taking the most > cumulative time on the processor. It sounds like the queries are doing a > ton of logical i/o on data that's cached in RAM. A few indexes might cut > down on the memory bandwidth needed to churn through all that data. Hmmm, yes we continue to use indexes judiciously. I actually think we've overdone it in some cases since inserts are starting to slow in some critical areas. > > Items changed in the postgresql.conf: > > ... > > random_page_cost = 1 # units are one sequential page fetch cost > > This makes it nigh impossible for the server from ever making a sequential > scan when an index would suffice. What query made you do this? What plan > did it fix? Yes, it got set back to 2. I was testing various settings suggested by a posting in the archives and that one didn't get reset.