DB2 10.5 for Linux, UNIX, and Windows

Prefetch size adjustment when adding or dropping containers

The default size for all prefetches from disk is set automatically for any table spaces created using DB2® versions 8.2 and later. This means that the database manager calculates a suitable prefetch size based on several factors, including the extent size, the number of containers in your table space, and the properties of your storage devices.

The degree to which prefetches of data can take place in parallel is a function of, among other things, the number of containers in a table space. For example, if you have two or more containers, then prefetches from each container can happen in parallel, which can improve overall database performance. If you change the number of containers in a table space by adding or dropping containers, the amount of data that you can efficiently prefetch might change. For example if you add a container, but the number of extents prefetched remains unchanged, then you might not be taking advantage of the opportunity to fetch additional data from the new container in parallel with that from the other containers. As containers are added or dropped, adjusting the prefetch size accordingly can maintain or improve performance by making I/O happen more efficiently.

You can set the prefetch size for table spaces manually, but once you do so, you must ensure that you update it as you change the containers in your table space if you want to maintain optimal prefetch performance. You can eliminate the need to update the prefetch size manually by setting PREFETCHSIZE for the table space to AUTOMATIC when using the CREATE TABLESPACE or ALTER TABLESPACE statements. AUTOMATIC is the default value for PREFETCHSIZE, unless you have modified the default value for the dft_prefetch_sz configuration parameter.

If you want to manually specify the prefetch size, you can do so in three ways:

Create the table space with a specific prefetch size. If you manually choose a value for the prefetch size, you need to remember to adjust the prefetch size whenever there is an adjustment in the number of containers associated with the table space.
When the dft_prefetch_sz database configuration parameter set to a value other than the default value of AUTOMATIC, omit the prefetch size when creating the table space. The database manager checks this parameter when there is no explicit mention of the prefetch size when creating the table space. If a value other than AUTOMATIC is found, then that value is what is used as the default prefetch size. You need to remember to adjust, if necessary, the prefetch size whenever there is an adjustment in the number of containers associated with the table space.
Alter the prefetch size manually by using the ALTER TABLESPACE statement.

When manually adjusting the prefetch size, specify a size that corresponds to a disk stripe for optimal I/O parallelism. To calculate the prefetch size manually, use the formula:

number_of_containers × number_of_disks_per_container × extent_size

For example, assume the extent size for a database is 8 pages, and that there are 4 containers, each of which exists on a single physical disk. Setting the prefetch size to: 4 × 1 × 8 = 32 results in a prefetch size of 32 pages in total. These 32 pages will be read from each of the 4 containers in parallel.

If you have more than one physical disk per container, as you might if each container is made up of a RAID array, then to optimize I/O parallelism, ensure that the DB2_PARALLEL_IO registry variable is set correctly. (See Parallel I/O for table space containers that use multiple physical disks.) As you add or drop containers, if the prefetch size has been set manually, remember to update it to reflect an appropriate prefetch size. For example, assume each of 4 containers resides on a RAID 4+1 array, and the DB2_PARALLEL_IO registry variable has been set to allow for parallel prefetches from each physical disk. Assume also an extent size of 8 pages. To read in one extent per container, you would set the prefetch size to 4 × 4 × 8 = 128 pages.

Parallel I/O for table space containers that use multiple physical disks

Before the prefetch requests are submitted to the prefetch queues, they are broken down into a number of smaller, parallel prefetch requests, based on the number of containers in a table space. The DB2_PARALLEL_IO registry variable is used to manually override the parallelism of prefetch requests. (This is sometimes referred to as the parallelism of the table space). When DB2_PARALLEL_IO is set to NULL, which is the default, the parallelism of a table space is equal to the number of containers in the table space. If this registry variable is turned on, it defines the number of physical disks per container; the parallelism of a table space is equal to the number of containers multiplied by the value given in the DB2_PARALLEL_IO registry variable.

What follows are several other examples of how the DB2_PARALLEL_IO registry variable influences the parallelism of prefetches. Assume that table spaces have been defined with an AUTOMATIC prefetch size.

DB2_PARALLEL_IO=NULL
- Prefetching from table space containers is done in parallel, based on a combination of the following:
  - The number of containers in each table space
  - The size that was specified for prefetches on the CREATE or ALTER TABLESPACE statements, and in the dft_prefetch_sz configuration parameter.
- Prefetches are not broken down into smaller, per-disk requests. If there are multiple physical disks associated with a container, prefetches from the disks for a single container will not take place in parallel.
DB2_PARALLEL_IO=*
- All table spaces use the default number of spindles (6) for each container. The prefetch size is 6 times larger with parallel I/O on.
- All table spaces have parallel I/O on. The prefetch request is broken down to several smaller requests, each equal to the prefetch size divided by the extent size (or equal to the number of containers times the number of spindles).
DB2_PARALLEL_IO=*:3
- All table spaces use 3 as the number of spindles per container.
- All table spaces have parallel I/O on.
DB2_PARALLEL_IO=*:3,1:1
- All table spaces use 3 as the number of spindles per container except for table space 1, which uses 1.
- All table spaces have parallel I/O on.