Skip site navigation (1) Skip section navigation (2)

Re: Hardware/OS recommendations for large databases (

From: Mark Kirkwood <markir(at)paradise(dot)net(dot)nz>
To: Luke Lonergan <llonergan(at)greenplum(dot)com>
Cc: stange(at)rentec(dot)com, Greg Stark <gsstark(at)mit(dot)edu>, Dave Cramer <pg(at)fastcrypt(dot)com>, Joshua Marsh <icub3d(at)gmail(dot)com>, pgsql-performance(at)postgresql(dot)org
Subject: Re: Hardware/OS recommendations for large databases (
Date: 2005-11-24 08:53:16
Message-ID: 43857F7C.6060305@paradise.net.nz (view raw or flat)
Thread:
Lists: pgsql-performance
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

In response to

Responses

pgsql-performance by date

Next:From: Mark KirkwoodDate: 2005-11-24 09:11:36
Subject: Re: Hardware/OS recommendations for large databases (
Previous:From: Luke LonerganDate: 2005-11-24 08:17:06
Subject: Re: Hardware/OS recommendations for large databases (

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group