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: [EMAIL PROTECTED] Technology. Integrity. Focus. V-Solve!
---------------------------(end of broadcast)--------------------------- TIP 1: subscribe and unsubscribe commands go to [EMAIL PROTECTED]
