pajama

You are using IBM Informix® Dynamic Server on the AIX operating system and you find this error in the message log:


KAIO: out of OS resources, errno = 11, pid = 117908 on different Versions of   AIX  *** _aiowrite() returning EAGAIN ***

Either AIX kernal tuning parameter, maxreqs is set low. Or, the IBM Informix® Dynamic Server's environment variable, IFMX_AIXKAIO_NUM_REQ for maximum number of requests, is set too low for the current I/O load on the system. The default value, if the environment variable is not set, is 1024. 

maxreqs - is AIX kernel tuning parameter which specifies the maximum number of asynchronous I/O requests that can be outstanding at any one time. The default value is 4096.

• Set environment variable IFMX_AIXKAIO_NUM_REQ to a higher value. The minimum value is 512 and the maximum value is the Maximum number of requests set for Kernel AIO.
• If Informix AIO is used, increase the number of AIO VP's in the onCONFIG configuration file so I/O will be faster and the queue for AIO control blocks will not overflow.

Several files with the prefix below (bui* srt* pro*) are created in the /tmp directory causing the filesystem to get full:

-rw------- 1 root informix 770048 Out 4 12:03 bui0920471_031
-rw------- 1 root informix 761856 Out 4 12:03 bui0920471_032
-rw------- 1 root informix 761856 Out 4 12:03 bui0920471_033
-rw------- 1 root informix 778240 Out 4 12:03 bui0920471_034
-rw------- 1 root informix 761856 Out 4 12:03 pro0920471_035
-rw------- 1 root informix 761856 Out 4 12:03 pro0920471_036
-rw------- 1 root informix 770048 Out 4 12:03 pro0920471_037
-rw------- 1 root informix 6995968 Out 4 12:03 pro0920471_038
-rw------- 1 root informix 3497984 Out 4 12:03 pro0920471_039
-rw------- 1 root informix 3792896 Out 4 12:03 pro0920471_040
-rw------- 1 root informix 4038656 Out 4 12:03 pro0920471_041
-rw------- 1 root informix 964337664 Out 4 12:23 pro0920471_042
-rw------- 1 root informix 968654848 Out 4 12:23 pro0920471_043
-rw------- 1 root informix 967467008 Out 4 12:23 srt0920471_044
-rw------- 1 root informix 963878912 Out 4 12:23 srt0920471_045
-rw------- 1 root informix 966320128 Out 4 12:23 srt0920471_046
-rw------- 1 root informix 961658880 Out 4 12:23 srt0920471_047

High intensive I/O activity can be seen, causing system overload.

Queries performing sort operations should consume a lot of temp space in the Informix temporary dbspaces. But if the environment variable PSORT_DBTEMP is set, the engine will send all the temporary data to its value. Also, it is possible that temporary dbspaces are no set in the DBSPACETEMP onconfig parameter, and therefore, causing all temporary data to be written to the ROOT dbspace and the /tmp filesystem.

Possibles causes:

1 - PSORT_DBTEMP environment variable is set;
2 - No temporary dbspace is set in the DBSPACETEMP onconfig parameter;
3 - There's no space available in the temporary dbspaces;
4 - Temp directories have wrong permissions.

1 - Check for the output from onstat -g env command and look for the value of the PSORT_DBTEMP environment variable:

PSORT_DBTEMP /tmp

2 - Set DBSPACETEMP onconfig parameter to a valid temporary dbspace;

3 - Check that your temp directories have 664 permissions.

1) Unset PSORT_DBTEMP environment variable:

$ unset PSORT_DBTEMP

2) Make sure that your onconfig parameter DBSPACETEMP is set to a temporary dbspace:
$ onstat -c|grep DBSPACETEMP
# DBSPACETEMP - The list of dbspaces used to store temporary
DBSPACETEMP tempdbs1,tempdbs2

Notice: You may want to check Technote 1292093 in the 'Related Information' section of this document.

3) Check if there's enough room for the sort operations in the temporary dbspaces:
$ dbaccess sysmaster -

Database selected.

> select name,
count(*) chunks, sum(chksize), sum(nfree)
from syschunks, sysdbspaces
where name matches '*temp*' and syschunks.dbsnum = sysdbspaces.dbsnum
group by 1;

>

name tempdbs2
chunks 1
(sum) 5000
(sum) 4939

name tempdbs1
chunks 1
(sum) 5000
(sum) 4947

2 row(s) retrieved.

>

This values are displayed in pages. 
You might want to multiply those values by the pagesize to get it in kilobytes.

IDS can only allocate a maximum of 16,777,215 disk pages per table partition or fragment. It should also be noted that this is independent of the dbspace configured page size.
Also, IDS can only track approximately 200 extents (logical grouping of disk pages) per table partition or fragment. The most likely root cause will be that one or both these values have been realized.

As user informix, run oncheck -pt <database>:<table name >".

In the resulting output, examine the value for "Number of pages used" or "Number of pages allocated". If either one is equated to 16,777,215 then this is the most likely root cause of the problem. 

However, should these values not be close to 16,777,215 then it's likely that the table has already allocated a high number of smaller extents (logical grouping of data or index pages).
If so , then the value for "Number of extents" will be close to or greater than 200, (there is no 'hard' limit due to various architectural reasons).

Example output to oncheck -pt :

TBLspace Report for dbase:informix.customer

Physical Address 1:36878
Creation date 801 Page Locking
TBLspace use 4 bit bit-maps
Maximum row size 134
Number of special columns 0
Number of keys 0
Number of extents 186
Current serial value 10139
Current SERIAL8 value 1
Current BIGSERIAL value 1
Current REFID value 2
First extent size 8
Next extent size 16
Number of pages allocated 16777215
Number of pages used 16777215
Number of data pages 16777215
Number of rows 1118341
Partition partnum 1049005
Partition lockid 1049005

If the value for Number of pages used or allocated is at 16777215, then try one of the following recommendations:

Non Fragmented Tables:

1. If the indexes defined for the table are attached , drop and recreate the table's indexes as "detached" (in their own separate partitions).

Note: A CREATE INDEX statement that does not specify IN TABLE as its storage clause creates indexes that store their pages in separate partitions from the data pages by default.

2. Fragment the table using a Round Robin or an ｅxpression Based strategy. This will result in additional partitions being created in order to track the table internally.

If the value for Number of extents is close or exceeds 200, then :

Reorganize the table using a larger initial extent size to defeat a high number of smaller extents being allocated.

3. Recreate the table in a dbspace with a larger page size. If you do this make sure you run dbschema -ss on the database first. This will capture the syntax for all database objects that depend on the table you are about to drop and create. After the table is created and loaded then run the dbschema output file. It's likely most of the objects will fail to create because they already exist but the objects that aren't there will be created. This method ensures you will not miss recreating any database objects dependant on the table ( synonyms, triggers, views, constraints, and so on ). Also, make sure you tune the BUFFERPOOL parameter for the new dbspace page size. If this wasn't already done then Informix will automatically create one for you with the default size.