Team with the best
Db2® LUW Performance Tools
company in the World

DB2 LUW Performance: Write I/O Optimization

April 15, 2009, 10:18 pm
Posted by Scott in DB2 Performance How-To
A student who took this class last week at WDUG emailed me the day after to thank me. He said he enjoyed the class and successfully reduced the elapsed time of a troublesome query from two hours to three minutes! If you missed the WDUG session, I hope you will be able to join my Ed Seminar at IDUG "DB2 LUW Performance Diagnosis Learning Lab". This post covers topics related to optimizing Write I/O performance.
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

Brother-Panther Logo 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 DB2 GOLD Consultant
Your Performance IS Our Business

Printer friendly