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@postgresql.org)