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 (