predicting bufferpool sizing

william giannelli

predicting bufferpool sizing

Hello,

First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?

thanks

Bill

 

Philip Sevetson

predicting bufferpool sizing
(in response to william giannelli)
William

As far as I know, bufferpools aren’t dynamically sized, although they may be swapped out depending on the use of available RAM (and whether PAGEFIX is applied). IF you give a Bufferpool 40000 pages, that’s what it will “have” addressable, and they will be loaded by a page retrieval instruction as needed (location of a tablespace/index/SORT page in a bufferpool is determined by a hashing algorithm). Bufferpool size is determined by a DB2 -SET BUFFERPOOL command (and may have a default setting in the compiled system parameters module; I don’t remember!).

Was that the question you were asking?


Philip Sevetson
Computer Systems Manager
FISA-OPA
5 Manhattan West
New York, NY 10001
[login to unmask email]<mailto:[login to unmask email]>
917-991-7052 m
212-857-1659 f
[cid:[login to unmask email]

From: william giannelli <[login to unmask email]>
Sent: Wednesday, May 20, 2020 3:58 PM
To: [login to unmask email]
Subject: [DB2-L] - predicting bufferpool sizing


Hello,

First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?

thanks

Bill



-----End Original Message-----
**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachments

  • image001.png (3.3k)

william giannelli

RE: predicting bufferpool sizing
(in response to Philip Sevetson)

Hi Philip,

Thanks for your response!

What I am asking is, If I am going to create a new partitioned tablespace of 12 4gig partitions how much larger would I need to make the bufferpools where I put the tablespace in?

thanks

Bill

Joel Goldstein

predicting bufferpool sizing
(in response to william giannelli)
Bill,



Proper sizing for existing objects in pools is not a problem.



Objects that don’t exist is largely a guessing game. How will the object be accessed?

All randomly thru indexes? Any scan activity?

Mostly read, or insert/update activity?

Rate of access?



Will pages be re-referenced within close timeframes, allowing pages to be re-used without requiring IO?

The key to good performance is finding the page in the pool and not needing an IO.



When you say Large -- well, how big is big?



If an object is very large, and very random, then it will not require a lot of space because most pages will not be in the pool.

This may be called death by random IO, and we see more of this every year.



You should be able to get some guesstimates about access and performance when it starts being

used on your test/dev system, even though it will probably be a small subset of your full production size.

I have always viewed test/dev as an early warning system -- if it runs poorly there, it a coming disaster for your Prod system.



Certainly I have not provided any direct answer, because there really isn’t one.



Hopefully I‘ve given you some things to think about as planning considerations.



Feel free to ask me any questions directly, or through the list.



Thanks,

Joel









Joel Goldstein

Responsive Systems

732-972-1261

[login to unmask email]



Buffer Pool Tool® for DB2

The only one that works !!



http://www.responsivesystems.com www.responsivesystems.com



From: william giannelli <[login to unmask email]>
Sent: Wednesday, May 20, 2020 12:58 PM
To: [login to unmask email]
Subject: [DB2-L] - predicting bufferpool sizing



Hello,

First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?

thanks

Bill





-----End Original Message-----

Javier Estrada Benavides

RE: predicting bufferpool sizing
(in response to Joel Goldstein)

Hey William, it's been a while

  Actually... Bufferpools can be automatically adjusted by WLM, here are the details: (I haven't tested it myself so I can't give you a "lessons learned")

https://www.ibm.com/support/knowledgecenter/en/SSEPEK_12.0.0/perf/src/tpc/db2z_enableautobpsize.html

Summary here:

- By activating AUTOSIZE, WLM can change the size of a bufferpool by up to 25%

- You'll see the message in SYSLOG whenever this has been changed

- You can't set any thresholds to be automatically changed, though.

The cons:

- As Joel said in the previous response, the art is not quite on the perfect sizing, but on knowing the nature of how the app will read on the table and that will give you hints on how to set the proper thresholds (random vs scan access, trying not to monopolize a common bufferpool or totally isolate it, etc).

Hope that helps

 

Javier Estrada Benavides, Czech Republic / Mexico

IBM Champion for Analytics

IBM Certified System Administrator - Db2 12 for z/OS

IBM Db2 12 DBA for z/OS - 2018 (the ugly brown badge from IBM Open Badge Program)

IBM Certified System Administrator - DB2 11 for z/OS

IBM Certified Database Administrator - DB2 11 DBA for z/OS

Edited By:
Javier Estrada Benavides[Organization Members] @ May 21, 2020 - 10:10 AM (Europe/Prague)

Lance Jackson

predicting bufferpool sizing
(in response to Philip Sevetson)
There actually  is  an AUTOSIZE option of the -ALTER BUFFERPOOL command Phil.On May 20, 2020 4:22 PM Sevetson, Phil <[login to unmask email]> wrote:

William As far as I know, bufferpools aren’t dynamically sized, although they may be swapped out depending on the use of available RAM (and whether PAGEFIX is applied).  IF you give a Bufferpool 40000 pages, that’s what it will “have” addressable, and they will be loaded by a page retrieval instruction as needed (location of a tablespace/index/SORT page in a bufferpool is determined by a hashing algorithm).  Bufferpool size is determined by a DB2 -SET BUFFERPOOL command (and may have a default setting in the compiled system parameters module; I don’t remember!). Was that the question you were asking? Philip SevetsonComputer Systems ManagerFISA-OPA5 Manhattan WestNew York, NY [login to unmask email] m212-857-1659 f From: william giannelli <[login to unmask email]>
Sent: Wednesday, May 20, 2020 3:58 PM
To: [login to unmask email]
Subject: [DB2-L] - predicting bufferpool sizing

Hello,
First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?
thanks
Bill


-----End Original Message-----**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
Attachment Links: image001.png (3 k)  
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG


Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

william giannelli

RE: predicting bufferpool sizing
(in response to Lance Jackson)

thank you all for your help and information!

Is there any concern with changing all to AUTOSIZE (as long as I have the memory to expand the 25%).

Also, how do the Group Bufferpools come into play?

I imagine the GBP are only for any "inter-db2" read-write interests?

thanks

Bill

Joel Goldstein

predicting bufferpool sizing
(in response to Lance Jackson)
Based on experience from yrs ago, you may not be happy with the overall results.



This comment is based on experience from more than a decade ago…. And it is possible it may work better with current releases.



When it increased a pool, and the increase did not provide any benefit, it never reduced it, and sometimes tried a second increase, with no benefit..



Unfortunately, bigger is not always better.







Joel Goldstein

Responsive Systems

732-972-1261



Buffer Pool Tool® for DB2

The only one that works !!



http://www.responsivesystems.com www.responsivesystems.com



From: Lance D. Jackson <[login to unmask email]>
Sent: Thursday, May 21, 2020 6:10 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: predicting bufferpool sizing



There actually is an AUTOSIZE option of the -ALTER BUFFERPOOL command Phil.

On May 20, 2020 4:22 PM Sevetson, Phil <[login to unmask email] <mailto:[login to unmask email]> > wrote:





William



As far as I know, bufferpools aren’t dynamically sized, although they may be swapped out depending on the use of available RAM (and whether PAGEFIX is applied). IF you give a Bufferpool 40000 pages, that’s what it will “have” addressable, and they will be loaded by a page retrieval instruction as needed (location of a tablespace/index/SORT page in a bufferpool is determined by a hashing algorithm). Bufferpool size is determined by a DB2 -SET BUFFERPOOL command (and may have a default setting in the compiled system parameters module; I don’t remember!).



Was that the question you were asking?





Philip Sevetson

Computer Systems Manager

FISA-OPA

5 Manhattan West

New York, NY 10001

[login to unmask email] <mailto:[login to unmask email]>

917-991-7052 m

212-857-1659 f





From: william giannelli <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Wednesday, May 20, 2020 3:58 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - predicting bufferpool sizing



Hello,

First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?

thanks

Bill





-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----



-----End Original Message-----

Jim Tonchick

predicting bufferpool sizing
(in response to william giannelli)

Remember the 80/20 rule.  You get 80% of the benefit from 20% effort (or in this case, 20% change to the pool).  
You need to know from where you are starting.  Take baseline measurements be they pool stats or query response times.  Then implement this cycle: make a 20% change, plus or minus.  Measure again.   If there is no benefit, go back to your original settings.  If you see a benefit, try another change and remeasure.  Stop changing when there is no longer a benefit.
And always remember, there are so many things that can affect application performance it may not be possible to tune a "bad" application by database buffer pools alone.  It may take changing SQL statements or physical database design, like new indexes or tablespace partitioning.
When in doubt, read Joel's books and articles.  He and they have helped me in my career since the days of DB2 v2.1.

-----Original Message-----
From: Joel Goldstein <[login to unmask email]>
To: db2-l <[login to unmask email]>
Sent: Thu, May 21, 2020 09:42 AM
Subject: [DB2-L] - RE: predicting bufferpool sizing


#yiv2819365043 #yiv2819365043 -- _filtered {} _filtered {} #yiv2819365043 #yiv2819365043 p.yiv2819365043MsoNormal, #yiv2819365043 li.yiv2819365043MsoNormal, #yiv2819365043 div.yiv2819365043MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:11.0pt;font-family:sans-serif;} #yiv2819365043 a:link, #yiv2819365043 span.yiv2819365043MsoHyperlink {color:blue;text-decoration:underline;} #yiv2819365043 span.yiv2819365043EmailStyle22 {font-family:sans-serif;color:windowtext;} #yiv2819365043 .yiv2819365043MsoChpDefault {font-size:10.0pt;} _filtered {} #yiv2819365043 div.yiv2819365043WordSection1 {} #yiv2819365043
Based on experience from yrs ago, you may not be happy with the overall results.

 

This comment is based on experience from more than a decade ago….  And it is possible it may work better with current releases.

 

When it increased a pool, and the increase did not provide any benefit, it never reduced it, and sometimes tried a second increase, with no benefit..

 

Unfortunately, bigger is not always better.

 

 

 

Joel Goldstein

Responsive Systems

732-972-1261

 

Buffer Pool Tool® for DB2

   The only one that works !!

 

www.responsivesystems.com

 

From: Lance D. Jackson <[login to unmask email]>
Sent: Thursday, May 21, 2020 6:10 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: predicting bufferpool sizing

 

There actually  is  an AUTOSIZE option of the -ALTER BUFFERPOOL command Phil.


On May 20, 2020 4:22 PM Sevetson, Phil <[login to unmask email]> wrote:

 

 

William

 

As far as I know, bufferpools aren’t dynamically sized, although they may be swapped out depending on the use of available RAM (and whether PAGEFIX is applied).  IF you give a Bufferpool 40000 pages, that’s what it will “have” addressable, and they will be loaded by a page retrieval instruction as needed (location of a tablespace/index/SORT page in a bufferpool is determined by a hashing algorithm).  Bufferpool size is determined by a DB2 -SET BUFFERPOOL command (and may have a default setting in the compiled system parameters module; I don’t remember!).

 

Was that the question you were asking?

 

 

Philip Sevetson

Computer Systems Manager

FISA-OPA

5 Manhattan West

New York, NY 10001

[login to unmask email]

917-991-7052 m

212-857-1659 f



 

From: william giannelli <[login to unmask email]>
Sent: Wednesday, May 20, 2020 3:58 PM
To: [login to unmask email]
Subject: [DB2-L] - predicting bufferpool sizing

 

Hello,

First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?

thanks

Bill

 

 

-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----


 
-----End Original Message-----
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
Try BCV5, the BCV5 Masking Tool, & XDM a rapid Refresh/Clone/TDM Suite for Db2 z & distributed.
DBARS -Audit,record,& block Db2 accesses to sensitive data real-time, NO audit trace or log required
http://www.ESAIGroup.com/IDUG



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2

David Williams

predicting bufferpool sizing
(in response to Joel Goldstein)
Hi,

That is the problem.

If self-tuning does not realise that the working set will not fit in memory it will just keep growing the bufferpool until it exhausts memory.

The real answer is that if predicting bufferpool sizing was easy that the server would do it already.

If not everything fits in memory then you need to prioritise which data is cached in the bufferpools and no smart/self-tuning/AI can decide that for you!

Also if not everything fits due to table scans/large index scans the answer may well be not to add bufferpool but to add indexes, redesign the schema, add caching in the app layer, force users to submit more selective query criteria.

I would say first ask developers

- how much data will go into the table per day (Some developers will not know this!)
- how many days data will be kept. (Developer tend to forget about the back end of the data lifecycle, archiving/purging/moving to cold storage. E.g. move to datawarehouse or CSV files with attached schema definition which be be loaded into any data store/have extracts into the dreaded Excel!).
- how many days data going back will be commonly queried.

Most developers cannot answer all those questions!

The usual answer is either guess or put new table into a general bufferpool and adjust if/when it slows down.

First answer then will be to look at the queries and check if the appropriate indexes are in place and if the app level caching can help. Or some other cache in front of the db e.g. memcached/varnish cache.

Adding bufferpool is last if other measures cannot reduce the load on the bufferpool!

Regards,
David.

> On 21 May 2020 at 15:42 Joel Goldstein <[login to unmask email]> wrote:
>
>
> Based on experience from yrs ago, you may not be happy with the overall results.
>
>
>
> This comment is based on experience from more than a decade ago…. And it is possible it may work better with current releases.
>
>
>
> When it increased a pool, and the increase did not provide any benefit, it never reduced it, and sometimes tried a second increase, with no benefit..
>
>
>
> Unfortunately, bigger is not always better.
>
>
>
>
>
>
>
> Joel Goldstein
>
> Responsive Systems
>
> 732-972-1261
>
>
>
> Buffer Pool Tool® for DB2
>
> The only one that works !!
>
>
>
> http://www.responsivesystems.com www.responsivesystems.com
>
>
>
> From: Lance D. Jackson <[login to unmask email]>
> Sent: Thursday, May 21, 2020 6:10 AM
> To: [login to unmask email]
> Subject: [DB2-L] - RE: predicting bufferpool sizing
>
>
>
> There actually is an AUTOSIZE option of the -ALTER BUFFERPOOL command Phil.
>
> On May 20, 2020 4:22 PM Sevetson, Phil <[login to unmask email] <mailto:[login to unmask email]> > wrote:
>
>
>
>
>
> William
>
>
>
> As far as I know, bufferpools aren’t dynamically sized, although they may be swapped out depending on the use of available RAM (and whether PAGEFIX is applied). IF you give a Bufferpool 40000 pages, that’s what it will “have” addressable, and they will be loaded by a page retrieval instruction as needed (location of a tablespace/index/SORT page in a bufferpool is determined by a hashing algorithm). Bufferpool size is determined by a DB2 -SET BUFFERPOOL command (and may have a default setting in the compiled system parameters module; I don’t remember!).
>
>
>
> Was that the question you were asking?
>
>
>
>
>
> Philip Sevetson
>
> Computer Systems Manager
>
> FISA-OPA
>
> 5 Manhattan West
>
> New York, NY 10001
>
> [login to unmask email] <mailto:[login to unmask email]>
>
> 917-991-7052 m
>
> 212-857-1659 f
>
>
>
>
>
> From: william giannelli <[login to unmask email] <mailto:[login to unmask email]> >
> Sent: Wednesday, May 20, 2020 3:58 PM
> To: [login to unmask email] <mailto:[login to unmask email]>
> Subject: [DB2-L] - predicting bufferpool sizing
>
>
>
> Hello,
>
> First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?
>
> thanks
>
> Bill
>
>
>
>
>
> -----End Original Message-----
>
> **This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**
>
> -----End Original Message-----
>
>
>
> -----End Original Message-----
>
>
> -----End Original Message-----

Joel Goldstein

predicting bufferpool sizing
(in response to Jim Tonchick)
Hi Jim,



Nice to hear from another dinosaur…



Stay well,



Joel





Joel Goldstein

Responsive Systems

732-972-1261



Buffer Pool Tool® for DB2

The only one that works !!



http://www.responsivesystems.com www.responsivesystems.com



From: Jim Tonchick <[login to unmask email]>
Sent: Thursday, May 21, 2020 8:04 AM
To: [login to unmask email]
Subject: [DB2-L] - RE: predicting bufferpool sizing




Remember the 80/20 rule. You get 80% of the benefit from 20% effort (or in this case, 20% change to the pool).



You need to know from where you are starting. Take baseline measurements be they pool stats or query response times. Then implement this cycle: make a 20% change, plus or minus. Measure again. If there is no benefit, go back to your original settings. If you see a benefit, try another change and remeasure. Stop changing when there is no longer a benefit.



And always remember, there are so many things that can affect application performance it may not be possible to tune a "bad" application by database buffer pools alone. It may take changing SQL statements or physical database design, like new indexes or tablespace partitioning.



When in doubt, read Joel's books and articles. He and they have helped me in my career since the days of DB2 v2.1.

-----Original Message-----
From: Joel Goldstein <[login to unmask email] <mailto:[login to unmask email]> >
To: db2-l <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Thu, May 21, 2020 09:42 AM
Subject: [DB2-L] - RE: predicting bufferpool sizing



Based on experience from yrs ago, you may not be happy with the overall results.



This comment is based on experience from more than a decade ago…. And it is possible it may work better with current releases.



When it increased a pool, and the increase did not provide any benefit, it never reduced it, and sometimes tried a second increase, with no benefit..



Unfortunately, bigger is not always better.







Joel Goldstein

Responsive Systems

732-972-1261



Buffer Pool Tool® for DB2

The only one that works !!



http://www.responsivesystems.com www.responsivesystems.com



From: Lance D. Jackson <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Thursday, May 21, 2020 6:10 AM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - RE: predicting bufferpool sizing



There actually is an AUTOSIZE option of the -ALTER BUFFERPOOL command Phil.

On May 20, 2020 4:22 PM Sevetson, Phil <[login to unmask email] <mailto:[login to unmask email]> > wrote:





William



As far as I know, bufferpools aren’t dynamically sized, although they may be swapped out depending on the use of available RAM (and whether PAGEFIX is applied). IF you give a Bufferpool 40000 pages, that’s what it will “have” addressable, and they will be loaded by a page retrieval instruction as needed (location of a tablespace/index/SORT page in a bufferpool is determined by a hashing algorithm). Bufferpool size is determined by a DB2 -SET BUFFERPOOL command (and may have a default setting in the compiled system parameters module; I don’t remember!).



Was that the question you were asking?





Philip Sevetson

Computer Systems Manager

FISA-OPA

5 Manhattan West

New York, NY 10001

[login to unmask email] <mailto:[login to unmask email]>

917-991-7052 m

212-857-1659 f





From: william giannelli <[login to unmask email] <mailto:[login to unmask email]> >
Sent: Wednesday, May 20, 2020 3:58 PM
To: [login to unmask email] <mailto:[login to unmask email]>
Subject: [DB2-L] - predicting bufferpool sizing



Hello,

First forgive my lack of knowledge in this area. the size of the bufferpools depend on how many pages you "need" in memory at one time? Correct? (understanding that pages get swapped in and out as needed). So sizing depends on watching the BP activity and I/O? Correct? But how do you predict ahead of time the sizing needed for a new large table before its been created and accessed?

thanks

Bill





-----End Original Message-----

**This e-mail, including any attachments, may be confidential, privileged, or otherwise legally protected. It is intended only for the addressee. If you received this e-mail in error or from someone who was not authorized to send it to you, do not disseminate, copy, or otherwise use this e-mail or its attachments. Please notify the sender immediately by reply e-mail and delete the e-mail from your system.**

-----End Original Message-----



-----End Original Message-----



-----End Original Message-----



-----End Original Message-----

Walter Jani&#223;en

RE: predicting bufferpool sizing
(in response to Joel Goldstein)

Hi

There is also an Option that you can simulate. Beneath the VPSIZE there is also the SPSIZE, which you can increase and then you can see via a DIS BUFFERPOOL how many I/O you will save. I would say the less you will reread a page the smaller the bufferpool can be.

Michael Hannan

RE: predicting bufferpool sizing
(in response to David Williams)



In Reply to David Williams:

The real answer is that if predicting bufferpool sizing was easy that the server would do it already.

The usual answer is either guess or put new table into a general bufferpool and adjust if/when it slows down.

First answer then will be to look at the queries and check if the appropriate indexes are in place and if the app level caching can help. Or some other cache in front of the db e.g. memcached/varnish cache.

Regards,
David.

Yes (agreeing), surely we all put objects into a general pool at first unless we know a lot about them.

The behavior of access to a table and its indexes will change with changing access paths and index tuning, therefore trying to tune the BP for very specific objects is a waste of time, maybe with some special object exceptions, until you reckon you have your access paths as good as possible. So absolutely tune your indexes and access paths first. 

Allow enough BP space for for the prefetch for parallel processes, if can work out how many of those could be happening, in addition to the random probe coverage for indexed lookups.

In the end, is the total I/O with its elapsed and CPU cost in the BP acceptable. That objective has never changed, although I/O elapsed time has shrunk a lot. Was about 20 msec when I first started. The CPU cost for an I/O remains significant, but offload prefetch CPU cost to zIIP has been a nice gain. Prefetch Pages aren't free though. The application still has to do a Getpage to prefetched pages (when it uses the prefetched pages), so we want to minimise the number of pages visited in the access paths. A lot of tuning SQLs without rewrite is about reducing Getpages on specific objects, or getting tables to join in the right sequence.

It has always been tough to measure gains of BP improvement against a varying workload. Reward for time invested can be low. As long as the BP performance is acceptable, and we cannot find anything that is clearly wrong.

Michael Hannan,
DB2 Application Performance Specialist

Edited By:
Michael Hannan[Organization Members] @ May 22, 2020 - 08:42 AM (Australia/Melbourne)