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
prior to Version 9.
To disable or enable file system caching,
specify the NO FILE SYSTEM CACHING or the FILE SYSTEM CACHING clause
in the CREATE TABLESPACE or ALTER TABLESPACE statement, respectively.
The default setting is used if neither clause is specified. In the
case of ALTER TABLESPACE, existing connections to the database must
be terminated before the new caching policy takes effect.
Note: If
an attribute is altered from the default to either FILE SYSTEM CACHING
or NO FILE SYSTEM CACHING, there is no mechanism to change it back
to the default.
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 non-DB2 files, 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 sortheap 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.
Performance tuning should be tested in a controlled environment
before implementing it on the production system.
When
choosing to use file system files versus devices for table space containers,
you should consider file system caching, which is performed as follows:
- 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.