Off Topic - Re: [PERFORM] Quad processor options - summary

This is somthing I wish more of us did on the lists. The list archives
have solutions and workarounds for every variety of problem but very few
summary emails exist. A good example of this practice is in the
sun-managers mailling list. The original poster sends a "SUMMARY" reply
to the list with the original problem included and all solutions found.
Also makes searching the list archives easier.

Simply a suggestion for us all including myself.

Greg

Bjoern Metzdorf wrote:
> 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
>
>
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
>
> http://www.postgresql.org/docs/faqs/FAQ.html

--
Greg Spiegelberg
Product Development Manager
Cranel, Incorporated.
Phone: 614.318.4314
Fax: 614.431.8388
Email: gspiegelberg(at)cranel(dot)com
Technology. Integrity. Focus. V-Solve!