DB2 performance considerations | Memory usage


Efficient I/O management


Reading from the database and writing back to the database (disk I/O) may become a bottleneck for any application accessing a database. Proper database layout can help reduce the potential for this bottleneck. It is a significant effort to change the physical layout of the database once it has been created. Hence, proper planning at the initial stages is important.

The first consideration is to ensure that the DB2 transaction logs reside on their own physical disk. Every update issued against the database is written to the logs (in addition to being updated in memory). Hence, there will be a lot of disk I/O in the location where the DB2 transaction logs reside. It is a good practice to try to ensure that all read/write activity on the disk is related only to the transaction logs, thus eliminating any I/O contention with other processes that may access the disk.

To set the location for the DB2 transaction logs, issue the following command:

db2 update db cfg for <dbalias> using NEWLOGPATH <path>

You will need to deactivate the database (disconnect all connected sessions or issue the db2 deactivate command) before the new location for the logs will be used.

Configuration parameters related to the log files can be found in 16.3.2, Parameters related to transaction logs.

The second consideration in terms of disk layout is to determine how to manage the tablespaces efficiently. One performance principle in the management of Relational Database Management Systems (RDBMs) is to separate the database table data and database index data onto different physical disks. This enables better query performance, since index scans can execute in parallel with data fetch operations because they are on different physical disks.

In DB2, two types of tablespaces can be defined:

The separation of table and index data can only be specified if database-managed storage tablespaces are defined. So should you always use DMS tablespaces? Not necessarily. In many installations, you may not have the luxury of many physical disks, and hence will not be able to separate data and index. Additionally, SMS tablespaces are easier to administer as compared to DMS tablespaces, so you may wish to trade off some performance for ease of use. A lot of up-front planning is required for DMS tablespaces, since the space is pre-allocated at creation time. SMS tablespaces allocate space as it is required by the database.

If you choose to stick with the default and use SMS tablespaces, there is a utility that you can run to improve write performance. Issue the following command:

db2empfa <dbalias>

After this command has been issued, as new space is required by the database, it will be allocated one extent at a time, as opposed to one page at a time.

If you choose to go with DMS tablespaces, in addition to redefining the three user tablespaces (USERPACE1, TAB8K, TAB16K), you will also need to define an additional 4 K tablespace for index data. You then have to modify the table definition in the schema creation file to point to the new tablespace. For example, if you chose to name the additional tablespace IND4K, then you would need to access the file:

WC_Install_Dir/schema/db2/wcs.schema.sql

You would replace the INDEX IN clause for every table definition to use IND4K. For example, consider the following coding:

CREATE TABLE acacgpdesc (
acactgrp_id          INTEGER NOT NULL, 
displayname          VARCHAR(254) NOT NULL,
description          VARCHAR(254),
language_id          INTEGER NOT NULL
)
IN USERSPACE1
INDEX IN USERSPACE1;

It changes as follows:

CREATE TABLE acacgpdesc (
acactgrp_id          INTEGER NOT NULL,
displayname          VARCHAR(254) NOT NULL,
description          VARCHAR(254),
language_id          INTEGER NOT NULL
)
IN USERSPACE1
INDEX IN IND4K;

This must be done prior to your WebSphere Commerce instance creation.

Note: Although you have different page sizes for your tablespaces for table data, you only need one size for the tablespace for index data, because all indexes created are less than 4 K in length.

On the same subject of achieving better disk I/O, read performance can be improved by spreading the tablespaces across many physical disks. When the database manager has to read from disk, if the data is stored across multiple disks, it can be read in parallel, yielding better read performance. This can be done in one of two ways:

A utility such as iostat on UNIX platforms can be used to identify disk I/O bottlenecks. On an AIX platform, you can install the nmon, which is a useful tool to analyze disk I/O.

Configuration parameters related to disk I/O can be found in 16.3.3, Parameters related to disk I/O.