Re: Hardware/OS recommendations for large databases (

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