How to find out bottleneck issues from sar outputs

[JanShu150]

Hi All,

When I run the following sar commands, how to tell if there is any cpu, disk io, memory, network, buffer bottleneck from the sar outputs? if there is a bottleneck, what's the solution?

vmstat -n 60 10

sar -b 2 10
sar -d 2 10
sar -Muqc 2 10
sar -v 4 10

Also, if a hp-ux oracle db server's %wcache or %rcache is constantly under 10 when the programmers running their sql batch jobs which were hanging. How to increase the %wcache or %rcache? Can you please help?

Thank you.

Kind Regards,

Jan

[jai123]

Pls share your output..

[JanShu150]

Hi Jai,

The sar bottleneck question is a general question. I have googled and found some documentations but am still looking for a up-to-date doc regarding troubleshooting performance bottleneck.

Here is the sar output and the %wcache is 15 and %rcache is almost 0. Sometimes it shows high rcache% and very low wcache%. I think they should be both > 65% , right?

$ sar -b 3 12

HP-UX B.11.23 U ia64    02/24/12

14:33:46 bread/s lread/s %rcache bwrit/s lwrit/s %wcache pread/s pwrit/s
14:33:49    7396    5835       0    2488    3866      36       0       0
14:33:52   10571    7970       0      30      49      39       0       0
14:33:55    7575    6924       0     273     298       8       0       0
14:33:58    6846    5733       0      51      69      26       0       0
14:34:01    8391    5867       0      41      50      17       0       0
14:34:04    8341   10364      20      14      58      75       0       0
14:34:07   10015    7290       0    3967    2507       0       0       0
14:34:10    8407    9107       8    2336    3822      39       0       0
14:34:13    9206    5429       0      18      38      52       0       0
14:34:16    6935    5272       0      21      47      55       0       0
14:34:19    7651    5143       0      19      34      45       0       0
14:34:22    7841    5224       0      14      32      57       0       0

Average     8264    6678 1784987     773     907      15       0       0

Thank you.

Kind Regards,

Jan

[Bill Hassell · Accepted solution]

write-cache and read-cache are metrics related to how much of the filesystem data has already been seen and is in the buffer cache. Write cache will *always* be low unless your programs are writing the same records over and over (not very common). Read cache will be low if your applications are constantly reading new data (typical during a backup). The simple answer is to increase the size of your buffer cache but since you did not state the version of HP-UX, I will assume 11.23 or earlier. For 11.23, your buffer cache could be as high as 4-6 GB, but for 11.11, no more than 2-3 GB and 11.00, 1 GB is about as big as you can go before performance levels off. You do have at least 8 GB of RAM, correct?

 But these are recommendations for a general server -- you indicate thatOracle is running so that changes everything. You DBAs should be using a large SGA to help with record buffering, perhaps 4 to 6 GB. Also, before trying to fix bad SQL code with HP-UX values, your DBAs should be running Oracle statspack on their biggest batch jobs. A missing index or unbalanced trees will cripple your server with massive I/Os. 

[JanShu150]

Thanks a lot for the helpful answer, Bill. These servers are running HP-UX 11.23 and 11.31, Oracle 10.2 and 11.2.

Kind Regards,

Jan