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

polin

WARNING:Could not increase the asynch I/O limit to 64 for SQL direct I/O. It is set to 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 ... pb_hpux.htm#i637036
  但是在本例中看出，已经启动了部分进程的异步I/O。但是只有50个。
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  -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]$
进一步确认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 max_async_ports
Tunable          Value  Expression  
max_async_ports     50  Default     
orarep@pmsdb2[/dev]$
问题可以看出来，在系统高并发下只有50个异步进程。因此需要调整 该值为实际最大process值。