As of Sybase IQ 12.7 ESD #9, you can control file system buffering on Linux, Linux IBM, and IBM AIX platforms using the database options OS_FILE_CACHE_BUFFERING and OS_FILE_CACHE_BUFFERING_TEMPDB. This direct I/O functionality is supported in Linux kernel versions 2.6.x. Sun Solaris and Windows platforms already support direct I/O.
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.
In versions prior to Sybase IQ 12.7 ESD #9, the database option OS_FILE_CACHE_BUFFERING controls the file system buffering of both IQ Main dbspaces and IQ Temporary dbspaces. As of Sybase IQ 12.7 ESD #9, OS_FILE_CACHE_BUFFERING controls the file system buffering of IQ Main dbspaces only. The new database option OS_FILE_CACHE_BUFFERING_TEMPDB controls the file system buffering of IQ Temporary dbspaces.
To enable direct I/O for IQ Main dbspaces, set OS_FILE_CACHE_BUFFERING to OFF.To enable direct I/O for IQ Temporary dbspaces, set OS_FILE_CACHE_BUFFERING_TEMPDB to OFF. The default value of both options is OFF.
Controls the use of file system buffering for IQ Temporary dbspaces.
ON, OFF
OFF
Can be set for the PUBLIC group only. You must shut down and restart the database for the change to take effect. DBA permissions are required to set this option.
Setting OS_FILE_CACHE_BUFFERING_TEMPDB to OFF prevents file system buffering for IQ Temporary Store files. Turning off file system buffering saves a data copy from the file system buffer cache to the main IQ buffer cache. Usually this reduces paging caused by competition for memory between the IQ buffer manager and the file system buffer of the operating system. When OS_FILE_CACHE_BUFFERING_TEMPDB reduces paging, this option improves performance; however, if the IQ page size for the database is less than the block size of the file system (typically only in testing situations), performance decreases, especially during multiuser operation.
Experiment with this option to determine the best setting for different conditions. You must restart the database for the new setting to take effect. This option has no effect on HP-UX and HP-UXi.
In Linux, direct I/O is supported in kernel versions 2.6.x.
OS_FILE_CACHE_BUFFERING controls file system buffering for IQ Main Store files.
“OS_FILE_CACHE_BUFFERING option” in Chapter 2, “Database Options” in the Sybase IQ Reference Manual
Chapter 5, “Managing System Resources” in the Sybase IQ Performance and Tuning Guide
