Oracle ASM - more exacting, load on IO?

[Yogeeraj_1]

Hi Nelson,

I would also recommend monitoring the system IO using the Oracle Enterprise manager Database control. There are loads of performance reports which you can analyse from there.

hope this helps too!
kind regards
yogeeraj

[Hein van den Heuvel]

I was just reading a topic in the 'storage' family which may apply here.

http://forums13.itrc.hp.com/service/forums/questionanswer.do?threadId=1278758

It shows how Linux OS choices can influence storage performance significantly (2x trhoughput) only 'trying to help'.

The same thing coudl easily be happening with ASM.
As it tries to help too much, it may make data access patterns less understood and it may rob the controller from opportunities to optimize. Given dedicated CPUS and cache, the controller has the potential to do a much better job than the OS or an ASM.

This is already suggested by Duncan when he writes: "sequential IO? Doing that over 48 disks on multiple ports might make that sequential IO harder for the XP to spot (confusing the cache algorithms)", but I though It important enough to re-itterate.

Typically I am not one for 'me too' replies, but I also want to repeat an other question by Duncan: "The total amount of IO generated regardless of filesystems, raw or ASM shouldn't be significantly different".
The work might be done slower, but supposedly the same work has to be done.
Unless...
- The workload changed as well.
- OS caches were helping and no longer are.
- odd transfer alignment actions


As I wrote earlier, IMHO it is critical to review the IO quantity and quality using the Oracle reports using Statspack or AWR.
Compare the data from busy hours on comparable days before or after. Use the REDO log bytes or COMMIT rate to normalize the data if needed be. Similar numbers of read/write? Worsened avserv time confirmed by the Oracle avg IO time report?

hth,
Hein van den Heuvel
HvdH Performance Consulting.

[Alzhy]

Awesome "QUALITY" answers gentlemen!

The stats are quite glaring -- we've more I/O alright. Our front end array stats on the XP12000 are also through the roof. 2 Changes in along with ASM implementation.

1) We went from RAID5 (7+1P) sets using 73G 15KRPM to RAID10 (2+2) 300G 10kRPM disks.
2) We're using 8 array groups (still spread accross 4 ACPs) instead of 16+

My backend stats (ACP load, LDEV perfs) look normal - ACP max peak is no more than 18%. But as I have said, front end ports are way way too busy -- greater than 80% average peak which according to HP support is indicative of a hardowrking ChIP MP (port processor).

There is CACHE between the port and the LDEVs (chunks of the RAID sets) and I have every reason to agree there may be a problem there -- cache coherency issues as you've pointed out. I also heard about a sequential I.O penalty of sorts under certain striped IO situations with the XP Line.

I am strongly urging my management to engage HP Storageworks support to look at the matter further as we still have about 4 more mega Databases to convert to ASM. I have XP Performance Advisor gathering stats but analysis is way beyond my skills sets.

It also does not help that our DBA seem to not really have in-depth monitoring - spcially OEM tools. They have PRECISE i3 but its mainly used as a Query optimising tool.

Thanks Migz.

N.

[Alzhy]

Duncan, Yog and Hein,

Any specific StatsPack query or OEM stats that I can pass on to my DBA?

BTW, below is a snippet of ASMvolume stats from VxVM which matches native SAR stats. The last tow are read and write service times. 1st 2 stats are read and write operations written and next 2 are read and write blocks (1KB blocks)

vol asmdata01 417 7 21960 80 128.6 47.1
vol asmdata02 299 1 16024 8 110.1 120.0
vol asmdata03 390 2 14760 24 96.1 100.0
vol asmdata04 413 4 18240 32 115.5 72.5
vol asmdata05 199 8 9880 160 122.5 90.0
vol asmdata06 344 1 17192 8 110.9 150.0
vol asmdata07 343 3 17504 88 109.4 130.0
vol asmdata08 393 0 20296 0 114.9 0.0
vol asmdata09 325 6 16736 256 101.0 116.7
vol asmdata10 308 2 13256 16 110.7 155.0
vol asmdata11 364 5 18848 56 110.9 124.0
vol asmdata12 418 1 19496 8 108.6 150.0
vol asmdata13 355 2 18672 16 126.6 105.0
vol asmdata14 277 15 13776 350 144.4 83.3
vol asmdata15 331 0 17456 0 131.7 0.0
vol asmdata16 274 0 13952 0 94.7 0.0

[Alzhy]

From the good book on XP12000 Best Practices it is written:

"On sequential reads the CHiP will do prefetchs from disk on order to keep the data needed in cache in advance of the host asking for it. For sequential writes the CHiP will keep full stripes in cache to avoid any penalty for parity creation. It will also manage the amount of cache being used for the writes to avoid starving other I/O of cache hits. The CHiPs can detect sequential I/O across multiple ports if the I/Os are split uniformly across those ports (for example, eight I/Os to one port and then eight to another)."

Since ASM stripes FAR and wide -- it is possible cache coherency is indeed an issue -- would you agree?

[Hein van den Heuvel]

>> From the good book on XP12000 Best Practices it is written:

:=)

Those write times are really rather poor. I'm used to seeing write times in the low-ms or even sub-millisecond thanks to controller write-back caching. Still, then write IO rate is so low that you

The average blocks/io is 50+, larger than a DB page size, so suggesting 'scattered' reads for table scans.

The AWR / statspack numbers I woas thinking of is the basic per-tablespace read & write counts and times and from there perhpas drilled down to specific objects in those tablespaces. That data is likely to be captures whether "Precise" is oused or not.

Cheers,
Hein.

[Alzhy]

Hein,
I was also thinking about that scattering theory too. My vx_vol_maxio is 16MB btw. Any past experience on how this is addressed sir?

All I got from my DBA is:


WARNING:Oracle process running out of OS kernel I/O resources

[Duncan Edmonstone]

So are you saying you have a *lot* of sequential IO?

Yes the XP can detect this when coming on multiple ports... you should in this case make sure that system mode 327 is set on the XP though (I think manufacturing set this mode by default these days anyway, but check it).

Also what DMP load balancing policy are you using? For lots of sequential IO on an XP you should use balanced mode with a hefty partitionsize (start with 32MB and even consider 64 or 128MB). If we were talking just a filesystem sat on top of that Vx volume, I'd be confident then that the XP would detect that sequential IO effectively... of course as we have ASM on top of that, I'm not so sure.

HTH

Duncan

[Yogeeraj_1]

hi,


Oracle Enterprise manager Database control is installed by default. All you need to do is start the process and access the web-based interface to monitor your database.


To verify if the Database control is running, you can issue the following command as the Oracle user:

e.g.
[oracle@server1 ~]$ emctl status dbconsole
Oracle Enterprise Manager 10g Database Control Release 10.2.0.3.0
Copyright (c) 1996, 2006 Oracle Corporation. All rights reserved.
http://server1.mydb.mu:1158/em/console/aboutApplication
EM Daemon is not running.
------------------------------------------------------------------
Logs are generated in directory /u01/app/oracle/product/10.2.0/mydb/server1.mydb.mu_pfs/sysman/log
[oracle@server1 ~]$

To start, use:
emctl start dbconsole


As per example above, you can access the database console from an url similar to:
http://server1.mydb.mu:1158/em


hope this helps!

kind regards
yogeeraj

[Yogeeraj_1]

hi again,

Please also note that using the Database Control you can also monitor the production database instance as well the ASM instance. In your case, the graphs from the ASM instance will be really interesting to analyse.


hope this helps too!
kind regards
yogeeraj