Group buffer pool Tuning

Sridharan Muthuraman

Group buffer pool Tuning

Hi All,

We are currently on DB2 V11(NFM). We are planning to perform group buffer pool tuning in our environment after a long time. Upon performing health check we found couple of GBP with very high Directory/Data ratio of 270 (GBP14) and 274 (GBP20). So we are planning to tune these GBP. For GBP14 we see below output for DIS GBPOOL.

DSNB750I -D2PD DISPLAY FOR GROUP BUFFER POOL GBP14 FOLLOWS

DSNB755I -D2PD DB2 GROUP BUFFER POOL STATUS

             CONNECTED                                 = YES

             CURRENT DIRECTORY TO DATA RATIO           = 270

             PENDING DIRECTORY TO DATA RATIO           = 270

             CURRENT GBPCACHE ATTRIBUTE                = YES

             PENDING GBPCACHE ATTRIBUTE               = YES

D XCF command output shows that only 50% (INITSIZE) of the space is allocated as shown below.

DUPLEXING REBUILD NEW STRUCTURE

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

ALLOCATION TIME: 08/20/2017 06:06:42

CFNAME         : P1CFSB2

COUPLING FACILITY: 002964.IBM.02.0000000A46D7

                   PARTITION: 04   CPCID: 00

STORAGE CONFIGURATION     ALLOCATED         MAXIMUM     %

ACTUAL SIZE:                              2798 M                   5578 M          50

DB2 Statistics report shows GBP14 has very poor hit ratio GBP SYN.READ(XI) HIT RATIO(%)  =   36.03 %.

GROUP BP14                     QUANTITY /SECOND /THREAD /COMMIT  

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

GROUP BP R/W RATIO (%)           189.32     N/A    N/A     N/A  

GBP SYN.READ(XI) HIT RATIO(%)     36.03     N/A     N/A     N/A  

GBP-DEPENDENT GETPAGES         2897.3M 4865.67   246.20     1.47  

SYN.READ(XI)-DATA RETURNED     1260.2K     2.12     0.11     0.00  

SYN.READ(XI)-NO DATA RETURN     2237.7K     3.76     0.19     0.00  

 

I was wondering why AUTO ALTER ( we have enabled it) was only adjusting only the Dir/Data ratio (despite very low Synch read(XI) hit ratio) and did not go for increase in GBP size. Does that mean neither Directory entries nor Data pages reached FULLTHRESHOLD?  Would increasing the GBP size (twice) automatically help AUTO ALTER to bring down the Dir/Data ratio in the next allocation? What Dir/Data ratio should I aim for?

Similarly, we have GBP20 with very high Dir/Data ratio of 274 which has 50% (INITSIZE) allocated and has a very good SYNCH READ(XI) HIT RATIO of 99%. So, is it sufficient if I just alter the Dir/Data ratio alone for this GBP20 without increasing the GBP size? What should be an ideal Dir/Data ratio for this GBP20? Please advice.

 

Thanks,
Sridharan.M

Olle Brostrom

Group buffer pool Tuning
(in response to Sridharan Muthuraman)
Hi,
The number of directory entries have to be enough many to keep track of all unique data pages in the BP14 virtual pools for all your members in the DSG, depending on your degree of shared pages you need more or less directory entries.
Since your hit ratio is very low I assume you have a quite low degree of data sharing in this pool, this have also caused the higher ratio of directory to data pages.
A very important counter on the –DIS BPOOL command is:
DSNB788I *DP01 CROSS INVALIDATIONS
DUE TO DIRECTORY RECLAIMS = 0
DUE TO WRITES = 136243371
EXPLICIT = 0
When you get CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS > 0 DB2 try to change the ratio, which obviously is your case.
This cause DB2 to loose track on your pages in the GBP and can drive your I/O!

I do not believe you need to increase the INITSIZE, The structure can grow up to SIZE with AUTO ALTER on the structure.

Best Regards

Olle Broström

From: Sridharan Muthuraman [mailto:[login to unmask email]
Sent: den 10 oktober 2017 08:14
To: [login to unmask email]
Subject: [DB2-L] - Group buffer pool Tuning


Hi All,

We are currently on DB2 V11(NFM). We are planning to perform group buffer pool tuning in our environment after a long time. Upon performing health check we found couple of GBP with very high Directory/Data ratio of 270 (GBP14) and 274 (GBP20). So we are planning to tune these GBP. For GBP14 we see below output for DIS GBPOOL.

DSNB750I -D2PD DISPLAY FOR GROUP BUFFER POOL GBP14 FOLLOWS

DSNB755I -D2PD DB2 GROUP BUFFER POOL STATUS

CONNECTED = YES

CURRENT DIRECTORY TO DATA RATIO = 270

PENDING DIRECTORY TO DATA RATIO = 270

CURRENT GBPCACHE ATTRIBUTE = YES

PENDING GBPCACHE ATTRIBUTE = YES

D XCF command output shows that only 50% (INITSIZE) of the space is allocated as shown below.

DUPLEXING REBUILD NEW STRUCTURE

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

ALLOCATION TIME: 08/20/2017 06:06:42

CFNAME : P1CFSB2

COUPLING FACILITY: 002964.IBM.02.0000000A46D7

PARTITION: 04 CPCID: 00

STORAGE CONFIGURATION ALLOCATED MAXIMUM %

ACTUAL SIZE: 2798 M 5578 M 50

DB2 Statistics report shows GBP14 has very poor hit ratio GBP SYN.READ(XI) HIT RATIO(%) = 36.03 %.

GROUP BP14 QUANTITY /SECOND /THREAD /COMMIT

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

GROUP BP R/W RATIO (%) 189.32 N/A N/A N/A

GBP SYN.READ(XI) HIT RATIO(%) 36.03 N/A N/A N/A

GBP-DEPENDENT GETPAGES 2897.3M 4865.67 246.20 1.47

SYN.READ(XI)-DATA RETURNED 1260.2K 2.12 0.11 0.00

SYN.READ(XI)-NO DATA RETURN 2237.7K 3.76 0.19 0.00



I was wondering why AUTO ALTER ( we have enabled it) was only adjusting only the Dir/Data ratio (despite very low Synch read(XI) hit ratio) and did not go for increase in GBP size. Does that mean neither Directory entries nor Data pages reached FULLTHRESHOLD? Would increasing the GBP size (twice) automatically help AUTO ALTER to bring down the Dir/Data ratio in the next allocation? What Dir/Data ratio should I aim for?

Similarly, we have GBP20 with very high Dir/Data ratio of 274 which has 50% (INITSIZE) allocated and has a very good SYNCH READ(XI) HIT RATIO of 99%. So, is it sufficient if I just alter the Dir/Data ratio alone for this GBP20 without increasing the GBP size? What should be an ideal Dir/Data ratio for this GBP20? Please advice.



Thanks,
Sridharan.M

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

Sridharan Muthuraman

RE: Group buffer pool Tuning
(in response to Sridharan Muthuraman)

Hi Olle,

Thanks for your reply. My understanding was that, a low value of SYNCH READ(XI) HIT RATIO means that the GBP residency time of the changed (GBP-dependent) pages is low and that's a cause for performance degradation. Because more directory entries means fewer data entries (absent an increase in the size of a GBP), a dir/data entry ratio increase will result in shorter GBP residency time for changed pages written to the GBP at commit time. That, in turn, leads to a degradation in the GBP Synch Read(XI) hit ratio. So, would that not call for a increase in GBP INTISIZE for GBP14 to increase the GBP residency time for changed pages? Besides, is it advisable to increase the Dir/Data entry ratio above 255 (eg 300) to avoid overhead caused by XES from performing AUTO ATLER to higher ratios?

Thanks,
Sridharan.M

Olle Brostrom

Group buffer pool Tuning
(in response to Sridharan Muthuraman)
Some pools have better hit ratios than others, How many BP14 Virtual pools do you have? And what are the size of them?
Is your workload well balanced over all your DB2 members?
If you have the storage you can try to increase GBP14 and see if you will have a better hit ratio. The best Getpage is the one without an I/O!
As I said before, you will never need more directory entries than the sum of all pages in your BP14 VPOOLs!
Always monitor CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS and keep them to zero!

Best Regards

Olle Broström

From: Sridharan Muthuraman [mailto:[login to unmask email]
Sent: den 10 oktober 2017 11:50
To: [login to unmask email]
Subject: [DB2-L] - RE: Group buffer pool Tuning


Hi Olle,

Thanks for your reply. My understanding was that, a low value of SYNCH READ(XI) HIT RATIO means that the GBP residency time of the changed (GBP-dependent) pages is low and that's a cause for performance degradation. Because more directory entries means fewer data entries (absent an increase in the size of a GBP), a dir/data entry ratio increase will result in shorter GBP residency time for changed pages written to the GBP at commit time. That, in turn, leads to a degradation in the GBP Synch Read(XI) hit ratio. So, would that not call for a increase in GBP INTISIZE for GBP14 to increase the GBP residency time for changed pages? Besides, is it advisable to increase the Dir/Data entry ratio above 255 (eg 300) to avoid overhead caused by XES from performing AUTO ATLER to higher ratios?

Thanks,
Sridharan.M

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

Andy Smith

RE: Group buffer pool Tuning
(in response to Sridharan Muthuraman)

Sridharan

If it's been a long while since you looked at your GBP's perhaps recalculate the size of your GBP's based on this article here, which aims to avoid Directory Entry Reclaims :

http://robertsdb2blog.blogspot.co.uk/2013/07/db2-for-zos-data-sharing-evolution-of.html

Whilst altering the size of any GBP structures, perhaps also consider altering the ratio back to the default of 5 and then monitor AutoAlter going forwards.

Andy

Steven Lamb

RE: Group buffer pool Tuning
(in response to Andy Smith)

As far as I can remember (and I may be wrong), Auto Alter will only increase the size of the GBP structure if both directory entries and data pages are over the FULLTHRESHOLD.

 

Regards,

Steve

Sridharan Muthuraman

RE: Group buffer pool Tuning
(in response to Andy Smith)

Andy,

Thanks for pointing to the article. I could see the Dir/Data entry ratio is high only for GBPs that have allocated size (INITSIZE) less than the GBP size calculated using Robert's formula. So I'm planning to increase the GBP14 size and simultaneously reset the Dir/Data entry ratio to 5. However, what's intriguing is that why GBP14 hasn't grown in size (from INITSIZE to SIZE by AUTO ALTER) despite having a low SYNCH READ(XI) HIT RATIO. Is that because neither the Directory entries nor Data pages exceeded FULLTHRESHOLD (80% of INITSIZE)? If yes, is there any way we can confirm it (either by DIS BPOOL command or DB2 Statistics report)? Can we calculate that using below stats from DIS GBPOOL GDETAIL(*)

DSNB799I -D2PD SECONDARY GBP ATTRIBUTES

             ALLOCATED SIZE                           = 2865152 KB

             VOLATILITY STATUS                         = NON-VOLATILE

             CFNAME                                   = P1CFSB2

             CFLEVEL - OPERATIONAL                     = 17

             CFLEVEL - ACTUAL                         = 21

             NUMBER OF DIRECTORY ENTRIES               = 7009552

             NUMBER OF DATA PAGES                     = 25961

 

Thanks,
Sridharan.M

Olle Brostrom

Group buffer pool Tuning
(in response to Sridharan Muthuraman)
If you increase the structure size and alter the RATIO at the same time be sure you then have 7 millions directory entries in the structure!

Best Regards

Olle Broström

From: Sridharan Muthuraman [mailto:[login to unmask email]
Sent: den 11 oktober 2017 11:28
To: [login to unmask email]
Subject: [DB2-L] - RE: Group buffer pool Tuning


Andy,

Thanks for pointing to the article. I could see the Dir/Data entry ratio is high only for GBPs that have allocated size (INITSIZE) less than the GBP size calculated using Robert's formula. So I'm planning to increase the GBP14 size and simultaneously reset the Dir/Data entry ratio to 5. However, what's intriguing is that why GBP14 hasn't grown in size (from INITSIZE to SIZE by AUTO ALTER) despite having a low SYNCH READ(XI) HIT RATIO. Is that because neither the Directory entries nor Data pages exceeded FULLTHRESHOLD (80% of INITSIZE)? If yes, is there any way we can confirm it (either by DIS BPOOL command or DB2 Statistics report)? Can we calculate that using below stats from DIS GBPOOL GDETAIL(*)

DSNB799I -D2PD SECONDARY GBP ATTRIBUTES

ALLOCATED SIZE = 2865152 KB

VOLATILITY STATUS = NON-VOLATILE

CFNAME = P1CFSB2

CFLEVEL - OPERATIONAL = 17

CFLEVEL - ACTUAL = 21

NUMBER OF DIRECTORY ENTRIES = 7009552

NUMBER OF DATA PAGES = 25961



Thanks,
Sridharan.M

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

Mark Rader

RE: Group buffer pool Tuning
(in response to Andy Smith)

Hello Andy, Sridharan and Olle,

I agree with Andy that Robert's blog post is a great starting point, and I agree with Olle on his various recommendations.  One thing to remember is that AutoAlter will not increase the size of the GBP because of a low XI hit ratio.  It will only increase the size of the structure (up to limit of SIZE) if there are write failures. 

Autoalter will increase the directory ratio if there are XIs due to directory reclaims, as Olle explains.  

In the excerpt of the display in an earlier post, the field from the DSNB786I message, FAILED DUE TO LACK OF STORAGE, was not included.  If that is non-zero, Autoalter may increase the GBP size.

One possible reason for low hit ratio for XI reads is that the re-reference rate across Db2 members is low.  One point of GBPs is to cache changed pages, so if one member changes a page and that page is invalidated in another member, the second member can refresh the invalidated page from the GBP rather than from an I/O.   However, if enough time goes by before the second member again accesses the invalidated page, that page will no longer be in the GBP. 

It is possible that increasing the size (INITSIZE and SIZE) of the GBP will increase the hit ratio for XI DATA RETURNED.  It is possible that large increases will not affect the hit ratio for XI DATA RETURNED.  It depends on the frequency and likelihood of page re-reference.

I hope this is helpful.

Mark Rader

Steven Lamb

RE: Group buffer pool Tuning
(in response to Mark Rader)

There are two algorithms used by Auto Alter - "Structure Full" and "Reclaim Avoidance". Reclaim Avoidance takes place if you're suffering from directory reclaims, increasing directory entries at the expense of data pages.

Try using the GDETAIL option on your Display GBPOOL command and look for reclaims - this should be zero.

DSNB787I  -BP0B   RECLAIMS                                       
               FOR DIRECTORY ENTRIES                   = 0       
               FOR DATA ENTRIES                        = 622350295

alain pary

RE: Group buffer pool Tuning
(in response to Steven Lamb)

hello, 

we have also found that sometimes the number of directory entries is very high with auto alter . In our case we lose 60 k pages of caching in our GBP.  If you don't plan to modify your BP size , you can easy calculate the number of entries need and adjust to ratio to meet your objectives. 

as mentionned before you have to avoid CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS

 

Alain Pary

DBA - technical support 

www.dbizners.com

Sridharan Muthuraman

RE: Group buffer pool Tuning
(in response to Steven Lamb)

Hi Mark,Olle,Steven and Alain,

Thanks for your valuable inputs. Yes, now I understand the difference between "Structure Full" and "Reclaim Avoidance". I usually use -DISPLAY GROUPBUFFERPOOL(GBP14) TYPE(*) MDETAIL(*) GDETAIL(*) command and got below output. CROSS INVALIDATIONS DUE TO DIRECTORY RECLAIMS is always 0 since I started monitoring the GBP14. Looks like the Dir/Data entry ratio settled for 270 for quite some time and is not increasing any longer(atleast for quite some time). So, I'm planning to increase the GBP14 size (INITSIZE) from current 2789 MB to 5190 MB (arrived using CFSIZER) to improve the SYNCH READ (XI) HIT RATIO and let the Dir/Data as is. Will increasing the INITSIZE reduce the Dir/Data ratio automatically or we need to manually ALTER BUFFERPOOL to reduce the ratio?

DSNB784I -D2PD GROUP DETAIL STATISTICS

             READS

               DATA RETURNED                           = 6667166

DSNB785I -D2PD     DATA NOT RETURNED

                 DIRECTORY ENTRY EXISTED               = 363530988

                 DIRECTORY ENTRY CREATED               = 328689076

                 DIRECTORY ENTRY NOT CREATED           = 189682001, 0

DSNB786I -D2PD   WRITES

               CHANGED PAGES                           = 5632025

               CLEAN PAGES                             = 0

               FAILED DUE TO LACK OF STORAGE           = 0

             CHANGED PAGES SNAPSHOT VALUE             = 104

DSNB787I -D2PD   RECLAIMS

               FOR DIRECTORY ENTRIES                   = 0

               FOR DATA ENTRIES                       = 4092527

             CASTOUTS                                 = 4649763

DSNB788I -D2PD   CROSS INVALIDATIONS

               DUE TO DIRECTORY RECLAIMS               = 0

               DUE TO WRITES                           = 3606300

               EXPLICIT                               = 0

Thanks,
Sridharan.M