Cached Quick I/O For Databases

32-bit applications (such as 32-bit databases) can use a maximum of only 4 GB of memory because of the 32-bit address limitation. The Cached Quick I/O feature improves database performance on machines with sufficient memory by also using the file system cache to store data.

For read operations through the Quick I/O interface, data is cached in the system buffer cache, so subsequent reads of the same data can access this cached copy and avoid doing disk I/O. To maintain the correct data in its buffer for write operations, Cached Quick I/O keeps the buffer cache in sync with the data written to disk.

With 64-bit applications, for which limited memory is not a critical problem, using the file system cache still provides performance benefits by using the read-ahead functionality. Because of the read-ahead functionality, sequential table scans will benefit the most from using Cached Quick I/O by significantly reducing the query response time.

To use this feature, set the qio_cache_enable system parameter with the vxtunefs utility, and use the qioadmin command to turn the per-file cache advisory on or off. See the vxtunefs(1M) and qioadmin(1) man pages for more information.

Enabling Cached Quick I/O

Caching for Quick I/O files can be enabled online when the database is running. You enable caching in two steps:

1. Set the qio_cache_enable parameter of vxtunefs to enable caching on a file system.
2. Enable the Cached Quick I/O feature for specific files using the qioadmin command.

   ---

     Note    Quick I/O must be enabled on the file system for Cached Quick I/O to operate.

   ---

Enabling Cached Quick I/O for File Systems

Caching is initially disabled on a file system. You enable Cached Quick I/O for a file system by setting the qio_cache_enable option of the vxtunefs command after the file system is mounted. For example, to enable Cached Quick I/O for the file system /database01, enter:

where /database01 is a VxFS file system containing the Quick I/O files.

You can make this setting persistent across mounts by adding a file system entry in the file /etc/vx/tunefstab. For example:

For information on how to add tuning parameters, see the tunefstab(4) manual page.

Enabling Cached Quick I/O for Individual Files

There are several ways to enable caching for a Quick I/O file. Use the following syntax to enable caching on an individual file:

To enable caching for the Quick I/O file /database01/names.dbf, type:

To disable the caching for that file, enter:

To make the setting persistent across mounts, create a qiotab file, /etc/vx/qioadmin, to list files and their caching advisories. Based on the following example, the file /database/sell.dbf will have caching turned on whenever the file system /database is mounted:

To check on the current cache advisory settings for a file, enter:

To check the setting of the qio_cache_enable flag for a file system, enter:

For more information on the format of the /etc/vx/qioadmin file and the command syntax, see the qioadmin(1) manual page.

Tuning Cached Quick I/O

Not all database files can take advantage of caching. Performance may even degrade in some instances (due to double buffering, for example). Determining which files and applications can benefit from Cached Quick I/O requires that you first collect and analyze the caching statistics.

See the qiostat(1) man page for information on gathering statistics, and the VERITAS Editions products documentation for a description of the Cached Quick I/O tuning methodology.