News, views, and items of interest on IBM's Db2 database management system and mainframes.

Tuesday, June 14, 2016

Four Important Buffer Pool Tuning Knobs in DB2 for z/OS

DB2 has five (well, four current) primary adjustable thresholds that can be modified
using the ALTER BUFFERPOOL command.

These thresholds are as follows:

The Sequential
Steal Threshold, or VPSEQT,is the percentage of the buffer
pool that can be occupied by sequentially accessed pages. For example, at the
default value of 80, when this threshold is reached, 80% of the buffer pool
represents pages for sequential processing. Of course, 80% is just the default;
you can modify this value based on your processing needs to any value ranging
from 0 to 100. When this threshold is reached, DB2 will steal a sequential page
first before stealing a page from a randomly accessed page. So, for data that
is accessed mostly sequentially (for example, through scans and prefetching)
consider increasing the value of this parameter, and for data that is accessed
most randomly, consider decreasing the value of this parameter. A VPSEQT value
of zero will prevent any sequential pages from lingering in the buffer pool and
it will turn off sequential prefetch. A
VPSEQT value of 100 allows the entire buffer pool to be monopolized by
sequential pages.

The next tunable
buffer pool threshold is the Parallel Sequential Threshold, or VPPSEQT. This
threshold indicates the amount of the buffer pool that can be consumed by
sequentially accessed data for parallel queries. When this threshold is
reached, DB2 will cease to steal random pages to store sequential pages
accessed by parallel queries. The default value for VPPSEQT is 50%, indicating
its size as 50% of the sequential steal threshold (VPSEQT). For example, if the
buffer pool is defined as 1000 pages and VPSEQT is set at 80%, a query using
I/O parallelism can consume up to 400 sequential pages (that is, 1000 x 80% =
800 for the sequential steal threshold and 800 x 50% = 400 for the parallel
sequential threshold).

The third, and final
sequential threshold is the Assisting Parallel Sequential Threshold (or
VPXPSEQT). This threshold is no longer supported as of DB2 11 because Sysplex Parallelism is no longer supported. When it was available, VPXPSEQT was used to indicate the portion of the buffer pool that
might be used to assist with parallel operations initiated from another DB2 in
the data sharing group.

The final two modifiable DB2 buffer pool thresholds are used
to indicate when modified data is to be written from the buffer pool to disk.
Log data is externalized when a COMMIT is taken, but writing of the actual data
itself is controlled by the two deferred write thresholds (and DB2 system checkpoints).

First we have the Deferred Write Threshold (or DWQT). When DWQT is reached, DB2 starts scheduling write I/Os to externalize the data pages to disk. By default,
the deferred write threshold is reached when 30% of the buffer pool is
allocated to unavailable pages, whether updated or in use. The default is
probably too high for most larger buffer pools.

DB2 also provides the Vertical Deferred Write Threshold (VDWQT),
which is basically the same as DWQT but for a single page set. By default, VDWQT is reached when 5% of the buffer pool is allocated to one data set. When
reached, DB2 will start scheduling write I/Os to externalize the data pages to disk.
Once again, this default is most likely too high for most shops.The VDWQT threshold can be specified as a percentage of the buffer pool, or as an absolute number of buffers. When you want to specify a relatively low threshold for VDWQT using an absolute number of buffers provides better granularity and control.

In general, consider ratcheting the deferred write thresholds down to
smaller percentages (from the defaults) for most of your buffer pools. Doing so enables “trickle”
write from the DB2 buffer pools. This means that the data is written asynchronously
to disk regularly over time in smaller amounts, instead of storing up a lot of
modified data that has to be written all at once when the threshold percentage
is reached. Of course, the needs of every shop will vary.

And yes, there are other buffer pool tuning options other than these 5 4 parameters, such as changing the size of the pool, specifying min/max size for BP expansion/contraction, or altering the page most often used to fine tune buffer pool operations.

All of the above thresholds can be changed using the -ALTER
BUFFERPOOL command.

Good to meet you (virtually) Nitin... and thanks for following the blog (and, of course, for buying my book)!

Anyone looking for the latest edition of the book can order it from amazon at this link:http://www.amazon.com/gp/product/0132836424/ref=as_li_tl?ie=UTF8&camp=1789&creative=9325&creativeASIN=0132836424&linkCode=as2&tag=mullinsconsul-20&linkId=7OXR7GR4TTKFBFMA

Would appreciate if could get more blogs on buffer pools topics such as:

1) Buffer Pool tuning (Local and Global) according to work load. How to set threshold to optimum based on few check that we could do at our environment.2) Buffer Pool Splitting or Consolidation - benefits and when should we go for this approach.3) Would I-O rate and any other factor is indicator to take decision on pool tuning.

I m really looking blogs from you about pool or,if could suggest any book.