Monitoring and tuning buffer pools by using online commands
The DISPLAY BUFFERPOOL and ALTER BUFFERPOOL commands enable
you to monitor and tune buffer pools on line, while DB2® is running,
without the overhead of running traces.
Procedure
To monitor and tune your buffer
pools with online commands:
Use the DISPLAY BUFFERPOOL command to display the current
status of one or more active or inactive buffer pools.
SYNC READ I/O (R) shows the number
of random synchronous read I/O operations. SYNC READ
I/O (S) shows the number of sequential synchronous
read I/O operations. Sequential synchronous read I/Os occur when prefetch
is disabled.
To determine the total number of synchronous read I/Os,
add SYNC READ I/O (S) and SYNC READ I/O (R).
In message DSNB412I, REQUESTS shows
the number of times that sequential prefetch was triggered, and PREFETCH
I/O shows the number of times that sequential prefetch
occurred. PAGES READ shows the number
of pages read using sequential prefetch.
SYS PAGE UPDATES corresponds to the
number of buffer updates.
SYS PAGES WRITTEN is the number of
pages written to disk.
DWT HIT is the number of times the
deferred write threshold (DWQT) was reached. This number is workload
dependent.
VERTICAL DWT HIT is the number of
times the vertical deferred write threshold (VDWQT) was reached. This
value is per data set, and it is related to the number of asynchronous
writes.
Use the LSTATS option of the DISPLAY BUFFERPOOL command
to obtain buffer pool information on a specific data set. For
example, you can use the LSTATS option to:
Provide page count statistics for a certain index. With this information,
you could determine whether a query used the index in question, and
perhaps drop the index if it was not used.
Monitor the response times on a particular data set. If you determine
that I/O contention is occurring, you could redistribute the data
sets across your available disks.
This same information is available with IFCID 0199 (statistics
class 8).
Use the ALTER BUFFERPOOL command to change the following
attributes:
Buffer pool size: VPSIZE
Thresholds:
VPSEQT
VPPSEQT
VPXPSEQT
DWQT
VDWQT
Page-stealing algorithm: PGSTEAL
Page fix attribute: PGFIX
Automatic adjustment attribute: AUTOSIZE
Example
Because
the number of synchronous read I/O is relatively high, you might tune
the buffer pools by changing the buffer pool specifications. For example,
you might increase the buffer pool size to reduce the amount of unnecessary
I/O, which would make buffer operations more efficient. To do that,
you would enter the following command:
-ALTER BUFFERPOOL(BP0) VPSIZE(nnnn)
What to do next
Buffer Pool Analyzer: You can use
the Buffer Pool Analyzer for z/OS® to get
recommendations buffer pool allocation changes and to do "what if" analysis of your buffer
pools.