Re: [PERFORM] Quad processor options

I'm confused why you say the system is 70% busy: the vmstat output
shows 70% *idle*.

The vmstat you sent shows good things and ambiguous things:
- si and so are zero, so your not paging/swapping. Thats always step
1. you're fine.
- bi and bo (physical IO) shows pretty high numbers for how many disks
you have.
(assuming random IO) so please send an "iostat 10" sampling during
peak.
- note that cpu is only 30% busy. that should mean that adding cpus
will *not* help.
- the "cache" column shows that linux is using 2.3G for cache. (way too
much)
you generally want to give memory to postgres to keep it "close" to
the user,
not leave it unused to be claimed by linux cache (need to leave
*some* for linux tho)

My recommendations:
- I'll bet you have a low value for shared buffers, like 10000. On
your 3G system
you should ramp up the value to at least 1G (125000 8k buffers)
unless something
else runs on the system. It's best to not do things too
drastically, so if Im right and
you sit at 10000 now, try going to 30000 then 60000 then 125000 or
above.

- if the above is off base, then I wonder why we see high runque
numbers in spite
of over 60% idle cpu. Maybe some serialization happening somewhere.
Also depending
on how you've laid out your 4 disk drives, you may see all IOs going
to one drive. the 7M/sec
is on the high side, if that's the case. iostat numbers will reveal
if it's skewed, and if it's random,
tho linux iostat doesn't seem to report response times (sigh)
Response times are the golden
metric when diagnosing IO thruput in OLTP / stripe situation.

On May 11, 2004, at 1:41 PM, Bjoern Metzdorf wrote:

> scott.marlowe wrote:
>
>> Well, from what I've read elsewhere on the internet, it would seem
>> the Opterons scale better to 4 CPUs than the basic Xeons do. Of
>> course, the exception to this is SGI's altix, which uses their own
>> chipset and runs the itanium with very good memory bandwidth.
>
> This is basically what I read too. But I cannot spent money on a quad
> opteron just for testing purposes :)
>
>> But, do you really need more CPU horsepower?
>> Are you I/O or CPU or memory or memory bandwidth bound? If you're
>> sitting at 99% idle, and iostat says your drives are only running at
>> some small percentage of what you know they could, you might be
>> memory or memory bandwidth limited. Adding two more CPUs will not
>> help with that situation.
>
> Right now we have a dual xeon 2.4, 3 GB Ram, Mylex extremeraid
> controller, running 2 Compaq BD018122C0, 1 Seagate ST318203LC and 1
> Quantum ATLAS_V_18_SCA.
>
> iostat show between 20 and 60 % user avg-cpu. And this is not even
> peak time.
>
> I attached a "vmstat 10 120" output for perhaps 60-70% peak load.
>
>> If your I/O is saturated, then the answer may well be a better RAID
>> array, with many more drives plugged into it. Do you have any spare
>> drives you can toss on the machine to see if that helps? Sometimes
>> going from 4 drives in a RAID 1+0 to 6 or 8 or more can give a big
>> boost in performance.
>
> Next drives I'll buy will certainly be 15k scsi drives.
>
>> In short, don't expect 4 CPUs to solve the problem if the problem
>> isn't really the CPUs being maxed out.
>> Also, what type of load are you running? Mostly read, mostly
>> written, few connections handling lots of data, lots of connections
>> each handling a little data, lots of transactions, etc...
>
> In peak times we can get up to 700-800 connections at the same time.
> There are quite some updates involved, without having exact numbers
> I'll think that we have about 70% selects and 30% updates/inserts.
>
>> If you are doing lots of writing, make SURE you have a controller
>> that supports battery backed cache and is configured to write-back,
>> not write-through.
>
> Could you recommend a certain controller type? The only battery backed
> one that I found on the net is the newest model from icp-vortex.com.
>
> Regards,
> Bjoern
> ~# vmstat 10 120
> procs memory swap io system
> cpu
> r b w swpd free buff cache si so bi bo in cs
> us sy id
> 1 1 0 24180 10584 32468 2332208 0 1 0 2 1 2
> 2 0 0
> 0 2 0 24564 10480 27812 2313528 8 0 7506 574 1199 8674
> 30 7 63
> 2 1 0 24692 10060 23636 2259176 0 18 8099 298 2074 6328
> 25 7 68
> 2 0 0 24584 18576 21056 2299804 3 6 13208 305 1598 8700
> 23 6 71
> 1 21 1 24504 16588 20912 2309468 4 0 1442 1107 754 6874
> 42 13 45
> 6 1 0 24632 13148 19992 2319400 0 0 2627 499 1184 9633
> 37 6 58
> 5 1 0 24488 10912 19292 2330080 5 0 3404 150 1466 10206
> 32 6 61
> 4 1 0 24488 12180 18824 2342280 3 0 2934 40 1052 3866
> 19 3 78
> 0 0 0 24420 14776 19412 2347232 6 0 403 216 1123 4702
> 22 3 74
> 0 0 0 24548 14408 17380 2321780 4 0 522 715 965 6336
> 25 5 71
> 4 0 0 24676 12504 17756 2322988 0 0 564 830 883 7066
> 31 6 63
> 0 3 0 24676 14060 18232 2325224 0 0 483 388 1097 3401
> 21 3 76
> 0 2 1 24676 13044 18700 2322948 0 0 701 195 1078 5187
> 23 3 74
> 2 0 0 24676 21576 18752 2328168 0 0 467 177 1552 3574
> 18 3 78
>
> ---------------------------(end of
> broadcast)---------------------------
> TIP 2: you can get off all lists at once with the unregister command
> (send "unregister YourEmailAddressHere" to
> majordomo(at)postgresql(dot)org)