As a brief review, DB2 basically does two kinds of I/O: Synchronous and Asynchronous. The application connection must wait for Synchronous I/O to complete before continuing processing, but Asynchronous I/O events can be preferable because they allow the application connection to continue processing without I/O wait delay.

In the blog post on Synchronous Read Percentage (SRP), I indicated that a high percentage of Synchronous reads was preferable for OLTP databases. In contrast, it is ALWAYS desirable to do a high percentage of Asynchronous Write I/O. Not only do Asynchronous Write I/Os avoid delaying the application connection, but they also tend to be 3 to 10 times faster depending on your disks and storage system.

AWP - The Asynchronous Write Percentage

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

It is desirable to have AWP > 90%. However, if your database has very little Insert, Update, or Delete activity, then achieving a high AWP will be difficult, if not impossible, to do. On the other hand, if your database has DMLTX >= 1, then achieving a high AWP value is very important.

To achieve a high AWP, you need to have a sufficient number of I/O Cleaners (DB CFG NUM_IOCLEANERS) and not have the Changed Pages Threshold set too high (DB CFG CHNGPGS_THRESH).

I/O Cleaners

Back in the old days (V8.2 and earlier), the default number of I/O cleaners was set to 1. With DB2 9, NUM_IOCLEANERS is set to AUTOMATIC by default. This basically sets the number of I/O cleaners equal to the number of CPUs, which may be too aggressive. Please note, we've gone from one extreme to the other (too few to possibly too many). If ALL of your CPUs are busy cleaning dirty pages out of the bufferpools, then what CPUs will be left to pay attention to your user or application queries? Therefore, consider setting NUM_IOCLEANERS equal to the number of CPU cores, divided by the number of partitions, minus one, but not less than one.

Changed Pages Threshold

The Changed Pages Threshold (DB CFG CHNGPGS_THRESH) is 60% by default. So, when 60% of the pages in the bufferpool are dirty with updates, the NUM_IOCLEANERS wake up and start shoveling your updated data and index pages out to disk. On some systems, this can create a periodic rolling "Brown Out" effect where transaction response times take a temporary nose dive while the NUM_IOCLEANERS are busily, asynchronously, shoveling updated pages to disk. The periodic rolling "Brown Out" effect can be mitigated by reducing CHNGPGS_THRESH from 60% downward towards 30-40%. Most customers I've worked with have very good results with 40%.

Putting it all together - Rule of Thumb

As general rules of thumb, to achieve a 90%+ AWP for a database having DMLTX > 1:

1. Increase NUM_IOCLEANERS upward from 1 (the old default) by increments of 1 until NUM_IOCLEANERS reaches (#CPUs/Partitions)-1.
   
2. If the optimum number of NUM_IOCLEANERS doesn't get you to 90%+ AWP, then gradually decrease CHNGPGS_THRESH in 5% decrements until 90%+ AWP is reached.
   

Some additional tips for improving write I/O performance:

1. LOGBUFSZ needs to be properly set to an adequate size. Refer to this blog post.
   
2. Compute the AWP and OWMS (Overall Write ms time) for the database and ALL of your tablespaces. Refer to this blog post on OWMS for details and tips.
   
3. With Automatic Storage and DMS tablespaces, you can place table data in one tablespace, index data in another tablespace, and data for LONG/LOB objects in yet an optional third tablespace. Separating these types of objects will allow you to assign different bufferpools to each tablespace, and place your objects accordingly on your fastest storage devices.
   

If you are unable to attend IDUG North America this year and would still like to improve the performance of your databases, DBI is offering free DB2 LUW Tuning Webinars every month. The next Webinar is May 8th, 2008, at 9am CDT - "Extreme Makeover: DB2 LUW Physical Design Edition". View all DBI Events or Register Now.

The Shameless Marketing Moment

DBI's Brother-Panther™ automatically computes AWP, ORMS, OWMS, SRP, IREF, TBRRTX, TBROVP, and dozens more performance metrics for you. Brother-Panther also allows you to click on a table name and quickly find the SQL statements that are driving the I/O to a particular table. You will quickly see the SQL statements that are causing I/O problems for a table, and be able to easily rectify performance problems using integrated Explain and DB2 Advisor tools.

Just for Fun

No matter what your political orientation is, this would be a very good year for you to exercise your right to vote if you are a US citizen. To vote smartly, you need to be informed about the candidates, their positions, and news. There are many good sources of information available - my political "Home Page" is www.cnn.com/POLITICS/ - perhaps you will find this helpful. Choose Wisely.

Cheers,
Scott

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant
www.Database-Performance.info
www.Database-Auditing.info
Your Performance IS Our Business

Please Note: DBI has moved to a new web address!