Buffer pool hit rates and hit ratios in a Db2 pureScale environment

One way of measuring the extent to which pages required by members are found in memory as opposed to on disk is by calculating the buffer pool hit ratio. The buffer pool hit ratio indicates the number of times that the database manager found a requested page in a buffer pool (also known as the hit rate) as compared to the number of times it had to read it from disk. In a Db2 pureScale environment, both the local buffer pool and group buffer pool hit rates and hit ratios are important factors in assessing overall performance.

Local buffer pool (LBP) hit ratios reflect the extent to which pages that a member needs can be found in a valid state in the local buffer pool. A page in the LBP of a member is deemed to be in a valid state if that page has not been changed by another member since it was loaded into the LBP. If another member has changed the page, which might happen before the page has been cast out to disk, then the page is said to be invalid. If the member with the invalid page requires that page to perform a transaction, the member has to go to the CF to request a new, valid version of the page.

A low LBP hit ratio is an indication that the pages were not found locally, and had to be requested from the CF.

However, in a Db2 pureScale environment, looking at the LBP hit ratios provides only one side of the buffer pool story. You also need to consider the role that the group buffer pool (GBP) plays in retrieving pages, and the hit ratio for the GBP itself. If a member is unable to locate a valid copy of a page in its LBP, it makes a request to the CF to search the GBP for a valid copy of the page. The GBP does one of the following actions:

If it has a valid copy of the page, the GBP provides it to the member making the request.
Otherwise, the GBP tells the requesting member that it must read the page from disk.

An additional consideration for LBP usage is the concept of GBP-independent page. A GBP-independent page is a page that is only ever accessed through a LBP of a member, and never exists in the GBP. Pages might be GBP-independent because the operations using the page, or the objects where the pages come from, are only accessed by the local member.

Group buffer pool hit ratios reflect the extent to which pages required by members, for which they do not have a valid local copy, are found in the group buffer pool, as compared to having to be read in from disk. A low hit ratio for the GBP is an indication that relatively few of the pages required by members across the instance are available in the GBP. Increasing the size of the GBP can improve hit rates, and overall performance. Therefore, when calculating the hit ratios for data pages in the local buffer pool (LBP) for a member, you need to consider the number of times the member attempted to read pages from the LBP in comparison to the number of times attempted reads did not find a valid page in the LBP. See Formulas for calculating buffer pool hit ratios for details on how LBP and GBP monitor elements are used to calculate the GBP hit rate.

Tip: Hit ratios can vary based on many factors, such as the nature of the data in your database, the queries that are run against it, as well as hardware and software configurations. Generally speaking, higher buffer pool hit ratios are reflective of better query performance. If you find hit ratios seem low, or are declining over time, increasing the size of the buffer pools can help. To increase the size of the group buffer pool, adjust the cf_gbp_sz configuration parameter on the CF. To adjust local buffer pools, run the ALTER BUFFERPOOL statement on the member with the buffer pools that need correction.

Buffer pool monitor element reporting

In Db2 pureScale environments, each member reports on its own local buffer pools. No aggregation of data across members takes place. You must take into account which member or members you are interested in, and interpret the data accordingly. In some cases, you might want to calculate the hit ratios for a specific member. In others cases, you might want to look at the data for all members together, to form an overall view of the hit rates and hit ratios for the Db2 pureScale environment as a whole.

For example, if you submit a query to return data for the number of times a data page was read into a local buffer pool from disk, because it was not found in the GBP (using the pool_data_gbp_p_reads monitor element) with the MON_GET_BUFFERPOOL table function, and you do not specify which member to return, you will see results like the ones that follow:

MEMBER BP_NAME              POOL_DATA_GBP_P_READS
------ -------------------- ---------------------
     0 IBMDEFAULTBP                           408
     0 IBMSYSTEMBP4K                            0
     0 IBMSYSTEMBP8K                            0
     0 IBMSYSTEMBP16K                           0
     0 IBMSYSTEMBP32K                           0
     1 IBMDEFAULTBP                           108
     1 IBMSYSTEMBP4K                            0
     1 IBMSYSTEMBP8K                            0
     1 IBMSYSTEMBP16K                           0
     1 IBMSYSTEMBP32K                           0
     2 IBMDEFAULTBP                           112
     2 IBMSYSTEMBP4K                            0
     2 IBMSYSTEMBP8K                            0
     2 IBMSYSTEMBP16K                           0
     2 IBMSYSTEMBP32K                           0

  15 record(s) selected.

Important: In the preceding example, you can see that the data reported for temporary buffer pools shows all zeros. This is not a coincidence; in Db2 pureScale instances, temporary objects and table spaces are local to the member they are associated with. They do not use the GBP on the CF.

If you are interested in the results across all members, you can use the SUM aggregate function to add the numbers for all members together:

SELECT  VARCHAR(BP_NAME,15) AS BP_NAME, 
        SUM(POOL_DATA_GBP_P_READS) AS TOTAL_P_READS 
FROM TABLE(MON_GET_BUFFERPOOL('', -2))
GROUP BY BP_NAME

The preceding query returns results like the following output:

BP_NAME         TOTAL_P_READS
--------------- --------------------
IBMDEFAULTBP                     310
IBMSYSTEMBP16K                     0
IBMSYSTEMBP32K                     0
IBMSYSTEMBP4K                      0
IBMSYSTEMBP8K                      0

  5 record(s) selected.