DB stability with async I/O

[Stephanie Nicholls]

We are running 3 Oracle 8.1.6.3 Databases on a L Class with HP11.00 using async I/O with raw volumes for the tablespaces.

Recently we have been experiencing a lot of db instability including some cases where the instance has killed itself.

The dba's are considering turning off async I/O but have asked me to find out if there were any known issues with it to see if this could be a possible reason for the instability.

On investigation the only thing I can see is that max_async_ports is set to low, my preference is to raise this before scrapping async i/o totally as I personally dont believe this is the problem.

Any thoughts ??

[harry d brown jr]

The only problem I have with async IO is that you don't wait for the disk to acknowledge that a write was committed. At least that's what I believe to happen.

Have a look at this thread, ESPECIALLY the patches regarding async IO:

http://forums.itrc.hp.com/cm/QuestionAnswer/1,,0x400e6049dbb6d611abdb0090277a778c,00.html


Also, do a google search on +async +oracle +IO +hpux


live free or die
harry

[T G Manikandan]

Nichollas,

I looks like the problem is not with the async I/O.
I see that the problem is with somewhere some other limit is being hit.
Could you find any information from the alert_log files or from the trace files.
They should give some sort of a hint for the abort of the instance.

With reference to your posting that max_async_ports are low.
If max_async_ports value is reached, subsequent processes will use synchronous i/o.
I do not see any problem with that.

Probably you can use glance to monitor the I/O on those raw devices with aio.

Also you can monitor the aio_max_ops parameter using glance.
This will show whether you are crossing the limits of maximum number of asynchronous i/o operations that can be queued at any time.The default value for this is 2048.

Please revert.

[Martha Mueller]

We use async I/O for Sybase, not for Oracle. However, we have had a lot of investigation into /dev/async - the device file that is created using the mknod command. Depending on the minor number used in the mknod command, you can have very different behavior. The minor number controls the handling of time-outs. If you use a minor number 4, like this: /dev/async 101 0x000004, then disk-timeouts will be reported to Sybase. Here are the options for the minor numbers:

0x000000 default
0x000001 enable immediate reporting
0x000002 flush the CPU cache after reads
0x000004 allow disks to timeout
0x000005 is a combination of 1 and 4
0x000007 is a combination of 1, 2 and 4

The minor number of 1 - enable immediate reporting - is of particular concern to me. The immediate reporting will allow a write operation to be reported back to the application as being successful, even though it has not actually been written - still in buffer. If you have a crash before the data is written, the application will think the write is committed, however, the data will not actually be on disk. This can cause data base corruption. The big advantage to using the immediate reporting is the speed gain. Last time I checked, Sybase would only support using a minor number of 4. I have had Support Tech people just tell me to change the minor number without understanding what it can do to a database. I have no idea what a minor number of 0 - default - would be.

Remember to stop your application before making any changes to /dev/async

[Martha Mueller]

This document seems to be current and has some good discussion of the /dev/async minor number.

http://www1.itrc.hp.com/service/cki/docDisplay.do?docLocale=en_US&docId=200000065451705