Asynch I/O on Oracle
Asynch I/O for 10gR1
Even on some documentation it is stated that async 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 experiencing 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 against 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 dynamically 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 statement 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 addresses 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.