Quad processor options - summary

Hi,

at first, many thanks for your valuable replies. On my quest for the
ultimate hardware platform I'll try to summarize the things I learned.

-------------------------------------------------------------

This is our current setup:

Hardware:
Dual Xeon DP 2.4 on a TYAN S2722-533 with HT enabled
3 GB Ram (2 x 1 GB + 2 x 512 MB)
Mylex Extremeraid Controller U160 running RAID 10 with 4 x 18 GB SCSI
10K RPM, no other drives involved (system, pgdata and wal are all on the
same volume).

Software:
Debian 3.0 Woody
Postgresql 7.4.1 (selfcompiled, no special optimizations)
Kernel 2.4.22 + fixes

Database specs:
Size of a gzipped -9 full dump is roughly 1 gb
70-80% selects, 20-30% updates (roughly estimated)
up to 700-800 connections during peak times
kernel.shmall = 805306368
kernel.shmmax = 805306368
max_connections = 900
shared_buffers = 20000
sort_mem = 16384
checkpoint_segments = 6
statistics collector is enabled (for pg_autovacuum)

Loads:
We are experiencing average CPU loads of up to 70% during peak hours. As
Paul Tuckfield correctly pointed out, my vmstat output didn't support
this. This output was not taken during peak times, it was freshly
grabbed when I wrote my initial mail. It resembles perhaps 50-60% peak
time load (30% cpu usage). iostat does not give results about disk
usage, I don't know exactly why, the blk_read/wrtn columns are just
empty. (Perhaps due to the Mylex rd driver, I don't know).

-------------------------------------------------------------

Suggestions and solutions given:

Anjan Dave reported, that he is pretty confident with his Quad Xeon
setups, which will cost less than $20K at Dell with a reasonable
hardware setup. ( Dell 6650 with 2.0GHz/1MB cache/8GB Memory, 5 internal
drives (4 in RAID 10, 1 spare) on U320, 128MB cache on the PERC controller)

Scott Marlowe pointed out, that one should consider more than 4 drives
(6 to 8, 10K rpm is enough, 15K is rip-off) for a Raid 10 setup, because
that can boost performance quite a lot. One should also be using a
battery backed raid controller. Scott has good experiences with the LSI
Megaraid single channel controller, which is reasonably priced at ~
$500. He also stated, that 20-30% writes on a database is quite a lot.

Next Rob Sell told us about his research on more-than-2-way Intel based
systems. The memory bandwidth on the xeon platform is always shared
between the cpus. While a 2way xeon may perform quite well, a 4way
system will be suffering due to the reduced memory bandwith available
for each processor.

J. Andrew Roberts supports this. He said that 4way opteron systems scale
much better than a 4way xeon system. Scaling limits begin at 6-8 cpus on
the opteron platform. He also says that a fully equipped dual channel
LSI Megaraid 320 with 256MB cache ram will be less that $1K. A complete
4way opteron system will be at $10K-$12K.

Paul Tuckfield then gave the suggestion to bump up my shared_buffers.
With a 3GB memory system, I could happily be using 1GB for shared
buffers (125000). This was questioned by Andrew McMillian, Manfred
Kolzar and Halford Dace, who say that common tuning advices limit
reasonable settings to 10000-20000 shared buffers, because the OS is
better at caching than the database.

-------------------------------------------------------------

Conclusion:

After having read some comparisons between n-way xeon and opteron systems:

http://www.anandtech.com/IT/showdoc.html?i=1982
http://www.aceshardware.com/read.jsp?id=60000275

I was given the impression, that an opteron system is the way to go.

This is what I am considering the ultimate platform for postgresql:

Hardware:
Tyan Thunder K8QS board
2-4 x Opteron 848 in NUMA mode
4-8 GB RAM (DDR400 ECC Registered 1 GB modules, 2 for each processor)
LSI Megaraid 320-2 with 256 MB cache ram and battery backup
6 x 36GB SCSI 10K drives + 1 spare running in RAID 10, split over both
channels (3 + 4) for pgdata including indexes and wal.
2 x 80 GB S-ATA IDE for system, running linux software raid 1 or
available onboard hardware raid (perhaps also 2 x 36 GB SCSI)

Software:
Debian Woody in amd64 biarch mode, or perhaps Redhat/SuSE Enterprise
64bit distributions.
Kernel 2.6
Postgres 7.4.2 in 64bit mode
shared_buffers = 20000
a bumbed up effective_cache_size

Now the only problem left (besides my budget) is the availability of
such a system.

I have found some vendors which ship similar systems, so I will have to
talk to them about my dream configuration. I will not self build this
system, there are too many obstacles.

I expect this system to come out on about 12-15K Euro. Very optimistic,
I know :)

These are the vendors I found up to now:

http://www.appro.com/product/server_4144h.asp
http://www.appro.com/product/server_4145h.asp
http://www.pyramid.de/d/builttosuit/server/4opteron.shtml
http://www.rainbow-it.co.uk/productslist.aspx?CategoryID=4&selection=2
http://www.quadopteron.com/

They all seem to sell more or less the same system. I found also some
other vendors which built systems on celestica or amd boards, but they
are way too expensive.

Buying such a machine is worth some good thoughts. If budget is a limit
and such a machine might not be maxed out during the next few months, it
would make more sense to go for a slightly slower system and an upgrade
when more power is needed.

Thanks again for all your replies. I hope to have given a somehow clear
summary.

Regards,
Bjoern