X-Original-To: pgsql-performance-postgresql.org@localhost.postgresql.org Received: from localhost (av.hub.org [200.46.204.144]) by svr1.postgresql.org (Postfix) with ESMTP id CFDB9D7E05 for ; Thu, 24 Nov 2005 04:53:19 -0400 (AST) 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 77617-02 for ; Thu, 24 Nov 2005 08:53:20 +0000 (GMT) X-Greylist: from auto-whitelisted by SQLgrey- Received: from linda-1.paradise.net.nz (bm-1a.paradise.net.nz [203.96.152.180]) by svr1.postgresql.org (Postfix) with ESMTP id 1FB9DD6EBC for ; Thu, 24 Nov 2005 04:53:17 -0400 (AST) Received: from smtp-3.paradise.net.nz (tclsnelbe2-src-nat-1 [203.96.152.177]) by linda-1.paradise.net.nz (Paradise.net.nz) with ESMTP id <0IQG00D0SBCUTY@linda-1.paradise.net.nz> for pgsql-performance@postgresql.org; Thu, 24 Nov 2005 21:53:18 +1300 (NZDT) Received: from [192.168.1.11] (218-101-28-4.dsl.clear.net.nz [218.101.28.4]) by smtp-3.paradise.net.nz (Postfix) with ESMTP id E8612F24F3F; Thu, 24 Nov 2005 21:53:17 +1300 (NZDT) Date: Thu, 24 Nov 2005 21:53:16 +1300 From: Mark Kirkwood Subject: Re: Hardware/OS recommendations for large databases ( In-reply-to: To: Luke Lonergan Cc: stange@rentec.com, Greg Stark , Dave Cramer , Joshua Marsh , pgsql-performance@postgresql.org Message-id: <43857F7C.6060305@paradise.net.nz> MIME-version: 1.0 Content-type: text/plain; format=flowed; charset=ISO-8859-1 Content-transfer-encoding: 7bit X-Accept-Language: en-us, en User-Agent: Mozilla Thunderbird 1.0.6 (X11/20051106) References: X-Virus-Scanned: by amavisd-new at hub.org X-Spam-Status: No, score=0.24 required=5 tests=[AWL=0.240] X-Spam-Score: 0.24 X-Spam-Level: X-Archive-Number: 200511/483 X-Sequence-Number: 15740 Luke Lonergan wrote: > ============================================================================ > 12.9GB of DBT-3 data from the lineitem table > ============================================================================ > llonergan=# select relpages from pg_class where relname='lineitem'; > relpages > ---------- > 1579270 > (1 row) > > 1579270*8192/1000000 > 12937 Million Bytes or 12.9GB > > llonergan=# \timing > Timing is on. > llonergan=# select count(1) from lineitem; > count > ---------- > 59986052 > (1 row) > > Time: 197870.105 ms So 198 seconds is the uncached read time with count (Just for clarity, did you clear the Pg and filesystem caches or unmount / remount the filesystem?) > llonergan=# select count(1) from lineitem; > count > ---------- > 59986052 > (1 row) > > Time: 49912.164 ms > llonergan=# select count(1) from lineitem; > count > ---------- > 59986052 > (1 row) > > Time: 49218.739 ms > and ~50 seconds is the (partially) cached read time with count > llonergan=# select fastcount('lineitem'); > fastcount > ----------- > 59986052 > (1 row) > > Time: 33752.778 ms > llonergan=# select fastcount('lineitem'); > fastcount > ----------- > 59986052 > (1 row) > > Time: 34543.646 ms > llonergan=# select fastcount('lineitem'); > fastcount > ----------- > 59986052 > (1 row) > > Time: 34528.053 ms > so ~34 seconds is the (partially) cached read time for fastcount - I calculate this to give ~362Mb/s effective IO rate (I'm doing / by 1024*1024 not 1000*1000) FWIW. While this is interesting, you probably want to stop Pg, unmount the filesystem, and restart Pg to get the uncached time for fastcount too (and how does this compare to uncached read with dd using the same block size?). But at this stage it certainly looks the the heapscan code is pretty efficient - great! Oh - and do you want to try out 32K block size, I'm interested to see what level of improvement you get (as my system is hopelessly cpu bound...)! > ============================================================================ > Analysis: > ============================================================================ > Bandwidth Percent of max > dd Read 407MB/s 100% > Count(1) 263MB/s 64.6% > HeapScan 383MB/s 94.1% Cheers Mark