What should the default bufferpool be?

Mike Holmans

What should the default bufferpool be?
I'm just about to do a bufferpool sorting-out exercise on one of our
subsystems, where things have been somewhat neglected, shall we say.

I'll be using Bufferpool Tool to sort out a separation into five pools -
system (BP0), sort, mostly sequential, mostly random, and a pool to put
control tables and frequently used indexes which might as well be
permanently resident. Nothing very controversial there.

But I rather expect that once I've done it, that will be the last anyone
does about it for some time. That won't prevent some application needing to
create a new tablespace or index. But which bufferpoool to assign it to? I'm
guessing that the DBAs for the apps won't necessarily have much idea about
which way a new table/indexspace will mostly be accessed, and will just want
to have a default. Which is likely to be worse - the mostly sequential or
the mostly random? My guess is that it is less likely to have a major
deleterious effect to have random datasets wrongly assigned to the
sequential pool than the other way around, but no doubt the esteemed Mr
Goldstein will be able to explain why I've got that arse about face.

Mike Holmans
Database Consultant
BT Affinitis Computing Partners
[login to unmask email]

This post is solely the opinion of its author and does not necessarily
reflect BT's view



[login to unmask email]

Re: What should the default bufferpool be?
(in response to Mike Holmans)
That depends... If the separate VBP objects were in logical databases by
this grouping then you could just use their intended VBP as that DB's
default. Otherwise it may be a craps shoot...

George



Bill Gallagher

Re: What should the default bufferpool be?
(in response to Eric Pearson)
Mike,

I would agree with you, probably the best place to put new objects would be
in the "mostly sequential" pool. That's what we typically do here in our
shop.

As an alternative, have you considered creating a sixth pool, which is
where all the new objects would go as a "staging area" until they can be
analyzed and assigned to one of the other pools? The advantage to doing
this is that it would be an easy way to identify those objects which have
not yet been classified as random/sequential/permanently cached. I would
tune this pool with the same BP parameters as your "mostly sequential"
pool.

Just something to think about.

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


Bill Gallagher, DBA
Phoenix Life Insurance
Enfield, CT 06083

IBM Certified Solutions Expert - DB2 UDB v7.1 Database Administration for
OS/390
IBM Certified Solutions Expert - DB2 UDB v7.1 Database Administration for
UNIX, Windows, and OS/2




mike.holmans@
BT.COM To: [login to unmask email]
Sent by: "DB2 cc:
Data Base Subject: What should the default bufferpool be?
Discussion
List"
<[login to unmask email]
OM>


12/14/01
09:37 AM
Please
respond to
"DB2 Data
Base
Discussion
List"






I'm just about to do a bufferpool sorting-out exercise on one of our
subsystems, where things have been somewhat neglected, shall we say.

I'll be using Bufferpool Tool to sort out a separation into five pools -
system (BP0), sort, mostly sequential, mostly random, and a pool to put
control tables and frequently used indexes which might as well be
permanently resident. Nothing very controversial there.

But I rather expect that once I've done it, that will be the last anyone
does about it for some time. That won't prevent some application needing to
create a new tablespace or index. But which bufferpoool to assign it to?
I'm
guessing that the DBAs for the apps won't necessarily have much idea about
which way a new table/indexspace will mostly be accessed, and will just
want
to have a default. Which is likely to be worse - the mostly sequential or
the mostly random? My guess is that it is less likely to have a major
deleterious effect to have random datasets wrongly assigned to the
sequential pool than the other way around, but no doubt the esteemed Mr
Goldstein will be able to explain why I've got that arse about face.

Mike Holmans
Database Consultant
BT Affinitis Computing Partners
[login to unmask email]

This post is solely the opinion of its author and does not necessarily
reflect BT's view








Eric Pearson

Re: What should the default bufferpool be?
(in response to truman.g.brown@VERIZON.COM)
I really look forward to hearing Joel G's answer to this one!

Regards,
eric pearson
NS ITO Database Support


-----Original Message-----
From: [login to unmask email] [mailto:[login to unmask email]
Sent: Friday, December 14, 2001 9:37 AM
To: [login to unmask email]
Subject: What should the default bufferpool be?


I'm just about to do a bufferpool sorting-out exercise on one of our
subsystems, where things have been somewhat neglected, shall we say.

I'll be using Bufferpool Tool to sort out a separation into five pools -
system (BP0), sort, mostly sequential, mostly random, and a pool to put
control tables and frequently used indexes which might as well be
permanently resident. Nothing very controversial there.

But I rather expect that once I've done it, that will be the last anyone
does about it for some time. That won't prevent some application needing to
create a new tablespace or index. But which bufferpoool to assign it to? I'm
guessing that the DBAs for the apps won't necessarily have much idea about
which way a new table/indexspace will mostly be accessed, and will just want
to have a default. Which is likely to be worse - the mostly sequential or
the mostly random? My guess is that it is less likely to have a major
deleterious effect to have random datasets wrongly assigned to the
sequential pool than the other way around, but no doubt the esteemed Mr
Goldstein will be able to explain why I've got that arse about face.

Mike Holmans
Database Consultant
BT Affinitis Computing Partners
[login to unmask email]

This post is solely the opinion of its author and does not necessarily
reflect BT's view








Joel Goldstein

Re: What should the default bufferpool be?
(in response to Bill Gallagher)
Mike,
It sounds like your biggest problem is your feeling that once the
initial pool tuning is done, nothing more will happen for a long time.
This is one of the
biggest problems in performance management. Everyone is understaffed and
overworked - and performance needs constant care and feeding to maintain
consistent response and throughput. I always recommend running Buffer
Pool Tool
at least twice a month and looking at the statistics (or maybe loading into
DB2 History tables)
to be sure performance is remaining within reasonable levels.

I think that having two "staging" pools would be the best approach. One
for indexes
and the other for tablespaces. If you have the memory to use this
approach,
a few thousand buffers for each pool, and favoring the index pool with
memory is usually
best. Perhaps the default pool thresholds for the tablespaces, and
lowering the
vpseqt for the index pool to 40-50%. Alternatively, I'd lower the vpseqt
for the
tablespace pool also so sequential activity doesn't destry random
performance.
(If your going to scan it, it desn't need much memory for that since you'll
just read it
in again anyway).
New applications should be staged into them, and then
moved into the proper pools based upon application performance/behavior.
This approach has saved several installations from performance disasters
when new
applications didn't run as expected when moved to production.
If you can use this approach, it does mean that somebody must look at the
application and pools
immediately after the app is moved in to production, and make pool changes.

As with many performance issues, there is no silver bullet answer.

Regards,
Joel


Message text written by DB2 Data Base Discussion List
>I'm just about to do a bufferpool sorting-out exercise on one of our
subsystems, where things have been somewhat neglected, shall we say.

I'll be using Bufferpool Tool to sort out a separation into five pools -
system (BP0), sort, mostly sequential, mostly random, and a pool to put
control tables and frequently used indexes which might as well be
permanently resident. Nothing very controversial there.

But I rather expect that once I've done it, that will be the last anyone
does about it for some time. That won't prevent some application needing to
create a new tablespace or index. But which bufferpoool to assign it to?
I'm
guessing that the DBAs for the apps won't necessarily have much idea about
which way a new table/indexspace will mostly be accessed, and will just
want
to have a default. Which is likely to be worse - the mostly sequential or
the mostly random? My guess is that it is less likely to have a major
deleterious effect to have random datasets wrongly assigned to the
sequential pool than the other way around, but no doubt the esteemed Mr
Goldstein will be able to explain why I've got that arse about face.

Mike Holmans
Database Consultant
BT Affinitis Computing Partners
[login to unmask email]<



Mike Holmans

Re: What should the default bufferpool be?
(in response to Joel Goldstein)
Joel,

Yes, my biggest problem is that once I've done the initial tuning, that will
be the end of it for a long time.

So the various things you suggest are of no real use to me as advice,
because I can't do them. And I certainly don't have the memory to back
several thousand pages of buffer space which won't be used until someone
creates a new object at some unspecified time in the future.

So my original question remains. Assuming that I have effectively two
bufferpools into which new objects could go, one for datasets mostly
accessed randomly and one for sequential, then which is likely to be the
safer default?

I think you're saying that the sequential pool would be best, because if the
access is mainly sequential, then it will be correct and if it's random,
then as long as VPSEQT isn't set too high, there's going to be some where
for the random pages to go. If, on the other hand, a sequentially accessed
object goes into a random pool, then it's either going to run into the very
low VPSEQT that a random pool should have or it will wipe out swathes of
pages at once and cause lots of other things to get re-read.

Mike Holmans
Database Consultant
BT Affinitis Computing Partners
[login to unmask email]

This post is solely the opinion of its author and does not necessarily
reflect BT's view



>>-----Original Message-----
>>From: Joel Goldstein [mailto:[login to unmask email]
>>Sent: Friday, December 14, 2001 7:09 PM
>>To: [login to unmask email]
>>Subject: Re: [DB2-L] What should the default bufferpool be?
>>
>>
>>Mike,
>>It sounds like your biggest problem is your feeling that once the
>>initial pool tuning is done, nothing more will happen for a
>>long time.
>>This is one of the
>>biggest problems in performance management. Everyone is
>>understaffed and
>>overworked - and performance needs constant care and feeding
>>to maintain
>>consistent response and throughput. I always recommend
>>running Buffer
>>Pool Tool
>>at least twice a month and looking at the statistics (or
>>maybe loading into
>>DB2 History tables)
>>to be sure performance is remaining within reasonable levels.
>>
>>I think that having two "staging" pools would be the best
>>approach. One
>>for indexes
>>and the other for tablespaces. If you have the memory to use this
>>approach,
>>a few thousand buffers for each pool, and favoring the index pool with
>>memory is usually
>>best. Perhaps the default pool thresholds for the tablespaces, and
>>lowering the
>>vpseqt for the index pool to 40-50%. Alternatively, I'd
>>lower the vpseqt
>>for the
>>tablespace pool also so sequential activity doesn't destry random
>>performance.
>>(If your going to scan it, it desn't need much memory for
>>that since you'll
>>just read it
>>in again anyway).
>>New applications should be staged into them, and then
>>moved into the proper pools based upon application
>>performance/behavior.
>>This approach has saved several installations from
>>performance disasters
>>when new
>>applications didn't run as expected when moved to production.
>>If you can use this approach, it does mean that somebody must
>>look at the
>>application and pools
>>immediately after the app is moved in to production, and make
>>pool changes.
>>
>>As with many performance issues, there is no silver bullet answer.
>>
>>Regards,
>>Joel
>>
>>
>>Message text written by DB2 Data Base Discussion List
>>>I'm just about to do a bufferpool sorting-out exercise on one of our
>>subsystems, where things have been somewhat neglected, shall we say.
>>
>>I'll be using Bufferpool Tool to sort out a separation into
>>five pools -
>>system (BP0), sort, mostly sequential, mostly random, and a
>>pool to put
>>control tables and frequently used indexes which might as well be
>>permanently resident. Nothing very controversial there.
>>
>>But I rather expect that once I've done it, that will be the
>>last anyone
>>does about it for some time. That won't prevent some
>>application needing to
>>create a new tablespace or index. But which bufferpoool to
>>assign it to?
>>I'm
>>guessing that the DBAs for the apps won't necessarily have
>>much idea about
>>which way a new table/indexspace will mostly be accessed, and
>>will just
>>want
>>to have a default. Which is likely to be worse - the mostly
>>sequential or
>>the mostly random? My guess is that it is less likely to have a major
>>deleterious effect to have random datasets wrongly assigned to the
>>sequential pool than the other way around, but no doubt the
>>esteemed Mr
>>Goldstein will be able to explain why I've got that arse about face.
>>
>>Mike Holmans
>>Database Consultant
>>BT Affinitis Computing Partners
>>[login to unmask email]<
>>
>>=======================To change your
>>subscription options or to cancel your subscription visit the
>>DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
>>list can
>>



Joel Goldstein

Re: What should the default bufferpool be?
(in response to Mike Holmans)
Mike,
GIven your time constaints, I agree that the sequential pool would be best,
and the vpseqt will be an important setting to keep tspace available for
random access, if this happens.
If the SP pool is => 20,000 pages, I'd try vpseqt at 40%, if less than
20,000
I'd try 50%. Just some guesses...
Regards,
Joel



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

Yes, my biggest problem is that once I've done the initial tuning, that
will
be the end of it for a long time.

So the various things you suggest are of no real use to me as advice,
because I can't do them. And I certainly don't have the memory to back
several thousand pages of buffer space which won't be used until someone
creates a new object at some unspecified time in the future.

So my original question remains. Assuming that I have effectively two
bufferpools into which new objects could go, one for datasets mostly
accessed randomly and one for sequential, then which is likely to be the
safer default?

I think you're saying that the sequential pool would be best, because if
the
access is mainly sequential, then it will be correct and if it's random,
then as long as VPSEQT isn't set too high, there's going to be some where
for the random pages to go. If, on the other hand, a sequentially accessed
object goes into a random pool, then it's either going to run into the very
low VPSEQT that a random pool should have or it will wipe out swathes of
pages at once and cause lots of other things to get re-read.

Mike Holmans
Database Consultant
BT Affinitis Computing Partners
[login to unmask email]<



Mike Holmans

Re: What should the default bufferpool be?
(in response to Joel Goldstein)
Joel,

Thanks very much.

Mike

>>-----Original Message-----
>>From: Joel Goldstein [mailto:[login to unmask email]
>>Sent: Monday, December 17, 2001 3:42 PM
>>To: [login to unmask email]
>>Subject: Re: [DB2-L] What should the default bufferpool be?
>>
>>
>>Mike,
>>GIven your time constaints, I agree that the sequential pool
>>would be best,
>>and the vpseqt will be an important setting to keep tspace
>>available for
>>random access, if this happens.
>>If the SP pool is => 20,000 pages, I'd try vpseqt at 40%, if less than
>>20,000
>>I'd try 50%. Just some guesses...
>>Regards,
>>Joel
>>
>>
>>
>>Message text written by DB2 Data Base Discussion List
>>>Joel,
>>
>>Yes, my biggest problem is that once I've done the initial
>>tuning, that
>>will
>>be the end of it for a long time.
>>
>>So the various things you suggest are of no real use to me as advice,
>>because I can't do them. And I certainly don't have the memory to back
>>several thousand pages of buffer space which won't be used
>>until someone
>>creates a new object at some unspecified time in the future.
>>
>>So my original question remains. Assuming that I have effectively two
>>bufferpools into which new objects could go, one for datasets mostly
>>accessed randomly and one for sequential, then which is
>>likely to be the
>>safer default?
>>
>>I think you're saying that the sequential pool would be best,
>>because if
>>the
>>access is mainly sequential, then it will be correct and if
>>it's random,
>>then as long as VPSEQT isn't set too high, there's going to
>>be some where
>>for the random pages to go. If, on the other hand, a
>>sequentially accessed
>>object goes into a random pool, then it's either going to run
>>into the very
>>low VPSEQT that a random pool should have or it will wipe out
>>swathes of
>>pages at once and cause lots of other things to get re-read.
>>
>>Mike Holmans
>>Database Consultant
>>BT Affinitis Computing Partners
>>[login to unmask email]<
>>
>>=======================To change your
>>subscription options or to cancel your subscription visit the
>>DB2-L webpage at http://www.ryci.com/db2-l. The owners of the
>>list can
>>