Abstract

This article follows on from the earlier TPC-C benchmarking performed on IDS 12.10, if you missed it, you can read it here. This article takes a view on the topic of DIRECT_IO and its use within IDS, it also highlights the impact of file system caching on Linux, which is relevant for those editions of Informix where DIRECT_IO cannot be used. Lastly, a ‘just for fun’ comparison of DIRECT_IO and RAW devices for readers to come to their own conclusions.

Content

Traditionally, an Informix database system would store its data on a RAW device. Using this method, Informix would communicate directly with the storage device and bypass the operating system’s file management and filesystem caching layer. This is generally considered to provide the best performance without compromising integrity.

DIRECT_IO is designed to mimic the behaviour and performance of RAW device access when Informix is accessing data stored in “cooked” files (i.e. data stored on a filesystem managed by the OS). DIRECT_IO is not new in the world of Linux and Informix (O_DIRECT was introduced to the Linux 2.4.10 kernel in 2001). However, it is still not commonly used in our experience when configuring Informix storage.

We were interested to see the effect that file system caching has in masking underlying I/O performance bottlenecks, and how enabling or disabling elements of filesystem caching might affect Informix performance working on both RAW and DIRECT_IO configurations.

It is important to note that IBM Informix Innovator-C Edition is not licensed to use DIRECT_IO.

Note: The following scenarios which adjust File System Caching are not recommended on any system as performance will be adversely affected.

All of the tests described in this document were performed on the following environment:

System Specification:

  • Dell M6600
  • Intel Core i7-2860QM (2.50GHz, 4 Cores, 8 Logical Processors)
  • 32GB RAM
  • 7200RPM SCSI HDD *

* Configured with an ext2 filesystem for the DIRECT_IO tests to avoid journaling and to honour the mount option ‘sync’.

Tests Performed:

  1. DIRECT_IO Enabled
  2. DIRECT_IO Disabled with File System Caching untouched – (We expect this to be faster)
  3. (We expect the two following changes to deteriorate the performance of DIRECT_IO disabled)
  4. DIRECT_IO Disabled with Read Caching Disabled
  5. DIRECT_IO Disabled with Write Caching Disabled
  6. (Expected to be similar to DIRECT_IO ON)
  7. DIRECT_IO Disabled with both Read and Write Caching Disabled
  8. DIRECT_IO Disabled with both Read and Write Caching Disabled and performing a continuous cache flush
  9. (Expected to be comparable to DIRECT_IO Enabled with File System Caching untouched)
  10. DIRECT_IO Enabled with both Read and Write Caching Disabled
  11. (Expected to be comparable to DIRECT_IO Enabled with File System Caching untouched)
  12. RAW

Enabling and Checking DIRECT_IO Settings:

Our first step was to enable DIRECT_IO. To enable DIRECT_IO, simply set the $ONCONFIG parameter (DIRECT_IO) to 1. This will require an engine restart to take effect, and is also only applicable when using COOKED files.

We can validate DIRECT_IO is being used at the Informix level in two ways: either (a) examine the Informix configuration or (b) examine the underlying Informix storage layout. Both techniques are explored below.

To examine the Informix configuration, use ‘onstat -g cfg DIRECT_IO’ (a feature introduced in version 12.10); or query the sysmaster:sysconfig table.

Copy to Clipboard

To examine the Informix storage layout use ‘onstat –d’:

Copy to Clipboard

The ‘D‘ flag on 6th position indicates that the chunk has been opened using the Linux O_DIRECT flag (DIRECT_IO).

From this point everything indicates IDS is using DIRECT_IO; however, we can further validate this at the OS level in two ways. As of Linux 2.6.22 and up, we can check the flag information found within the processes open file descriptors under /proc/$PID/fdinfo/$FD:

Copy to Clipboard

This method obviously requires to decode the octal flag for each open file descriptor, so a simpler method exists. Using the Linux utility ‘lsof’ provides a human readable decoded version of the octal flag found in the method above.

The “DIR” FILE-FLAG represents O_DIRECT (DIRECT_IO) is being used in the lsof output.

Notice how the Temporary DBSpaces do not use this… (also shown in the ‘onstat -d’ output)

Copy to Clipboard

Using these methods above, provides the ability to accurately validate that Informix is using DIRECT_IO.

The following output shows an I/O test with and without O_DIRECT, which demonstrates clearly the impact of file system caching at the system level, whereby the cached reads are orders of magnitude greater when exploiting the file system cache, which is fully expected.

Copy to Clipboard

Disabling what appears to be all elements of the caching system at the OS is difficult and is not something you’d ever want to do in a real-life situation.

To attempt to disable the read-cache, the mount option “sync” was used:

Copy to Clipboard

To attempt to disable the write-cache the following was used:

Copy to Clipboard

The above two were used in combination to attempt to “fully” disable the file system caching, with a final test which also utilises a continuous flush of the cache using the following method:

Copy to Clipboard

Before looking at the results, a final comparative test was performed with DIRECT_IO vs. RAW on our Amazon EC2 cloud environment where the disk I/O performance far exceeded the base commodity hardware and should demonstrate a fairer test between the two.

Results:

The following graph shows the I/O performance for each test taken from ‘onstat -g ckp’:

From our perspective, there are three interesting observations here:

  1. File System Caching can massively mask the I/O performance, and with database technology we want to make sure the data is written to disk to preserve data integrity and avoid corruption.
  2. Based on the above tests and results, it strongly appears that File System Caching is used at some level when using DIRECT_IO, as turning elements of File System Caching off and using DIRECT_IO severely effects Diskflsh Time.
  3. DIRECT_IO and RAW have very similar results.

Conclusion

The above article is intended to outline the different performance characteristics between RAW and non-RAW device access and to describe the performance relationship between DIRECT_IO and filesystem caching.

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.