Group Buffer Pool Tuning



IBM's flagship relational database management system

Group Buffer Pool Tuning

Postby msridharan » Tue Oct 10, 2017 12:00 pm

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
msridharan
 
Posts: 1
Joined: Tue Oct 10, 2017 11:53 am
Has thanked: 0 time
Been thanked: 0 time

Re: Group Buffer Pool Tuning

 

Return to DB2

 


  • Related topics
    Replies
    Views
    Last post