Buffer pool settings. DB2 V8 on Z/OS 1.10

William Proctor

Buffer pool settings. DB2 V8 on Z/OS 1.10
Thanks for all replies in advance. We are experimenting with pinning certain tablespaces into their on bufferpools along with the primary index in it's own pool. I expected to see the hit ratio's on those pools to start out low but get to pretty close to 100% after most of the data was accessed. On several of the pools I am getting good numbers but on a couple of them the numbers are staying low around 27% and on one of the pools it is -132. I have enough buffers assigned so that the tablespace will fit. Can someone point out what I'm not understanding about this process?



Bill Proctor
DB2 DBA
DB2 UDB V9 Database Administration for Z/OS
TG
Round Rock, TX 78683-3100
(800) 252-9743, ext. 4847
www.tgslc.org





_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Robert Catterall

Re: Buffer pool settings. DB2 V8 on Z/OS 1.10
(in response to William Proctor)
Where are you getting the numbers you've provided, Bill? From a DB2 monitor?
If so, do you know how the figures were calculated (sometimes this can be
found in the monitor's documentation)? For example, the 27% figure -- what's
that? Depending on how the ratio is calculated, it's conceivable that the
negative value you're seeing has something to do with a preponderance of
asynchronous versus synchronous reads.

In case your numbers are coming from a DB2 monitor, have you tried
corroborating the figures with data from a DISPLAY BUFFERPOOL DETAIL
command?

As our friend Joel Goldstein has been saying for years (and he's right),
it's more important to look at the read I/O rates (synchronous and
asynchronous) associated with a buffer pool, versus focusing on a hit ratio
figure. Do you know how the I/O rates for your pinned objects compare to the
pre-pinned rates? You can get I/O information from a monitor, or from
DISPLAY BUFFERPOOL DETAIL. When I use the latter, I issue the command and
then issue it again in an hour, so that the output from the second execution
of the command will show activity over a 1-hour period of time. I then
divide the numbers by 3600 to get per-second values for the various read I/O
fields (synchronous reads - random, synchronous reads - sequential,
sequential prefetch reads, list prefetch reads, and dynamic prefetch reads).
I add all these together to get total read I/O requests per second for a
pool.

Robert


On Tue, Jan 5, 2010 at 11:07 AM, Proctor, William <[login to unmask email]
> wrote:

> Thanks for all replies in advance. We are experimenting with pinning
> certain tablespaces into their on bufferpools along with the primary index
> in it’s own pool. I expected to see the hit ratio’s on those pools to start
> out low but get to pretty close to 100% after most of the data was
> accessed. On several of the pools I am getting good numbers but on a couple
> of them the numbers are staying low around 27% and on one of the pools it is
> -132. I have enough buffers assigned so that the tablespace will fit. Can
> someone point out what I’m not understanding about this process?
>
>
>
>
>
>
>
> Bill Proctor
>
> DB2 DBA
>
> DB2 UDB V9 Database Administration for Z/OS
>
> TG
>
> Round Rock, TX 78683-3100
>
> (800) 252-9743, ext. 4847
>
> www.tgslc.org
>
>
>
>
>
>
>
>
>
> ------------------------------
>
> [image: IDUG - The Worldwide DB2 User Community! ] < http://www.idug.org/db2-north-america-conference/index.html >
>
> The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are
> not already an IDUG member, please register here. < http://www.idug.org/register >
>



--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L

Joel Goldstein

Re: Buffer pool settings. DB2 V8 on Z/OS 1.10
(in response to Robert Catterall)
Without seeing any numbers or data - One obvious thought here - if you are still having I/O, then the objects do not fit into the pools.

Even though the hit ratio is useless for performance measurement, it would be 100% if all pages stayed in the pool.

A negative hit ratio is quite commonly cause by dynamic prefetch, because many pages are read in that the applications never reference.

You have Buffer Pool Tool - run a collection and send me the Consrep file.
This will tell you everything you need to know.

If you need help, please me know.
Also, download the new workstation code that has the Performance Wizard beta code.

Thanks,
Joel


Joel Goldstein
Responsive Systems
IBM Gold Consultant
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works...... Predicts IO Rate !!
Predicts Group Buffer Pool performance too
www.responsivesystems.com

Buffer Pool Tool for DB2 on www.LinkedIn.com
Watch the 3-Minute Buffer Pool Tool Movie at: www.responsivesystems.com/Movie1

tel. (732) 972-1261
fax.(732) 972-9416
----- Original Message -----
From: Robert Catterall
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, January 05, 2010 2:48 PM
Subject: Re: [DB2-L] Buffer pool settings. DB2 V8 on Z/OS 1.10


Where are you getting the numbers you've provided, Bill? From a DB2 monitor? If so, do you know how the figures were calculated (sometimes this can be found in the monitor's documentation)? For example, the 27% figure -- what's that? Depending on how the ratio is calculated, it's conceivable that the negative value you're seeing has something to do with a preponderance of asynchronous versus synchronous reads.

In case your numbers are coming from a DB2 monitor, have you tried corroborating the figures with data from a DISPLAY BUFFERPOOL DETAIL command?

As our friend Joel Goldstein has been saying for years (and he's right), it's more important to look at the read I/O rates (synchronous and asynchronous) associated with a buffer pool, versus focusing on a hit ratio figure. Do you know how the I/O rates for your pinned objects compare to the pre-pinned rates? You can get I/O information from a monitor, or from DISPLAY BUFFERPOOL DETAIL. When I use the latter, I issue the command and then issue it again in an hour, so that the output from the second execution of the command will show activity over a 1-hour period of time. I then divide the numbers by 3600 to get per-second values for the various read I/O fields (synchronous reads - random, synchronous reads - sequential, sequential prefetch reads, list prefetch reads, and dynamic prefetch reads). I add all these together to get total read I/O requests per second for a pool.

Robert



On Tue, Jan 5, 2010 at 11:07 AM, Proctor, William <[login to unmask email]> wrote:

Thanks for all replies in advance. We are experimenting with pinning certain tablespaces into their on bufferpools along with the primary index in it’s own pool. I expected to see the hit ratio’s on those pools to start out low but get to pretty close to 100% after most of the data was accessed. On several of the pools I am getting good numbers but on a couple of them the numbers are staying low around 27% and on one of the pools it is -132. I have enough buffers assigned so that the tablespace will fit. Can someone point out what I’m not understanding about this process?







Bill Proctor

DB2 DBA

DB2 UDB V9 Database Administration for Z/OS

TG

Round Rock, TX 78683-3100

(800) 252-9743, ext. 4847

www.tgslc.org











----------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.




--
Robert Catterall
Catterall Consulting
www.catterallconsulting.com


------------------------------------------------------------------------------



The IDUG DB2-L Listserv is only part of your membership in IDUG. If you are not already an IDUG member, please register here.

_____________________________________________________________________

* IDUG North America * Tampa, Florida, * May 10-14 2010 * http://IDUG.ORG/NA *
_____________________________________________________________________

http://www.IDUG.org membership is now free.
Do you have people in your office who are not an IDUG member?
Show them how to access the information and help train the next generation of DB2 Users!
_____________________________________________________________________

If you need to change settings, http://www.idug.org/cgi-bin/wa?A0=DB2-L is the home of IDUG's DB2-L