Abstract

Informix Dynamic Server (IDS) stores database objects in temporary “dbspaces” comprised of one or more “chunks” (physical storage devices). Historically, chunks were disk devices or logical volumes.

In more recent years, with the advent of general-purpose network storage arrays and virtual machines, it became desirable to store chunks in file systems (“cooked files”). For applications needing comparable performance, this became a viable option with configuration parameter DIRECT_IO introduced in IDS 11.10, which enables files to be accessed using Kernel Asynchronous I/O (KAIO) with the O_DIRECT flag available in later operating system versions, bypassing file system buffering and caching.

The manual page here states that “IBM Informix does not use direct I/O for cooked files used for Temporary DBSpaces chunks.” That is no longer accurate as of IDS 12.10.FC13 or 14.10.FC2 due to a feature still undocumented as at June 2020: see forum thread here. This article explores when/why/how this should be enabled.

Content

Temporary dbspaces perform best in RAM disk (using the term broadly including “tmpfs” which isn’t pinned): see article here. Next best are disk devices (raw character if available although those are deprecated on Linux where KAIO on block devices works fine), and lastly cooked files. There may be situations where there is insufficient memory for RAM disk, and disk devices are not directly accessible. Fortunately, there is now a way to improve the performance of the remaining option as indicated in the last line of the following:

Copy to Clipboard

These comments may also be in your template configuration file $INFORMIXDIR/etc/onconfig.std:

Copy to Clipboard

If you are using cooked file chunks, you should already have changed DIRECT_IO from the default of 0 so that KAIO is being used, which reduces the numbers of AIO VPs and is lighter overall on the system. You can now extend that to temporary dbspaces by adding 4 to the setting in the $ONCONFIG file and restarting IDS, for example 5 on Linux:

Copy to Clipboard

The following is a stored procedure returning average times over 20 checkpoints each after populating an 80MB temp table:

Copy to Clipboard

Results were:

% change in checkpoint times compared to Informix AIO in standard file system
ServerHost type
Linux version
IDS version
Server role
Docker
Debian 8
14.10.FC2
Sandpit
VMware
RHEL 7.7
12.10.FC14
Prod/Test
DIRECT_IO 5Flush
Checkpoint
Critical wait
-29.91
-80.51
-80.46
-06.12
-34.85
-33.35
RAM diskFlush
Checkpoint
Critical wait
-66.61
-94.50
-94.43

Note that you cannot use DIRECT_IO on RAM disk as it doesn’t support the O_DIRECT flag, but it doesn’t need it: as you can see above, it’s unsurprisingly the quickest of all anyway. With DIRECT_IO 5 and the chunk symbolically linked into a Docker “tmpfs” file system, this message resulted during startup:

Copy to Clipboard

You may have previously set the number of Informix AIO VPs with a configuration file entry such as:

Copy to Clipboard

Once temporary dbspaces are using Kernel AIO, you will not need as many. It is probably simplest to comment that entry out but make sure AUTO_AIOVPS is set to 1 (enabled) so that the number can be increased autonomically. They will still be used if PSORT_DBTEMP is set, which we often recommend, particularly for index builds.

You can check the number of I/O VPs and their activity with the command documented here and as illustrated below on an actual production system before implementing DIRECT_IO 5:

Copy to Clipboard

VPs in use:

  • kio – Kernel AIO – not separate processes, hence column 2 being -1 and no entry in “onstat -g glo”
  • aio – Informix AIO – separate “oninit” processes, see PID and other details with “onstat -g glo”
  • msc – Miscellaneous – for example writing to the Informix message log file (MSGPATH)

VPs not in use (cannot be disabled):

  • fifo – external table access
  • pio – physical log in cooked file without DIRECT_IO
  • lio – logical logs in cooked files without DIRECT_IO

After setting DIRECT_IO 5 and restarting, there was only one “aio” VP.

Conclusion

If you are on a recent IDS version and have temporary dbspaces in file systems, setting DIRECT_IO as described in this article will make flushing to disk substantially faster with less “oninit” processes running.

Disclaimer

Suggestions above are provided “as is” without warranty of any kind, either express or implied, including without limitation any implied warranties of condition, uninterrupted use, merchantability, fitness for a particular purpose, or non-infringement.

Contact us

If you have any questions or would like to find out more, simply contact us.

Author