%sys is high

[Stijn V]

I do have an HP Integrity VM (HP-UX 11.31). We do have an Oracle 10g database running on it and DB admins are complaining that the IO is slow...

The %sys is quite high in sar 5 10

tbe1css3# sar 5 10

HP-UX tbe1css3 B.11.31 U ia64 07/24/07

17:21:18 %usr %sys %wio %idle
17:21:23 7 54 5 34
17:21:28 7 28 2 64
17:21:33 11 27 2 61
17:21:38 11 39 2 48
17:21:43 17 15 1 67
17:21:48 3 7 16 73
17:21:53 12 24 3 61
17:21:58 2 12 1 84
17:22:03 3 7 2 88
17:22:08 7 13 3 78

Average 8 22 4 66

[A. Clay Stephenson · Accepted solution]

You have really gathered metrics over too short a period to be very meaningful and in any event the i/o wait's dominate in almost all the samples. I have found over many years that when database performance is poor, blame the OS tuning when in the vast majority of cases the problem lies in inefficient code. I suspect that you are doing many logical i/o's vs. physical i/o's (check that with sar -b) and while that is normally a good thing, it may indicate inefficient SQL code as the same data are being reread many times which in turn means that the code is often rereading data that it already "knows".

[Stijn V]

I totally agree with you ;)

But the problem is -like always- that I have to prove that the system is behaving as expected. To prove that the system is ok, I have to solve the issue even if it is the application (if you know what I mean ;)

[Duncan Edmonstone]

What is your backing store for the disks in your VM? Have you read the best practices whitepaper?

http://docs.hp.com/en/9983/BestPractices2.2.pdf

IO in a VM is always less efficient than in a normal host, but if you follow best practices for performance it should be less so.

HTH

Duncan

[Stijn V]

Sar -b doesn't work

tbe1css3# sar -b
sar: Can't open /var/adm/sa/sa24


The VM itself is created on a LUN, the Oracle database files and archives are stored on another LUN? You can see 2 LUNs with hpvmstatus on Host level.

Device Adaptor Bus Dev Ftn Tgt Lun Storage Device
======= ========== === === === === === ========= =========================
disk scsi 0 0 0 0 0 disk /hpap/rdsk/hpap0
disk scsi 0 0 0 2 0 disk /hpap/rdsk/hpap2

[Stijn V]

I can execute sar -d, please note that there is currently almost no usage of the system (out of business hours):
tbe1css3# sar -d 2 5

HP-UX tbe1css3 B.11.31 U ia64 07/24/07

19:12:35 device %busy avque r+w/s blks/s avwait avserv
19:12:37 c0t2d0 1.99 0.50 10 167 0.00 2.15
disk3 40.30 0.56 406 3084 0.08 2.79
disk5 1.99 0.50 10 167 0.00 2.15
19:12:39 c0t2d0 1.99 0.50 6 72 0.00 2.45
disk3 10.45 0.51 140 1126 0.00 2.13
disk5 1.99 0.50 6 72 0.00 2.45
19:12:55 c0t2d0 2.65 0.50 10 233 0.00 2.69
disk3 4.11 0.84 43 647 0.78 1.93
disk5 2.65 0.50 10 233 0.00 2.69
c0t2d0 1.11 0.50 14 400 0.00 1.36
disk3 37.78 0.51 262 3304 0.01 2.75
disk5 1.11 0.50 14 400 0.00 1.36
19:12:57 c0t2d0 1.50 0.50 10 154 0.00 1.03
disk3 16.50 0.57 176 1440 0.30 2.73
disk5 1.50 0.50 10 154 0.00 1.03

Average c0t2d0 2.36 0.50 10 212 0.00 2.39
Average disk3 10.50 0.63 106 1094 0.29 2.46
Average disk5 2.36 0.50 10 212 0.00 2.39


tbe1css3# sar 5 100
HP-UX tbe1css3 B.11.31 U ia64 07/24/07
Average 4 14 3 79

[A. Clay Stephenson]

You have to supply sample_period and count arguments to sar -b just as you did to s [-s] 5 10.

If you are going to use sar, you really need to run it in data collector mode so that it samples data every 20 minutes and stores it. That way you can see good and bad periods on the box. Man sadc for details.

You really need to use Glance to analyze your performance problems as it allows much more precision than the decades old sar. If you do not have Glance installed, a 60-day Trial version can be found on any Applications CD set.

[Stijn V]

Ok thanks.

In the mean time I know thet the %sys mode depends on the application code an can be normal (in our case oracle).

tbe1css3# sar -d 10 10

HP-UX tbe1css3 B.11.31 U ia64 07/24/07

23:10:48 device %busy avque r+w/s blks/s avwait avserv

Average c0t2d0 4.53 0.50 8 286 0.00 6.01
Average disk3 2.56 5.02 22 294 0.62 2.06
Average disk5 4.53 0.50 8 286 0.00 6.01
tbe1css3#

I guess that 5ms is not that abnormal, although the oracle 10g enterprise monitoring tool sais (User IO is high)?? The DBA sais that the system IO is slow ... but -up to me- it is ok??

[Ramones]

I Clay

What do you mean with
"you are doing many logical i/o's vs. physical i/o's (check that with sar -b)"
The output of sar -b is about reads and writes from/to cache and reads and writes from/to physical disks.
I am interessed in this problem and didn't understood your explanation, I am hoping you could clarify me.


Thanks,
STP

[Stijn V]

I think that logical IO is reading/writing from cache. And physical IO is reading/writing from disk.

And up to me, Claw is correct in his findings about my issue. There are indeed a lot of logical IOs and almost no physical IOs. Still investigating why (probably bad SQL code)?

[Stijn V]

Sorry I mean A. Clay Stephenson instead of Claw...

[Ramones]

Hi Stijn

If that definition of logical/physical IOs is as you say I have the idea that what is happening there is perfectly normal.
Since access to memory is must faster then access to disk , buffer cache minimizes acesses to disk by keeping in memory the most recently used blocks so...
in reading: if there's a copy of the block in cache it is used, otherwise it will read from disk
in writting: it is kept a copy of the block on cache. Not always that block is immediately written on disk.
I think about physical/logical IOs has a disk on a machine may be composed by many disks in an external storage ...but that has nothing to do with this issue...so...I am still confused with Clays statement....


Regards,
STP

[A. Clay Stephenson]

Your understanding of logical and physical i/o is correct and as I said, normally a high ratio of logical to physical i/o IS a good thing as buffer cache is much faster than disk BUT suppose that you really didn't need that many reads (logical or physical) in the first place. For example, if you are creating some sort of tuple (e.g a join) that has a parent/child relationship you could do this by reading the parent entry each time you read the child entry or you could read the parent only once, store those data in variables and then read each child entry. You might reduce the parent accesses by a factor 50 or so. Another example is a heavily accessed table small enough to fit within cache but is not indexed in a manner which avoids a sequential search. This will trigger tons of logical i/o's when the presence of a single additional index would
allow an efficient search.

Of course, I'm shooting in the dark but I have seen exactly these symptoms and when combined with the keyword "database" I tend to look at the application first.

[Ramones]

Hi Clay

I got it, I was confused with the term logical IO I guess ...I would call it buffer IO but I see the point now.
So, do you know about some kind of normal relation between physical and logical IOs? I guess one couldn't never know for sure hein...
Thanks, I've appreciated it.


Regards,
STP

[Stijn V]

FYI,

We migrated the Oracle DB to another setup and everything is again ok (the %sys mode is normal as well). I guess that there is somewhere a bug and therefore the logical reads (memory) were just not performing as they should ...

Bug is somwhere in ;)

Whe changed the bad performing setup (was just migrated from another setup) to the following:

- changed From Unix Itanium Integrity Virtual Machine 11.31 (2 X dual core and 7,5GB) towards PA-RISC 11.11 (rp3440 2 CPU and 8GB)
- Oracle 10g binaries from IA to PA
- The HP-UX Virtual Machine Host layer is gone

I will update this thread once we found the root cause (only in case we still want to put the DB again on the Integrity Virtual Machine).

Tanks for your help.

[Stijn V]

FYI,

We migrated the Oracle DB to another setup and everything is again ok (the %sys mode is normal as well). I guess that there is somewhere a bug and therefore the logical reads (memory) were just not performing as they should ...

Bug is somwhere in ;)

Whe changed the bad performing setup (was just migrated from another setup) to the following:

- changed From Unix Itanium Integrity Virtual Machine 11.31 (2 X dual core and 7,5GB) towards PA-RISC 11.11 (rp3440 2 CPU and 8GB)
- Oracle 10g binaries from IA to PA
- The HP-UX Virtual Machine Host layer is gone

I will update this thread once we found the root cause (only in case we still want to put the DB again on the Integrity Virtual Machine).

Thanks for your help.

[Ramones]

Hi

I haven't had good experiences with Integrity Virtual Machine myself...please update if you discover something.
Do you still have the sar -b outputs from before and after the change? I would like to see that...


Regards,
STP