db2top Bottlenecks Screen (Part 1)

A database workload performs at its best when resources are made available to it in all the right proportions. Having any one resource constrained too much and everything slows down. You can have more CPU cycles available than you’ll ever need, but if your IO subsystem can’t keep up with the work, it becomes a bottleneck and you’ll never achieve your desired throughput. In today’s post, we’ll take a look at the db2top Bottlenecks screen and see how it can help you find and resolve bottlenecks in your system by finding the top consumers of each of the major system resources.

You can launch the Bottlenecks screen by holding the Shift key and pressing the ‘B’ key. This produces a screen like the following:

The screen is the usual combination of a set of gauges on top and a table beneath it. The gauges are as follows:

Gauge Name	Gauge Type	Definition
wait lock ms	Normal	Percentage of total time spent waiting for locks.  lock_wait_time / (lock_wait_time + total_sort_time + pool_read_time + pool_write_time + pool_async_read_time + pool_async_write_time + prefetch_wait_time + direct_read_time + direct_write_time)
sort ms	Normal	Percentage of total time spent performing sorts. total_sort_time / (lock_wait_time + total_sort_time + pool_read_time + pool_write_time + pool_async_read_time + pool_async_write_time + prefetch_wait_time + direct_read_time + direct_write_time)
bp r/w/ ms	Normal	Percentage of total time spent performing buffer pool I/O. (pool_read_time + pool_write_time) / (lock_wait_time + total_sort_time + pool_read_time + pool_write_time + pool_async_read_time + pool_async_write_time + prefetch_wait_time + direct_read_time + direct_write_time)
async r/w ms	Normal	Percentage of total time spent performing asynchronous I/O. (pool_async_read_time + pool_async_write_time) / (lock_wait_time + total_sort_time + pool_read_time + pool_write_time + pool_async_read_time + pool_async_write_time + prefetch_wait_time + direct_read_time + direct_write_time)
pref wait ms	Normal	Percentage of total time spent waiting for prefetches to occur. prefetch_wait_time / (lock_wait_time + total_sort_time + pool_read_time + pool_write_time + pool_async_read_time + pool_async_write_time + prefetch_wait_time + direct_read_time + direct_write_time)
dir r/w ms	Normal	Percentage of total time spent performing direct I/O. (direct_read_time + direct_write_time) / (lock_wait_time + total_sort_time + pool_read_time + pool_write_time + pool_async_read_time + pool_async_write_time + prefetch_wait_time + direct_read_time + direct_write_time)

The table consists of the following columns:

Column Name	Definition
Server Resource	The name of the resource. The following are the resources reported in this table: Cpu SessionCpu IO r/w Memory Locks Sorts Sort Time Log Used Overflows RowsRead RowsWritten TQ r/w MaxQueryCost XDAPages
Top Agent	The system-wide unique ID for the application that is the greatest consumer of this resource (agent_id).
Resource Usage	Different for each resource. Described later.
Resource Value	Different for each resource. Described later.
Application Name	The name of the application running at the client, as known to the database, that is the greatest consumer of this resource (appl_name). “N/A” if resource usage is zero.

In the second post on the db2top Bottlenecks screen, I will cover each row reported in the table.

For other articles on db2top at this site, see the main db2top page.