X-Original-To: pgsql-admin-postgresql.org@localhost.postgresql.org Received: from localhost (unknown [200.46.204.2]) by svr1.postgresql.org (Postfix) with ESMTP id ACC75D1B26B; Thu, 13 May 2004 10:15:50 -0300 (ADT) Received: from svr1.postgresql.org ([200.46.204.71]) by localhost (neptune.hub.org [200.46.204.2]) (amavisd-new, port 10024) with ESMTP id 95287-03; Thu, 13 May 2004 10:15:32 -0300 (ADT) Received: from COLSWEEPER.cranel.com (newmail.cranel.com [66.192.200.227]) by svr1.postgresql.org (Postfix) with ESMTP id 17E50D1EA37; Thu, 13 May 2004 10:15:26 -0300 (ADT) Received: from colmail01.cranel.local (colmail01.cranel.local) by COLSWEEPER.cranel.com (Content Technologies SMTPRS 4.3.12) with ESMTP id ; Thu, 13 May 2004 09:14:53 -0400 Received: from cranel.com (192.168.11.134 [192.168.11.134]) by colmail01.cranel.local with SMTP (Microsoft Exchange Internet Mail Service Version 5.5.2657.72) id K4629B6R; Thu, 13 May 2004 09:20:04 -0400 Message-ID: <40A374E8.5040809@cranel.com> Date: Thu, 13 May 2004 09:15:20 -0400 From: Greg Spiegelberg Organization: Cranel, Inc. User-Agent: Mozilla/5.0 (X11; U; Linux i686; en-US; rv:1.6) Gecko/20040113 X-Accept-Language: en-us, en MIME-Version: 1.0 To: pgsql-performance@postgresql.org, pgsql-admin@postgresql.org Subject: Off Topic - Re: [PERFORM] Quad processor options - summary References: <40A2A7FA.6060403@turtle-entertainment.de> In-Reply-To: <40A2A7FA.6060403@turtle-entertainment.de> Content-Type: text/plain; charset=us-ascii; format=flowed Content-Transfer-Encoding: 7bit X-Virus-Scanned: by amavisd-new at postgresql.org X-Spam-Status: No, hits=0.0 tagged_above=0.0 required=5.0 tests= X-Spam-Level: X-Archive-Number: 200405/123 X-Sequence-Number: 13439 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@cranel.com Technology. Integrity. Focus. V-Solve!