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 C58428B9FB6 for ; Wed, 9 Feb 2005 21:00:15 +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 41200-08 for ; Wed, 9 Feb 2005 21:00:02 +0000 (GMT) Received: from stark.xeocode.com (gsstark.mtl.istop.com [66.11.160.162]) by svr1.postgresql.org (Postfix) with ESMTP id 475E88B9F04 for ; Wed, 9 Feb 2005 21:00:03 +0000 (GMT) Received: from localhost ([127.0.0.1] helo=stark.xeocode.com) by stark.xeocode.com with smtp (Exim 3.36 #1 (Debian)) id 1CyywB-0003tN-00; Wed, 09 Feb 2005 15:59:51 -0500 To: Chris Kratz Cc: pgsql-performance@postgresql.org Subject: Re: Performance Tuning References: <200502091501.31447.chris.kratz@vistashare.com> In-Reply-To: <200502091501.31447.chris.kratz@vistashare.com> From: Greg Stark Organization: The Emacs Conspiracy; member since 1992 Date: 09 Feb 2005 15:59:50 -0500 Message-ID: <87wtthcuux.fsf@stark.xeocode.com> Lines: 27 User-Agent: Gnus/5.09 (Gnus v5.9.0) Emacs/21.3 MIME-Version: 1.0 Content-Type: text/plain; charset=us-ascii X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.057 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200502/152 X-Sequence-Number: 10481 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. > 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? -- greg