Table spaces without file system caching
The recommended method of enabling or disabling non-buffered I/O on UNIX, Linux®, and Windows is at the table space level.
This allows you to enable or disable non-buffered I/O on specific table spaces while avoiding any dependency on the physical layout of the database. It also allows the database manager to determine which I/O is best suited for each file, buffered or non-buffered.
The NO FILE SYSTEM CACHING clause is used to enable non-buffered I/O, thus disabling file caching for a particular table space. Once enabled, based on platform, the database manager automatically determines which of the Direct I/O (DIO) or Concurrent I/O (CIO) is to be used. Given the performance improvement in CIO, the database manager uses it whenever it is supported; there is no user interface to specify which one is to be used.
In order to obtain the maximum benefits of non-buffered I/O, it might be necessary to increase the size of buffer pools. However, if the self-tuning memory manager is enabled and the buffer pool size is set to AUTOMATIC, the database manager will self-tune the buffer pool size for optimal performance. Note that this feature is not available before Version 9.
This method of enabling and disabling file system caching provides control of the I/O mode, buffered or non-buffered, at the table space level.
To determine whether file system caching is enabled, query the value of the fs_caching monitor element for the table space in the MON_GET_TABLESPACE table.
- Alternate methods to enable/disable non-buffered I/O on UNIX, Linux, and Windows
- Some UNIX platforms support
the disabling of file system caching at a file system level by using
the MOUNT option. Consult your operating system documentation for
more information. However, it is important to understand the difference
between disabling file system caching at the table space level and
at the file system level. At the table space level, the database manager
controls which files are to be opened with and without file system
caching. At the file system level, every file residing on that particular
file system will be opened without file system caching. Some platforms
such as AIX® have certain requirements
before you can use this feature, such as serialization of read and
write access. although the database manager adheres to these requirements,
if the target file system contains files not from the database manager,
before enabling this feature, consult your operating system documentation
for any requirements.Note: The now-deprecated registry variable DB2_DIRECT_IO, introduced in Version 8.1 FixPak 4, enables no file system caching for all SMS containers except for long field data, large object data, and temporary table spaces on AIX JFS2. Setting this registry variable in Version 9.1 or later is equivalent to altering all table spaces, SMS and DMS, with the NO FILE SYSTEM CACHING clause. However, using DB2_DIRECT_IO is not recommended, and this variable will be removed in a later release. Instead, you should enable NO FILE SYSTEM CACHING at the table space level.
- Alternate methods to enable/disable non-buffered I/O on Windows
- In previous releases, the performance registry variable DB2NTNOCACHE could be used to disable file system caching for all Db2® files in order to make more memory available to the database so that the buffer pool or sort heap can be increased. The difference between DB2NTNOCACHE and using the NO FILE SYSTEM CACHING clause is the ability to disable caching for selective table spaces. Starting in Version 9.5, since the NO FILE SYSTEM CACHING is used as the default, unless FILE SYSTEM CACHING is specified explicitly, there is no need to set this registry variable to disable file system caching across the entire instance if the instance includes only newly created table spaces.
- Performance considerations
- Non-buffered I/O is essentially used for performance improvements.
In some cases, however, performance degradation might be due to, but
is not limited to, a combination of a small buffer pool size and a
small file system cache. Suggestions for improving performance include:
- If self-tuning memory manager is not enabled, enable it and set
the buffer pool size to automatic using
ALTER BUFFERPOOL name SIZE AUTOMATIC
. This allows the database manager to self-tune the buffer pool size. - If self-tuning memory manager is not to be enabled, increase the buffer pool size in increments of 10 or 20 percent until performance is improved.
- If self-tuning memory manager is not to be enabled, alter the
table space to use
FILE SYSTEM CACHING
. This essentially disables the non-buffered I/O and reverts back to buffered I/O for container access.
- If self-tuning memory manager is not enabled, enable it and set
the buffer pool size to automatic using
- For DMS file containers (and all SMS containers), the operating system might cache pages in the file system cache (unless the table space is defined with NO FILESYSTEM CACHING).
- For DMS device container table spaces, the operating system does not cache pages in the file system cache.