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

灰太狼 2021-11-19 16:46 202阅读 0赞

今天是2014-01-07，解决一下hp-unix异步Ｉ／Ｏ问题。

从trace日志中看：  
WARNING:Could not increase the asynch I/O limit to 32 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 64 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 64 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 96 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 64 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 96 for SQL direct I/O. It is set to 0  
\*\*\* 2014-01-07 09:18:51.911  
WARNING:Could not increase the asynch I/O limit to 64 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 96 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 128 for SQL direct I/O. It is set to 0  
\*\*\* 2014-01-07 09:19:03.482  
WARNING:Could not increase the asynch I/O limit to 64 for SQL direct I/O. It is set to 0  
WARNING:Could not increase the asynch I/O limit to 128 for SQL direct I/O. It is set to 0

可以看到是数据库异步I/0出现问题。问题排查有两个方面，数据库方面和主机方面：  
第一：排查数据库：  
主要有如下参数：  
filesystemio\_options=asynch  
 disk\_asynch\_io=TRUE  
 tape\_asynch\_io=TRUE  
 dbwr\_io\_slaves=0  
 backup\_tape\_io\_slaves=FALSE  
经过排查参数设置没有问题。  
第二：主机侧问题。  
因为机器是hp-unix，需要关注如下参数：  
The following kernel parameters are used for managing asynchronous I/O operations. The first four are related to POSIX asynchronous I/O operations; the last pertains to open ports between processes and the asynchronous disk-I/O driver:

aio\_listio\_max  
Specifies how many POSIX asynchronous I/O operations are allowed in a single listio() call.

aio\_max\_ops  
System-wide maximum number of POSIX asynchronous I/O operations that are allowed at any given time.

aio\_physmem\_pct  
Maximum total system memory that can be locked for use in POSIX asynchronous I/O operations.

aio\_prio\_delta\_max  
Maximum priority offset allowed in a POSIX asynchronous I/O control block (aiocb).

max\_async\_ports  
Maximum number of ports to the asynchronous disk-I/O driver that processes can have open at any given time.

另外主机检查是否已经配置了异步i/0参考官网文档：  
[http://docs.oracle.com/cd/B19306\_01/server.102/b15658/appb\_hpux.htm\#i637036][http_docs.oracle.com_cd_B19306_01_server.102_b15658_appb_hpux.htm_i637036]  
  但是在本例中看出，已经启动了部分进程的异步I/O。但是只有50个。  
[orarep@pmsdb2\[/dev\]$/usr/sbin/fuser][orarep_pmsdb2_dev_usr_sbin_fuser] /dev/async  
/dev/async:    18635o   11336o   11376o    8833o   18637o    4527o    5834o   10723o   18643o    7740o    5751o   18647o   11330o   11348o   11370o   18639o    2486o   11354o    7738o   11391o   11346o    5795o   11334o   10719o   11389o    5867o   10721o   11356o    8835o    5749o   12291o     390o    3617o   11352o   11344o   11340o   18645o   11338o   11342o    4029o   11372o   11350o   11358o    5785o   11328o   11332o     388o    5779o

[orarep@pmsdb2\[/dev\]$ps][orarep_pmsdb2_dev_ps]  -ef | grep ora\_dbw3\_repdb  
  orarep 11340     1  0 12ÔÂ 31  ?         2:13 ora\_dbw3\_repdb  
  orarep  6010   170  3 09:49:44 pts/6     0:00 grep ora\_dbw3\_repdb  
[orarep@pmsdb2\[/dev\]$][orarep_pmsdb2_dev]

进一步确认max\_async\_ports 内核参数：如下：  
Description  
max\_async\_ports limits the total number of open ports to the ansynchronous disk-I/O driver that processes on the system can have at any given time (this has nothing to do with any RS-232 asynchronous data-communications interfaces). The system allocates an array of port structures for each port when it is opened that is used for all communication between the process and the asynchronous disk driver. The number of asynchronous ports required by a given application is usually specified in the documentation for that application (such as database applications software, video management software, etc.).

To determine a suitable value for max\_async\_ports:

Determine how many ports are required for each application and/or process that uses asynchronous disk I/O.

Determine which of these applications will be running simultaneously as separate processes. Also determine whether multiple copies of an application will be running at the same time as separate processes.

Based on these numbers, determine the maximum number of open ports to the asynchronous disk driver that will be needed by all processes any given time to obtain a reasonable total.

Set max\_async\_ports to a value that is not less than this number.  
查看该内核参数：  
[orarep@pmsdb2\[/dev\]$/usr/sbin/kctune][orarep_pmsdb2_dev_usr_sbin_kctune] max\_async\_ports  
Tunable          Value  Expression   
max\_async\_ports     50  Default      
[orarep@pmsdb2\[/dev\]$][orarep_pmsdb2_dev]  
问题可以看出来，在系统高并发下只有50个异步进程。因此需要调整 该值为实际最大process值。

另外有ｂｕｇ也会出现这种问题：Bug 10208905  "WARNING:Could not increase the asynch I/O limit ..." messages in trace file

转载于:https://www.cnblogs.com/riasky/p/3508747.html

[http_docs.oracle.com_cd_B19306_01_server.102_b15658_appb_hpux.htm_i637036]: http://docs.oracle.com/cd/B19306_01/server.102/b15658/appb_hpux.htm#i637036
[orarep_pmsdb2_dev_usr_sbin_fuser]: mailto:orarep@pmsdb2%5B/dev%5D%24/usr/sbin/fuser
[orarep_pmsdb2_dev_ps]: mailto:orarep@pmsdb2%5B/dev%5D%24ps
[orarep_pmsdb2_dev]: mailto:orarep@pmsdb2%5B/dev%5D%24
[orarep_pmsdb2_dev_usr_sbin_kctune]: mailto:orarep@pmsdb2%5B/dev%5D%24/usr/sbin/kctune