Buffer Pool: prefetch disabled/read engines

Joseph Orndorff

Buffer Pool: prefetch disabled/read engines
Recently, we have done following to the Buffer Pools in one of our
production subsystems:

1. eliminated all buffers from all hiper pools (HP's)
2. added these buffers to the related virtual pools (VP's)
3. moved all ESTOR originally used by hiper pools to CSTOR

This was done to eliminate I/O between VP & HP. The various BP threshold
settings were initially left unchanged.

Problem: After doing all this, the incidence of "prefetch disabled/read
engines" has increased substantially. Prior to getting rid of the hiper
pools, this occurred only a few times per month; now, it occurs several
times a week. Focusing in on bufferpool BP4 used for Large Random-accessed
tables, I have tried numerous adjustments to the VPSEQT, DWQT & VDWQT, but
to no apparent effect on addressing this error. I have modified the
threshold settings as shown:

VPSEQT from 10 to 45
HPSEQT (no change; set to 10, but shouldn't matter with no hiperpools
allocated)
DWQT from 25 to 10
VDWQT from 5 to 1

Note: We are on DB2 V4 & are not doing data sharing or query parallelism
(DEGREE=1 in ZPARM).

Question:

Any remedies suggested for this problem ? (Increasing the size of the VP
has been suggested, but I am hoping for some other tips).

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * *

Here is the Platinum Subsystem Analyzer info for the bufferpool in question
for an interval of about 8 hours (starting at 11:02 AM):

V02.00 ------ SS Analyzer Buffer Pool Detail Display ------ 12-05-00
20:05
Command ===> Scroll ==>
CSR
PT014W - ALREADY AT TOP
DB2 SSID ==> DSNP Buffer Pool ==> BP4
Interval Date => 12/04/00 Interval Time => 11:02:00 Elaps Time =>
07:58:03
----------------------------------------------------------------------------
---

Buffer Pool Size Information
----------------------------
VP size defined 308000K HP size defined
0K
VP allocated 308000K HP allocated
0K
VP inuse/updated 436K HP expanded stg in use
0K

Buffer Pool Thresholds Defined
------------------------------
VP seq threshold 45 HP seq threshold
0
Deferred write threshold 10 Vertical DW threshold
1
IOP seq threshold 0

Buffer Pool Read Access Activity
--------------------------------
Getpage total reqs 20653237 Seq pfetch reqs
83700
Getpage seq access reqs 2720170 Seq pfetch read I/O reqs
70521
Synch read reqs 252596 Seq pfetch read ratio
1.1
Synch read via seq access 14753 Seq pfetch pages read
2213492
Getpage/sync read ratio 81.7 Seq pfetch pages per I/O
31.3

List pfetch reqs 15460 Dyn pfetch reqs
949152
List pfetch read I/O reqs 11558 Dyn pfetch read I/O reqs
59642
List pfetch read ratio 1.3 Dyn pfetch read ratio
15.9
List pfetch page read 102965 Dyn pfetch pages read
1484507
List pfetch pages per I/O 8.9 Dyn pfetch pages per I/O
24.8

Buffer Pool Write Access Activity
---------------------------------
Update buffer reqs 142477 Immediate write reqs
579
Pages written 20444 Async write I/O reqs
5493

Buffer Pool Paging and Threshold Activity
-----------------------------------------
Page in for read reqs 112906 Page in for write reqs
108
DWTH threshold reached 1 Seqpf disable/no buffer
0
Vertical DWTH reached 0 Seqpf disable/read engine
5640
DMTH threshold reached 0 No write engine available
0
HSM recall reqs 0 Getpage failed/VP full
0
HSM recall req timeouts 0 Expansion failed/storage
0

Buffer Pool Hiper Pool Read Activity
------------------------------------
Pages read from HP to VP synchronously using MVPG
0
Pages read from HP to VP asynchronously using MVPG
0
Pages read from HP to VP asynchronously using Async Data Mover
0
Page read requests using MVPG which failed
0
Page read requests using Async Data Mover which failed
0

Buffer Pool Hiper Pool Write Activity
-------------------------------------
Pages written from VP to HP synchronously using MVPG/CWRITE
0
Pages written from VP to HP asynchronously using MVPG/CWRITE
0
Pages written from VP to HP asynchronously using Async Data Mover
0
Pages write requests using MVPG/CWRITE which failed
0
Pages write requests using Async Data Mover which failed
0
PF8 for additional display information



Joel Goldstein

Re: Buffer Pool: prefetch disabled/read engines
(in response to Joseph Orndorff)
Joseph,
Read engines disabled is first a direct indication of a poorly performing
DASD
subsystem, because the I/Os cannot complete fast enough, and all the
engines
are busy waiting for reads to complete. Dataset placement is usually the
culprit.
Additonally, your vdwqt s/b set to ZERO.. for overall better performance,
but this
won't help the engine disabled problem.
Some serious bufferpool tuning will probably help your performance, and
will reduce the i/o load.
You neglected to mention if your total number of buffers is the same now,
as it was
when you had HPs.
What is your average elapsed times for a prefetch I/O?

Lastly, NIKE is a licensed user of Buffer Pool Tool for DB2.,
Get acces to the software and do some tuning. A proper re-grouping of
objects
into pools will help your performance.

Regards,
Joel
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++
Buffer Pool Tool for DB2 - The only product that can Predict the effect
of changes & show how to properly group objects into pools based on
access
DASD/Xpert for DB2 - Easily shows all your DASD performance problems,
and recommends tuning options.
Visit www.responsivesystems.com - performance white papers and
presentations
+++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++++
+++

>Message text written by DB2 Data Base Discussion List
>Recently, we have done following to the Buffer Pools in one of our
>production subsystems:

1. eliminated all buffers from all hiper pools (HP's)
2. added these buffers to the related virtual pools (VP's)
3. moved all ESTOR originally used by hiper pools to CSTOR

This was done to eliminate I/O between VP & HP. The various BP threshold
settings were initially left unchanged.

Problem: After doing all this, the incidence of "prefetch disabled/read
engines" has increased substantially. Prior to getting rid of the hiper
pools, this occurred only a few times per month; now, it occurs several
times a week. Focusing in on bufferpool BP4 used for Large Random-accessed
tables, I have tried numerous adjustments to the VPSEQT, DWQT & VDWQT, but
to no apparent effect on addressing this error. I have modified the
threshold settings as shown:

VPSEQT from 10 to 45
HPSEQT (no change; set to 10, but shouldn't matter with no hiperpools
allocated)
DWQT from 25 to 10
VDWQT from 5 to 1

Note: We are on DB2 V4 & are not doing data sharing or query parallelism
(DEGREE=1 in ZPARM).

Question:

Any remedies suggested for this problem ? (Increasing the size of the VP
has been suggested, but I am hoping for some other tips).

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * *

Here is the Platinum Subsystem Analyzer info for the bufferpool in question
for an interval of about 8 hours (starting at 11:02 AM):

V02.00 ------ SS Analyzer Buffer Pool Detail Display ------ 12-05-00
20:05
Command ===> Scroll ==>
CSR
PT014W - ALREADY AT TOP
DB2 SSID ==> DSNP Buffer Pool ==> BP4
Interval Date => 12/04/00 Interval Time => 11:02:00 Elaps Time =>
07:58:03
---------------------------------------------------------------------------
-
---

Buffer Pool Size Information
----------------------------
VP size defined 308000K HP size defined
0K
VP allocated 308000K HP allocated
0K
VP inuse/updated 436K HP expanded stg in use
0K

Buffer Pool Thresholds Defined
------------------------------
VP seq threshold 45 HP seq threshold
0
Deferred write threshold 10 Vertical DW threshold
1
IOP seq threshold 0

Buffer Pool Read Access Activity
--------------------------------
Getpage total reqs 20653237 Seq pfetch reqs
83700
Getpage seq access reqs 2720170 Seq pfetch read I/O reqs
70521
Synch read reqs 252596 Seq pfetch read ratio
1.1
Synch read via seq access 14753 Seq pfetch pages read
2213492
Getpage/sync read ratio 81.7 Seq pfetch pages per I/O
31.3

List pfetch reqs 15460 Dyn pfetch reqs
949152
List pfetch read I/O reqs 11558 Dyn pfetch read I/O reqs
59642
List pfetch read ratio 1.3 Dyn pfetch read ratio
15.9
List pfetch page read 102965 Dyn pfetch pages read
1484507
List pfetch pages per I/O 8.9 Dyn pfetch pages per I/O
24.8

Buffer Pool Write Access Activity
---------------------------------
Update buffer reqs 142477 Immediate write reqs
579
Pages written 20444 Async write I/O reqs
5493

Buffer Pool Paging and Threshold Activity
-----------------------------------------
Page in for read reqs 112906 Page in for write reqs
108
DWTH threshold reached 1 Seqpf disable/no buffer
0
Vertical DWTH reached 0 Seqpf disable/read engine
5640
DMTH threshold reached 0 No write engine available
0
HSM recall reqs 0 Getpage failed/VP full
0
HSM recall req timeouts 0 Expansion failed/storage
0

Buffer Pool Hiper Pool Read Activity
------------------------------------
Pages read from HP to VP synchronously using MVPG
0
Pages read from HP to VP asynchronously using MVPG
0
Pages read from HP to VP asynchronously using Async Data Mover
0
Page read requests using MVPG which failed
0
Page read requests using Async Data Mover which failed
0

Buffer Pool Hiper Pool Write Activity
-------------------------------------
Pages written from VP to HP synchronously using MVPG/CWRITE
0
Pages written from VP to HP asynchronously using MVPG/CWRITE
0
Pages written from VP to HP asynchronously using Async Data Mover
0
Pages write requests using MVPG/CWRITE which failed
0
Pages write requests using Async Data Mover which failed
0
PF8 for additional display information<



Max Scarpa

Re: Buffer Pool: prefetch disabled/read engines
(in response to Joel Goldstein)
What kind of DASDs do you have ? We had this problems some days ago (only
few prefetch disabled, anyway), and
we resolved it moving some datasets to another DASD (from HDS7700 to
HDS7700E) with more cache (> 1 Gb) to achieve an higher cache hit ratio.
Check if all cache features are enabled.

Take a look to your hit cache ratio and DASDs response time (for each
component of response time), sometimes it happens that you may have an high
CONN time other than an high IOSQ time.

If BP4 is the BP that has problems, take a look if there's any new
application and if any check if access path is good,
as it seems that every time you read almost 32 (31.3) pages per I/O.Some
years ago I had a BP flooded by an application (with the statement SET
CURRENT DEGREE=ANY).

I was not a supporter of HP, but after I read some good articles and after
I used them I changed my opinion: in a serious BP tuning they may help a
lot to avoid I/O.

HTH

Max Scarpa
DB2 non-certified sysprog



Dave Nance

Re: Buffer Pool: prefetch disabled/read engines
(in response to Max Scarpa)
Dave Nance
First Health Services, Corp.
(804)527-6841

>>> [login to unmask email] 12/27/00 06:43PM >>>
Recently, we have done following to the Buffer Pools in one of our
production subsystems:

1. eliminated all buffers from all hiper pools (HP's)
2. added these buffers to the related virtual pools (VP's)
3. moved all ESTOR originally used by hiper pools to CSTOR

This was done to eliminate I/O between VP & HP. The various BP threshold
settings were initially left unchanged.

Problem: After doing all this, the incidence of "prefetch disabled/read
engines" has increased substantially. Prior to getting rid of the hiper
pools, this occurred only a few times per month; now, it occurs several
times a week. Focusing in on bufferpool BP4 used for Large Random-accessed
tables, I have tried numerous adjustments to the VPSEQT, DWQT & VDWQT, but
to no apparent effect on addressing this error. I have modified the
threshold settings as shown:

VPSEQT from 10 to 45
HPSEQT (no change; set to 10, but shouldn't matter with no hiperpools
allocated)
DWQT from 25 to 10
VDWQT from 5 to 1

Note: We are on DB2 V4 & are not doing data sharing or query parallelism
(DEGREE=1 in ZPARM).

Question:

Any remedies suggested for this problem ? (Increasing the size of the VP
has been suggested, but I am hoping for some other tips).

* * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * * *
* * * * * * * * * * * * * *

Here is the Platinum Subsystem Analyzer info for the bufferpool in question
for an interval of about 8 hours (starting at 11:02 AM):

V02.00 ------ SS Analyzer Buffer Pool Detail Display ------ 12-05-00
20:05
Command ===> Scroll ==>
CSR
PT014W - ALREADY AT TOP
DB2 SSID ==> DSNP Buffer Pool ==> BP4
Interval Date => 12/04/00 Interval Time => 11:02:00 Elaps Time =>
07:58:03
----------------------------------------------------------------------------
---

Buffer Pool Size Information
----------------------------
VP size defined 308000K HP size defined
0K
VP allocated 308000K HP allocated
0K
VP inuse/updated 436K HP expanded stg in use
0K

Buffer Pool Thresholds Defined
------------------------------
VP seq threshold 45 HP seq threshold
0
Deferred write threshold 10 Vertical DW threshold
1
IOP seq threshold 0

Buffer Pool Read Access Activity
--------------------------------
Getpage total reqs 20653237 Seq pfetch reqs
83700
Getpage seq access reqs 2720170 Seq pfetch read I/O reqs
70521
Synch read reqs 252596 Seq pfetch read ratio
1.1
Synch read via seq access 14753 Seq pfetch pages read
2213492
Getpage/sync read ratio 81.7 Seq pfetch pages per I/O
31.3

List pfetch reqs 15460 Dyn pfetch reqs
949152
List pfetch read I/O reqs 11558 Dyn pfetch read I/O reqs
59642
List pfetch read ratio 1.3 Dyn pfetch read ratio
15.9
List pfetch page read 102965 Dyn pfetch pages read
1484507
List pfetch pages per I/O 8.9 Dyn pfetch pages per I/O
24.8

Buffer Pool Write Access Activity
---------------------------------
Update buffer reqs 142477 Immediate write reqs
579
Pages written 20444 Async write I/O reqs
5493

Buffer Pool Paging and Threshold Activity
-----------------------------------------
Page in for read reqs 112906 Page in for write reqs
108
DWTH threshold reached 1 Seqpf disable/no buffer
0
Vertical DWTH reached 0 Seqpf disable/read engine
5640
DMTH threshold reached 0 No write engine available
0
HSM recall reqs 0 Getpage failed/VP full
0
HSM recall req timeouts 0 Expansion failed/storage
0

Buffer Pool Hiper Pool Read Activity
------------------------------------
Pages read from HP to VP synchronously using MVPG
0
Pages read from HP to VP asynchronously using MVPG
0
Pages read from HP to VP asynchronously using Async Data Mover
0
Page read requests using MVPG which failed
0
Page read requests using Async Data Mover which failed
0

Buffer Pool Hiper Pool Write Activity
-------------------------------------
Pages written from VP to HP synchronously using MVPG/CWRITE
0
Pages written from VP to HP asynchronously using MVPG/CWRITE
0
Pages written from VP to HP asynchronously using Async Data Mover
0
Pages write requests using MVPG/CWRITE which failed
0
Pages write requests using Async Data Mover which failed
0
PF8 for additional display information






Joseph Orndorff

Re: Buffer Pool: prefetch disabled/read engines
(in response to Dave Nance)
Joel,

Thank you for your suggestions. I have changed the VDWQT to 0 & will
monitor the effects. In answer to your question, the total number of
buffers for each buffer pool is the same now as when we had HP's. Where can
I find the average elapsed prefetch I/O time ? (I'm new at this).

Also, we currently have a weekly process to reassign objects into pools
based upon their average usage over the past 6 months. This was set up
several years ago, though, & may need to be reviewed.

-JO



Joel Goldstein

Re: Buffer Pool: prefetch disabled/read engines
(in response to Joseph Orndorff)
Jo,
You can get avg I/O wait from the application actg reports,
or Buffer Pool Tool will give this to you for the pool, and for every
object.
If the total number of buffers is the same now, then something else changed
in your system or applications or dataset placement to cause the problem
you are having..... or maybe it's because of your weekly re-assignment of
objects.

Objects should not be assigned to pools based on avng usage (and I don't
know what you mean by this)... they should be assigned to pools based on
access type and working set size.
Also, objects should not be moved/re-assigned in this manner. The only
reason to move
an object is if its access characteristics suddenly change (then find out
why), or as
part of an overall usage pool restructuring.
I believe your contact there for Buffer Pool Tool is Patti Hanken,
503-532-9187

Regards,
Joel

Message text written by DB2 Data Base Discussion List
>Joel,

Thank you for your suggestions. I have changed the VDWQT to 0 & will
monitor the effects. In answer to your question, the total number of
buffers for each buffer pool is the same now as when we had HP's. Where
can
I find the average elapsed prefetch I/O time ? (I'm new at this).

Also, we currently have a weekly process to reassign objects into pools
based upon their average usage over the past 6 months. This was set up
several years ago, though, & may need to be reviewed.

-JO<