Part 1  |  Part 2  |  Part 3

Other Settings

MINCOMMIT

MINCOMMIT is a powerful tuning tool for OLTP databases that process high volumes of transactions per second. When properly set, I/Os to DB2 logs are grouped together — resulting in fewer log I/Os. To set MINCOMMIT, determine how many transactions per second the database is performing, and divide this value by 10:

Commits + Rollbacks (Transactions) / 10 = MINCOMMIT

An example: If MINCOMMIT is set to 5, then five transactions must be ready before the commit will be performed. DB2 will wait up to one second for the five transactions to be ready.

In a DW database, set MINCOMMIT to 1. Also, be aware that MINCOMMIT > 1 can be painful if there are not enough concurrent transactions per second to warrant it. One client accidentally added six hours elapsed time to a Siebel batch process that did frequent commits by merely setting MINCOMMIT > 1.

LOGBUFSZ

The default LOGBUFSZ value of 8 is too small. This buffer should be increased to at least 256 for most OLTP databases.

DW databases can benefit from extra LOGBUFSZ memory, too. Try setting it to 128.

INTRA_PARALLEL

For an OLTP database, set the database manager configuration value for INTRA_PARALLEL to NO. Using CPU parallelism to have Coordinator Agents manage Sub-Agents in pursuit of retrieving a small answer set for well-tuned and indexed SQL would be a waste of CPU resources and can slow down transaction throughput rates.

In a DW database, you nearly always want to use an INTRA_PARALLEL value of YES to enable CPU parallelism. And be sure to set the database configuration parameter DFT_DEGREE to the value of ANY or -1. As such, DB2 will compute the degree of parallelism based on the number of system CPUs and other available resources.

MAX_QUERYDEGREE

Because parallelism is generally undesirable in well-tuned OLTP databases, set MAX_QUERYDEGREE to 1.

A proactive, cautious DBA for a DW database will set MAX_QUERYDEGREE to a value equal to the number of CPUs on the system. Doing so prevents rogue users from “accidentally” setting their CURRENT DEGREE too high. We’ve seen disastrous results when users try to inflate their CURRENT DEGREE in excess of the number of CPUs.

SHEAPTHRES and SORTHEAP

The database manager configuration parameter SHEAPTHRES works in concert with the database configuration parameter SORTHEAP to govern sort memory. SHEAPTHRES dictates a soft limit for total sort memory used by an entire instance, and SORTHEAP controls the limit on memory for any one sort. The respective DB2 Unix default values of 20,000 and 256 provide for up to 78 (20000/256) concurrent 1MB sorts. OLTP databases should perform as few sorts as possible, and the sorts should be very small. We’ve seen a simple two-column sort of two to five rows consume 33 percent of the CPU time on a machine with four CPUs. The key to avoiding sorts is having the right clustering indexes defined. A well-tuned OLTP database can use a SORTHEAP value as small as 128. That value results in double the number of concurrent sorts without increasing the SHEAPTHRES memory. We’ll say more about SQL, indexes, and clustering later.

DW databases perform a lot of sorts, and many of them can be very large. SORTHEAP memory is also used for hash joins, which an alarming number of DB2 users fail to enable. To do so, use the db2set command to set environment variable DB2_HASH_JOIN=ON. For a DW database, at a minimum, double or triple the SHEAPTHRES (to between 40,000 and 60,000) and set the SORTHEAP size between 4,096 and 8,192. If real memory is available, some clients use even larger values for these configuration parameters.

FCM_NUM_BUFFERS

This parameter specifies the number of 4K buffers created for the Fast Communications Manager (FCM) buffer pool. With intrapartition parallelism enabled or when using Enterprise-Extended Edition (EEE), FCM manages the communications between parallel agents. A shortage of FCM_NUM_BUFFERS can cause serious performance degradation (DB2 may stop responding). Error messages indicating FCM resource shortages are written to the db2diag.log file.

FCM resources can be monitored via database snapshot monitoring. Start with the defaults, but monitor frequently. Use the following formulas to determine whether or not to increase the number of buffers, anchors, entries, or request blocks:

Free FCM buffers low water mark / Free FCM buffers * 100 = Percentage of FCM Buffers
Used
Free FCM message anchors low water mark/Free FCM message anchors * 100 = Percentage
of FCM Message Anchors UsedFree FCM connection entries low water mark / Free FCM connection entries * 100
= Percentage of FCM Connection Entries Used
Free FCM request blocks low water mark / Free FCM request blocks * 100 =
Percentage of FCM Request Blocks Used

If any of the results are less than 15 percent of the Free initial allocation, increase the initial allocation until all low water marks are greater than or equal to 15 percent of the initial Free allocation. This allocation will ensure that adequate FCM resources are always available.

Because we don’t recommend the use of intrapartition parallelism with OLTP, FCM buffers don’t come into play. You’ll want to make sure you set INTRA_PARALLEL to NO. And, you can free up an AIX memory segment for buffer pool or other use by setting the registry variable DB2_FORCE_FCM_BP=NO.

DFT_QUERYOPT

The SQL executed by OLTP databases should be short, sweet, relatively simple, properly indexed, and well tuned. Selecting an access strategy (plan) shouldn’t take much DB2 brainpower. Set the database configuration parameter DFT_QUERYOPT to 1 so that DB2 spends minimal time preparing its access strategy. (The default value is 5.) Spending a few seconds thinking about the SQL access strategy when actual execution only takes a quarter of a second is a waste. The smaller the value, the less time DB2 spends pondering its plan. SQL execution begins sooner.

It’s a different story for data warehousing. SQL in a DW database is very complex and often consumes large quantities of CPU and I/O resources, so a few extra seconds of time spent preparing the SQL access strategy could be a very wise investment, especially if it yields an access strategy that trims minutes or hours off of a query’s elapsed time. In a DW database, set DFT_QUERYOPT to 7 or 9.

If your OLTP database is blessed with occasional decision-support queries, use a compromise value of 3 (or keep the default 5) for DFT_QUERYOPT.

CHNGPGS_THRESH

The default value for CHNGPGS_THRESH is 60 percent, meaning that when 60 percent of the pages in the buffer pools become dirty, then the NUM_IO_CLEANERS begin asynchronously writing the changed pages out to disk. For a DW database, the default usually delivers good results.

For a high-transaction volume OLTP database that runs a lot of DML SQL (such as INSERTS, UPDATES, and D ELETE s), lowering the CHNGPGS_THRESH from 60 percent to 50 or 40 percent can be beneficial. If you leave it at 60 percent, more than half of all buffer pool pages are dirty before DB2 starts writing them to disk. And, when that threshold is reached, some users experience a spike in transaction elapsed times while this surge-like tidal wave of write I/O to disk occurs. By lowering the CHNGPGS_THRESH, the tidal wave of write I/Os is smaller and transaction response times remain more consistent. I’m not aware of any DB2 customer that has this set lower than 30 percent, however, so don’t overdo it.

NUM_IO_CLEANERS

Performance experience tells us that asynchronous write I/Os are usually at least twice as fast as synchronous write I/Os, so it’s important to try to achieve an asynchronous write percentage (AWP) of 90 or higher. The formula for AWP is:

AWP = ( (Asynchronous pool data page writes + Asynchronous pool index page
writes ) x 100 ) / ( Buffer pool data writes + Buffer pool index writes )

This formula applies equally well to database, buffer pool, and tablespace snapshot data. The default value of 1 is rarely sufficient. I usually suggest that people should increase the number of NUM_IO_CLEANERS by one until either 90 percent of writes are performed asynchronously or the number of NUM_IO_CLEANERS is equal to the number of CPUs on the DB2 server. If the latter limit is reached, consider making gradual reductions in CHNGPGS_THRESH until AWP is greater than 90, but set CHNGPGS_THRESH no lower than 30 percent.

In a DW database, DB2 uses NUM_IO_CLEANERS for writing to TEMPSPACE, temporary intermediate tables, index creations, and more. Set NUM_IO_CLEANERS equal to the number of CPUs on the DB2 server and focus on your disk layout strategy instead.

NUM_IO_SERVERS

To achieve maximum parallelism in a DW database, it’s imperative to have enough NUM_IO_SERVERS available, but not too many. IO_SERVERS are used to prefetch data into DB2’s buffer pools. To set NUM_IO_SERVERS, add up the number of physical disk devices (arms) on the DB2 server and use that value, but don’t use more than four to six times the number of CPUs.

A well-tuned OLTP application database shouldn’t need to perform extreme I/O parallelism. Set NUM_IO_SERVERS equal to the number of CPUs so that prefetch Agents may be available for occasional ad hoc DW queries, but don’t set it to less than the default value of three.

RAID Disk and Parallelism

In a DW environment using regular SCSI or IDE disk drives, tablespaces should have multiple containers on different disks. For RAID devices where several disks appear as one to the operating system, be sure to do the following:

1. db2set DB2_STRIPED_CONTAINERS=YES (do this before creating tablespaces or before a redirected restore)
2. db2set DB2_PARALLEL_IO=* (or use TablespaceID numbers for tablespaces residing on the RAID devices — for example DB2_PARALLEL_IO=4,5,6,7,8,10,12,13 )
3. Alter the tablespace PREFETCHSIZE for each tablespace residing on RAID devices such that the PREFETCHSIZE is a multiple of the EXTENTSIZE. Most companies use a multiple of three to five. Four is my favorite.

Proper container placement and alignment of the extent with the raid stripe size can reduce I/O times by 50 percent. Remember, I/O is still the slowest component in the transaction mix.

--

Scott Hayes is a well known DB2 UDB performance expert. He is a regular speaker at International DB2 User Group conferences, IBM DB2/Data Management conferences, and is frequently sought as a guest speaker for regional DB2 user group meetings. Scott has authored several published articles on DB2 UDB performance, is an IBM DB2 GOLD Consultant (an elite, exclusive group of IBM recognized top DB2 advocates), has obtained Advanced IBM DB2 Certifications, and is widely regarded by the worldwide DB2 community as the top performance expert for DB2 on distributed (UNIX, Linux, and Windows) platforms. Most recently, he co-founded Database-Brothers Incorporated (DBI), a group of database performance and accountability specialists.

Philip Gunning started Gunning Technology Solutions, LLC in 2003. He is a Principal Consultant specializing in DB2 with Gunning Technology Solutions. You might even call him a DB2 Evangelist. Phil has over 17 years experience in the Information Technology industry. Prior to starting his own company, Phil held senior positions with DGI and others,and has consulted with Fortune 500 companies and with government. Phil has presented at IDUG North America, IDUG Europe, and DB2 User Groups all over North America. You can reach him at pgunning@breakthroughdb2.com