Analyzing database buffer pools

Buffer pools are areas of virtual storage that temporarily store pages of table spaces or indexes. When an application program accesses a row of a table, Db2 searches for that page in the buffer pool. If the requested data is already in a buffer pool, the application program need not wait for it to be retrieved from DASD. Avoiding the I/O needed to retrieve data from DASD results improves performance.

If the row is changed, the data in the buffer pool must be written to DASD eventually. That write operation might be delayed until Db2 takes a checkpoint, or until Db2 must use the space for a more frequently used page. Until that time, the data can be read or changed without a DASD I/O operation.

You can change the size and other characteristics of a buffer pool at any time while Db2 is running, using the ALTER BUFFERPOOL command. You can also get online statistics of your Db2 buffer pools using the DISPLAY BUFFERPOOL command. These commands are available only with Db2 Version 3 Release 1.

Depending on your hardware and software configuration, Db2 can maintain two levels of storage for each buffer pool:

  • The virtual buffer pool, the first level of storage, is backed by central storage, expanded storage, or auxiliary storage.
  • The hiperpool, the second level of storage, uses expanded storage or hiperspace. Hiperpools are optional. Hiperpools are available only for Db2 Version 3 Release 1.

Virtual buffer pools hold the most frequently accessed data. Hiperpools serve as a cache for data that is accessed less frequently. When a row of data is needed from a page in a hiperpool, the entire page is read into the corresponding virtual buffer pool.

Because DASD read operations are not required for accessing data that resides in hiperspace, response time is shorter than for DASD retrieval. Using hiperpools can reduce transaction and query response times.

Backing buffer pools with hiperpools also reduces central storage contention between buffer pools and other virtual resources, because hiperspaces are backed only by expanded storage.

A database buffer pool can have these page types:

In-use pages
These pages are currently being read or updated. The data they contain is unavailable for use by other applications.
Updated pages
These are pages whose data has been changed but which have not yet been written to DASD.
Available pages
These pages can be considered for new use, to be overwritten by an incoming page of new data. Both in-use and updated pages are unavailable in this sense; they are not considered for new use.

Db2’s use of a buffer pool or hiperpool is governed by several preset values called thresholds. Each threshold is a level of use which, when exceeded, causes Db2 to take some corrective action. The level of use is usually expressed as a percentage of the total size of the buffer pool or hiperpool.

Db2 has two types of thresholds:

Fixed thresholds
You cannot change these thresholds. Monitoring buffer pool usage includes noting how often those thresholds are reached. If they are reached too often, the remedy is to increase the size of the buffer pool or hiperpool. Increasing the size, though, can affect other buffer pools, depending on the total amount of space available for your buffers.

The fixed thresholds are more critical for performance than the variable thresholds are. Generally, you want to set buffer pool sizes large enough to avoid reaching any of these thresholds, except occasionally, when performance is not critical.

The fixed thresholds are:

Immediate write threshold (IWTH) - 97.5%
This threshold is checked whenever a page is to be updated. If it has been exceeded, the updated page is written to DASD as soon as the update is completed. The write is synchronous with the SQL request; that is, the request waits until the write has been completed and the two operations are not carried out concurrently. Avoid reaching this threshold, because it has a significant effect on processor usage and on I/O operations.
Data management threshold (DMTH) - 95%
This threshold is checked before a page is read or updated. If the threshold has been exceeded, Db2 accesses the page in the buffer pool once for each row that is retrieved or updated in that page.
Sequential prefetch threshold (SPTH) - 90%
When this threshold is reached, sequential prefetch is inhibited until more buffers become available. Operations that use sequential prefetch, such as those using large and frequent scans, are affected. Avoid reaching this threshold, because it seriously limits performance.
Deferred write threshold (DWQT) - 50%
This threshold is a percentage of the virtual buffer pool that might be occupied by unavailable pages, including both updated pages and pages in use. If the percentage of unavailable pages in the virtual buffer pool exceeds the threshold, Db2 schedules write operations for enough data sets to decrease the number of unavailable buffers to 10% below the threshold.
Vertical deferred write threshold (VDWQT) - 10%
This threshold is expressed as a percentage of the virtual buffer pool that might be occupied by updated pages from a single data set. If the percentage of updated pages for the data set exceeds the threshold, Db2 schedules writes for that data set.
Variable thresholds
You can change some thresholds directly, using the ALTER BUFFERPOOL command. These variable thresholds are available only with Db2 Version 3 Release 1:
Sequential steal threshold
This threshold is a percentage of the virtual buffer pool that might be occupied by sequentially accessed pages. If the threshold is exceeded, Db2 tries to steal a buffer holding a sequentially accessed page rather than one holding a randomly accessed page.
Hiperpool sequential steal threshold (HPSEQT)
This threshold is a percentage of the hiperpool that might be occupied by sequentially accessed pages. The effect of this threshold on the hiperpool is essentially the same as that of the sequential steal threshold on the virtual pool.
Virtual buffer pool parallel sequential steal threshold (VPPSEQT)
This threshold is a portion of the virtual buffer pool that might be used to support parallel I/O operations.
Deferred write threshold (DWQT)
This threshold is a percentage of the virtual buffer pool that might be occupied by unavailable pages, including both updated pages and pages in use. If the percentage of unavailable pages in the virtual buffer pool exceeds the threshold, Db2 schedules write operations for enough data sets to decrease the number of unavailable buffers to 10% below the threshold.
Vertical deferred write threshold (VDWQT)
This threshold is expressed as a percentage of the virtual buffer pool that might be occupied by updated pages from a single data set. If the percentage of updated pages for the data set exceeds the threshold, Db2 schedules writes for that data set.

Use the DB2 Buffer Pool Statistics, Overview report to analyze the efficiency of your buffer pools:

Figure 1. DB2 Buffer Pool Statistics, Overview report
DB2 Buffer Pool Statistics, Overview report

The read efficiency shows the hit ratio within the buffer pool (that is, how often Db2 finds the required page in the buffer pool). The prefetch availability is the availability of the sequential prefetch mechanism. This value should always be close to 100%. The DASD write efficiency shows the percentage of the total pages written that were synchronous and asynchronous write operations. The access profile shows how much of the Db2 load comes from logical reading (GETPAGE requests). A high value indicates high logical reading activity. A low value indicates high logical update activity. Use this report to determine how your buffer pools are used.

The DB2 Buffer Pool Exceptions report shows you when problems occur with your buffer pools:

Figure 2. DB2 Buffer Pool Exceptions report

                           DB2 Buffer Pool Exceptions
                         System: 'MVS1'  DB2 ID: 'DB2A'
                      Date: '2000-01-01'  to  '2000-03-09'


                              Defer
                              write      Sync     Seq         No
                        BP    thrsh      write  prefetch    write      No
  Date        Time      ID   reached     I/Os   disabled    engine  workfile
  ----------  --------  --  --------  --------  --------  --------  --------

  2000-01-14  07.56.40   0         0       231         0         0         0
              14.25.24   0         8         0         0         0         0
              16.24.49   0         2         0         0         0         0
                            --------  --------  --------  --------  --------
         2000-01-14 total:        10       231         0         0         0


  2000-01-19  10.28.21   0       157         0         0         0         0
              11.59.53   0        53         0         0         0         0
              13.57.33   0        59         0         0         0         0
              14.28.55   0         3         0         0         0         0
              15.27.05   0        18         0         0         0         0
              15.57.03   0        24         0         0         0         0
                            --------  --------  --------  --------  --------
         2000-01-19 total:       314         0         0         0         0


  2000-03-02  13.09.09   0       182         0         0         0         0
              13.39.09   0        83         0         0         0         0
              14.09.22   0        87         0         0         0         0
              14.40.34   0        22         0         0         0         0
              16.39.14   0         2         0         0         0         0
              22.07.14   0        15         0         0         0         0
              22.40.31   0        13         0         0         0         0
                            --------  --------  --------  --------  --------
         2000-03-02 total:       404         0         0         0         0

                            ========  ========  ========  ========  ========
                    Total:       728       231         0         0         0


                IBM Z Decision Support Report: DB219

The report shows the number of times the deferred write threshold was reached for the buffer pool.

The report also shows the number of immediate (synchronous) write I/Os for the buffer pool. Ideally, there should be no synchronous write I/Os. A synchronous write I/O can occur:

  • At the checkpoint when updated pages are scheduled to be written. If a page is being updated when a checkpoint occurs, and at the next checkpoint, it is also being updated, then the application updating the page is forced to do a synchronous write I/O. Look at the DB2 System Parameters report to determine the checkpoint frequency and then determine the number of checkpoints that occurred in the reporting interval. If the checkpoints were occurring more than once every fifteen minutes and it is a normal workload that is occurring, the checkpoint frequency should be decreased.
  • Synchronous writes also occur when the buffer pool immediate write threshold of 97.5% is reached. This threshold should rarely be reached, if at all, and indicates that the buffer pool size should be increased.

The report also shows the number of times the sequential prefetch was disabled because of unavailable buffers for the buffer pool, the number of times no write engine was available for the asynchronous write I/O for the buffer pool, and the number of times the work files required were too many and could not be created because of insufficient buffer pool resources.

The exceptions shown in this report all affect the efficient operation of buffer pools. When problems occur in the buffer pools, the applications experience delays in response time.