To enable prefetching, the database manager starts separate threads of control, known as I/O servers, to perform page reading. As a result, the query processing is divided into two parallel activities: data processing (CPU) and data page I/O. The I/O servers wait for prefetch requests from the CPU processing activity. These prefetch requests contain a description of the I/O needed to satisfy the anticipated data needs. The reason for prefetching determines when and how the database manager generates the prefetch requests. (See "Understanding Sequential Prefetching" and "Understanding List Prefetching" for more information.)
The following figure illustrates how I/O servers are used to prefetch data into a buffer pool.
Figure 56. Prefetching Data using I/O Servers
The following steps are illustrated in Figure 56:
Configuring enough I/O servers with the num_ioservers configuration parameter can greatly enhance the performance of queries for which prefetching of data can be used. Having some extra I/O servers configured will not hurt performance because extra I/O servers are not used and their memory pages will get paged out. Each I/O server process is numbered and the database manager will always use the lowest numbered process that is available and, as a result, some of the upper numbered processes may never be used.
To determine the number of I/O servers that you should configure, consider the following:
For situations in which multiple containers exist for a table space, the database manager can initiate parallel I/O. Parallel I/O refers to the ability of the database manager to use multiple I/O servers to process the I/O requirements of a single query. Each I/O server is assigned the I/O workload for a separate container, allowing several containers to be read in parallel. Performing I/O in parallel can result in significant improvements to I/O throughput.
While a separate I/O server will handle the workload for each container, the actual number of I/O servers that can perform I/O in parallel will be limited to the number of physical devices over which the requested data is spread. This also means you need as many I/O servers as the number of physical devices.
How parallel I/O is initiated and used is dependent on the reason for performing the I/O:
For sequential prefetch, parallel I/O is initiated when the prefetch size is a multiple of the extent size for a table space. Each prefetch request is then broken into multiple, smaller, requests along the extent boundaries. These smaller requests are then assigned to different I/O servers.
For list prefetch, each list of pages is divided into smaller lists according to the container in which the data pages are stored. These smaller lists are then assigned to different I/O servers.
For backing up or restoring data, the number of parallel I/O requests are equal to the backup buffer size divided by the extent size up to a maximum value equal to the number of containers.
For restoring data, the parallel I/O requests are initiated and split in a manner that is the same as that used for sequential prefetch. Instead of restoring the data into the buffer pool, the data is moved directly from the restore buffer to disk.
When loading data you can specify the level of I/O parallelism with the LOAD command's DISK_PARALLELISM option. (If it is not specified, a default is used based on the cumulative number of table space containers for all table spaces associated with the table.)
For optimal performance of parallel I/O, ensure that:
If one or more containers are smaller than the others, they will reduce the potential for optimized parallel prefetch. For example:
Prefetching must occur at higher rates to use I/O capacity effectively. The prefetch size should be higher for prefetching to occur at higher rates. The prefetch size should be a multiple of the extent size and the number of table space containers. Ideally, containers should be configured to reside on separate physical drives.
The number of physical drives required could depend on the speed and capacity of the drives and the I/O bus, and on the speed of the processors.
The database manager expands the database as needed when the multipage_alloc database configuration parameter is set to YES. (See "MultiPage File Allocation Enabled (multipage_alloc)".) The default value for this parameter is NO, causing the database manager to expand the database file one page at a time.
To set multipage_alloc to YES (and gain the associated performance improvement), use the db2empfa tool in the sqllib/bin directory. (For more information on this tool, see the Command Reference.)
[ DB2 List of Books | Search the DB2 Books ]