From: Mark Kirkwood <markir@paradise.net.nz>
To: Luke Lonergan <llonergan@greenplum.com>
Cc: stange@rentec.com, Greg Stark <gsstark@mit.edu>
Cc: Dave Cramer <pg@fastcrypt.com>
Cc: Joshua Marsh <icub3d@gmail.com>
Cc: pgsql-performance@postgresql.org
Subject: Re: Hardware/OS recommendations for large databases (
Date: Thu, 24 Nov 2005 21:53:16 +1300
Message-ID: <43857F7C.6060305@paradise.net.nz> (raw)
In-Reply-To: <BFAAB702.1460D%llonergan@greenplum.com>
References: <BFAAB702.1460D%llonergan@greenplum.com>
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
Reply instructions:
You may reply publicly to this message via plain-text email
using any one of the following methods:
* Reply to all the recipients using the --to and --cc options:
reply via email
To: pgsql-performance@postgresql.org
Cc: markir@paradise.net.nz, llonergan@greenplum.com, gsstark@mit.edu, pg@fastcrypt.com, icub3d@gmail.com
Subject: Re: Hardware/OS recommendations for large databases (
In-Reply-To: <43857F7C.6060305@paradise.net.nz>
* Save the following mbox file, import it into your mail client,
and reply-to-all from there: mbox
This inbox is served by DDX for PostgreSQL; see mirroring instructions
for how to clone and mirror all data and code used for this inbox