DB2 Version 10.1 for Linux, UNIX, and Windows

Optimizing table space performance when data is on RAID devices

Follow these guidelines to optimize performance when data is stored on Redundant Array of Independent Disks (RAID) devices.

About this task

When creating a table space on a set of RAID devices, create the containers for a given table space (SMS or DMS) on separate devices.
Consider an example where you have fifteen 146 GB disks configured as three RAID-5 arrays with five disks in each array. For RAID-5, the space of one disk is used for parity information, which is spread over all disks in the array. Hard drive manufacturers define a gigabyte as 1,000,000,000 bytes, but most operating systems use the 2-based notation, which explains why a 146 GB disk shows up as a 136 GB disk. After initializing the RAID-5 arrays, each array can store approximately 544 GB ¹. If you have a table space that requires 300 GB of storage, create three containers, and put each container on a separate array. Each container uses 100 GB (300 GB/3) on a device, and there are 444 GB (544 GB - 100 GB) left on each device for additional table spaces.

Note: ¹ (146,000,000,000 bytes/1024/1024/1024) = 135.97 GB, (5*136 GB - 1*136 GB) = 544 GB. Depending on the file system, which is placed on the array, additional space might be used for meta data and for the internal file system structure.
Select an appropriate extent size for the table spaces. The extent size for a table space is the amount of data that the database manager writes to a container before writing to the next container. Ideally, the extent size should be a multiple of the underlying segment size of the disks, where the segment size is the amount of data that the disk controller writes to one physical disk before writing to the next physical disk. Choosing an extent size that is a multiple of the segment size ensures that extent-based operations, such as parallel sequential read in prefetching, do not compete for the same physical disks. Also, choose an extent size that is a multiple of the page size.
In the example, if the segment size is 64 KB and the page size is 16 KB, an appropriate extent size might be 256 KB.
Use the DB2_PARALLEL_IO registry variable to enable parallel I/O for all table spaces and to specify the number of physical disks per container.
For the situation in the example, set DB2_PARALLEL_IO = *:5.
If you set the prefetch size of a table space to AUTOMATIC, the database manager uses the number of physical disks value that you specified for DB2_PARALLEL_IO to determine the prefetch size value. If the prefetch size is not set to AUTOMATIC, you can set it manually, taking into account the RAID stripe size, which is the value of the segment size multiplied by the number of active disks. Choose a prefetch size value that meets the following conditions:
- It is equal to the RAID stripe size multiplied by the number of RAID parallel devices (or a whole number representation of this product).
- It is a whole number representation of the extent size.
In the example, you might set the prefetch size to 768 KB. This value is equal to the RAID stripe size (256 KB) multiplied by the number of RAID parallel devices (3). It is also a multiple of the extent size (256 KB). Choosing this prefetch size means that a single prefetch will engage all the disks in all the arrays. If you want the prefetchers to work more aggressively because your workload involves mainly sequential scans, you can instead use a multiple of this value, such as 1536 KB (768 KB x 2).
Do not set the DB2_USE_PAGE_CONTAINER_TAG registry variable. As described earlier, you should create a table space with an extent size that is equal to, or a multiple of, the RAID stripe size. However, when you set DB2_USE_PAGE_CONTAINER_TAG to ON, a one-page container tag is used, and the extents do not line up with the RAID stripes. As a result, it might be necessary during an I/O request to access more physical disks than would be optimal.

Procedure

When creating a table space on a set of RAID devices, create the containers for the table space (SMS or DMS) on separate devices.
Consider an example where you have fifteen 146 GB disks configured as three RAID-5 arrays with five disks in each array. After formatting, each disk can hold approximately 136 GB of data. Each array can therefore store approximately 544 GB (4 active disks x 136 GB). If you have a table space that requires 300 GB of storage, create three containers, and put each container on a separate device. Each container uses 100 GB (300 GB/3) on a device, and there are 444 GB (544 GB - 100 GB) left on each device for additional table spaces.
Select an appropriate extent size for the table spaces.
The extent size for a table space is the amount of data that the database manager writes to a container before writing to the next container. Ideally, the extent size should be a multiple of the underlying segment size of the disks, where the segment size is the amount of data that the disk controller writes to one physical disk before writing to the next physical disk. Choosing an extent size that is a multiple of the segment size ensures that extent-based operations, such as parallel sequential read in prefetching, do not compete for the same physical disks. Also, choose an extent size that is a multiple of the page size.

In the example, if the segment size is 64 KB and the page size is 16 KB, an appropriate extent size might be 256 KB.
Use the DB2_PARALLEL_IO registry variable to enable parallel I/O for all table spaces and to specify the number of physical disks per container.
For the situation in the example, set DB2_PARALLEL_IO = *:4.
If you set the prefetch size of a table space to AUTOMATIC, the database manager uses the number of physical disks value that you specified for DB2_PARALLEL_IO to determine the prefetch size value. If the prefetch size is not set to AUTOMATIC, you can set it manually, taking into account the RAID stripe size, which is the value of the segment size multiplied by the number of active disks. Choose a prefetch size value that meets the following conditions:
- It is equal to the RAID stripe size multiplied by the number of RAID parallel devices (or a whole number representation of this product).
- It is a whole number representation of the extent size.
In the example, you might set the prefetch size to 768 KB. This value is equal to the RAID stripe size (256 KB) multiplied by the number of RAID parallel devices (3). It is also a multiple of the extent size (256 KB). Choosing this prefetch size means that a single prefetch engages all the disks in all the arrays. If you want the prefetchers to work more aggressively because your workload involves mainly sequential scans, you can instead use a multiple of this value, such as 1536 KB (768 KB x 2).
Do not set the DB2_USE_PAGE_CONTAINER_TAG registry variable. As described earlier, you should create a table space with an extent size that is equal to, or a multiple of, the RAID stripe size. However, when you set DB2_USE_PAGE_CONTAINER_TAG to ON, a one-page container tag is used, and the extents do not line up with the RAID stripes. As a result, it might be necessary during an I/O request to access more physical disks than would be optimal.