On some file systems, you can turn file system buffering on or off. Turning file system buffering off usually reduces paging and improves performance.
SET OPTION "PUBLIC".OS_FILE_CACHE_BUFFERING = OFF
SET OPTION "PUBLIC".OS_FILE_CACHE_BUFFERING_TEMPDB = OFF
You can only set this option for the PUBLIC role. Shut down the database and restart it for the change to take effect.
This direct I/O performance option is available on 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 Solaris and Windows.
SAP 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 SAP 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 buffer cache pages, leading to excess operating system paging activity and reduced performance. Use raw devices for databases on Solaris whenever possible.
Windows can bias the paging algorithms to favor applications at the expense of the file system. This bias is recommended for SAP Sybase IQ performance.