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 6C1FB5655F for ; Tue, 22 Mar 2005 22:19: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 02754-04 for ; Tue, 22 Mar 2005 22:19:12 +0000 (GMT) Received: from cmailm4.svr.pol.co.uk (cmailm4.svr.pol.co.uk [195.92.193.211]) by svr1.postgresql.org (Postfix) with ESMTP id D4D09564E7 for ; Tue, 22 Mar 2005 22:19:11 +0000 (GMT) Received: from modem-2890.lynx.dialup.pol.co.uk ([217.135.203.74] helo=192.168.0.102) by cmailm4.svr.pol.co.uk with esmtp (Exim 4.41) id 1DDriS-00015N-GZ; Tue, 22 Mar 2005 22:19:12 +0000 Subject: Re: CPU 0.1% IOWAIT 99% for decisonnal queries From: Simon Riggs To: Patrick Vedrines Cc: performance pgsql In-Reply-To: <002601c52f0a$2312a4d0$280baf96@PCPATRICK> References: <002601c52f0a$2312a4d0$280baf96@PCPATRICK> Content-Type: text/plain Organization: 2nd Quadrant Date: Tue, 22 Mar 2005 22:18:11 +0000 Message-Id: <1111529891.11750.577.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.048 tagged_above=0 required=5 tests=AWL, FORGED_RCVD_HELO X-Spam-Level: X-Archive-Number: 200503/476 X-Sequence-Number: 11181 On Tue, 2005-03-22 at 19:08 +0100, Patrick Vedrines wrote: > I have 2 databases (A and B) with exactly the same schemas: > -one main table called "aggregate" having no indexes and supporting > only SELECT statements (loaded one time a month with a new bundle of > datas). Row size # 200 bytes (50 columns of type char(x) or integer) > -and several small 'reference' tables not shown by the following > example for clarity reasons. > -Database A : aggregate contains 2,300,000 records ( 500 Mb) > -Database B : aggregate contains 9,000,000 records ( 2 Gb) > (For example : shared_buffers = 190000 , sort_mem = 4096 , > effective_cache_size = 37000 and kernel/shmmax=1200000000 ) > Do I have to upgrade the RAM to 6Gb or/and buy faster HD (of what > type?) ? Setting shared_buffers that high will do you no good at all, as Richard suggests. 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... 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 > PS (maybe of interest for some users like me) : > I created a partition on a new similar disk but on the last cylinders > (near the periphery) and copied the database B into it: the response > time is 25% faster (i.e. 15mn instead of 21mn). But 15 mn is still too > long for my customers (5 mn would be nice). Sounds like your disks/layout/something is pretty sick. You don't mention I/O bandwidth, controller or RAID, so you should look more into those topics. On the other hand...just go for more RAM, as you suggest...but you should create a RAMdisk, rather than use too large shared_buffers....that way your data is always in RAM, rather than maybe in RAM. Best Regards, Simon Riggs