Buffer Pool Write

This topic shows detailed information about Statistics - Buffer Pool Write.

This block is only printed when the buffer pool is active. If more than one 4 KB or 32 KB buffer pool block is present, a summary block showing buffer pool totals is also printed. If the report contains both 4 KB and 32 KB buffer pool blocks, then a block showing the totals for all buffer pools is printed.

Statistics - Buffer Pool Write

The field labels shown in the following sample layout of Statistics - Buffer Pool Write are described in the following section. 


BP0    WRITE OPERATIONS      QUANTITY  /SECOND  /THREAD  /COMMIT
---------------------------  --------  -------  -------  -------
BUFFER UPDATES                6257.00    34.42      N/C      N/C
PAGES WRITTEN                  939.00     5.17      N/C      N/C
BUFF.UPDATES/PAGES WRITTEN       6.66

SYNCHRONOUS WRITES              75.00     0.41      N/C      N/C
ASYNCHRONOUS WRITES            282.00     1.55      N/C      N/C
PAGES WRITTEN PER WRITE I/O      2.63
PAGES WRTN FOR CASTOUT I/O       0.00     0.00      N/C      N/C
NUMBER OF CASTOUT I/O            0.00     0.00      N/C      N/C

HORIZ.DEF.WRITE THRESHOLD        0.00     0.00      N/C      N/C
VERTI.DEF.WRITE THRESHOLD        0.00     0.00      N/C      N/C
DM THRESHOLD                     0.00     0.00      N/C      N/C
PAGE-INS REQUIRED FOR WRITE      0.00     0.00      N/C      N/C
BUFFER UPDATES

The number of times buffer updates were requested against pages in the buffer pool.

Background and Tuning Information

The ratio of Buffer Updates to Pages Written (QBSTPWS) suggests a high level of efficiency as it increases, because more updates are being externalized per physical write.

Buffer updates per pages written depends strongly on the type of application. For example, a batch program that processes a table in skip sequential mode with a high row update frequency in a dedicated environment can achieve very good update efficiency. In contrast, update efficiency tends to be lower for transaction processing applications, because transaction processing tends to be random.

The following can influence the number of updates per page:
Number of rows per page
A small PCTFREE value gathers more rows on the same page. However, at the same time this can impact concurrency.
Buffer pool size and deferred write thresholds
Increase DWQT and VDWQT or the size of the buffer pool. This causes DB2 to let page updates accumulate in the buffer pool. Therefore, the probability that more updates per page get captured increases. This effect is less significant if the buffer pool is concurrently used by several transactions, it also depends on the type of transaction.

Field Name: QBSTSWS

This is an exception field.

PAGES WRITTEN

The number of pages in the buffer pool written to DASD.

Background and Tuning Information

Consider the ratio of Pages Written per write I/O. The number of write I/O operations includes Asynchronous Writes (QBSTWIO) and Synchronous Writes (QBSTIMW).

The ratio of pages per write I/O suggests a high level of efficiency as the ratio increases, because more pages are being externalized per physical write.

The following factors impact the ratio of pages written per write I/O:
Checkpoint frequency
At checkpoint time, I/Os are scheduled to write all updated pages on the deferred write queue to DASD. If this occurs too frequently, the deferred write queue does not grow large enough to achieve a high ratio of pages written per write I/O.

The checkpoint frequency depends on the number of logs written between two consecutive checkpoints. This number is set at installation time; see the field CHECKPOINT FREQ of installation panel DSNTIPN.

Frequency of active log switch
DB2 takes a system checkpoint each time the active log is switched. High frequency of active log switches causes the problem described under checkpoint frequency.
Buffer pool size and deferred write thresholds
The deferred write thresholds (VDWQT and DWQT) are a function of buffer pool size. If the buffer pool size is decreased, these thresholds are reached more frequently, causing I/Os to be scheduled more often to write some of the pages on the deferred write queue to DASD. This prevents the deferred write queue from growing large enough to achieve a high ratio of pages written per write I/O.
Number of data sets, and the spread of updated pages across them
The efficiency of write I/O also depends on the number of data sets associated with the buffer pool and spread of updated pages across them. Because of the nature of batch processing, the ratio of pages written to write I/Os can be expected to be higher than that expected for transaction type workloads.

To determine update efficiency, use also the value in the Buffer Updates field (QBSTSWS) to check the number of buffer updates per page written.

Field Name: QBSTPWS

This is an exception field.

BUFF.UPDATES/PAGES WRITTEN

The number of buffer updates per page written from the buffer pool to DASD.

The ratio of BUFFER UPDATES (QBSTSWS) to PAGES WRITTEN (QBSTPWS) suggests a high level of efficiency as the ratio increases, because more updates are being externalized per physical write. For example, if there are 10 updates on the same page before it is externalized, then the ratio is 10:1 or 10. If all 10 updates are on 10 distinct pages, then the ratio is 10:10 or 1.

Background and Tuning Information

Buffer updates per pages written depends strongly on the type of application. For example, a batch program that processes a table in skip sequential mode with a high row update frequency in a dedicated environment can achieve very good update efficiency. In contrast, update efficiency tends to be lower for transaction processing applications, because transaction processing tends to be random.

The following factors can influence the number of updates per page:
Number of rows per page
A small PCTFREE value will gather more rows on the same page. However, at the same time this can have impact on concurrency.
Buffer pool size and deferred write thresholds
Increase DWQT and VDWQT or the size of the buffer pool. This would tell DB2 to let page updates accumulate in the buffer pool. This means, the probability that more updates per page get captured increases. This effect is less significant if the buffer pool is concurrently used by multiple transactions, it depends on the type of transaction.

Field Name: SBRBUPW

SYNCHRONOUS WRITES

The total number of immediate writes.

Immediate writes occur when:
  • An immediate write threshold (IWTH) is reached
  • No deferred write engines are available
  • More than two checkpoints pass without a page being written.

Sometimes DB2 uses synchronous writes even when the IWTH is not exceeded. As an example, when more than two checkpoints pass without a page being written. This type of situation does not indicate a buffer shortage.

Background and Tuning Information

A small number of immediate writes can be expected. Synchronous writes occur if there are too many checkpoints and/or the buffer pool is too small.

If a large number of synchronous writes occur, monitor the DM Critical Threshold Reached (QBSTDMC) field. Reaching Immediate Write Threshold (IWTH-97.5%) implies that the Data Management Threshold (DMTH-95%) has been crossed. You can ignore the value in the immediate write field when DM Critical Threshold Reached is zero. Otherwise consider increasing the size of the buffer pool. You can use the ALTER BUFFERPOOL command.

Check also the System Event Checkpoint field (QWSDCKPT) in the Subsystem Services block to see whether the frequency of DB2 checkpoints should be reduced. To do this, increase the value of ZPARM LOGLOAD.

Field Name: QBSTIMW

This is an exception field.

ASYNCHRONOUS WRITES

The number of asynchronous write I/O operations performed by media manager to a direct access storage device.

Field Name: QBSTWIO

This is an exception field.

PAGES WRITTEN PER WRITE I/O

The number of pages written from the buffer pool to DASD per synchronous or asynchronous write I/O. This count does not include preformatting I/O, such as I/O needed to prepare a data set for use.

Background and Tuning Information

The following factors impact the ratio of pages written per write I/O:
Checkpoint frequency
At checkpoint time, I/Os are scheduled to write all updated pages on the deferred write queue to DASD. If this occurs too frequently, the deferred write queue does not grow large enough to achieve a high ratio of pages written per write I/O.

The checkpoint frequency depends on the number of logs written between two consecutive checkpoints. This number is set at installation time; see the field CHECKPOINT FREQ of installation panel DSNTIPN.

Frequency of active log switch
DB2 takes a system checkpoint each time the active log is switched. High frequency of active log switches causes the problem described under checkpoint frequency.
Buffer pool size and deferred write thresholds
The deferred write thresholds (VDWQT and DWQT) are a function of buffer pool size. If the buffer pool size is decreased, these thresholds are reached more frequently, causing I/Os to be scheduled more often to write some of the pages on the deferred write queue to DASD. This prevents the deferred write queue from growing large enough to achieve a high ratio of pages written per write I/O.
Number of data sets, and the spread of updated pages across them
The efficiency of write I/O also depends on the number of data sets associated with the buffer pool and spread of updated pages across them. Because of the nature of batch processing, the ratio of pages written to write I/Os can be expected to be higher than that expected for transaction type workloads.
To determine update efficiency check also the ratio Buffer Updates / Pages Written (SBRBUPW).

Field Name: SBRPWWIO

PAGES WRTN FOR CASTOUT I/O

The number of pages written for castout I/O operations.

Field Name: QBSTPCO

NUMBER OF CASTOUT I/O

The number of castout I/O operations.

Field Name: QBSTCIO

HORIZ.DEF.WRITE THRESHOLD

The number of times the deferred write threshold (DWTH) was reached.

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. DB2 checks this threshold when an update to a page is completed. If the percentage of unavailable pages in the virtual buffer pool exceeds the threshold, write operations are scheduled for enough data sets (up to 128 pages per data set) to reduce the number of unavailable buffers to 10% below the threshold.

Background and Tuning Information

The default value for this threshold is 30%. You can change that to any value from 0% to 90% by using the DWQT option on the ALTER BUFFERPOOL command.

The deferred write thresholds, DWQT and VDWQT, are specified as a percentage, their absolute value depends on the size of the virtual buffer pool.

Consider the following aspects when changing the deferred write thresholds:
Optimize the ratio of pages written per write I/O
The ratio can be monitored using the Pages Written (QBSTPWS) field.

When the buffer pool is relatively small, the default thresholds could prevent the deferred write queue from growing large enough to achieve a high ratio of pages written per write I/O. Raising these thresholds will, in this instance, reduce the I/O write frequency, increasing the number of pages written per I/O.

Distribute I/O evenly over time
If a virtual buffer pool is very large, it is unlikely that the default values of either DWQT or VDWQT will ever be reached. In this case, write I/Os tend to occur in surges, triggered by DB2 checkpoints. Lowering the VDWQT and the DWQT could improve performance by distributing the write I/Os more evenly over time.
Impact on other buffer pool thresholds
Increasing DWQT and VDWQT allows updated pages to use a larger portion of the virtual buffer pool. Large DWQT and VDWQT can have a significant effect on the other thresholds. For example, in work load where pages are frequently updated, and the updated pages exceed the size of the virtual buffer pool, setting both DWQT and VDWQT to 90% would probably cause frequent threshold-reached events for sequential prefetch (and possibly the data management and immediate write).

Field Name: QBSTDWT

This is an exception field.

VERTI.DEF.WRITE THRESHOLD

The number of times the vertical deferred write threshold (VDWQT) was reached. This threshold is similar to the deferred write threshold but it applies to the number of updated pages for one single page set in the buffer pool. If the percentage or number of updated pages for the data set exceeds the threshold, writes up to 128 pages are scheduled for that data set.

Field Name: QBSTDWV

This is an exception field.

DM THRESHOLD

The number of times the data manager critical threshold (DMTH-95%) was reached.

This field shows how many times a page was immediately released because the data management threshold was reached.

The threshold is checked before a page is read or updated. If the threshold has not been exceeded, DB2 accesses the page in the virtual buffer pool once for each page, no matter how many rows are retrieved or updated in that page. If the threshold has been exceeded, Getpage requests and RELEASEs apply to rows instead of pages. That is, if more than one row is retrieved or updated in a page, more than one Getpage request and RELEASE is performed on that page.

Background and Tuning Information

Avoid reaching this threshold wherever possible because it significantly affects CPU usage. Set virtual buffer pool sizes large enough or reduce the workload on the buffer pool.

Field Name: QBSTDMC

This is an exception field.

PAGE-INS REQUIRED FOR WRITE

The number of page-ins required for a write I/O.

Field Name: QBSTWPI