Off Topic - Re: Quad processor options - summary
От | Greg Spiegelberg |
---|---|
Тема | Off Topic - Re: Quad processor options - summary |
Дата | |
Msg-id | 40A374E8.5040809@cranel.com обсуждение исходный текст |
Ответ на | Quad processor options - summary (Bjoern Metzdorf <bm@turtle-entertainment.de>) |
Список | pgsql-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@cranel.com Technology. Integrity. Focus. V-Solve!
В списке pgsql-performance по дате отправления:
Предыдущее
От: Shridhar DaithankarДата:
Сообщение: Re: Query plan on identical tables differs . Why ?