WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O.

dcswinner發表於2012-11-02
MITSQL

今天檢查電商的資料庫發現trace檔案中有如下警告：

WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O. It is set to 128
WARNING:Could not increase the asynch I/O limit to 224 for SQL direct I/O. It is set to 128

資料庫版本：

SQL> select * from v$version;
 
BANNER
----------------------------------------------------------------
Oracle Database 10g Enterprise Edition Release 10.2.0.5.0 - 64bi
PL/SQL Release 10.2.0.5.0 - Production
CORE 10.2.0.5.0 Production
TNS for Linux: Version 10.2.0.5.0 - Production
NLSRTL Version 10.2.0.5.0 - Production

查詢metalink後發現：

In this Document
  Symptoms
  Cause
  Solution
  References

Applies to:

Oracle Server - Enterprise Edition - Version: 10.2.0.5 and later   [Release: 10.2 and later ]
Generic Linux
disk_asynch_io = TRUE
filesystemio_options = none
[root@xyz ~]# cat /etc/issue
Enterprise Linux Enterprise Linux Server release 5.2 (Carthage)

Symptoms

Using 10.2.0.5 RDBMS on Linux.

The following error occurs in trace files.

ERROR
-----------------------
WARNING:Could not increase the asynch I/O limit to 240 for SQL direct I/O. It is set to 128

Cause

aio-max-size is set not set, the default is128


[root@xyz ~]# cat /proc/sys/fs/aio-max-size
cat: /proc/sys/fs/aio-max-size: No such file or directory
[root@xyz ~]# cat /proc/sys/fs/aio-max-nr
65536


 

Solution

The aio-max-size kernel parameter doesn't exist in the 2.6.x Linux kernels.
This feature is now "automatic" in the 2.6.x kernel, based on the physical capabilities of the disk device driver.
This should mean that the Linux Kernel is ready to perform. ASYNC I/O.

All install requirements should be met.

To ensure ASYNC I/O can be performed by Oracle Database you need to verify or set the following parameters in the Database:

sql> alter system set disk_asynch_io=true scope=spfile;
sql> alter system set filesystemio_options=setall scope=spfile;


Then shutdown and startup the database and check if the warning reappears.
An HCVE report (refer to Note 250262.1) should report no remaining issues

If the above doesn't resolve the problem, then increase fs.aio-max-nr

檢視本資料設定：

SQL>  show parameter sync
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
disk_asynch_io                       boolean     TRUE
tape_asynch_io                       boolean     TRUE

SQL> show parameter systemio
 
NAME                                 TYPE        VALUE
------------------------------------ ----------- ------------------------------
filesystemio_options                 string      none
 

由於調整filesystemio_options且使其生效，需要重啟例項，而這個資料庫目前在承擔重要的業務，重啟是不太可能了，嘗試調整核心引數：fs.aio-max-nr將目前的預設值改為：

fs.aio-max-nr = 1048576

執行sysctl -p使其生效。

在觀察一段時間看看。

來自 “ ITPUB部落格 ” ，連結：http://blog.itpub.net/12129601/viewspace-748218/，如需轉載，請註明出處，否則將追究法律責任。

相關文章