Re: Hardware/OS recommendations for large databases (

Dave,

On 11/18/05 5:00 AM, "Dave Cramer" <pg(at)fastcrypt(dot)com> wrote:
>
> Now there's an interesting line drawn in the sand. I presume you have
> numbers to back this up ?
>
> This should draw some interesting posts.

OK, here we go:

The $1,000 system (System A):

- I bought 16 of these in 2003 for $1,200 each. They have Intel or Asus
motherboards, Intel P4 3.0GHz CPUs with an 800MHz FSB. They have a system
drive and two RAID0 SATA drives, the Western Digital 74GB Raptor (10K RPM).
They have 1GB of RAM.

* A test of write and read performance on the RAID0:

> [llonergan(at)kite4 raid0]$ time dd if=/dev/zero of=bigfile bs=8k count=250000
> 250000+0 records in
> 250000+0 records out
>
> real 0m17.453s
> user 0m0.249s
> sys 0m10.246s

> [llonergan(at)kite4 raid0]$ time dd if=bigfile of=/dev/null bs=8k
> 250000+0 records in
> 250000+0 records out
>
> real 0m18.930s
> user 0m0.130s
> sys 0m3.590s

> So, the write performance is 114MB/s and read performance is 106MB/s.

The $6,000 system (System B):

* I just bought 5 of these systems for $6,000 each. They are dual Opteron
systems with 8GB of RAM and 2x 250 model CPUs, which are close to the
fastest. They have the new 3Ware 9550SX SATA RAID adapters coupled to
Western Digital 400GB RE2 model hard drives. They are organized as a RAID5.

* A test of write and read performance on the RAID5:

> [root(at)modena2 dbfast1]# time dd if=/dev/zero of=bigfile bs=8k count=2000000
> 2000000+0 records in
> 2000000+0 records out
>
> real 0m51.441s
> user 0m0.288s
> sys 0m29.119s
>
> [root(at)modena2 dbfast1]# time dd if=bigfile of=/dev/null bs=8k
> 2000000+0 records in
> 2000000+0 records out
>
> real 0m39.605s
> user 0m0.244s
> sys 0m19.207s
>
> So, the write performance is 314MB/s and read performance is 404MB/s (!) This
> is the fastest I¹ve seen 8 disk drives perform.
>
So, the question is: which of these systems (A or B) can scan a large table
faster using non-MPP postgres? How much faster would you wager?

Send your answer, and I¹ll post the result.

Regards,

- Luke