Re: Hardware/OS recommendations for large databases

Поиск
Список
Период
Сортировка
От Ron
Тема Re: Hardware/OS recommendations for large databases
Дата
Msg-id 6.2.5.6.0.20051118092018.03b6cba0@earthlink.net
обсуждение исходный текст
Ответ на Re: Hardware/OS recommendations for large databases (  ("Luke Lonergan" <llonergan@greenplum.com>)
Список pgsql-performance
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" <gsstark@mit.edu> 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




В списке pgsql-performance по дате отправления:

Предыдущее
От: "Luke Lonergan"
Дата:
Сообщение: Re: Hardware/OS recommendations for large databases (
Следующее
От: Dave Cramer
Дата:
Сообщение: Re: Hardware/OS recommendations for large databases (