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.
DB2 10.5 for Linux, UNIX, and Windows