Asynch I/O on Oracle
 


 

Asynch I/O for 10gR1

 

Even on some documentation it is stated that aync I/O is enable by default: you should need only to relink the binaries with the command:
linux: # su - oracle
oracle@linux > cd $ORACLE_HOME/rdbms/lib
oracle@linux > make PL_ORALIBS=-laio -f ins_rdbms.mk async_on
oracle@linux > make PL_ORALIBS=-laio -f ins_rdbms.mk ioracle

and set

filesystemio_options=setall in you spfile
which enable direct I/O plus async I/O.

Note: Even for 10g you have compatibility problem with libaio so follow the above instruction to change the libraries if you are experincing any issue.

If you wish to check if your system is using AIO then check into /proc/slabinfo:

oracle10g@breonldblc02:/u01/app/oracle> grep kio /proc/slabinfo
kioctx                 0      0    256   15    1 : tunables  120   60    8 : slabdata      0      0      0
kiocb                  0      0    256   15    1 : tunables  120   60    8 : slabdata      0      0      0

The zeros indicates that this is not a system exploiting AIO.

While this is:

oracle10g@breonldblc01:/u01/app/oracle> grep kio /proc/slabinfo
kioctx                70     90    256   15    1 : tunables  120   60    8 : slabdata      6      6      0
kiocb                240    240    256   15    1 : tunables  120   60    8 : slabdata     16     16     60

 

Asynch I/O for 10gR2

Oracle 10gR2 comes with the binaries already linked aginst libaio so the procedure showed above (async_on) is not needed anymore. Actually to relink against another libaio is not possible since some libraries are missing. Oracle provides, as for any other operating system, all it is needed for asynchronous I/O.

However the oracle executable are dinamically linked so the libaio packages are *needed*.

Here you can see how they are still used by the shadow processes:

rangelife:~ # ps -fe|grep dbw
oracle   15299     1  0 Jan05 ?        00:00:00 asm_dbw0_+ASM
oracle   23429     1  0 Jan05 ?        00:00:06 ora_dbw0_TESTASM
root     17027 23239  0 10:43 pts/4    00:00:00 grep dbw
rangelife:~ # lsof -p 23429|grep libaio
oracle  23429 oracle  mem    REG  104,2      6560  881714 /usr/lib64/libaio.so.1

Still filesystemio_options should be set to asynch or setall in order to use this feature on filesystems.

 

ASM and Asynch I/O
 

Oracle claims that ASM is asynch ready and all depends on the operating system. That's true but on linux and 10gR1 still you need to relink the binaries as seen above (not true for 10gR2 were the oracle staitment is correct).

To be precise ASM can be used against raw devices (chracter devices) or the normal block device (like a /dev/sda1).
A raw device, by default, is used with direct I/O (non buffered) and aynch I/O (oracle should have the parameter disk_asynch_io=TRUE).
But what happens if you use block devices?
The latter option can be exploit by 10gR1 through ASMlib or natively with 10gR2.

Well, the the files are opened with O_DIRECT (even if filesystemio_options is not set for using it) and the AIO is used as showed by /proc/slabinfo.
But still, in this configuration, things are different for filesystemio_options=asynch (or setall) and filesystemio_options=none.
Both configuration use AIO but the syscalls used by oracle in the two cases are different.

I'm going to advocate the use of filesystemio_options=setall even if I don't have benchmark at the moment supporting the claim.

 

SLES9 ( up to SP1) and oracle 9i (9.2.0.4)

Let's say that SLES9 at this stage seems to break async I/O compatibility with oracle.
A common error you could meet is:

ORA-27083: skgfrliopo: waiting for async I/Os failed

My guess is that the libc syscall(2) mechanism introduced by libaio 0.3.97 and above is not supported by oracle.
So my solution is quite simple. Download the sources of libaio 0.3.96-3 here, compile them with 'make' and then copy
libaio.a
libaio.so.1
in $ORACLE_HOME/rdbms/lib.
 

linux: # cp libaio.a $ORACLE_HOME/rdbms/lib
linux: # cp libaio.so.1 $ORACLE_HOME/rdbms/lib
linux: # ln -s $ORACLE_HOME/rdbms/lib/libaio.so.1 $ORACLE_HOME/rdbms/lib/libaio.so.0

in this way you still have the libaio-0.99 shipped with SLES9 on your system but oracle uses the 0.96 you provided.

If you don't want to insert anything into your oracle installation tree you can create a directory wherever you wish and modify you LD_LIBRARY_PATH.
example:
 

linux: # mkdir -p /usr/oracle/lib
linux: # cp libaio.a  /usr/oracle/lib
linux: # cp libaio.so.1  /usr/oracle/lib
linux: # ln -s  /usr/oracle/lib/libaio.so.1  /usr/oracle/lib/libaio.so.0

Then relink the binaries with the command:
 

linux: # su - oracle
oracle@linux > cd $ORACLE_HOME/rdbms/lib
oracle@linux > make PL_ORALIBS=-laio -f ins_rdbms.mk async_on
oracle@linux > make PL_ORALIBS=-laio -f ins_rdbms.mk ioracle

and set

filesystemio_options=setall in you spfile
which enable direct I/O plus async I/O.

A good reference for the problem is Simon Grabinar's webpage:

http://www.grabinar.com/simon/aio.html

UPDATE


A new libaio has been released by SuSE (libaio-0.3.102-1.2.i586.rpm) and can be found in SLES9 SP2 (or downloaded by SUSE website at http://portal.suse.com/psdb/a8f77f12fc92c1b0908e6fc810c34af2.html with a valid account).

The new libaio adresses the problem with asynch I/O, SLES9 and oracle 9i.
However from 9.2.0.7 some asynch(and direct I/O)  problems  as been reported.

The above steps can be a used even for testing purpose if somebody wish to check how oracle behave with different libaio.
 

Contact information:
fabrizio.magni _at_ gmail.com