system performance

[Ferdinand_2]

I have an 1998 180Mhz 1GB machine running 15 oracle databases with a fixed memory allocation.

I need some help interpreting my sar data to determine bottlenecks:
I have attached a sar -A
Here's a quick view.
(mowdb014)/tmp # sar -q

HP-UX mowdb014 B.11.00 A 9000/820 12/10/02

00:00:00 runq-sz %runocc swpq-sz %swpocc
00:15:01 1.2 2 0.0 0
00:30:00 1.0 2 0.0 0
00:45:00 1.1 1 0.0 0
01:00:00 1.0 2 0.0 0
01:15:00 1.2 2 0.0 0
01:30:01 1.0 1 0.0 0
01:45:00 1.0 2 0.0 0
02:00:00 1.0 1 0.0 0
02:15:00 1.0 2 0.0 0
02:30:00 1.0 2 0.0 0
02:45:01 1.0 1 0.0 0
03:00:01 1.2 1 0.0 0
03:15:00 1.1 7 0.0 0
03:30:00 1.1 1 0.0 0
03:45:01 1.0 1 0.0 0
04:00:01 1.2 2 0.0 0
04:15:00 1.1 38 0.0 0
04:30:00 1.1 32 0.0 0
04:45:00 1.2 3 0.0 0
05:00:00 1.1 2 0.0 0
05:15:00 1.1 2 0.0 0
05:30:00 1.0 2 0.0 0
05:45:00 1.2 2 0.0 0
06:00:00 1.2 3 0.0 0
06:15:00 1.0 4 0.0 0
06:30:01 1.1 1 0.0 0
06:45:00 1.1 3 0.0 0
07:00:00 2.4 24 0.0 0
07:15:00 1.8 19 0.0 0
07:30:00 1.9 45 0.0 0
07:45:00 1.7 27 0.0 0
08:00:00 1.1 4 0.0 0
08:15:00 1.1 3 0.0 0
08:30:00 1.0 3 0.0 0
08:45:01 1.1 4 0.0 0
09:00:00 1.1 4 0.0 0
09:15:00 1.2 29 0.0 0
09:30:00 1.2 52 0.0 0
09:45:00 1.2 50 0.0 0
10:00:01 1.1 47 0.0 0
10:15:00 1.5 46 0.0 0
10:30:00 1.3 57 0.0 0
10:45:00 1.3 28 0.0 0
11:00:00 1.1 8 0.0 0
11:15:00 1.1 9 0.0 0

Average 1.3 13 0.0 0
(mowdb014)/tmp #


(db014)/ # sar -a

[Stefan Farrelly]

Your stats look fine. Disk and i/o is not very high, cpu usage is not very high. The runq is just over 1 which is also fine for a single cpu system.

However, sar doesnt really show memory usage, you need vmstat output for that. Do>
vmstat 1 10
to see free (free memory pool) and pi/po for any possible paging to see if you are running out of memory.

[Ravi_8]

Hi,

looking at your sar report shows that your oracle database server is healthy.

[Ferdinand_2]

Yep, I submitted the data from the wrong machine. What do I see here?

(mowdb015)/ # sar -q

HP-UX mowdb015 B.11.00 A 9000/810 12/10/02

00:00:00 runq-sz %runocc swpq-sz %swpocc
00:15:00 1.5 99 0.0 0
00:30:00 1.5 100 0.0 0
00:45:00 1.4 100 0.0 0
01:00:01 1.6 100 0.0 0
01:15:00 1.6 100 0.0 0
01:30:00 1.6 100 0.0 0
01:45:00 1.9 100 0.0 0
02:00:01 1.8 100 0.0 0
02:15:00 3.2 100 0.0 0
02:30:00 2.8 100 0.0 0
02:45:00 1.6 100 0.0 0
03:00:00 1.6 100 0.0 0
03:15:01 2.9 99 1.8 1
03:30:00 2.6 100 0.0 0
03:45:00 1.7 100 0.0 0
04:00:01 1.5 100 0.0 0
04:15:01 3.2 100 0.0 0
04:30:01 4.5 100 0.0 0
04:45:01 5.5 99 0.0 0
05:00:01 7.3 99 0.0 0
05:15:01 5.1 100 0.0 0
05:30:01 2.7 100 0.0 0
05:45:01 1.7 100 0.0 0
06:00:01 1.5 100 0.0 0
06:15:01 2.5 100 0.0 0
06:30:00 1.6 100 0.0 0
06:45:00 1.5 100 0.0 0
07:00:00 2.8 99 0.0 0
07:15:00 1.7 100 0.0 0
07:30:00 1.8 100 0.0 0
07:45:01 1.7 100 0.0 0
08:00:01 1.8 100 0.0 0
08:15:00 2.6 100 0.0 0
08:30:01 2.8 99 0.0 0
08:45:00 3.2 100 0.0 0
09:00:01 3.2 100 1.0 0
09:15:00 3.0 100 0.0 0
09:30:00 2.8 100 0.0 0
09:45:01 2.2 100 0.0 0
10:00:00 2.6 100 0.0 0
10:15:00 3.7 100 0.0 0
10:30:00 2.6 100 0.0 0
10:45:00 2.3 100 4.9 2
11:00:01 4.0 100 0.0 0
11:15:00 3.0 100 0.0 0
11:30:01 3.2 100 0.0 0

Average 2.6 100 3.5 0

[Stefan Farrelly]

The 2nd sar attachment from mowdb015 isnt the same as from mowdb014. The same sar -A output would be good as well as vmstat output.

The only thing obvious so far is your out of cpu - its running flat out.

[Ferdinand_2]

After reading the man pages I thought that
%runocc should be close to 100%

I thought it meant ready to get a cycle on the cpu.

I am trying to find something like swapinfo/vmstat/sar/iostat which tells me what is slowing down that system.

HP-UX mowdb015 B.11.00 A 9000/810 12/10/02

14:37:30 %usr %sys %wio %idle
14:37:31 1 1 47 51
14:37:32 0 1 35 64
14:37:33 13 1 28 58
14:37:34 0 1 70 29
14:37:35 0 1 14 85

Average 3 1 39 57
(mowdb015)/tmp # swapinfo -mta
Mb Mb Mb PCT START/ Mb
TYPE AVAIL USED FREE USED LIMIT RESERVE PRI NAME
dev 1152 265 887 23% 0 - 1 /dev/vg00/lvol2
dev 512 268 244 52% 0 - 1 /dev/vg01/lvol2
reserve - 1111 -1111
memory 836 438 398 52%
total 2500 2082 418 83% - 0 -
(mowdb015)/tmp #

[Stefan Farrelly]

From your last stas you can see;

1. swapinfo device swap is 0% used which means youve never run out of memory since last reboot which is good. No memory pressure.

2. cpu usage is also very low so now cpu problems. good.

3. wio% is averaging 39% - this is terrible. You're completely io bound. Your disk subsystem cannot keep up with the number of i/o requests its getting. You need to look into who/why so many i/o requests and/or what you can do to increase the speed of your disk subsystem. More diks ? faster disks ? more controllers ? stripe your filesyetems ? more cache memory if you use external disk arrays ?

[Steven E. Protter]

I find the swap figure impossible to believe. I run oracle and it needs swap.

In fact, I'd check to see if swap is properly configured, but that seems farfetched to me.

Make sure swap is enabled, and is twice physical memory, otherwise oracle will run slow as heck. Problem is when it happened to me my sar information was a little more out of whack than your numbers.

Steve.

[Stefan Farrelly]

Oops, I read your swapinfo output wrong. It shows USED device swap at 533Mb (on your 1Gb machine) which is TERRIBLE. You are completely out of ram. Your server must be swapping terribly (vmstat will show this in its pi and po fields).

The idea is to keep USED device swap to ZERO which means youve never run out of memory. This, along with your high wio%, are the reasons your server is running at a crawl.

What is the SGA size of all your oracle databases set to ?
ipcs -ma | grep oracle
will show you all oracle shared memory SGA sizes -total them up. They should be far less than total RAM (about 70-80% of RAM max).

Heres a command to show you which processes are using the most ram (largest first);

UNIX95= ps -e -o vsz=Kbytes -o ruser -o pid,args=Command-Line | sort -rnk1 | more

[Ferdinand_2]

Thanks for all your time and imput.

The information I get out sar and vmstat look still a bit ambiguous to me.

I have logged a call at HP support.

I have made a .doc from all the commands you send me. All your feedback is appriciated.

[Stefan Farrelly]

Your stats arent really ambiguous. They just show;

1. you have too many oracle instances configured.
2. Each oracle instance - when up, uses at least 6 deamon processes each using from 10-30Mb each (60-180Mb for each instance) in addition to their shared memory (SGA) size. Unfortunately your ipcs -ma output was chopped off in your word document attachment so its not possbile to see their SGA sizes.
3. As a result your completely out of memory (swapinfo shows this - DEVICE usage is over 500Mb which means your over 500Mb short of RAM) and thus performance is very poor.
4. wio% when non zero is 30+ which means you are completely i/o bound. Your disks simply cant keep up with the number of io requests they are receiving - again probably because you have too many oracle instances running.

I think the solution is to add another 500Mb - 1Gb of RAM or remove some oracle instances. And if you can improve your disk speed (faster disks, more disks) then this will help also.

[Dave Elliott]

Hi everything said is good advice, i had a similar problem a while ago and and was at the stage were i was having to close down less important databases to maintain some sort of performance.
I had to double my memory on my D-class server, i now have over 2gb ram .
also there is alot that you can do with your kernal parameters to help.
The details can be got from the oracle website.
Also i would look at how many spindles you have got oracle spread across, spindles win prizes.
i have never had a problem with i/o on a database server and have as many databases running on my d class as you.