Skip site navigation (1) Skip section navigation (2)

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

From: Greg Spiegelberg <gspiegelberg(at)cranel(dot)com>
To: pgsql-performance(at)postgresql(dot)org, pgsql-admin(at)postgresql(dot)org
Subject: Off Topic - Re: [PERFORM] Quad processor options - summary
Date: 2004-05-13 13:15:20
Message-ID: 40A374E8.5040809@cranel.com (view raw or flat)
Thread:
Lists: pgsql-adminpgsql-performance
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!


In response to

pgsql-performance by date

Next:From: Fabio PanizzuttiDate: 2004-05-13 14:06:01
Subject: R: Query plan on identical tables differs . Why ?
Previous:From: Shridhar DaithankarDate: 2004-05-13 13:05:19
Subject: Re: Query plan on identical tables differs . Why ?

pgsql-admin by date

Next:From: Jodi KanterDate: 2004-05-13 13:36:03
Subject: upgrade
Previous:From: Laurens WagemakersDate: 2004-05-13 07:23:50
Subject: GNUmakefile size 0

Privacy Policy | About PostgreSQL
Copyright © 1996-2014 The PostgreSQL Global Development Group