Interpreting I/O Information in IIVWPROF View

Data is divided into I/O blocks (as configured on the [cbm] block_size parameter (see block_size). Whenever the query processing layer needs to access a given data block, a data_blocks counter is increased. If this block is not in memory, this causes a synchronous (blocking) load request, increasing the sync_io counter. At the same time, if the query knows it will require additional blocks in the future, an asynchronous (non-blocking) load (sometimes called look-ahead load or prefetching) for some future blocks is issued (this allows overlapping processing and I/O). If these blocks are not in memory, the async_io parameter is increased. Note that if the "synchronous" request for the same block is issued before the "asynchronous" look-ahead request is finalized, the sync_io counter for that block will also be increased.

This logic translates to the following "rules of thumb:"

• If async_io is zero (or low), the entire (or most) of the processed data is in memory and no I/O was performed. This is often a desired situation from the performance perspective.

• If async_io is high, close to data_blocks, the buffer pool might not be able to keep the used data in memory, causing extra I/O. Increasing [cbm] bufferpool_size (see bufferpool_size) may help.

• If sync_io is low, the "asynchronous" look-ahead loads are fulfilled fast, before particular data blocks are actually needed for processing. This means the query is CPU-bound.

• If sync_io is high, the I/O subsystem is not fast enough to fulfill the "asynchronous" look-ahead requests, making the query I/O-bound. This can often be addressed by using a storage system providing a higher I/O bandwidth.