In my previous column, I introduced you to some basic DB2 storage concepts and pointed out that network storage provisioning can have a significant effect on database performance. In this installment, I'll show you how to set an important storage I/O-related registry variable and how to fine-tune table-space characteristics, such as extent size, prefetch size, overhead, and transfer rate, when a DB2 database is deployed in a network-attached storage (NAS) or storage-area network (SAN) environment.

Table-Space Performance

If you read part 1 in this series, you know that RAID, which stands for redundant array of independent disks, is used to combine two or more disk drives into an array that can then be presented to a host as a single logical disk drive. If your database contains table spaces whose containers reside on a RAID device (which will most likely be the case if your database is deployed on NAS or SAN), DB2 experts at IBM recommend that you do the following:

• Set the DB2_PARALLEL_IO registry variable to enable parallel I/O for each table space used if the table space's containers span multiple physical disks.
• Make the extent size of each table space equal to, or a multiple of, the RAID stripe size if striping is used. (If RAID 1 is used, the default extent size is appropriate in most cases.)
• Ensure that the prefetch size for each table space is either assigned the value AUTOMATIC or is assigned a value that is both equal to the RAID stripe size multiplied by the number of RAID devices used (or a whole multiple of this product) and a multiple of the extent size of the table space.
• Ensure that the overhead and transfer rate for the table space is appropriate for the types of disk drives being used.

Keep in mind that in a NAS or SAN environment, IBM recommends that you use one of the following:

• Automatic resizing of database managed space (DMS) table spaces that use files for their storage containers (FILE)
• Automatic storage table spaces to store permanent data and system managed space (SMS)
• Automatic storage table spaces for temporary data.

DMS FILE and automatic storage table spaces are enabled for automatic resizing by specifying the AUTORESIZE ON option with either the CREATE TABLESPACE or the ALTER TABLESPACE SQL statement.

In decision-support and data warehouse environments, in which heavy report generation workloads are common, you can use DMS FILE table spaces to hold temporary data. However, these temporary table spaces should never be enabled for automatic resizing.

The DB2_PARALLEL_IO Variable

You can use the DB2_PARALLEL_IO registry variable to force DB2 to use parallel I/O for table spaces that only have one container, or for table spaces whose containers reside on more than one physical disk (which is the case if the container resides on a RAID 5 or a RAID 6 device). If this registry variable isn't set, the level of I/O parallelism used is equal to the number of containers used by the table space. Therefore, if a table space spans three containers and the DB2_PARALLEL_IO registry variable hasn't been set, the level of I/O parallelism used is 3.

On the other hand, if this registry variable is assigned a value, the level of I/O parallelism used is equal to the number of containers used multiplied by the value stored in the DB2_PARALLEL_IO registry variable — provided the prefetch size of the table space isn't AUTOMATIC. (In other words, the parallelism of the table space is equal to the prefetch size divided by the extent size of the table space.) Therefore, if the DB2_PARALLEL_IO registry variable has been set for a table space that has a prefetch size of 160 and an extent size of 32 pages, each prefetch request will be broken into five extent-sized prefetch requests (160 / 32 = 5).

Often, the DB2_PARALLEL_IO registry variable is assigned the asterisk (*) value to indicate that every table space in the database is to use parallel I/O. (The asterisk value implies that each table space container used spans six physical data disk spindles.)

However, in most cases, this setting isn't the correct one; using this value for table-space containers residing on anything other than RAID 5 6+1 arrays will result in a mismatch between the way DB2 attempts to parallelize I/O and the way data is actually striped across disks, which in turn will hurt performance. Instead, you should set the DB2_PARALLEL_IO registry variable by executing a db2set command that looks like this:

db2set DB2_PARALLEL_IO=[TS_ID]:[
DisksPerContainer] ,…

where:

• TS_ID identifies one or more individual table spaces by numeric table space ID. (An asterisk can be used to indicate all table spaces.)
• DisksPerContainer identifies the number of physical data disks used by each table space container that is assigned to the table space specified.

So, to set the DB2_PARALLEL_IO registry variable for a table space whose numeric ID is 1 to reflect that its storage containers reside on a RAID 5 3+1 array (three data disk spindles), you would execute a db2set command such as this:

db2set DB2_PARALLEL_IO=1:3

To set the DB2_PARALLEL_IO registry variable to indicate that the storage containers for every table space in the database reside on a RAID 5 4+1 array (four data disk spindles), you would execute a db2set command more like this:

db2set DB2_PARALLEL_IO=*:4

The num_ioservers Configuration Parameter

I/O servers, also called prefetchers, are used on behalf of database agents to perform prefetch I/O and asynchronous I/O for backup and other utilities. The num_ioservers database configuration parameter specifies the maximum number of I/O servers that can be in progress for a database at any given point in time. Non-prefetch I/Os are scheduled directly from database agents and, as a result, aren't constrained by the value assigned to the num_ioservers database configuration parameter.