[DB2 Z/OS] Bufferpool clusters part2

Joel Goldstein

[DB2 Z/OS] Bufferpool clusters part2
Hi Mike,

Perhaps this can clear up some of the murkiness.

Working set size, and use frequency, may not have any relationship at all.

Let's take Object A that has 10,000,000 pages, and Object B that has 1,000,000 pages.

All GP requests will be random. These two objects are the top 2 in the pool, in terms of getpage activity.

Object A
Over the course of 15 minutes you issue 2,000,000 GP requests. You actually reference only 1,000 pages.
How large is your wkset? We can't know this without a simulation playing back all the accesses to ALL the objects
in the pool - but the wkset will be 1,000 or less.

Object B
Over the course of the same several minutes you issue 500,000 GP requests. You actually reference 50,000 pages.
Also, let's say that this activity was mostly grouped within 2 mins of your overall 15 minute measurement interval.
What happens in your pool? Because of the high intensity, and short duration, this object will monopolize the pool resources
during this short interval, and cause most of the pages of object A to fall off the lru queue - so they will have to be read in
again the next time they are needed. The max wkset size of the this object, at some point within the 15 minute measurement
interval, will be much larger than object A, it may be most of the available pool pages.
Let's say these short bursts of high activity occur a few times every hour.
It will dominate the pool resources during these periods, and hurt the performance of the
other objects in the pool. SO - moving it out of this pool (removing the large wkset object) will improve the performance
of the remaining objects, and provide more consistent response times and throughput.

Grouping objects by access, they would stay in the same pool, but this is not what you want, to get better performance.

Now Dynamic Prefetch.
From a real performance and pool tuning perspective, this is a mixed bag, and a real pain in the butt.
We don't have any control over dynamic prefetch. For online environments, it does generally ensure better transaction response
times by avoiding synch IOs. At the same time, it often floods the pool with pages that are never referenced by the applications.
This generates sometimes large Negative hit ratios. The actual analysis of DP benefit vs. pain has to take place for each object
that causes significant DP activity. It does impact the performance of objects that don't have a high degree of access (GP activity).

All the Indexes into one big pool
As usual with DB2, it depends - and it depends upon your specific application and objects.
If all the indexes can get good performance together, then yes this can work well.
One of the analysis points is looking at the Top IO Rate/Sec graph. If one or more indices obviously dominate the
IO rate/sec, then they may be very large and very random, and will cost you an IO most of the time - no matter how big
you make the pool with all the indexes in there. They are hurting the performance of the other indexes, and should
usually be moved someplace else. Remember, double clicking on the bar for any object will show you the underlying
object statistical performance data.

We had a large site that was well tuned across several pools years ago. Some bozo consultant walked in the door and told them
to just put all the indexes in one pool, and all the TS in another pool - and make the pools large. Their IO Rate/Sec more than doubled,
and they have had to upgrade their processor several times to get their daily workload thru the system.

Happy to go around again as your questions come to mind. Need to cut this email off before DB2L barfs because it's too long.

Regards,
Joel


Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
(732) 972-1261
----- Original Message -----
From: [login to unmask email]
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Monday, November 27, 2006 9:42 AM
Subject: Re: [DB2-L] [DB2 Z/OS] Bufferpool clusters


Hi Joel,
Thanks for taking the cue.
That reply has cleared up quite a few things for me, but there's one bit which is still a bit murky to me.
You mention systems with almost wholly random access as being slightly weird: naturally, I'm currently tuning a system where that appears to be the case. And that was what I was driving at with the combined pool question: if all the access is random (but includes dynamic prefetch), why wouldn't it be sensible to put all the indexes into one big pool? And if it wouldn't, why would you do it by working set size rather than by, say, putting the objects in a list from most frequently used to least, allocating three pools and putting 1, 4, 7, 10 ... from your ranked list into pool A, 2,5,8,11... into B, 3,6,19,12... into C? (I don't discount the possibility that that question is partly nonsensical because "working set size" and "use frequency" are actually different ways of naming what is essentially the same thing.)
Mike

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Mike Holmans

Re: [DB2 Z/OS] Bufferpool clusters part2
(in response to Joel Goldstein)
Hi Joel,

It cleared up much of the murkiness. Indeed, I even begin to think I
understand. I hope some other people have benefited from those clear
explanations.

Now to run another couple of simulations, because now I think I can do
better (and I've already done quite well).

Thanks very much for your patience.

Mike


_____

From: DB2 Data Base Discussion List [mailto:[login to unmask email] On
Behalf Of Joel Goldstein - Responsive Systems
Sent: 27 November 2006 16:25
To: [login to unmask email]
Subject: Re: [DB2-L] [DB2 Z/OS] Bufferpool clusters part2


Hi Mike,

Perhaps this can clear up some of the murkiness.


---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

Joel Goldstein

Re: [DB2 Z/OS] Bufferpool clusters part2
(in response to Mike Holmans)
My pleasure Mike.

Ask whatever comes to mind, at any time.

Regards,
Joel

Joel Goldstein
Responsive Systems
Buffer Pool Tool for DB2, the worldwide industry standard
Performance software that works......
Predicts Group Buffer Pool performance too!
www.responsivesystems.com
(732) 972-1261
----- Original Message -----
From: [login to unmask email]
Newsgroups: bit.listserv.db2-l
To: [login to unmask email]
Sent: Tuesday, November 28, 2006 11:01 AM
Subject: Re: [DB2-L] [DB2 Z/OS] Bufferpool clusters part2


Hi Joel,

It cleared up much of the murkiness. Indeed, I even begin to think I understand. I hope some other people have benefited from those clear explanations.

Now to run another couple of simulations, because now I think I can do better (and I've already done quite well).

Thanks very much for your patience.

Mike




------------------------------------------------------------------------------
From: DB2 Data Base Discussion List [mailto:[login to unmask email] On Behalf Of Joel Goldstein - Responsive Systems
Sent: 27 November 2006 16:25
To: [login to unmask email]
Subject: Re: [DB2-L] [DB2 Z/OS] Bufferpool clusters part2


Hi Mike,

Perhaps this can clear up some of the murkiness.

--------------------------------------------------------------------------------- Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm

---------------------------------------------------------------------------------
Welcome to the IDUG DB2-L list. To unsubscribe, go to the archives and home page at http://www.idugdb2-l.org/archives/db2-l.html. From that page select "Join or Leave the list". The IDUG DB2-L FAQ is at http://www.idugdb2-l.org. The IDUG List Admins can be reached at [login to unmask email] Find out the latest on IDUG conferences at http://conferences.idug.org/index.cfm