DB2 LUW Performance: Direct I/O Times


Posted by Scott on April 15, 2009, 9:49 pm
in DB2 Performance Metrics ( DB2 Performance)

Returning our attention to the question "Where does the time go?", we need to look at Direct I/O times. Direct I/O is I/O that occurs directly to disk without an intermediate visit or presence in the Bufferpools. Direct I/O is used by DB2 in support of LONG and LOB objects. Even if you think you are not using LONG and LOB objects, you are implicitly using them as these data types are found throughout the DB2 catalog.
Returning our attention to the question "Where does the time go?", we need to look at Direct I/O times. Direct I/O is I/O that occurs directly to disk without an intermediate visit or presence in the Bufferpools. Direct I/O is used by DB2 in support of LONG and LOB objects. Even if you think you are not using LONG and LOB objects, you are implicitly using them as these data types are found throughout the DB2 catalog.
DRIOMS - The average time (ms) required to complete a Direct Read

DRIOMS = Direct reads elapsed time (ms) / Direct Reads

DWIOMS - The average time (ms) required to complete a Direct Write

DWIOMS = Direct write elapsed time (ms) / Direct Writes

To compute DRIOMS and DWIOMS, use Database Snapshots ('dbsnap2.txt'), Bufferpool Snapshots ('bpsnap2.txt'), and Tablespace Snapshots ('tssnap2.txt'). First, find the average for the database for each metric for the database overall. Second, compute DRIOMS and DWIOMS for each tablespace. If a value for any given tablespace is significantly greater than the average for the database overall, then you have yourself a genuine "opportunity for improvement" on your hands. Examine the containers for the slowest tablespaces and see if they can be relocated to faster storage devices.

DRIOMSTX - The average amount of Direct Read I/O time consumed per database transaction

DRIOMSTX = (Direct reads elapsed time (ms) / (Commit statements attempted + Rollback statements attempted))

DWIOMSTX - The average amount of Direct Write I/O time consumed per database transaction

DWIOMSTX = (Direct write elapsed time (ms) / (Commit statements attempted + Rollback statements attempted))

To compute DRIOMSTX and DWIOMSTX, use a Database Snapshot ('dbsnap2.txt'). These two metrics will tell you how much time each database transaction is spending on performing Direct Reads and Direct Writes, respectively.

If you relocate tablespace containers to faster storage devices, you should be able to measure improvements for all four of the direct I/O time measurements.

The Shameless Marketing Moment

I just returned from IDUG Australia in Sydney where I taught the class "DB2 LUW Performance Diagnosis Learning Lab" which is the foundation of material for some of these blog posts. 100% of all class participants evaluated the class solidly "Excellent" in every category. It is incredibly valuable to all participants to work through these numbers together, understand why the metrics are important, and to be able to compare performance numbers with your peers. Relevance?

The "DB2 LUW Performance Diagnosis Learning Lab" is being offered at the Wisconsin DB2 User Group special meeting next month on April 9th. The fee is only $225. If you live in Wisconsin, Chicago, or the Twin Cities area, please support WDUG and attend this event! Details are available at the WDUG Web Site.

If you cannot make the WDUG class for only $225, the Ed Seminar "DB2 LUW Performance Diagnosis Learning Lab" is also being offered at IDUG North America for only $425. ( Quick! Wisconsin people do the math! )

During these classes, you will evaluate the performance of your own organization's databases, you will learn where the problem areas are, and you will learn things you can do to improve the performance of your databases! At the end of every class, I always ask participants if the session was valuable, if they learned something... everyone agrees "Great Class!" If you are reading this blog and you have attended one of my Ed Seminars in the past, please post a comment that will help your peers make the justification to management.

Just For Fun

Is saving money or cutting costs on your to-do list this year? Check out the Money Saving Ideas from Frugal Moms to help cut your personal expenses. If you are also looking to lower your IT Costs and improve Business Performance, check out software savings at DBI.

Cheers,
Scott

Scott Hayes
President & CEO, DBI
IBM DB2 GOLD Consultant
Your Performance IS Our Business

Post from : http://www.dbisoftware.com/blog/db2_performance.php
Printed from : http://www.dbisoftware.com/blog/db2_performance.php?id=115