Introduction to ORACLE Database asynchronous IO

Introduction to ORACLE Database asynchronous IO
Asynchronous I/O (AIO) is an enhanced function provided by the Linux kernel. It is a standard feature of the Linux 2.6 kernel. Of course we can find it in the patch of the 2.4 kernel. The basic idea behind AIO is to allow a process to initiate many I/O operations without blocking or waiting for any operation to complete. The process can retrieve the results of an I/O operation later or when it receives a notification that the I/O operation is complete. Linux I/O models (synchronous I/O models) and asynchronous models ). In synchronous I/O, the thread starts an I/O operation and immediately enters the waiting state. It does not wake up until the I/O operation is complete and continues. In asynchronous IO mode, the thread sends an IO request to the kernel and then continues to process other things. After the kernel completes the IO request, the thread IO operation will be notified to be completed. If the IO request requires a large amount of time for execution, the asynchronous file IO mode can significantly improve the efficiency, because during the time when the thread waits, the CPU will schedule other threads for execution. If there are no other threads to execute, this time will be wasted (the operating system's zero-page thread may be scheduled ). If I/O requests are operated quickly, asynchronous I/O is still inefficient. It is better to use synchronous I/O. Advantages of asynchronous IO asynchronous I/O: asynchronous I/O is compared with synchronous I/O. If synchronous I/O is used, when an I/O operation is executed, the application must wait until the I/O execution is complete. On the contrary, asynchronous I/O operations are run in the background, and I/O operations and applications can run at the same time, improving the system performance. Using asynchronous I/O will increase the I/O traffic, this advantage is even more obvious if applications operate on bare devices. Therefore, applications such as databases and file servers often use asynchronous I/O to execute multiple I/O operations at the same time. from the official documents, ORACLE also recommends enabling asynchronous IO for ORACLE databases. With synchronous I/O, when an I/O request is submitted to the operating system, the writing process blocks until the write is confirmed as complete. it can then continue processing. with asynchronous I/O, processing continues while the I/O request is submitted and processed. use asynchronous I/O when possible to avoid bottlenecks. some platforms support asynchronous I/O by default, others need spe Cial configuration, and some only support asynchronous I/O for certain underlying file system types. Q: 2. What are the benefits of Asynchronous I/O? A: The implementation of Asynchronous I/O on Red Hat Advanced Server allows Oracle processes to issue multiple I/O requests to disk with a single system call, rather than a large number of single I/O requests. this improves performance in two ways: First, because a process can queue multiple requests for the kernel to handle, so the kernel can optimize disk activity by recording requests or combin Ing individual requests that are adjacent on disk into fewer and larger requests. secondary, because the system does not put the process in sleep state while the hardware processes the request. so, the process is able to perform other tasks until the I/O complete. this involves making use of I/O capabilities such as: Asynchronous I/O does not reduce traffic but allows processes Do other things while waiting for IO to complete. direct I/O (bypassing the Operating System's File Caches): Direct IO does not reduce traffic but may use a shorter code path/fewer CPU cycles to perform the IO. enabling an asynchronous io oracle Database starts with ORACLE 9i Release 2 and supports the asynchronous IO feature. Earlier versions do not support asynchronous IO features. In addition, the asynchronous feature is disabled by default in ORACLE 9i R2 and ORACLE 10g R1, And the asynchronous IO feature is not enabled until ORACLE 10g R2 is enabled by default. Q: 4. Can I use Asynchronous I/O with Oracle 8i or Oracle 9i release 1? A: No. Asynchronous I/O feature is only available with Oracle RDBMS 9i release 2 (Oracle9iR2). Q: 5. Is Asynchronous I/O active with Oracle RDBMS by default? A: No. By default, Oracle9iR2 and Oracle10gR1 are shipped with asynchronous I/O support disabled. In 10gR2 asyncIO is enabled by default. How can I enable the asynchronous IO feature of ORACLE databases? Follow these steps: 1. First, check whether the system platform (operating system) of the ORACLE Database supports asynchronous IO. Currently, the popular Linux/Unix platform supports asynchronous IO, however, some old versions are not necessarily the same. You can search for relevant documents for details. 2: Check whether libaio and libaio-devel packages are installed. (It seems that the libaio-devel package is not required. If the test environment does not have libaio-devel, it seems OK. Of course, it is best to install it together) [root @ DB-Server ~] # Rpm-qa | grep aio libaio-0.3.106-5libaio-0.3.106-5 [root @ DB-Server] # rpm-ivh libaio-devel-0.3.106-5.i386.rpmwarning: libaio-devel-0.3.106-5.i386.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159Preparing... ######################################## ### [100%] 1: libaio-devel ##################################### ###### [100%] [root @ DB-Server] # rpm-ivh libaio-devel-0.3.106-5.x86_64. Rpmwarning: libaio-devel-0.3.106-5.x86_64.rpm: Header V3 DSA signature: NOKEY, key ID 1e5e0159Preparing... ######################################## ### [100%] 1: libaio-devel ##################################### ###### [100%] [root @ DB-Server] # rpm-qa | grep libaiolibaio-0.3.106-5libaio-devel-0.3.106-5libaio-devel-0.3.106-5libaio-0.3.106-5 3: check whether the system supports asynchronous I/O. According to [Note 370579.1], you can view the slabinfo statistics. Check whether AIO is running in the operating system. slab is a Linux memory distributor and the memory structure related to AIO has been allocated, if the value of kiocb is not 0 in the second and third columns, [root @ DB-Server ~] is used. # Cat/proc/slabinfo | grep kio kioctx 62 110 384 10 1: tunables 54 27 8: slabdata 11 11 0 kiocb 0 0 256 15 1: tunables 120 60 8: the second and third columns of the slabdata 0 0 0kiocb values are not 0, indicating that the system has enabled asynchronous IO. As shown above, asynchronous I/O is not in use. The kioctx and kiocb are Async I/O data structures that are defined in aio. h. if it shows a non zero value that means async io is enabled. source code loaded/usr/src/linux-<version>/include/linux/aio. h 4: the Linux kernel Parameter has been modified and optimized since 2.6 kernel, and the IO size restriction has been removed. Oracle recommends setting the aio-max-nr value to 1048576 or higher. [Root @ DB-Server ~] # Cat/proc/sys/fs/aio-max-nr 65536 command echo 1048576>/proc/sys/fs/aio-max-nr modify the parameter, which is only valid for the current environment, if the default value is used after the system is restarted, you are advised to modify the parameter file/etc/sysctl. conf. Edit/etc/sysctl. conf to add or modify the parameter fs. aio-max-nr = 1048576. Save the settings. Run sysctl-p to make it take effect. [Root @ DB-Serveruat ~] # Cat/proc/sys/fs/aio-max-nr 1048576 5: Check whether ORACLE software supports enabling AIO. The output value is shown below, indicating that ORACLE software supports enabling AIO. In fact, ORACLE has supported enabling asynchronous IO (AIO) Since ORACLE 9i R2. However, in versions earlier than 10GR1, You need to manually enable AIO, which is relatively troublesome. [Oracle @ DB-Server ~] $/Usr/bin/ldd $ ORACLE_HOME/bin/oracle | grep libaio. so.1 =>/usr/lib64/libaio. so.1 (0x00007f5a247f4000) [oracle @ DB-Server ~] $/Usr/bin/nm $ ORACLE_HOME/bin/oracle | grep io_getevent w io_getevents @ LIBAIO_0.4 6: Enable asynchronous I/O at the database level to set the parameter disk_asynch_io to true, in fact, the disk_asynch_io parameter in ORACLE 10g R2 is true by default. SQL> alter system set filesystemio_options = setall scope = spfile; System altered. SQL> alter system set disk_asynch_io = true scope = spfile; System altered. the filesystemio_options parameter has four values: asynch, directio, setall, none. it is generally recommended to set it to setall. You can use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on file system files. this parameter is platform-specific and has a default value that is best for a particle platform. it can be dynamically changed to update the default setting. FILESYTEMIO_OPTIONS can be set to one of the following values: · ASYNCH: enable asynchronous I/O on fil E system files, which has no timing requirement for transmission. Enable asynchronous I/O on file system files without timing requirements for data transmission. · DIRECTIO: enable direct I/O on file system files, which bypasses the buffer cache. enable direct I/O on the file system file to bypass the buffer cache. · SETALL: enable both asynchronous and direct I/O on file system files. enable asynchronous and direct I/O on file system files. · NONE: disable both asynchronous and direct I/O on file system files. disable asynchronous and direct I/O on file system files. After the configuration is complete, restart the database to verify that the asynchronous IO feature is enabled. As shown below, the second and third columns of kiocb are not 0, indicating that the asynchronous IO feature of ORACLE has been enabled. [Oracle @ DB-Server ~] $ Cat/proc/slabinfo | grep kio kioctx 60 80 384 10 1: tunables 54 27 8: slabdata 8 8 0 kiocb 6 30 256 15 1: tunables 120 60 8: slabdata 2 2 0 [oracle @ DB-Server ~] $