Determining Candidates for Cached Quick I/O

Determining which files can benefit from Cached Quick I/O is an iterative process that varies with each application. For this reason, you may need to complete the following steps more than once to determine the best possible candidates for Cached Quick I/O.

Prerequisites

• You must enable Cached Quick I/O for the file systems. See Enabling Cached Quick I/O on the File System.

Usage Notes

• See the qiostat(1M) manual page for more information.

Collecting I/O Statistics

  To collect statistics needed to determine files that benefit from Cached Quick I/O

1. Reset the qiostat counters by entering:
      $ /opt/VRTS/bin/qiostat -r /mount_point/filenames 
2. Run the database under full normal load and through a complete cycle (24 to 48 hours in most cases) to determine your system I/O patterns and database traffic in different usage categories (for example, OLTP, reports, and backups) at different times of the day.
3. While the database is running, run qiostat -l to report the caching statistics as follows:
      $ /opt/VRTS/bin/qiostat -l /mount_point/filenames

   or, use the -i option to see statistic reports at specified intervals:

   
      $ /opt/VRTS/bin/qiostat -i n /mount_point/filenames

   where n is time in seconds

Example

To collect I/O statistics from all database files on file system /db01:

Analyzing I/O Statistics

The output of the qiostat command is the primary source of information to use in deciding whether to enable or disable Cached Quick I/O on specific files. Statistics are printed in two lines per object. The second line of information is defined as follows:

• CREAD is the number of reads from the VxFS cache (or total number of reads to Quick I/O files with cache advisory on)
• PREAD is the number of reads going to the disk for Quick I/O files with the cache advisory on
• HIT RATIO is displayed as a percentage and is the number of CREADS minus the number of PREADS times 100 divided by the total number of CREADS. The formula looks like this:

  (CREADs - PREADs) * 100/ CREADs

The qiostat -l command output looks similar to the following:

Analyze the output to find out where the cache-hit ratio is above a given threshold. A cache-hit ratio above 20 percent on a file for a given application may be sufficient to justify caching on that file. For systems with larger loads, the acceptable ratio may be 30 percent or above. Cache-hit-ratio thresholds vary according to the database type and load.

Using the sample output above as an example, the file /db01/system.dbf does not benefit from the caching because the cache-hit ratio is zero. In addition, the file receives very little I/O during the sampling duration.

However, the file /db01/stk.dbf has a cache-hit ratio of 21.6 percent. If you have determined that, for your system and load, this figure is above the acceptable threshold, it means the database can benefit from caching. Also, study the numbers reported for the read and write operations. When you compare the number of reads and writes for the /db01/stk.dbf file, you see that the number of reads is roughly twice the number of writes. You can achieve the greatest performance gains with Cached Quick I/O when using it for files that have higher read than write activity.

Based on these two factors, /db01/stk.dbf is a prime candidate for Cached Quick I/O. For more information on enabling and disabling Cached Quick I/O at the file level, see Enabling and Disabling Cached Quick I/O for Individual Files.

Effects of Read-Aheads on I/O Statistics

The number of CREADs in the qiostat output is the total number of reads performed, including Cached Quick I/O, and the number of PREADs is the number of physical reads. The difference between CREADs and PREADs (CREADS - PREADS) is the number of reads satisfied from the data in the file system cache. Thus, you expect that the number of PREADs would always be equal to or lower than the number of CREADs.

However, the PREADs counter also increases when the file system performs read-aheads. These read-aheads occur when the file system detects sequential reads. In isolated cases where cache hits are extremely low, the output from qiostat could show that the number of CREADs is lower than the number of PREADs. The cache-hit ratio calculated against these CREAD/PREAD values is misleading when used to determine whether Cached Quick I/O should be enabled or disabled.

Under these circumstances, you can make a more accurate decision based on a collective set of statistics by gathering multiple sets of data points. Consequently, you might want to enable Cached Quick I/O for all the data files in a given tablespace, even if just one of the files exhibited a high cache-hit ratio.

Using Other Tools for Analysis

While the output of the qiostat command is the primary source of information to use in deciding whether to enable Cached Quick I/O on specific files, we also recommend using other tools in conjunction with qiostat. For example, benchmarking software that measures database throughput is also helpful. If a benchmark test in which Cached Quick I/O was enabled for a certain set of data files resulted in improved performance, you can also use those results as the basis for enabling Cached Quick I/O.