The deferred write threshold is the percentage threshold that determines when DB2 starts turning on write engines to begin deferred writes. The value can be from 0 to 90 percent. When the threshold is reached, write engines begin writing pages out to disk.
Running out of write engines can occur if the write thresholds are not set to keep a constant flow of updated pages being written to disk. DB2 turns on these write engines one vertical pageset queue at a time until a 10 percent reverse threshold is met. Check the WRITE ENGINES NOT AVAILABLE indicator on Statistics report to find out when DB2 ran out of write engines.
When setting the DWQT threshold, a high value is useful to help improve the hit ratio for updated pages, but it increases I/O time when deferred write engines begin. Using a low value to reduce I/O length for deferred write engines increases the number of deferred writes. Set this threshold based on the referencing of the data by the applications.
If you choose to set the DWQT threshold to 0 so that all objects defined to the buffer pool are scheduled to be written immediately to disk, DB2 uses its own internal calculations for exactly how many changed pages can exist in the buffer pool before it is written to disk.
32 pages are written per I/O, but it takes 40 updated pages to trigger the threshold so that the highly re-referenced updated pages remain in the buffer pool.
When implementing LOBs (Large Objects), a separate buffer pool should be used and this buffer pool should not be shared. The DWQT threshold should be set to 0 so that LOBs with LOG NO force-at-commit processing occurs and the updates continually flow to disk instead of surges of writes. For LOBs defined with LOG YES, DB2 could use deferred writes and could avoid massive surges at the checkpoint.
The DWQT threshold works at a buffer pool level for controlling writes of pages to the buffer pools, but for a more efficient write process, control writes at the pageset partition level by using the VDWQT, which is the percentage threshold that determines when DB2 starts turning on write engines and begins the deferred writes for a data set. This practice helps to keep a particular pageset partition from monopolizing the entire buffer pool with its updated pages. The value is 0 to 90 percent with a default of 10 percent. The VDWQT should always be less than the DWQT.
When setting the buffer pool writes, if fewer than ten pages are written per I/O, set the deferred write threshold to 0. You may also want to set it to 0 to trickle write the data out to disk. It is best to keep this value low to prevent heavily updated pagesets from dominating the section of the deferred write area. A percentage of pages or actual number of pages, from 0 through 9999, can be specified for VDWQT. You must set the percentage to 0 to use the number that is specified for VDWQT. Set to 0,0 and system uses MIN(32,1%) for good trickle I/O.
Set VDWQT using a number rather than a percentage because if someone increases the buffer pool, more pages for a particular pageset can occupy the buffer pool, and this result may not always be optimal.
When looking at any performance report showing the amount of activity for VDWQT and DWQT, you want to see VDWQT being triggered most of the time and DWQT less. There can be no general ratios because that would depend on the activity and the number of objects in the buffer pools. You want to be controlling I/O by VDWQT with DWQT watching for and controlling activity across the entire pool and writing out rapidly queuing up pages. This practice also assists in limiting the amount of I/O that checkpoints would have to perform.
|
Copyright © 2013 CA Technologies.
All rights reserved.
|
|