ANBOB™

环境Oracle 11g(11.2.0.4) on RHEL6.9， 文件存储在SATA SSD的文件系统，每秒redo 50-100MB， 存在部分时间段40多组2GB online redo logfile 同时”active”状态的现象，cpu使用率60%左右。除了优化checkpoint外发现有2个少见的TOP event， 查看FG top event为’free buffer waits’， BG top event为 ‘db file async I/O submit’。

AWR

这是一个5分钟128Cpu的AWR

Free buffer waits
Server processes scan LRU list to get free buffers (e.g. while reading a block from disk, or cloning a buffer for CR etc.). After scanning it up to a threshold level, if server process could not find a free buffer, it request DBWR to write dirty buffers from the LRU lists to disk or it waits until a pinned buffer is freed. While DBWR writes the dirty buffers/ a pinned buffer is freed, the session waits on ‘free buffer waits’.

解决方法

A)DBWR 不是足够快去flush dirty blocks到磁盘.
1， Check if CPU is not saturated. A saturated CPU can amplify wait events where a background process does not get enough CPU to progress faster.
2， Check Slow IO (Poor data file write performance). Some file systems have poor write performance (writes take too long) and is impacting DBwriter’s ability to keep enough clean buffers in the buffer cache.
3， The DBWriter will achieve optimal throughput when asynchronous I/O is available to it. DBWriter may not be able to keep up with buffer demands if asynch I/O is not available. If your platform doesn’t support it, then adding multiple DBWriters can help divide the workload.
4，Tune checkpoints, so that we can flush dirty buffers fast enough.
5， Tune the SQL which can eliminate excess IO

B)Buffer cache is too small:
1, If the buffer cache is too small and filled with hot blocks, then sessions will be starved for free buffers (clean, cold blocks) and will need to spend too much time looking for free buffers and/or posting DBWR to write dirty blocks and make them free. Increase the parameter or DB_CACHE_SIZE and monitor the effect of the change.
2, Tune the SQL which can eliminate excess IO

Db file async i/o submit

the wait event ‘db file async I/O submit’ does not time anything when using ASM, only when using a filesystem, where this wait event essentially times the time the system call io_submit takes.this wait event is unique to the database writer.  When the Oracle datebase engine is set to using asynchronous I/O, and when it makes sense to use asynchronous I/O (!), the engine will use the combination of io_submit() to issue I/O requests to the operating system,  In general (so not consistently), the engine does not time io_submit,A lot of ‘%parallel%’ IO related wait events can time asynchronous IO calls. io_submit() is not a blocking call, UNLESS the device queue to which the requests are submitted is full. This means that the developers of the database writer code decided to implement a wait event for io_submit, which is not the case for any other process. When blocks are dirtied in the cache and these blocks are checkpointed later on, these must be written to disk.

a) the database writer picks up a batch of blocks needing writing, for up to 128 IO requests.
b) that batch is submitted, timed by ‘db file async I/O submit’
c) a blocking io_getevents call is issued, timed by ‘db file parallel write’, to wait for the IOs to finish. The interesting thing specifically for the database writer is that the minimal number of IOs ready to wait for is very low (a few IOs to 25-75% of the IOs if the amount gets bigger). Any finished IO will be picked up here, however it’s perfectly possible IOs are still active after this step. In fact, I think it’s deliberately made that way.
d) if any IO requests are still pending, a nonblocking, non-wait event timed io_getevents call is issued to pick up any finished IOs.
e) if any blocks still need writing for which no IO request have been submitted, go to a).
f) if at this point IO requests are still pending, to to c).

According to the Oracle documentation, asynch I/O is disabled for normal file systems if FILESYSTEMIO_OPTIONS=NONE. However, when setting DISK_ASYNCH_IO to TRUE, the wait event list shows ‘db file async I/O submit’.

Synchronous I/O vs Asynchronous I/O
In asynchronous I/O, a process can work at the same time as an I/O request and process each data block as it arrives, without waiting for all data blocks to be loaded. For database files stored in ASM, by default async IO is enabled and hence there is no need to configure async IO manually.

Buffered I/O vs Direct I/O
In general linux or unix filesystems, I/O operations has to pass through filesystem cache. Using buffered I/O with Oracle creates redundant cache between the OS buffer cache and the Oracle buffer cache. Wasted extra CPU time. Direct I/O allows all read and write requests to come directly from the hard drive, avoiding the overhead of double-buffering.

select name,value from v$parameter 
where name like '%disk_async%' or name like '%filesystemio%';

FILESYSTEMIO_OPTIONS的参数:
“asynch”：允许asynchronous IO在OS层面上。
“directIO”:允许directIO
“setall”：开启ASYNC和DIRECT IO，
“none”；(default )Oracle使用synchronous writes，不做任何的direct io的选项。

disk_asynch_io参数：
disk_asynch_io 参数默认是true，是asynchronous I/O的主开关。关闭，就只能走synchronous机制，忽略filesystemio_options参数的设定。

Note:
ORA-1578 ORA-353 ORA-19599 Corrupt blocks with zeros when filesystemio_options=SETALL on ext4 file system using Linux (Doc ID 1487957.1)记录当FILESYSTEMIO_OPTIONS setall值时在OEL 5/6 EXT4文件系统可能存在bug导致产生corrupted block, 解决方法是升级LINUX内核或 set filesystemio_options=NONE or filesystemio_options=DIRECTIO or filesystemio_options=ASYNCH 。

ALTER SYSTEM SET FILESYSTEMIO_OPTIONS = ASYNCH SCOPE = SPFILE;
-- shutdown and restart instance

How To Check if Asynchronous I/O is Working On Linux (Doc ID 237299.1)

$ cat /proc/slabinfo | grep kio

if Async I/O is disabled or not in using, first columns value is “0”

trace dbwr process

io_submit和io_getevents是用于确认使用的异步和直接 IO 的系统调用.

Example strace of dbw0 process with AIO enabled (init.ora parameter filesystemio_options = asynch) shows:

    ...
    io_submit(3071864832, 1, {{0xb7302e34, 0, 1, 0, 21}}) = 1
    gettimeofday({1176916625, 58882}, NULL) = 0
    io_getevents(-1223102464, 1, 1024, {{0xb7302e34, 0xb7302e34, 8192, 0}}, {600, 0}) = 1
    ...

Example strace of dbw0 process with AIO disabled (filesystemio_options = none):

    ...
    pwrite64(21, "\6\242\0\0004\21\300\0\220B\243\0\0\0\1\6\207\357\0\0\1"..., 8192, 36077568) = 8192
    times(NULL) = 1775653082
    times(NULL) = 1775653082
    pwrite64(21, "\6\242\0\0<\21\300\0\220B\243\0\0\0\1\6\254\0\0\0\2\0*"..., 8192, 36143104) = 8192
    ...

Reference

Doc ID 1476046.1
Oracle database wait event ‘db file async I/O submit’ timing bug

打赏

微信扫一扫，打赏作者吧～