Db file async I/O submit waiting for event optimization, asyncsubmit

Db file async I/O submit waiting for event optimization, asyncsubmit

Db file async I/O submit wait for event Optimization

 

I. Data Generation db file async I/O submit

We often see Wait events for high db file async I/O submit from the database awr report:

SQL> select event,wait_class,wait_time from v$session_wait where wait_class<>'Idle'EVENT                            WAIT_CLASS   WAIT_TIME-------------------------------- ----------- ----------SQL*Net message to client        Network             -1Data file init write             User I/O             0control file sequential read     System I/O           0db file sync I/O submit          System I/O          79

Ii. Analyze db file async I/O submit

From the name of the wait event, it is not difficult to see that it is related to IO. IO is divided into the following:

Synchronous I/O: In synchronous I/O, the thread starts an I/O operation and immediately enters the waiting state. Only after the I/O operation is completed can other tasks be executed.

Asynchronous IO: the thread sends an IO request to the kernel and continues to process other tasks. After the content completes the IO request, it will notify the thread that the IO operation is completed.

IO summary:

If I/O requests require a large amount of time for execution, the asynchronous file I/O method can significantly improve the efficiency, because the CPU will schedule other threads to execute during the waiting time, if no other thread needs to be executed, this time will be wasted (the operating system's zero-page thread may be scheduled ). If I/O requests are operated quickly and the asynchronous IO method is more inefficient, I/O synchronization will be better.

Synchronous IO allows only one IO operation at a time. That is to say, IO operations on the same file handle are serialized. Using both threads in a timely manner cannot simultaneously send read/write operations on a file handle. Overlapping IO allows one active multi-thread colleague to send IO requests.

When the asynchronous IO request is complete, the application is notified by setting the file handle to a signal state, or the application can use GetOverlappedResult to check whether the IO request is complete, you can also use an event object to notify the application.

 

3. Find relevant parameters

Let's take a look at the asynchronous IO settings:

In Oracle 11g, the default value is as follows:

SQL> select * from v$version; BANNER--------------------------------------------------------------------------------Oracle Database 11g Enterprise Edition Release 11.2.0.4.0 - 64bit ProductionPL/SQL Release 11.2.0.4.0 - ProductionCORE 11.2.0.4.0 ProductionTNS for Linux: Version 11.2.0.4.0 - ProductionNLSRTL Version 11.2.0.4.0 - Production SQL>SQL> show parameter disk_asynch_io NAME TYPE VALUE------------------------------------ ----------- ------------------------------disk_asynch_io boolean TRUESQL> show parameter filesystemio  NAME TYPE VALUE------------------------------------ ----------- ------------------------------filesystemio_options string none

Iv. Interpretation of the filesystemio_options parameter:

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.
Use the FILESYSTEMIO_OPTIONS initialization parameter to enable or disable asynchronous I/O or direct I/O on the file system file. This parameter is platform-specific. It is best to have a default value for a specific platform.

FILESYTEMIO_OPTIONS can be set to one of the following values:

ASYNCH: enable asynchronous I/O on file 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.

 

5. Enable asynchronous IO

In this case, run the following statement to enable the asynchronous IO option of filesystemio_options:

SQL> alter system set filesystemio_options =asynch sid = ‘*’ scope=spfile;

6. Check after restarting the database:

In Oracle11gR2, AIO is enabled by default. You can use ldd or nm to check whether oracle has enabled AIO support. If the output is enabled.

[root@db02 ~]# grep kio /proc/slabinfokioctx 104 140 384 10 1 : tunables 54 27 8 : slabdata 14 14 0kiocb 123 210 256 15 1 : tunables 120 60 8 : slabdata 14 14 0[root@db02 ~]# su - oracle[oracle@db02 ~]$ /usr/bin/nm $ORACLE_HOME/bin/oracle | grep io_getevent                 w io_getevents@@LIBAIO_0.4

The proc file system contains two virtual files that can be used to optimize the performance of asynchronous I/O:

The/proc/sys/fs/aio-nr file provides the current number of System-range asynchronous I/O requests.

The/proc/sys/fs/aio-max-nr file is the maximum number of concurrent requests allowed. The maximum number is usually 64 KB, which is sufficient for most applications.

Check whether asynchronous I/O is in use

According to [Note 370579.1], you can view the slabinfo statistics to 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, the second and third columns of the kiocb values are used if they are not 0. Unlike kernel 2.4.x, kiobuf is not displayed because kiobuf has been removed from the kernel since kernel 2.5.43.

Kioctx:

The AIO context corresponds to the data structure kioctx in the kernel space, which stores all information about asynchronous IO:

[oracle@db02 ~]$ grep kio /proc/slabinfokioctx 103 140 384 10 1 : tunables 54 27 8 : slabdata 14 14 0kiocb 134 195 256 15 1 : tunables 120 60 8 : slabdata 13 13 0

VII. Postscript:

1. operating system cache:

The operating system and the device controller provide database caches, which do not directly conflict with the database cache management. However, these architectures may consume resources when there are few or no performance benefits. This situation is obvious when database files are stored in Linux or UNIX file systems. By default, all database I/O is cached by the file system.
In some Linux and UNIX systems, direct I/O is available for file storage. This protocol allows database file access within the file system and bypasses the file system cache. Direct I/O saves CPU resources and allows the file system cache to be independent when there is no database activity, such as program texts and spool files.
Although the operating system cache is often redundant because of the database cache buffers blocks. In some cases, the database buffer cache cannot be used for databases. In these cases, using direct I/O or bare devices may cause more serious performance problems than using the System buffer. For example

· Reads or writes to the TEMP tablespace

· Data stored in NOCACHE LOBs

· Parallel Query slaves reading data

2. related parameters:

In oracle, db_writer_processes and dbwr_io_slaves are commonly used to increase io speed.
The number of transactions in the database is very high, or the database cache is very large. A DBWn process cannot keep up with the load of data. We can adjust these two parameters to increase the io read/write process, enable asynchronous io to speed up io
Multiple slaves can write data files in parallel, while multiple dbwr can also write data files in parallel.
Multiple slaves in a dbwr are the dirty buffer collected by dbwr, while the slaves write data files.
Multiple dbwr can concurrently collect dirty buffer and write data files in parallel.
However, if the system supports AIO, you do not need to set multiple dbwr or io slaves.
Db_writer_processes: In a multi-cpu, multi-disk environment, it is generally a dbwr process every 8 CPUs.

When to configure these two parameters?
If the system io is the bottleneck, check whether the OS supports asynchronous io. If the system supports asynchronous io but is not currently in use, enable asynchronous io to alleviate the io bottleneck. If the OS does not support asynchronous io,
Or the OS has enabled asynchronous io, But io is still the bottleneck. We can configure multiple dbwr processes. Note the following When configuring these two parameters:
One db_writer_processes and multiple dbwr_io_slaves
Multiple db_writer_processes and dbwr_io_slaves are not enabled

 

VIII. Reference:

• FILESYSTEMIO_OPTIONS (Reference)
• FILESYSTEMIO_OPTIONS (Performance Tuning Guide)
• Asynchronous I/O Support
• Direct I/O Support
• Asynchronous I/O
• Direct I/O Support
• Direct and Asynchronous I/O