DIRECT_IO for temporary DBspaces

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

$ onstat -g cfg full DIRECT_IO

IBM Informix Dynamic Server Version 14.10.FC2DE -- On-Line -- Up 04:24:06 -- 395844 Kbytes

Configuration Parameter Info

id   name                      type    maxlen   units   rsvd  tunable
145  DIRECT_IO                 UINT4   12

default : 0
     onconfig: 1
     current : 1x1

Description:
     Use the DIRECT_IO configuration parameter to control the use of
     special types of I/O for cooked files used for dbspace chunks.

This parameter enables direct I/O (bypassing file system buffering)
     on UNIX platforms or concurrent IO (bypassing both file system
     buffering and unnecessary write serialization) on AIX® operating
     systems.

Direct I/O can only be used for dbspace chunks whose file systems
     support direct I/O for the page size.

By using direct I/O, you might be able to reduce the number of AIO
     virtual processors.

The value of this parameter is a set of flags, which may be combined:

0x1  Direct I/O
     0x2  Concurrent I/O (AIX only)
     0x4  Enable special I/O for TEMP DBspaces

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:

sp_test_temp_io.sql

Copy to Clipboard

CREATE PROCEDURE sp_test_temp_io()
    RETURNING
        FLOAT AS flush_time,
        FLOAT AS checkpoint_time,
        FLOAT AS critical_wait;
/*
    Test performance of temporary dbspace disk I/O
    Doug Lawry, Oninit Consulting, June 2020

Proves relative performance whether:
    1) in disk devices, file system, or RAM disk;
    2) with or without DIRECT_IO 5 (Kernel AIO).

Averages 20 checkpoints after inserting 80MB.

Usage: EXECUTE PROCEDURE sp_test_temp_io();
*/
    DEFINE ft, ct, cw FLOAT;
    DEFINE c, i INT;
    DEFINE msg VARCHAR(255);

DROP TABLE IF EXISTS temp_table;

CREATE TEMP TABLE temp_table (
        uniq_id SERIAL,
        content CHAR(2000)
    ) WITH NO LOG;

FOR c = 1 TO 20 -- syscheckpoint keeps last 20 only

TRUNCATE temp_table;

FOR i = 1 TO 40000
            INSERT INTO temp_table (content) VALUES (CURRENT);
        END FOR

CALL sysadmin:task('onmode', 'c') RETURNING msg;

END FOR

SELECT
        AVG(flush_time),
        AVG(cp_time),
        AVG(longest_crit_wait)
    INTO ft, ct, cw
    FROM sysmaster:syscheckpoint;

RETURN ft, ct, cw;

END PROCEDURE;

Results were:

% change in checkpoint times compared to Informix AIO in standard file system
Server	Host 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 5	Flush Checkpoint Critical wait	-29.91 -80.51 -80.46	-06.12 -34.85 -33.35
RAM disk	Flush 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

$ onstat -g iov

IBM Informix Dynamic Server Version 14.10.FC3XFWE -- On-Line (Prim) -- Up 55 days 23:15:36 -- 14890080 Kbytes

AIO I/O vps:
class/vp/id s  io/s totalops  dskread dskwrite  dskcopy   wakeups  io/wup  errors tempops
 fifo  7  0 i   0.0        0        0        0        0         0   0.0       0        0
  kio -1  0 i  54.5 65689954 63033219  2656735        0 121066794   0.5       0        0
  kio -1  1 i  53.0 63917570 62055254  1862316        0 118510264   0.5       0        0
  kio -1  2 i  34.3 41275079 40062366  1212713        0  73975119   0.6       0        0
  kio -1  3 i  19.9 24031938 23236512   795426        0  40411773   0.6       0        0
  kio -1  4 i  11.6 14031560 13465134   566426        0  21750537   0.6       0        0
  kio -1  5 i   4.1  4981266  4653512   327754        0   7357247   0.7       0        0
  kio -1  6 i   7.1  8502714  8074719   427995        0  12589671   0.7       0        0
  kio -1  7 i   2.3  2824663  2570265   254398        0   4244608   0.7       0        0
  kio -1  8 i   0.5   553685   446089   107596        0    897072   0.6       0        0
  kio -1  9 i   1.3  1574956  1368354   206602        0   2427179   0.6       0        0
  kio -1 10 i   0.7   888494   743114   145380        0   1418768   0.6       0        0
  kio -1 11 i   0.3   324283   277731    46552        0    556482   0.6       0        0
  msc  6  0 i   0.0    54699        0        0        0     54684   1.0       0   242509
  aio  5  0 i  10.0 12059542  5461643  6457545        0  11781294   1.0       0     2911
  aio 19  1 i   0.4   482978    26192   456617        0    530135   0.9       0        1
  aio 20  2 i   0.2   219099      118   218978        0    250312   0.9       0        0
  aio 21  3 i   0.1   114911        8   114903        0    137619   0.8       0        0
  aio 22  4 i   0.1    65118        8    65110        0     77217   0.8       0        0
  aio 23  5 i   0.0    43587        6    43581        0     53463   0.8       0        0
  aio 24  6 i   0.0    31522        1    31521        0     40587   0.8       0        0
  aio 25  7 i   0.0    25072        1    25071        0     31754   0.8       0        0
  pio  4  0 i   0.0        0        0        0        0         0   0.0       0        0
  lio  3  0 i   0.0        0        0        0        0         0   0.0       0        0

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.

Caveats

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.