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.
Bjoern Metzdorf wrote:
> 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:
> 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).
> 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)
> 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.
> After having read some comparisons between n-way xeon and opteron systems:
> 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:
> 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)
> 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:
> 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
> ---------------------------(end of broadcast)---------------------------
> TIP 5: Have you checked our extensive FAQ?
Product Development Manager
Technology. Integrity. Focus. V-Solve!
In response to
pgsql-performance by date
|Next:||From: Fabio Panizzutti||Date: 2004-05-13 14:06:01|
|Subject: R: Query plan on identical tables differs . Why ?|
|Previous:||From: Shridhar Daithankar||Date: 2004-05-13 13:05:19|
|Subject: Re: Query plan on identical tables differs . Why ?|
pgsql-admin by date
|Next:||From: Jodi Kanter||Date: 2004-05-13 13:36:03|
|Previous:||From: Laurens Wagemakers||Date: 2004-05-13 07:23:50|
|Subject: GNUmakefile size 0|