Calculating buffer pool hit ratios in a DB2 pureScale environment

Calculating buffer pool hit ratios for a DB2 pureScale instance can help you understand where there are opportunities to tune buffer pools to improve I/O efficiency.

Before you begin

Determine which ratio or ratios you are interested in. If you want to see a ratio across all members in an instance, consider formulating your SQL to aggregate data across members using the SUM aggregate function. If you are interested in seeing the data for a specific member only, you can use specify the member for which you want to see data in the MON_GET_BUFFERPOOL table function.

Procedure

To calculate buffer pool hit ratios, follow these steps:

Retrieve the information for the required monitor elements.

This example uses the MON_GET_BUFFERPOOL table function to retrieve the monitor elements that contain the values needed to calculate the hit ratio for data pages for the GBP, pool_data_gbp_l_reads and pool_data_gbp_p_reads.

SELECT varchar(bp_name,20) AS bp_name,
       pool_data_gbp_l_reads,
       pool_data_gbp_p_reads,
       member 
FROM TABLE(MON_GET_BUFFERPOOL('',-2))

The preceding query returns data like the following example:

BP_NAME              POOL_DATA_GBP_L_READS POOL_DATA_GBP_P_READS MEMBER
-------------------- --------------------- --------------------- ------
IBMDEFAULTBP                       1814911                456990      1
IBMSYSTEMBP4K                            0                     0      1
IBMSYSTEMBP8K                            0                     0      1
IBMSYSTEMBP16K                           0                     0      1
IBMSYSTEMBP32K                           0                     0      1
IBMDEFAULTBP                       1807959                455287      3
IBMSYSTEMBP4K                            0                     0      3
IBMSYSTEMBP8K                            0                     0      3
IBMSYSTEMBP16K                           0                     0      3
IBMSYSTEMBP32K                           0                     0      3
IBMDEFAULTBP                       1813932                455225      2
IBMSYSTEMBP4K                            0                     0      2
IBMSYSTEMBP8K                            0                     0      2
IBMSYSTEMBP16K                           0                     0      2
IBMSYSTEMBP32K                           0                     0      2
IBMDEFAULTBP                       1113396                278845      0
IBMSYSTEMBP4K                            0                     0      0
IBMSYSTEMBP8K                            0                     0      0
IBMSYSTEMBP16K                           0                     0      0
IBMSYSTEMBP32K                           0                     0      0

  20 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.

Use the values returned for the monitor elements to calculate the hit ratio.
The formula for calculating the hit ratio for the GBP (expressed as a percentage) is
((pool_data_gbp_l_reads - pool_data_gbp_p_reads ) ÷ pool_data_gbp_l_reads) × 100
So, using the data returned for the monitor elements in step 1:

    (((1,814,911+1,807,959  + 1,813,932+1,113,396) - (456,990+455,287 + 455,225+278,845))
      ÷ (1,814,911+1,807,959 + 1,813,932+1,113,396)) × 100
    = ((6,550,198 - 1,646,347) ÷ 6,550,198) × 100
    = 74.9%

In this example, the hit ratio for the GBP is 74.9%
Note: The values shown in the output for queries are for illustrative purposes only.

Example

Example 1: Find the overall hit rates across all members

This example is similar to the one shown in the preceding procedure, except that it uses an aggregate function to provide overall hit rates across all members.

SELECT VARCHAR(BP_NAME,20) AS BP, 
       SUM(POOL_DATA_GBP_L_READS) AS POOL_DATA_GBP_L_READS, 
       SUM(POOL_DATA_GBP_P_READS) AS POOL_DATA_GBP_P_READS 
FROM TABLE(MON_GET_BUFFERPOOL('',-2))
GROUP BY BP_NAME

Results:


BP                   POOL_DATA_GBP_L_READS POOL_DATA_GBP_P_READS
-------------------- --------------------- ---------------------
IBMDEFAULTBP                       6550198               1646347
IBMSYSTEMBP16K                           0                     0
IBMSYSTEMBP32K                           0                     0
IBMSYSTEMBP4K                            0                     0
IBMSYSTEMBP8K                            0                     0

  5 record(s) selected.

Example 2: Determining the GBP hit ratio for all data, index, and XML storage object (XDA) pages

To calculate the GBP hit ratio for all data, index, and XDA pages, use the following formula:

((pool_data_gbp_l_reads + pool_index_gbp_l_reads+pool_xda_gbp_l_reads)
- (pool_data_gbp_p_reads + pool_index_gbp_p_reads+pool_xda_gbp_p_reads ))
÷ (pool_data_gbp_l_reads + pool_index_gbp_l_reads+pool_xda_gbp_l_reads) × 100

The following example uses the MON_GET_BUFFERPOOL table function to retrieve the data contained in the required monitor elements and calculates the hit ratio for each member:

WITH BPMETRICS AS (
     SELECT BP_NAME, 
            POOL_DATA_GBP_L_READS +
              POOL_INDEX_GBP_L_READS +
              POOL_XDA_GBP_L_READS 
            AS LOGICAL_READS, 
            POOL_DATA_GBP_P_READS + 
              POOL_INDEX_GBP_P_READS + 
              POOL_XDA_GBP_P_READS 
            AS PHYSICAL_READS,
            MEMBER 
     FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
     SELECT VARCHAR(BP_NAME,20) AS BP_NAME,
            LOGICAL_READS,
            PHYSICAL_READS,
     CASE WHEN LOGICAL_READS > 0 
          THEN DEC(((
            FLOAT(LOGICAL_READS) - FLOAT(PHYSICAL_READS)) / 
            FLOAT(LOGICAL_READS)) 
            * 100,5,2) 
          ELSE NULL END AS HIT_RATIO,
          MEMBER
     FROM BPMETRICS

Results:


BP_NAME          LOGICAL_READS PHYSICAL_READS HIT_RATIO MEMBER
---------------- ------------- -------------- --------- ------
IBMDEFAULTBP           5730213         617628     89.22      1
IBMSYSTEMBP4K                0              0         -      1
IBMSYSTEMBP8K                0              0         -      1
IBMSYSTEMBP16K               0              0         -      1
IBMSYSTEMBP32K               0              0         -      1
IBMDEFAULTBP           5724845         615395     89.25      3
IBMSYSTEMBP4K                0              0         -      3
IBMSYSTEMBP8K                0              0         -      3
IBMSYSTEMBP16K               0              0         -      3
IBMSYSTEMBP32K               0              0         -      3
IBMDEFAULTBP           5731714         615814     89.25      2
IBMSYSTEMBP4K                0              0         -      2
IBMSYSTEMBP8K                0              0         -      2
IBMSYSTEMBP16K               0              0         -      2
IBMSYSTEMBP32K               0              0         -      2
IBMDEFAULTBP           5024809         409159     91.85      0
IBMSYSTEMBP4K                0              0         -      0
IBMSYSTEMBP8K                0              0         -      0
IBMSYSTEMBP16K               0              0         -      0
IBMSYSTEMBP32K               0              0         -      0

  20 record(s) selected.

Example 3: Using the SUM aggregate function to compute an overall hit ratio

You can also use the SUM aggregate function to compute an overall hit ratio across all members as follows:

WITH BPMETRICS AS (
     SELECT SUM(POOL_DATA_GBP_L_READS) + 
       SUM(POOL_INDEX_GBP_L_READS) + 
       SUM(POOL_XDA_GBP_L_READS) 
     AS LOGICAL_READS, 
     SUM(POOL_DATA_GBP_P_READS) + 
       SUM(POOL_INDEX_GBP_P_READS) + 
       SUM(POOL_XDA_GBP_P_READS) 
     AS PHYSICAL_READS 
     FROM TABLE(MON_GET_BUFFERPOOL('',-2)) AS METRICS)
     SELECT LOGICAL_READS,
            PHYSICAL_READS,
     CASE WHEN LOGICAL_READS > 0 
     THEN DEC(((FLOAT(LOGICAL_READS) - FLOAT(PHYSICAL_READS)) / 
       FLOAT(LOGICAL_READS)) 
       * 100,5,2) 
     ELSE NULL END AS HIT_RATIO 
FROM BPMETRICS

Results:


LOGICAL_READS        PHYSICAL_READS       HIT_RATIO
-------------------- -------------------- ---------
            22211581              2255996     89.84

  1 record(s) selected.

What to do next

If hit ratios seem low, or if they decline over time, you might want to increase the size of the buffer pools on either members, CFs, or both. If you are seeing lower than expected hit rates for the LBPs overall across the DB2 pureScale instance, look at the hit rates for each member individually, since the buffer pools on each member can have different sizes. An smaller sized LBP on one member might be unduly influencing the average hit rate for the instance.

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.