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

Easy accessibility to key Db2 buffer metrics enhances buffer pool tuning analyses so that they can be quickly completed. This video explores key metrics for one buffer pool tuning methodology.

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. How to Measure the Benefits of Db2 Buffer Pool Tuning
7. Exploring Other Db2 Statistics Metrics
8. Walkthrough of Db2 Statistics Dashboard for Buffer Pool Tuning
9. Integrating Db2 SMF Data with RMF Metrics (70, 71, 72, 74)
10. Integrating Db2 Data and RMF 70 & 72 (CPU & WLM) Metrics
11. Integrating RMF 71 Memory & Large Frame Metrics with SMF
12. Integrating with RMF 74.4 Coupling Facility Metrics

Video Transcript

Db2 industry experts present various buffer pool tuning methodologies. They differ in their criteria for prioritizing which buffer pools to expand, but they’re all entirely driven by a common set of metrics derived from the Db2 statistics data, and thus are more easily carried out when those metrics are easily accessible. This accessibility not only makes buffer pool tuning exercises less time-consuming, it also increases the likelihood they will be periodically revisited using data from new time intervals, both for continuous incremental improvement, as well as to adjust to changing workloads. One methodology presented at multiple conferences this year prioritizes buffer pools with the highest total read IO rates. So let’s begin our exploration there.

This initial view of all I/Os read and write shows that for the environment in this demo database, one data-sharing group has far more activity than all the others. So for the rest of this buffer pool topic, we’re going to focus our analysis on that particular data sharing group by setting this global filter so that all reporting will be limited to just that data sharing group until we remove the filter. One other level setting topic, this view of random sync read I/Os shows far more activity for the 4K buffer pools than for those supporting other page sizes. I think that’s common across Db2 implementations.

Random sync read I/Os tend to be the dominant contributor to total read I/Os. So many of the buffer pool views in IntelliMagic Vision like this one start there. But to confirm that total read I/O activity follows a similar distribution, let’s go ahead and customize this chart to include all sync and prefetch read I/Os.

So, total read I/O activity in this environment also takes place largely in the 4K buffer pools. And so, these 4K buffer pools will be the primary focus of our subsequent buffer pool analysis.

So, as we explore the Db2 Statistics data today, I’m going to collect key charts into a dashboard so that I only have to identify and customize the views I want to see one time, and then I can go back and easily access them in the future. So let me go ahead and create this new dashboard and add this as the first view on that dashboard.

So, if our methodology directs us to buffer pools with the highest read I/O rates, we found those were the 4K buffer pools. So let’s start from this view of all I/O to the 4K pools and drill down by buffer pool. So, total read I/O rate consists of the five metrics listed here at the bottom of the legend. Two types of sync reads, random and sequential, and three types of prefetch reads, dynamic list and sequential. So as mentioned earlier, random sync read I/Os tend to be the dominant driver of I/Os. They are in this environment; I expect they’ll be in your environment as well.

But this buffer pool tuning methodology focuses on read I/Os. So let’s go ahead and customize this chart and remove the write I/Os, WRITE, and change this title accordingly. So again, we see in this demo environment that buffer pool 20 has by far the more read I/Os than the other buffer pools. And so in this methodology, it would be the initial candidate for allocating additional memory. And let’s go ahead and capture that in our dashboard.