Re: Hardware/OS recommendations for large databases

While I agree with you in principle that pg becomes CPU bound 
relatively easily compared to other DB products (at ~110-120MBps 
according to a recent thread), there's a bit of hyperbole in your post.

a. There's a big difference between the worst performing 1C x86 ISA 
CPU available and the best performing 2C one (IIRC, that's the 
2.4GHz, 1MB L2 cache AMDx2 4800+ as of this writing)

b. Two 2C CPU's vs one 1C CPU means that a pg process will almost 
never be waiting on other non pg processes.  It also means that 3-4 
pg processes, CPU bound or not, can execute in parallel.  Not an 
option with one 1C CPU.

c. Mainboards with support for multiple CPUs and lots' of RAM are 
_not_ the cheap ones.

d.  No one should ever use RAID 0 for valuable data.  Ever.  So at 
the least you need 4 HD's for a RAID 10 set (RAID 5 is not a good 
option unless write performance is unimportant.  4HD RAID 5 is 
particularly not a good option.)

e. The server usually needs to talk to things over a network 
connection.  Often performance here matters.  Mainboards with 2 1GbE 
NICs and/or PCI-X (or PCI-E) slots for 10GbE cards are not the cheap ones.

f. Trash HDs mean poor IO performance and lower reliability.  While 
TOTL 15Krpm 4Gb FC HDs are usually overkill (Not always.  It depends 
on context.),
you at least want SATA II HDs with NCQ or TCQ support.  And you want 
them to have a decent media warranty- preferably a 5 year one if you 
can get it.  Again, these are not the cheapest HD's available.

g. Throughput limitations say nothing about latency 
considerations.  OLTP-like systems _want_ HD spindles.  AMAP.  Even 
non OLTP-like systems need a fair number of spindles to optimize HD 
IO: dedicated WAL set, multiple dedicated DB sets, dedicated OS and 
swap space set, etc, etc.  At 50MBps ASTR, you need 16 HD's operating 
in parallel to saturate the bandwidth of a PCI-X channel.
That's ~8 independent pg tasks (queries using different tables, 
dedicated WAL IO, etc) running in parallel.  Regardless of application domain.

h. Decent RAID controllers and HBAs are not cheap either.  Even SW 
RAID benefits from having a big dedicated RAM buffer to talk to.

While the above may not cost you $80K, it sure isn't costing you $1K either.
Maybe ~$15-$20K, but not $1K.

Ron


At 01:07 AM 11/18/2005, Luke Lonergan wrote:
>Greg,
>
>
>On 11/17/05 9:17 PM, "Greg Stark"  wrote:
>
> > Ok, a more productive point: it's not really the size of the database that
> > controls whether you're I/O bound or CPU bound. It's the available I/O
> > bandwidth versus your CPU speed.
>
>Postgres + Any x86 CPU from 2.4GHz up to Opteron 280 is CPU bound after
>110MB/s of I/O.  This is true of Postgres 7.4, 8.0 and 8.1.
>
>A $1,000 system with one CPU and two SATA disks in a software RAID0 will
>perform exactly the same as a $80,000 system with 8 dual core CPUs and the
>world's best SCSI RAID hardware on a large database for decision support
>(what the poster asked about).
>
>Regards,
>
>- Luke
>
>
>
>---------------------------(end of broadcast)---------------------------
>TIP 9: In versions below 8.0, the planner will ignore your desire to
>        choose an index scan if your joining column's datatypes do not
>        match