Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 2)

Easy accessibility to key Db2 buffer metrics simplifies buffer pool tuning analyses, increasing the likelihood they will be periodically revisited using data from new time intervals. This video explores key metrics for two other buffer pool tuning methodologies.

More Db2 Statistics Videos

1. Exploring Assessments of Key Metrics
2. Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 1)
3. Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 2)
4. Db2 I/O Cache Insights from IFCID 199 and SMF 42 Data
5. Buffer Efficiency from IFCID 199 and SMF 42
6. Measuring Benefits of Db2 Buffer Pool Tuning
7. Exploring Other Db2 Statistics Metrics
8. Walkthrough of Db2 Statistics Dashboard for Buffer Pool Tuning
9. Integrating with RMF Metrics (70, 71, 72, 74)
10. Integrating with RMF 70 and 72 CPU and WLM Metrics
11. Integrating with RMF 71 Memory and Large Frame Metrics
12. Integrating with RMF 74.4 Coupling Facility Metrics

Video Transcript

A second buffer pool tuning approach presented at a major Db2 conference identifies page residency time as a key focus metric. Namely the average time a page is resident in a buffer pool. This is a metric that’s well suited to automated assessment, as we saw earlier in the health insights view. So by default the thresholds here, warn at 90 seconds with an exception at 60 seconds. So as we scroll down and look at the buffer pools here, the size of the red icon here on buffer pool 20 indicates that it has more exception intervals than other buffer pools indicating that under this methodology, it would again be the top candidate to receive additional memory. Or we can start with a view like this one that focuses specifically on page residency times. So starting from this basically 24 hour interval, we see buffer pools 3, 20, and 25 with lower residency time.

Well, let’s go ahead and customize this report to only include those buffer pools. When we view this data over time shows comparable residency time among these three buffer pools for non-prime intervals, but during the prime interval buffer pool 25 is much higher time. So let’s just assume our primary focus is prime shift. So let’s go ahead and remove buffer pool 25 as well, and that will then serve to expand the scale for the other two buffer pools. And again, buffer pool 20 emerges with the lowest residency time. Again, let’s go ahead and capture this in our dashboard.

So in addition to residency time, this by-time view also brings in random and sequential get ratios just for context. And we’ll say a little bit more about those hit ratios shortly. But here we observe that the random hit ratio doesn’t improve much even with relatively significant increases in the residency times. Although these non-prime intervals likely have very different workload profiles in terms of batch versus online, so that may call the significance of that observation into question. And let’s go ahead and capture this in our dashboard.

And then finally here, let’s go ahead and compare across the members of the data sharing group. And we see that the page residency times are relatively similar except for the last member, which has a separate function. A third approach also presented in a major Db2 conference focuses on buffer pools with lower random buffer hit ratios. That ratio quantifies a percentage of random get pages that are satisfied from a buffer and thereby avoiding a sync read I/O. That’s another metric that’s well-suited to automated assessment. Here we see the random buffer pool get page hit ratio, and the thresholds are set to warn at 75% and have an exception at 50%. And again, as we scroll down that column, we see that buffer pool 20 again, with a large red icon, has more exception intervals than the other buffer pools.

Or once again, we can use a view like this that focuses specifically on the random buffer pool hit ratio with a timeline view, and many buffer pools result in lots of noise. So we changed this to a stack bar. It becomes apparent that the random buffer hit ratio for buffer pool 20 is by far the lowest. And that’s true kind of over the entire day, or if we look at the prime shift also very much the case there as well. So interestingly under all three methodologies that we looked at buffer pool 20 is the top candidate for additional memory. Let’s go ahead and add this to the dashboard as well.

Learn More About Analyzing Db2 Statistics and Accounting Data