Parallel I/O management

If multiple containers exist for a table space, the database manager can initiate parallel I/O, whereby the database manager uses multiple I/O servers to process the I/O requirements of a single query.

Each I/O server processes the I/O workload for a separate container, so that several containers can be read in parallel. Parallel I/O can result in significant improvements in I/O throughput.

Although a separate I/O server can handle the workload for each container, the actual number of I/O servers that can perform parallel I/O is limited to the number of physical devices over which the requested data is spread. For this reason, you need as many I/O servers as physical devices.

Parallel I/O is initiated differently in the following cases:
  • For sequential prefetching, parallel I/O is initiated when the prefetch size is a multiple of the extent size for a table space. Each prefetch request is divided into smaller requests along extent boundaries. These small requests are then assigned to different I/O servers.
  • For readahead prefetching, each list of data pages is divided into smaller lists according to extent boundaries. These small lists are then assigned to different I/O servers.
  • For list prefetching, each list of pages is divided into smaller lists according to the container in which the data pages are stored. These small lists are then assigned to different I/O servers.
  • For database or table space backup and restore, the number of parallel I/O requests is equal to the backup buffer size divided by the extent size, up to a maximum value that is equal to the number of containers.
  • For database or table space restore, the parallel I/O requests are initiated and divided the same way as what is done for sequential prefetching. The data is not restored into a buffer pool; it moves directly from the restore buffer to disk.
  • When you load data, you can specify the level of I/O parallelism with the DISK_PARALLELISM command option. If you do not specify this option, the database manager uses a default value that is based on the cumulative number of table space containers for all table spaces that are associated with the table.
For optimal parallel I/O performance, ensure that:
  • There are enough I/O servers. Specify slightly more I/O servers than the number of containers that are used for all table spaces within the database.
  • The extent size and the prefetch size are appropriate for the table space. To prevent overuse of the buffer pool, the prefetch size should not be too large. An ideal size is a multiple of the extent size, the number of physical disks under each container (if a RAID device is used), and the number of table space containers. The extent size should be fairly small, with a good value being in the range of 8 to 32 pages.
  • The containers reside on separate physical drives.
  • All containers are the same size to ensure a consistent degree of parallelism.
    If one or more containers are smaller than the others, they reduce the potential for optimized parallel prefetching. Consider the following examples:
    • After a smaller container is filled, additional data is stored in the remaining containers, causing the containers to become unbalanced. Unbalanced containers reduce the performance of parallel prefetching, because the number of containers from which data can be prefetched might be less than the total number of containers.
    • If a smaller container is added at a later date and the data is rebalanced, the smaller container will contain less data than the other containers. Its small amount of data relative to the other containers will not optimize parallel prefetching.
    • If one container is larger and all of the other containers fill up, the larger container is the only container to store additional data. The database manager cannot use parallel prefetching to access this additional data.
  • There is adequate I/O capacity when using intrapartition parallelism. On SMP machines, intrapartition parallelism can reduce the elapsed time for a query by running the query on multiple processors. Sufficient I/O capacity is required to keep each processor busy. Additional physical drives are usually required to provide that I/O capacity.

    The prefetch size must be larger for prefetching to occur at higher rates, and to use I/O capacity effectively.

    The number of physical drives required depends on the speed and capacity of the drives and the I/O bus, and on the speed of the processors.