You can find information about buffer pools in the IBM® OMEGAMON® for Db2 Performance Expert on z/OS® statistics
report.
Procedure
To analyze your buffer pools
with IBM OMEGAMON for Db2 Performance Expert on z/OS :
Examine the IBM OMEGAMON for Db2 Performance Expert on z/OS statistics
report.
Increase the size of the buffer pool in the following situations:
Sequential prefetch is inhibited. PREF.DISABLED-NO
BUFFER shows how many times sequential prefetch is
disabled because the sequential prefetch threshold (90% of the pages
in the buffer pool are unavailable) has been reached.
You detect poor update efficiency. You can determine update efficiency
by checking the values in both of the following fields:
BUFF.UPDATES/PAGES WRITTEN
PAGES WRITTEN PER WRITE I/O
In evaluating the values you see in these fields, remember
that no values are absolutely acceptable or absolutely unacceptable.
Each installation's workload is a special case. To assess the update
efficiency of your system, monitor for overall trends rather than
for absolute high values for these ratios.
The following factors
impact buffer updates per pages written and pages written per write
I/O:
Sequential nature of updates
Number of rows per page
Row update frequency
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
affect the ratio of pages written per write I/O:
Checkpoint frequency
The checkpoint
frequency values specify the intervals for Db2 system checkpoints. The CHCKTYPE subsystem
parameter controls whether the system checkpoint interval is based
on the number of log records, a time interval, or both. The CHKFREQ
subsystem parameter contains the interval value when only one type
is specified. If both time-based and log based intervals are specified,
the values of the CHKLOGR and CHKMINS subsystem parameters define
the intervals.
Frequency of active log switch
Db2 takes
a system checkpoint each time the active log is switched. If the active
log data sets are too small, checkpoints occur often, which prevents
the deferred write queue from growing large enough to achieve a high
ratio of pages written per write I/O.
Buffer pool size
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 disk. 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 maximum number of pages written per write I/O is 32, subject to a limiting scope of 180 pages (roughly one cylinder). For example, if your application updates page 2 and page 179 in a series of pages, the two changed pages could potentially be written with one write I/O. But if your application updates page 2 and page 185 within a series of pages, writing the two changed pages would require two write I/Os because of the 180-page limit. Updated pages are placed in a deferred write queue based on the data set. For batch processing it is possible to achieve a high ratio of pages written per write I/O, but for transaction processing the ratio is typically lower.
For LOAD, REORG, and RECOVER, the maximum number of pages written per write I/O is 64, and the scope is typically unlimited. However, in some cases, such as loading a partitioned table space with nonpartitioned indexes, a 180-page limit scope exists.
The SYNCHRONOUS
WRITES field contains a large value. This field represents
the total number of immediate writes. Synchronous or immediate
writes occur in the following situations:
The immediate write threshold is reached.
No deferred write engines are available.
More than two checkpoints pass before a page is written.
Db2 sometimes uses immediate
writes even when the data manager and immediate write thresholds are
not exceeded, such as when more than two checkpoints pass before a
page is written. That type of situation does not indicate a buffer
pool shortage.
If a large number of synchronous writes occur,
monitor the value of the DM Critical Threshold Reached field.
You can ignore the value in theSYNCHRONOUS WRITES field
when DM Critical Threshold Reached value
is zero. Reaching the data manager threshold implies that the lower
immediate write threshold was crossed. Otherwise, increase the size
of the buffer pool.
The data management threshold is reached.The
extra increased getpage and release requests that result from reaching
the data manager threshold increase CPU usage. The DM
THRESHOLD field shows how many times a page was immediately
released because the data management threshold was reached. The
counter is incremented whenever 95% or more of the buffer pool was
filled with updated pages. The quantity listed for this field should
be zero. If
a high number is shown, increase the buffer pool size. Reducing the
buffer pool deferred write thresholds might also help.
Also note the following fields:
WRITE ENGINE NOT AVAILABLE
Records the number of times that asynchronous writes were deferred
because Db2 reached
its maximum number of concurrent writes. You cannot change this maximum
value. This field has a nonzero value occasionally.
You
can reduce the value of the deferred write thresholds to reduce the
intensity of deferred write spikes. If response times are long, the
I/O subsystem might require tuning.
PREF.DISABLED-NO READ ENG
Records the number of times that a sequential prefetch was not
performed because the maximum number of concurrent sequential prefetch
operations was reached. Instead,
synchronous reads were used. You cannot change this maximum value.
If the I/O response times are long, the I/O subsystem might required
tuning.
PAGE-INS REQUIRED FOR WRITE and PAGE-INS
REQUIRED FOR READ
Records the number of page-ins that are required for a read or
write I/O. When the buffer pools are first allocated, the count might
be high. After the first allocations are complete, the count should
be close to zero. Page-ins
for read might also occur if the buffer pool AUTOSIZE option is used
to enable dynamic expansion of the buffer pool. If the counts are
high during steady state, check MVS paging.
If excessive MVS paging occurs,
allocate more real storage to the LPAR.
Example
The following figure shows where
you can find important values in the statistics report.
Figure 1. IBM OMEGAMON for Db2 Performance Expert on z/OS database buffer pool
statistics (modified)
TOT4K READ OPERATIONS QUANTITY TOT4K WRITE OPERATIONS QUANTITY
--------------------------- -------- --------------------------- --------
BPOOL HIT RATIO (%) 73.12 BUFFER UPDATES 220.4K
PAGES WRITTEN 35169.00
GETPAGE REQUEST 1869.7K BUFF.UPDATES/PAGES WRITTEN 6.27
GETPAGE REQUEST-SEQUENTIAL 1378.5K
GETPAGE REQUEST-RANDOM 491.2K SYNCHRONOUS WRITES 3.00
ASYNCHRONOUS WRITES 5084.00
SYNCHRONOUS READS 54187.00
SYNCHRON. READS-SEQUENTIAL 35994.00 PAGES WRITTEN PER WRITE I/O 5.78
SYNCHRON. READS-RANDOM 18193.00
HORIZ.DEF.WRITE THRESHOLD 2.00
GETPAGE PER SYN.READ-RANDOM 27.00 VERTI.DEF.WRITE THRESHOLD 0.00
DM THRESHOLD 0.00
SEQUENTIAL PREFETCH REQUEST 41800.00 WRITE ENGINE NOT AVAILABLE 0.00
SEQUENTIAL PREFETCH READS 14473.00 PAGE-INS REQUIRED FOR WRITE 45.00
PAGES READ VIA SEQ.PREFETCH 444.0K
S.PRF.PAGES READ/S.PRF.READ 30.68
LIST PREFETCH REQUESTS 9046.00
LIST PREFETCH READS 2263.00
PAGES READ VIA LST PREFETCH 3046.00
L.PRF.PAGES READ/L.PRF.READ 1.35
DYNAMIC PREFETCH REQUESTED 6680.00
DYNAMIC PREFETCH READS 142.00
PAGES READ VIA DYN.PREFETCH 1333.00
D.PRF.PAGES READ/D.PRF.READ 9.39
PREF.DISABLED-NO BUFFER 0.00
PREF.DISABLED-NO READ ENG 0.00
PAGE-INS REQUIRED FOR READ 460.4K
To analyze your buffer pools
with IBM OMEGAMON for Db2 Performance Expert on z/OS :