On Sun Solaris UFS, Linux, Linux IBM, AIX, and Windows file systems only, you can control whether file system buffering is turned on or off. Turning off file system buffering saves a data copy from the file system buffer cache to the main IQ buffer cache. Usually, doing so reduces paging, and therefore improves performance. Be aware of one exception: If the IQ page size for the database is less than the block size of the file system (typically only in testing situations), turning off file system buffering may decrease performance, especially during multiuser operation.
File system buffering is turned off by default for newly created Sybase IQ databases.
To disable file system buffering for IQ Main dbspaces of existing databases, issue the following statement:
SET OPTION "PUBLIC".OS_FILE_CACHE_BUFFERING = OFF
To disable file system buffering for IQ Temporary dbspaces of existing databases, issue the following statement:
SET OPTION "PUBLIC".OS_FILE_CACHE_BUFFERING_TEMPDB = OFF
Experiment with this option to determine the best setting for different conditions. You can only set this option for the PUBLIC group. You must shut down the database and restart it for the change to take effect.
This direct I/O performance option is available on Sun Solaris UFS, Linux, Linux IBM, AIX, and Windows file systems only. This option has no effect on HP-UX and HP-UXi and does not affect databases on raw disk. In Linux, direct I/O is supported in kernel versions 2.6.x.
To enable direct I/O on Linux kernel version 2.6 and AIX, also set the environment variable IQ_USE_DIRECTIO to 1. Direct I/O is disabled by default in Linux kernel version 2.6 and AIX. IQ_USE_DIRECTIO has no effect on Sun Solaris and Windows.
Sybase IQ does not support direct I/O
on Linux kernel version 2.4. If you set the IQ_USE_DIRECTIO
environment variable on Linux kernel version 2.4, the Sybase IQ server
does not start. The error “Error: Invalid
Block I/O argument, maybe <pathname>
is a directory, or it exceeds maximum file size limit for the platform,
or trying to use Direct IO on unsupported OS” is
reported.
Solaris does not have a kernel parameter to constrain the size of its file system buffer cache. Over time, the file system buffer cache grows and displaces the IQ buffer cache pages, leading to excess operating system paging activity and reduced Sybase IQ performance.
Windows can bias the paging algorithms to favor applications at the expense of the file system. This bias is recommended for Sybase IQ performance. See Chapter 6, “Tuning Servers on 32-bit Windows Systems” for details.
See also “OS_FILE_CACHE_BUFFERING option” in Chapter 2, “Database Options” of Reference: Statements and Options.
