Effect of disk_asynch_io=true on Oracle Instances that are not on RAW Storage

[Alzhy]

Will this have an effect in the performance of Oracle? DBWR instance is at 1 and our DBA's think they can use async IO even if we are using filesystems.

[TwoProc]

This is well documented that this won't work. Pretty easy to find lots of data and notes on that subject saying that it won't. You may even risk data loss trying it, if you're lucky it will have no effect.

[Alzhy]

DBAs think it's ORacle's default and will just be "ignored" if the "asyncdsk" driver is not present.

Is this true?

I quote a response:

"The value below is the default and it means oracle will try to use asynchronous IO if possible. With filesystem oracle uses threaded asynchronous IO only when the operating system supports it. But on filesystems we can simulate threaded asynchronous IO in Oracle by using more DBWn writers"

[TwoProc]

Nelson, I don't know if it's true that it's safe to let the system merely try - b/c I know that it's not supported. So, either it will try (possibly wasting time in the failed effort repeatedly) - or even worse it will think it can use it, and start trashing files.

If you need more I/O however, there are things you can do. Definitely you can support more than one DBWriter - no matter how small your system is. I generally allocate one or so per filesystem - but try not to exceed number of procs. But, the real answer is to start up the statspack, get some baseline, then start changing the number of db writers you have and see if the delays from writes start coming down. Your ability to support a 'bunch' of db writers really depends on how much bandwidth you've got to the storage system (in terms of concurrency). Can your storage system support lots of concurrent writes ? Then it's time to start increasing and measuring.

Another thing I like to do to increase my ability to write thing concurrently is to pre-allocate my tablespaces across many files, so that as data grows - it grows across the files in the tablespace - giving me some approximation of concurrency - at times. So, if you need a new file added to your tablespace (and say it's a hot one), don't just add a 2G file - why not add 4 512MB files or even 8 256M B files instead (Check that your nfile parameter can handle it)? Even better, if this tablespace is really hit hard - why not put those files across several mount points and interleave them? This will give you a lot of disk mechs doing your bidding for you. You can watch as the data grows over time in the files, it will be pretty evenly spread.

Keep in mind the load your storage system can handle - on direct attached I/O with ultra-SCSI I'll often use a db_writer_processes value of 4 or 6 (depending on how many disks/controllers/cache I'm using), but on a system with large enterprise storage - I'll use a value of 16 or 24.

As long as my total I/O doesn't go red-line on me, and my statspack shows better response times on delivering I/O and no ugly adverse effects in the rest of the system - I'll keep pushing db_writers until I'm happy with it.

Also, you can use one DB_Writer with a bunch of I/O slaves. I've not seen when using synchronous I/O myself on how that's better, or worse for that matter. In my researching papers, etc on the topic, I've seen how I/O slaves is MUCH better when doing asynchronous I/O in raw partitions. Keep in mind that when using multiple I/O slaves - you'll only have one DB writer ... hmmm ... is that what you have now??? If so, this is all a non-discussion...

You could do either - increase db-writers or increase with I/O slaves - either one will get you there. But, measure and test your way (or at least track) through it. Statspack reporting will help you figure it out.

[Steven E. Protter]

I remember the December 2001 Patch set disabling my oracle and adabas installations because they could not get "asynch" i/o. They both went down for the reason, due one of the patches.

Had to set up a file called /etc/privgroup

That let let oracle run in its supposed asynch mode.

I don't know whether its getting asynch i/o or not, but the documenation seems to be trying to tell us that it is.

Its probably some form of psuedo asynch or they devide up the blocks and let multiple processes work on the same tablespace at the same time.

Your dba is probably a little confused. As we've discussed going raw i/o is not going to provide enough performance boost to justify your time.

SEP

[Jean-Luc Oudart]

Nelson,

as far as know you must be on raw device to support async IO (That's Oracle prospective).
I async IO exists on fs, I don't know Oracle's position (support, ...)

check this thread
http://forums1.itrc.hp.com/service/forums/questionanswer.do?threadId=753721

Also, you may want to check DBWR_IO_SLAVES parameter as an alternative.
check Metalink doc id 97291.1 "DB_WRITER_PROCESSES or DBWR_IO_SLAVES?"

Regards
Jean-Luc

[Eric Antunes]

Hi Nelson,

It is quite simple:

You can set disk_asynch_io to TRUE (I've it setted) but it won't work unless you REALLY HAVE RAW DEVICES and all the HP-UX stuffs right configured (see Metalink note 139272.1)!

If you don't have raw devices, Oracle will do synchronous I/O EVEN WITH disk_asynch_io = TRUE!

Best Regards,

Eric Antunes

[Eric Antunes]

Nelson,

There is one thing I've discovered last week. If your OS don't support asynchronous I/O it is better for performance to set disk_asynch_io = false (I will see this at checkpoints). With disk_asynch_io = true you will have much more CPU work!!

Best Regards,

Eric Antunes

[Alzhy]

Eric,

Thanks.

But did you verify this from Oracle that this indeed is the case? Our DBAs are all in concert in saying it will just be ignored since it is not active and we're running on cooked storage.

[Eric Antunes]

Yes it is!

I've found note 1016262.102 (Excessive CPU Usage by Checkpoint Process) when I was searching for I/O issues...

Here is part of the Note:

...
Explanation: ============ The "init.ora" file has a parameter that is causing additional I/O to increase CPU usage of checkpoint. Setting disk_asynch_io = false will reduce the overall CPU consumed by the io of the checkpoint process.
...

I've changed it and now I've less CPU consumption at checkpoints!!

Eric

[Eric Antunes]

Hi again,

Note:47328.1 says that we should use dbwr_io_slaves if we can't get asynchronous I/O...

[anugrahargon]

Got the following note from HP-Oracle Competency center (www.hporaclectc.com):

Appendix 2: Raw Devices + Asynchronous IO

We recommend to use raw devices instead of file system files for Oracle
datafiles and to use asynchronous IO, both resulting in much better
performance.

Use stripping with a large number of disks to spread IO over multiple disks (high number is good). Define the number of disks in your stripe set also dependent of your future growth of your storage, otherwise you will get probably hot spots in the future. For the stripping are 2 possibility exists. For a high number of disks we did not figured out big difference in performance.
Use the HP-UX Logical Volume Manager to stripe logical volumes across multiple disks. Recommended stripe size: Quite often is 64KB still used
or
use HP-UX Extend-based-striping with 1 MB or even larger extends.

To implement asynchronous IO on HP-UX systems (raw devices are required)
- add the asyncdsk Driver (Asynchronous Disk Pseudo Driver)
to the HP-UX Kernel (using SAM) and regenerate new kernel
- create the device file:
- /sbin/mknod /dev/async c 101 0x0
- chown oracle:dba /dev/async
- chmod 660 /dev/async
- depending on the HP-UX version you have to provide privilege to group dba
- HP-UX 11.0 (dependend on the patch level)
Patch PHKL_22380 and all newer patches which supersede it (like PHKL_22440) use a special "feature" which has to be set to really use asyncio. As root:
/usr/sbin/setprivgrp dba MLOCK

- HP-UX 11i (>=11.11)
To use asyncio: As root:
/usr/sbin/setprivgrp dba MLOCK

- set oracle init.ora parameters:
- use_async_io=true (Oracle 7.3)
- disk_async_io = true (>= Oracle 8i)
- db_writers=1 ( < Oracle 7.3)

To test the async IO
- check with fuser /dev/async
- or look with glance/gpm at the ora_dbwr.
- look for open files:
- /dev/async must be shown as opened file
- if /dev/async is not seen and you want to use asyncio:
To check the priviliges to use async IO on HP-UX 11 (with Patch PHKL_22380 and all newer patches which supersede it like PHKL_22440) and HP-UX 11i
- To check the privilege capabilities for a group, issue the command: /usr/bin/getprivgrp
- If the output of getprivgrp(1) does not indicate that the group has the MLOCK privilege, it can be set by issuing the following command as root:
/usr/bin/setprivgrp dba MLOCK

When not using asynchronous IO
- set db_writers to number of disk (for Oracle 7)
- set db_writer_processes = 1 â 10 (>= Oracle 8i)
- set dbwr_io_slaves = 0 â 999 (>= Oracle 8i)
(Please test with your application, if the behaviour using multiple db_writer_processes is better than use multiple dbwr_io_slaves)

Please check also appendix 6 for Oracle 9i (SCHED_NOAGE)

Could someone please explain it?
Thanks,
Dado

[Eric Antunes]

Hi Dado,

It's simple: you need Raw Devices to use Asyncronous I/O mode on Oracle, witch has a better performance than Syncronous I/O mode.

Best Regards,

Eric Antunes