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 DAD135370E for ; Thu, 24 Mar 2005 21:57:13 +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 82037-06 for ; Thu, 24 Mar 2005 21:57:05 +0000 (GMT) Received: from cmailm1.svr.pol.co.uk (cmailm1.svr.pol.co.uk [195.92.193.18]) by svr1.postgresql.org (Postfix) with ESMTP id 1367D52A76 for ; Thu, 24 Mar 2005 21:57:04 +0000 (GMT) Received: from modem-3062.python.dialup.pol.co.uk ([217.134.219.246] helo=192.168.0.102) by cmailm1.svr.pol.co.uk with esmtp (Exim 4.41) id 1DEaK9-0003Cb-8e; Thu, 24 Mar 2005 21:57:05 +0000 Subject: Re: CPU 0.1% IOWAIT 99% for decisonnal queries From: Simon Riggs To: Patrick Vedrines Cc: performance pgsql In-Reply-To: <006801c53056$a4585420$1e0baf96@pcpatrickxp> References: <002601c52f0a$2312a4d0$280baf96@PCPATRICK> <1111529891.11750.577.camel@localhost.localdomain> <006801c53056$a4585420$1e0baf96@pcpatrickxp> Content-Type: text/plain Organization: 2nd Quadrant Date: Thu, 24 Mar 2005 21:55:56 +0000 Message-Id: <1111701356.11750.683.camel@localhost.localdomain> Mime-Version: 1.0 X-Mailer: Evolution 2.0.2 (2.0.2-3) Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, hits=0.062 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/522 X-Sequence-Number: 11227 On Thu, 2005-03-24 at 10:48 +0100, Patrick Vedrines wrote: > > You've got 1.5Gb of shared_buffers and > 2Gb data. In 8.0, the scan > will > > hardly use the cache at all, nor will it ever, since the data is > bigger > > than the cache. Notably, the scan of B should NOT spoil the cache > for A > Are you sure of that ? Is Postgres able to say to OS: "don't use the > cache for this query"? PostgreSQL 8.0 has the ARC algorithm which prevents cache spoiling of the shared_buffers, but has no direct influence over the OS cache. > > Priming the cache is quite hard...but not impossible. > > What will kill you on a shared_buffers that big is the bgwriter, > which > > you should turn off by setting bgwriter_maxpages = 0 > Is bgwriter concerned as my application applyes only SELECT ? With very large shared_buffers the bgwriter's default settings are a problem. You don't need it, so I suggest turning it off. Best Regards, Simon Riggs