Group Buffer Pool Activity
This topic shows detailed information about Statistics - Group Buffer Pool Activity
.
This block shows activity for the group buffer pool connected to the reported Db2 system. The counters are cumulative from the time when the buffer pool was first connected. If more than one 4 KB or 32 KB group buffer pool block is printed, blocks showing the 4 KB and 32 KB group buffer pool totals are printed. If the report contains both 4 KB and 32 KB group buffer pool blocks, a block showing the totals of all group buffer pools is printed.
Statistics - Group Buffer Pool Activity
The field labels shown in the following sample layout of Statistics - Group Buffer Pool Activity
are described in the following section.
GROUP BP0 QUANTITY /SECOND /THREAD /COMMIT
----------------------------- -------- ------- ------- -------
GROUP BP R/W RATIO (%) 71.22 N/A N/A N/A
GBP SYN.READ(XI) HIT RATIO(%) 5.51 N/A N/A N/A
GBP-DEPENDENT GETPAGES 6212.00 0.15 22.43 3.28
SYN.READ(XI)-DATA RETURNED 197.00 0.00 0.71 0.10
SYN.READ(XI)-NO DATA RETURN 3379.00 0.08 12.20 1.79
SYN.READ(NF)-DATA RETURNED 1.00 0.00 0.00 0.00
SYN.READ(NF)-NO DATA RETURN 0.00 0.00 0.00 0.00
UNREGISTER PAGE 0.00 0.00 0.00 0.00
CLEAN PAGES SYNC.WRITTEN 0.00 0.00 0.00 0.00
CLEAN PAGES ASYNC.WRTN 0.00 0.00 0.00 0.00
REG.PAGE LIST (RPL) REQUEST 115.00 0.00 0.42 0.06
NUMBER OF PAGES RETR.FROM GBP 0.00 0.00 0.00 0.00
PAGES CASTOUT 37.00 0.00 0.13 0.02
UNLOCK CASTOUT 37.00 0.00 0.13 0.02
READ CASTOUT CLASS 223.00 0.01 0.81 0.12
READ DIRECTORY INFO 0.00 0.00 0.00 0.00
READ STORAGE STATISTICS 4907.00 0.12 17.71 2.59
REGISTER PAGE 0.00 0.00 0.00 0.00
DELETE NAME 93.00 0.00 0.34 0.05
ASYNCH GBP REQUESTS 2959.00 0.07 10.68 1.56
EXPLICIT X-INVALIDATIONS 0.00 0.00 0.00 0.00
CASTOUT CLASS THRESHOLD 0.00 0.00 0.00 0.00
GROUP BP CASTOUT THRESHOLD 0.00 0.00 0.00 0.00
GBP CHECKPOINTS TRIGGERED 175.00 0.00 0.63 0.09
WRITE FAILED-NO STORAGE 0.00 0.00 0.00 0.00
WRITE TO SEC-GBP FAILED 0.00 0.00 0.00 0.00
COMPL CHECKS SUSPENDED 0.00 0.00 0.00 0.00
DELETE NAME LIST SEC-GBP 0.00 0.00 0.00 0.00
DELETE NAME FROM SEC-GBP 0.00 0.00 0.00 0.00
UNLOCK CASTOUT STATS SEC-GBP 0.00 0.00 0.00 0.00
ASYNCH SEC-GBP REQUESTS 0.00 0.00 0.00 0.00
IXLCACHE REQS WITH ASYNCH XI 1.00 0.00 0.00 0.00
IXLAXISN SYNCH-UP CALLS 1.00 0.00 0.00 0.00
IXLAXISN SUSPENDS WAITING XI 0.00 0.00 0.00 0.00
GROUP BP0 CONTINUED QUANTITY /SECOND /THREAD /COMMIT
----------------------- -------- ------- ------- -------
WRITE AND REGISTER 114.00 0.00 0.41 0.06
WRITE AND REGISTER MULT 24.00 0.00 0.09 0.01
CHANGED PGS SYNC.WRTN 278.00 0.01 1.00 0.15
CHANGED PGS ASYNC.WRTN 0.00 0.00 0.00 0.00
PAGES WRITE & REG MULT 164.00 0.00 0.59 0.09
READ FOR CASTOUT 37.00 0.00 0.13 0.02
READ FOR CASTOUT MULT 0.00 0.00 0.00 0.00
PAGE P-LOCK LOCK REQ 394.00 0.01 1.42 0.21
SPACE MAP PAGES 94.00 0.00 0.34 0.05
DATA PAGES 231.00 0.01 0.83 0.12
INDEX LEAF PAGES 69.00 0.00 0.25 0.04
PAGE P-LOCK UNLOCK REQ 440.00 0.01 1.59 0.23
PAGE P-LOCK LOCK SUSP 0.00 0.00 0.00 0.00
SPACE MAP PAGES 0.00 0.00 0.00 0.00
DATA PAGES 0.00 0.00 0.00 0.00
INDEX LEAF PAGES 0.00 0.00 0.00 0.00
PAGE P-LOCK LOCK NEG 0.00 0.00 0.00 0.00
SPACE MAP PAGES 0.00 0.00 0.00 0.00
DATA PAGES 0.00 0.00 0.00 0.00
INDEX LEAF PAGES 0.00 0.00 0.00 0.00
PAGES IN WRITE-AROUND 0.00 0.00 0.00 0.00
- GROUP BP R/W RATIO (%)
-
The group buffer pool read/write ratio. This reflects the effectiveness of the group buffer pool and whether the GBPCACHE NONE option can be used.
Field Name: SGBRWRAT
- GBP SYN.READ(XI) HIT RATIO(%)
-
The percentage of all requests made to read a page from the group buffer pool because the page was invalidated in the member's buffer pool, which found the data in the group buffer pool and did not have to retrieve the page from DASD.
Background and Tuning Information
For highly active group buffer pools, consider increasing the GBP size if the SYN.READ(XI) HIT RATIO percent is smaller than 90.
Field Name: SGBXIRAT
- GBP-DEPENDENT GETPAGES
-
The number of Getpages made for GBP-dependent objects.
Field Name: QBGLGG
- SYN.READ(XI)-DATA RETURNED
-
The number of requests made to read a page from the group buffer pool because the page was invalidated in the member's buffer pool. The member found the required page in the group buffer pool.
Background and Tuning Information
When you increase the size of the group buffer pool (GBP), the number of pages returned from the GBP can increase. Conversely, decreasing the size of the GBP can cause Db2 to return fewer pages because the GBP cannot hold pages long enough to allow them to be retrieved again.
Field Name: QBGLXD
This is an exception field.
- SYN.READ(XI)-NO DATA RETURN
-
The number of requests to read a page from the group buffer pool that were required because the page was invalidated in the member's buffer pool. The member did not find the data in the group buffer pool and had to retrieve the page from DASD.
Background and Tuning Information
Normally, when the page in a member's buffer is cross invalidated, the buffer is refreshed from the group buffer pool. In this instance, the requested page was not found in the group buffer pool though the page set is still GBP-dependent. The page has been removed from the group buffer pool for one of two reasons:- Shortage of data pages and consequent reclamation of this page
- Shortage of directory entries and consequent removal of the page together with cross invalidation of that page in the local buffer pools of all members using that page.
If the value in this field is high, you may want to tune the group buffer pool (GBP). Depending on the reason, increase the number of GBP data pages, increase the size of the directory entry space, or increase both the number of GBP data pages and the space for directory entries. Oversizing the group buffer pool can cause unnecessary GBP checkpoint overhead.
Field Name: QBGLXR
- SYN.READ(NF)-DATA RETURNED
-
The number of requests made to read a page from the group buffer pool because the page was not in the buffer pool of the member. The member found the page in the group buffer pool.
Background and Tuning Information
The requesting member needs a page from a table space or index that is GBP-dependent or has GBPCACHE ALL defined. To get that page, the group buffer pool is checked before the page set on DASD.
If the group buffer pool is used to cache both clean and changed pages (GBPCACHE ALL is used for all data), you can try to get more pages returned from the group buffer pool by increasing the size of the group buffer pool. Do not tune the GBP based on this counter if it is used for caching changed pages only (GBPCACHE CHANGED).
Field Name: QBGLMD
This is an exception field.
- SYN.READ(NF)-NO DATA RETURN
-
The number of requests made to read a page from the group buffer pool because the page was not in the member's buffer pool. The member did not find the required data in the group buffer pool and had to retrieve the page from DASD.
Background and Tuning Information
The requesting member needs a page from a table space or index that is GBP-dependent or has GBPCACHE ALL defined. To get that page, the group buffer pool is checked before the page set on DASD.
You can compare the value in this counter with the number of pages that were returned from the group buffer pool, see Sync.Read (Not Found) - Data Returned. If the group buffer pool is used to cache both clean and changed pages (GBPCACHE ALL is used for all data), you can try to get more pages returned from the group buffer pool by increasing the size of the group buffer pool. Do not tune the GBP based on this counter if it is used for caching changed pages only (GBPCACHE CHANGED).
Field Name: QBGLMR
- UNREGISTER PAGE
-
The number of times Db2 unregistered interest for a single page. This happens when Db2 steals pages from the member's buffer pool that belong to GBP-dependent page sets or partitions.
Background and Tuning Information
A large value here indicates that the local buffer pool contains a mixture of GBP-dependent data and non-GBP-dependent data.
The page stolen from the local buffer pool is replaced by a new one. This counter makes a distinction on whether the new page depends on the group buffer pool or not.
Usually a page of a GBP-dependent page set or partition is replaced by a page that is also GBP-dependent. In this instance, the unregister request for the page being stolen is combined with the read and register request for the new page. These combined requests do not contribute to this counter.
If, however, a page of a GBP-dependent page set or partition is replaced by a page that is not GBP-dependent, then only an unregister request is sent to the coupling facility. These separate requests are counted here.
Field Name: QBGLDG
- CLEAN PAGES SYNC.WRITTEN
-
The number of clean pages that were synchronously written to the group buffer pool from the virtual pool.
Background and Tuning Information
Only GBPCACHE ALL causes clean (unchanged) pages to be written to the coupling facility. The pages are written to the coupling facility even if the page set is not GBP-dependent. If group buffer pool caching works effectively for prefetch, the value in this field should be much smaller than the value in Synchronous Read (Not Found) - Data Returned.
Field Name: QBGLWC
This is an exception field.
- CLEAN PAGES ASYNC.WRTN
-
The number of clean pages that were asynchronously written to the group buffer pool from the virtual pool.
Background and Tuning Information
Only GBPCACHE ALL causes clean (unchanged) pages to be written to the group coupling facility. In this instance pages are written even if the page set is not GBP-dependent. Asynchronous write is done under prefetch processing.
If group buffer pool caching works effectively for prefetch, the value in this field should be much smaller than the combined values in:- Synchronous Read (Not Found) - Data Returned
- Asynchronous Reads - Data Returned
- Clean pages - Read after register page list
Field Name: QBGLAC
This is an exception field.
- REG.PAGE LIST (RPL) REQUEST
-
The number of register page list (RPL) requests made by prefetch. The group buffer pool must be allocated in a group coupling facility with CFLEVEL=2 or higher.
Background and Tuning Information
Performance might be improved by enabling RPL.
Field Name: QBGLAX
This is an exception field.
- NUMBER OF PAGES RETR.FROM GBP
-
The number of coupling facility reads performed by prefetch to retrieve a changed page from the group buffer pool.
Field Name: QBGLAY
This is an exception field.
- PAGES CASTOUT
-
The number of data pages that were cast out of the group buffer pool of the member.
Castout to a page set or partition is done by the castout owner of the page set or partition. This is normally the Db2 subsystem that had the first update intent on the page set or partition.
Background and Tuning Information
The number of pages written per I/O is normally close to the value of this field divided by the value in Unlock Castout.
For example, if an average of four pages is written per castout write I/O, the number of pages cast out should be four times the number in this field.
Because Db2 usually includes more than one page in the request to write pages to DASD, the number in this field should always be significantly more than Unlock Castout. If it is not (for example, when "unlock castout" is more than half of "pages castout"), the castout write I/O is inefficient; probably because you have random update patterns on the Db2 data or a low castout threshold.
Field Name: QBGLRC
This is an exception field.
- UNLOCK CASTOUT
-
The number of times Db2 issued an unlock request to the coupling facility for completed castout I/Os.
When pages are cast out to DASD, they are locked for castout in the coupling facility. This castout lock is not an IRLM lock; it is to ensure that only one system can cast out a given page at a time.
Background and Tuning Information
The number of pages written per I/O is normally close to the value of pages castout divided by the value of this field.
For example, if an average of four pages is written per castout write I/O, the number of pages cast out should be four times the value in this field.
Because Db2 usually includes more than one page in a write request, the number in this field should always be significantly less than pages castout. If it is not (for example, when "unlock castout" is more than half of "pages castout"), the castout write I/O is inefficient; possibly because you have random update patterns on the Db2 data or a low castout threshold.
Field Name: QBGLUN
- READ CASTOUT CLASS
-
The number of requests made to the group buffer pool to determine which pages, from a particular page set or partition, must be cast out because they are cached as changed pages.
This request is issued either by the page set or partition castout owner, or, when the group buffer pool castout threshold is reached, by the group buffer pool structure owner.
Field Name: QBGLCC
- READ DIRECTORY INFO
-
The number of requests issued by the group buffer pool structure owner to read the directory entries of all changed pages in the group buffer pool.
This request is issued at group buffer pool checkpoints to record the oldest recovery log record sequence number (LRSN). It is used as a basis for recovery if the group buffer pool fails.
Such requests might have to be issued several times for each group buffer pool checkpoint to read the directory entries for all changed pages.
Background and Tuning Information
If the value of this counter appears to be abnormally high, consider upgrading the coupling facility to CFLEVEL=2 or higher to raise the number of directory entries that can be read with one request. You can also increase the group buffer pool checkpoint interval, but this can lengthen the recovery for the group buffer pool.
Field Name: QBGLRD
- READ STORAGE STATISTICS
-
The number of times Db2 requested statistics information from the group buffer pool. It is issued by the group buffer pool structure owner at timed intervals to determine whether the group buffer pool castout threshold (GBPOOLT) has been reached.
Field Name: QBGLOS
- REGISTER PAGE
-
The number of times Db2 registered interest in a single page.
These are "register-only" requests, which means that Db2 is not requesting any data back from the request.
This request is made only to create a directory entry for the page to be used for cross-invalidation when the page set or partition P-lock is downgraded from S to IS mode, or from SIX to IX mode.
Field Name: QBGLRG
- DELETE NAME
-
The number of requests made by Db2 to delete directory and data entries associated with a particular page set or partition from the group buffer pool.
Db2 issues this request when it changes a page set or partition from GBP-dependent to non GBP-dependent. Db2 also issues this request for objects that are defined with GBPCACHE ALL when those objects are first opened.
Background and Tuning Information
This counter is a measure of how often page sets or partitions change between being and not being dependent on the group buffer pool.
You can prevent Db2 going in and out of GBP dependency too often by tuning the following subsystem parameters that affect data sets when they are switched to a different state:- PCLOSEN
- Pseudoclose frequency. The number of checkpoints required before a data set that was
not updated can be a pseudoclose candidate.
If the PCLOSEN condition is met, the page set or partition is converted from read-write to read-only state. Depending on other concurrent users, this could raise the chance for the page set or partition to go out of GBP dependency.
- PCLOSET
- Pseudoclose time. The amount of time (in minutes) that must elapse before a data set
can be a pseudoclose candidate.
If the PCLOSEN or PCLOSET condition is met, the page set or partition is converted from read-write to read-only state. Depending on other concurrent users, this could raise the chance for the page set or partition to go out of GBP dependency.
- LOGLOAD
- The number of log records that Db2 writes between successive checkpoints.
Field Name: QBGLDN
- ASYNCH GBP REQUESTS
-
The number of IXLCACHE invocations for the primary group buffer pool.
Field Name: QBGLHS
- EXPLICIT X-INVALIDATIONS
-
The number of times an explicit coupling facility cross-invalidation request was issued.
Field Name: QBGLEX
- CASTOUT CLASS THRESHOLD
-
The number of times group buffer pool castout was initiated because the group buffer pool class castout threshold was detected.
Background and Tuning Information
The class castout threshold is one of two group buffer pool thresholds. In most cases the default value for the class threshold (5 percent) is a good choice. Depending on your workload, altering this value can reduce DASD contention during castout.
Field Name: QBGLCT
This is an exception field.
- GROUP BP CASTOUT THRESHOLD
-
The number of times a group buffer pool castout was initiated because the group buffer pool castout threshold was detected.
Background and Tuning Information
The GBP castout threshold, GBP class castout threshold, and the length of the GBP checkpoint interval determine the castout characteristics of the group buffer pool.
You can consider this threshold a safety margin to protect the group buffer pool from being accidentally flooded by overactive applications.
In most situations, the default value for the group buffer pool castout threshold of 30 percent is a good choice. Use the ALTER GROUPBUFFERPOOL command to tune the group buffer pool thresholds.
Field Name: QBGLGT
This is an exception field.
- GBP CHECKPOINTS TRIGGERED
-
The number of group buffer pool checkpoints triggered by this member.
Background and Tuning Information
The value of this counter depends on the length of the group buffer pool checkpoint interval.
Field Name: QBGLCK
- WRITE FAILED-NO STORAGE
-
The number of coupling facility write requests that could not complete due to a lack of coupling facility storage resources.
Background and Tuning Information
A value greater than zero indicates that the data page resources of the coupling facility are being consumed faster than the Db2 castout processes can free them.
On write failure, the affected Db2 member initiates castout and retries several times, and finally, if it is a changed page, it will be added to the logical page list (LPL) requiring recovery.
If the problem is not simply due to a momentary surge in activity, you need either to decrease the group buffer pool castout thresholds, or to increase the number of data entries in the group buffer pool. To increase the number of data entries, you can do one of the following:- Increase the total size of the group buffer pool.
- Adjust the ratio of directory entries to data entries in favor of data entries.
Field Name: QBGLWF
This is an exception field.
- WRITE TO SEC-GBP FAILED
-
The number of coupling facility requests to write changed pages to the secondary group buffer pool for duplexing that failed because of a lack of storage in the coupling facility.
Field Name: QBGL2F
This is an exception field.
- COMPL CHECKS SUSPENDED
-
The number of completion checks for writes to the secondary GBP that were suspended because the write had not yet been completed.
Field Name: QBGL2S
- DELETE NAME LIST SEC-GBP
-
The number of DELETE NAME LIST requests to delete pages from the secondary group buffer pool that have just been cast out from the primary.
Field Name: QBGL2D
- DELETE NAME FROM SEC-GBP
-
The number of group buffer pool requests to delete a page from the secondary group buffer pool. These requests are issued by the group buffer pool structure owner to delete orphaned data entries in the secondary GBP as part of the garbage collection logic.
Field Name: QBGL2N
- UNLOCK CASTOUT STATS SEC-GBP
-
The number of coupling facility requests to read the castout statistics for the secondary group buffer pool. These requests are issued by the group buffer pool structure owner to check for orphaned data entries in the secondary group buffer pool.
Field Name: QBGL2R
- ASYNCH SEC-GBP REQUESTS
-
The number of asynchronous IXLCACHE invocations for the secondary group buffer pool.
Field Name: QBGL2H
- IXLCACHE REQS WITH ASYNCH XI
-
Specifies the number of IXLCACHE requests with asynchronous cross- invalidation (XI).
Field Name: QBGLWX
- IXLAXISN SYNCH-UP CALLS
-
Specifies the number of asynchronous XLAXISN synch-up calls to determine whether all outstanding cross- invalidation (XI) notifications have been delivered.
Field Name: QBGLSU
- IXLAXISN SUSPENDS WAITING XI
-
Specifies the number of suspensions of IXLAXISN sync-up calls that occurred while waiting for asynchronous cross- invalidation (XI) to complete.
Field Name: QBGLAS
- WRITE AND REGISTER
-
The number of Write and Register requests.
Field Name: QBGLWS
- WRITE AND REGISTER MULT
-
The number of Write and Register Multiple requests.
Field Name: QBGLWM
- CHANGED PGS SYNC.WRTN
-
The number of changed pages written synchronously to the group buffer pool.
Pages are written with Write and Register (WAR) requests or Write and Register Multiple (WARM) requests.
At commit time changed pages are forced from the virtual buffer pool of the member to the coupling facility.
Background and Tuning Information
In data sharing, changed pages must have been written to the group buffer pool by the time a transaction commits. The pages are written either synchronously (force at commit) or asynchronously, for example, when a local buffer pool threshold is reached or at a member's checkpoint. The number of pages that have to be forced out synchronously (in
burst mode
) at commit time can be reduced if asynchronous writes are triggered more frequently.You can use the vertical deferred write threshold (VDWQT) to reduce the number of pages that have to be forced out synchronously and to increase the number of pages that are asynchronously written before the transaction commits. For GBP-dependent page sets, writes triggered by the vertical deferred write threshold go to the coupling facility. You can cause changed pages to be written out quicker and in smaller increments, by reducing the vertical deferred write threshold (VDWQT).
Field Name: QBGLSW
This is an exception field.
- CHANGED PGS ASYNC.WRTN
-
The number of changed pages written asynchronously to the group buffer pool.
Pages are written in response to Write and Register (WAR) and Write and Register Multiple (WARM) requests.
Changed pages can be written from the member's virtual buffer pool to the group coupling facility before the application commits. This happens when, for example, a local buffer pool threshold is reached, or when P-lock negotiation forces the pages on the vertical deferred write queue to be written to the group buffer pool.
Background and Tuning Information
In data sharing, changed pages must have been written to the group buffer pool before a transaction commits. The pages are written either synchronously during commit processing or asynchronously before the transaction commits when, for example, a local buffer pool threshold is reached or at a member's checkpoint. See Changed Pages - Written Synchronously for the number of changed pages synchronously written to the group buffer pool.
The vertical deferred write threshold (VDWQT) can be used to reduce the number of pages that have to be forced out synchronously and to increase the number of pages that are asynchronously written before the transaction commits. For GBP-dependent page sets, writes triggered by the vertical deferred write threshold go to the coupling facility. If you want changed pages to be written out quicker and in smaller increments, you can lower the vertical deferred write threshold (VDWQT).
Field Name: QBGLAW
This is an exception field.
- PAGES WRITE & REG MULT
-
The number of pages written using Write and Register Multiple (WARM) requests.
Field Name: QBGLWP
- READ FOR CASTOUT
-
The number of Read For Castout requests. One page read per request.
Field Name: QBGLCR
- READ FOR CASTOUT MULT
-
The number of Read For Castout Multiple requests.
Field Name: QBGLCM
- PAGE P-LOCK LOCK REQ
-
The sum of all page P-lock lock requests.
Field Name: SBGLPLR
- SPACE MAP PAGES
-
The number of page P-lock lock requests for space map pages.
Field Name: QBGLP1
- DATA PAGES
-
The number of page P-lock requests for data pages.
Field Name: QBGLP2
- INDEX LEAF PAGES
-
The number of page P-lock requests for index leaf pages.
Field Name: QBGLP3
- PAGE P-LOCK UNLOCK REQ
-
The number of page P-lock unlock requests.
Field Name: QBGLU1
- PAGE P-LOCK LOCK SUSP
-
The sum of all page P-lock lock suspensions.
Field Name: SBGLPLS
- SPACE MAP PAGES
-
The number of page P-lock suspensions for space map pages.
Field Name: QBGLS1
- DATA PAGES
-
The number of page P-lock suspensions for data pages.
Field Name: QBGLS2
- INDEX LEAF PAGES
-
The number of page P-lock suspensions for index leaf pages.
Field Name: QBGLS3
- PAGE P-LOCK LOCK NEG
-
The sum of all page P-lock lock negotiations.
Field Name: SBGLPLN
- SPACE MAP PAGES
-
The number of page P-lock negotiations for space map pages.
Field Name: QBGLN1
- DATA PAGES
-
The number of page P-lock negotiations for data pages.
Field Name: QBGLN2
- INDEX LEAF PAGES
-
The number of page P-lock negotiations for index leaf pages.
Field Name: QBGLN3
- PAGES IN WRITE-AROUND
-
The number of changed pages that were written to disk through group buffer pool write-around due to condition write failures to the group buffer pool.
Field Name: QBGLWA