Read operations and prefetch I/O

Db2 uses prefetch I/O to read data in almost all cases, but uses synchronous I/O in certain cases.

When synchronous read is used, just one page is retrieved at a time. The unit of transfer for a synchronous read is one page per single I/O operation. Db2 uses prefetch mechanisms such as dynamic prefetch, sequential prefetch, and list prefetch, whenever possible, to avoid costly wait times from synchronous I/O.

Prefetch is a mechanism for reading a set of pages, usually 32, into the buffer pool with only one asynchronous I/O operation. Prefetch provides for substantial savings in both CPU and I/O costs. The maximum number of pages read by a single prefetch operation is determined by the size of the buffer pool that is used for the operation.

Db2 uses the following types of prefetch to avoid using costly synchronous read to access data, indexes, and LOBs:
Sequential prefetch
Db2 uses sequential prefetch for table scans and for sequential access to data in a multi-table segmented table space when index access is not available.
Dynamic prefetch
In dynamic prefetch, Db2 uses a sequential detection algorithm to detect whether pages are being read sequentially. Db2 tries to distinguish between clustered or sequential pages from random pages. Db2 uses multi-page asynchronous prefetch I/Os for the sequential pages, and synchronous I/Os for the random pages.

For example, if the cluster ratio for an index is 100% and a table is read in key-sequential order according to that index, all of the data pages are clustered and read sequentially. However, if the cluster ratio is somewhat less than 100%, then some of the pages are random and only those pages are read using synchronous I/Os. Dynamic prefetch works for both forward and backwards scans.

Because dynamic prefetch uses sequential detection, it is more adaptable to dynamically changing access patterns than sequential prefetch. Db2 uses dynamic prefetch in almost all situations, the main exception is for table space scans. Index scans always use dynamic prefetch.

List prefetch
Db2 uses list prefetch to read a set of data pages that is determined by a list of record identifiers (RIDs) from an index or from the Db2 log. Db2 also uses list prefetch to read non-consecutive index leaf pages which are determined from the non-leaf pages, and to read LOB pages which are determined from the LOB map. Unlike other types of prefetch, list prefetch does not use any kind of sequential detection. Instead, Db2 uses list prefetch in certain situations, such as the following examples:
  • Reading leaf pages of a disorganized index.
  • The optimizer chooses a list prefetch access path.
  • Fast log apply operations.
  • Incremental image copies.
  • Access to fragmented LOB data.
  • RUNSTATS table sampling.

Db2 uses the RID pool to process the RID list for list prefetch. The size of the RID pool is controlled by the value of the MAXRBLK subsystem parameter. If the RID pool is too small to contain the RID list processing for a list prefetch operation, a table space scan might be used instead.Db2 might use work files to continue processing the RID list if the size of the RID pool is too small. The use of work files for RID list processing is controlled by the value of the MAXTEMPS_RID subsystem parameter.

You can use the sequential steal threshold (VPSEQT) to protect randomly accessed pages in the buffer pool. It is beneficial to protect the random pages from the sequential pages, because it is generally faster to read sequential pages than random pages from disk, and sequential pages are less likely to be re-accessed.

Because all prefetch I/Os are executed under a service request block in the database services address space (ssnmDBM1), the I/O time for prefetch I/Os is asynchronous with respect class 2 CPU time. When a get page operation waits for a prefetch I/O to complete, the class 3 suspension time is captured as "other read I/O" suspension.

Prefetch CPU time is captured as system SRB time. CPU time for prefetch is usually small, but it can become significant for index scans because the compressed index pages are decompressed by the prefetch engine.

The number of pages read by prefetch

The following table shows the number pages read by prefetch for each asynchronous I/O for each buffer pool size (4 KB, 8 KB, 16 KB, and 32 KB).

Table 1. The number of pages read for each asynchronous I/O by prefetch, by buffer pool size
Buffer pool size Number of buffers Pages Read by Sequential and LOB List Prefetch Pages Read by Dynamic and Non-LOB list Prefetch Pages Read by Utility sequential Prefetch
4 KB VPSIZE < 224 8 8 16
225 < VPSIZE <1,000 16 16 32
1000 <= VPSIZE < 40,000
or
VPSIZE*VPSEQT < 40000
 32 32 64
40,000 <= VPSIZE*VPSEQT < 80,000 64 32 64
80,000 <= VPSIZE*VPSEQT 64 32 128
8 KB VPSIZE < 48 4 4 8
48 < VPSIZE <400 8 8 16
400 <= VPSIZE< 20,000  or  
VPSIZE*VPSEQT
< 20000
 16 16 32
20,000 <= VPSIZE*VPSEQT < 40,000 32 16 32
40,000 <= VPSIZE*VPSEQT 32 16 64
16 KB VPSIZE < 24 2 2 4
24 < VPSIZE < 200 4 4 8
200 <= VPSIZE< 10,000 or VPSIZE*VPSEQT < 10000 8 8 16
10,000 <= VPSIZE*VPSEQT < 20,000 16 8 16
20,000 <= VPSIZE*VPSEQT 16 8 32
32 KB VPSIZE < 12 1 1 2
12 < VPSIZE < 100 2 2 4
100 <= VPSIZE< 5,000
or
VPSIZE*VPSEQT < 5,000
 4 4 8
5,000 <= VPSIZE*VPSEQT < 10,000 8 4 8
10,000 <= VPSIZE*VPSEQT 8 4 16