DB2 LUW Performance: I/O Write Times (OWMS)

If your database updates its data via Inserts, Updates, Deletes, Imports, or Loads, then this blog post is for you. Write times tend to be slower than read times, and synchronous writes can be particularly painful. When tuning your databases, it is desirable to achieve a high percentage of Asynchronous writes as this type of write is faster. We need to learn the average write time for the database overall, and write times for each tablespace.

---

Overall Write Milliseconds (OWMS)

OWMS = Total buffer pool write time (milliseconds) / (Buffer pool data writes + Buffer pool index writes)

Compute OWMS for your database and each tablespace. Use 'dbsnap2.txt' and 'tssnap2.txt' respectively as derived from commands:

• get snapshot for database on DBNAME
• get snapshot for tablespaces on DBNAME

OWMS at the database level tells us the average time for the database to perform a write (any write, whether synchronous or asynchronous). OWMS at the tablespace level tells us the average write time for each tablespace. If the OWMS for a tablespace is significantly higher than the OWMS for the database, then you have likely uncovered a "performance opportunity for improvement". This situation needs to be investigated. For the tablespaces with the slowest write times, carefully examine their definitions, containers, and placement of containers, and consider these best practices:

• If possible, tablespaces should use multiple containers on different devices.
• Containers should have equal sizes
• Containers should not be placed on devices containing OS paging space or other extremely busy devices
• DMS tablespaces tend to perform 5-10% faster than SMS tablespaces, but DMS is more difficult to administrate unless you are taking advantage of Automatic Storage
• Use Automatic Storage with multiple data paths

If you are using DB2 9, you can run these handy SQL commands to compute OWMS---

OWMS for the Database: db2 “select (POOL_WRITE_TIME / (POOL_DATA_WRITES + POOL_INDEX_WRITES + 1) as OWMS from sysibmadm.snapdb where db_name = ‘DBNAME’”

OWMS for the top 10 slowest tablespaces: db2 “select tbsp_name, (POOL_WRITE_TIME / (POOL_DATA_WRITES + POOL_INDEX_WRITES + 1) as OWMS from sysibmadm.snaptbsp order by OWMS desc fetch first 10 rows only”

While OWMS tells us about average write times, it is also valuable to know how much time, on average, each transaction spends on bufferpool write time. For this, we need to look at BPWIOTX (Bufferpool Write I/O per Transaction).

Bufferpool Write I/O ms per Transaction (BPWIOTX)

BWRIOTX = (Total buffer pool write time (milliseconds) / (Commit statements attempted + Rollback statements attempted))

Bufferpool write time is just one important component of understanding where transaction time goes. In the previous blog post on read times we covered BPRIOTX, and in future blog posts, we'll also look direct I/O times, lock times, sort times, and CPU times. Once we know where time is spent inside the database, then we can focus on the resource that is the greatest bottleneck to optimized performance. We'll also look at determining average transaction times, and how much time, and what percent of time, is spent inside the database and out.

The Shameless Marketing Moment

At DBI, our slogan is "Accountability Starts Here". Why is it that whenever there is an application performance performance problem, the database always gets blamed first and the issue lands on the DBA's back? Performance problems don't always belong to the DBA team - sometimes problems are outside the database. DBI's Brother-Thoroughbred® was specifically designed to help DBA teams manage performance accountability, identify ownership of issues, accurately ascertain resource bottlenecks inside the database, and monitor achievement of Service Level Agreements (SLAs).

This Brother-Thoroughbred image shows the average database transaction response time, how much time is inside the database (owned by the DBA team), and how much time is outside the database.

This Brother-Thoroughbred image shows the time distribution of time spent inside the database. The largest pie slice represents the primary resource bottleneck. Clicking on a pie slice will automatically take you to Brother-Panther's statement analysis sorted according to the resource bottleneck selected. Click, click, solved!

Last week we helped a customer reduce CPU utilization by 97% and cut average transaction response times in half. If you're ready to become a Performance Hero in your organization, contact DBI and we'll help you too!

Just for Fun

Every once in a while I like to go outside at night and gaze up at the sky. I think about how big the universe is and how small we all are in relative proportion. Still, each of us can make a difference in the world when we focus on doing the right things. You can gaze into the night time sky and see incredible pictures of space by visiting the "Astronomy Picture of the Day" at http://antwrp.gsfc.nasa.gov/apod/astropix.html. While you check out the amazing photo, ask yourself "Who have I helped today?" You can make a difference in society with one helpful action at a time.

Cheers,
Scott

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