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
- Exploring Assessments of Key Metrics
- Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 1)
- Db2 Buffer Pool Tuning: Exploring Key Metrics (Part 2)
- Integrating Db2 IFCID 199 with SMF 42 Data
- Buffer Efficiency from IFCID 199 and SMF 42
- Measuring Benefits of Db2 Buffer Pool Tuning
- Exploring Other Db2 Statistics Metrics
- Sample Dashboard of Db2 Statistics Metrics
- Integrating with RMF Metrics (70, 71, 72, 74)
- Integrating with RMF 70 and 72 CPU and WLM Metrics
- Integrating with RMF 71 Memory and Large Frame Metrics
- Integrating with RMF 74.4 Coupling Facility Metrics
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.
Speak to a Technical Expert Today
Whether you are conducting product research, need support on a project, are experiencing downtime, or want to learn more about how IntelliMagic can support your business, our experts are here to help.
You May Also Be Interested In:
Implementing Asynchronous Duplexing in a Production Db2 Lock Structure - User Experience
Analyze the performance impacts of implementing asynchronous duplexing for Db2 lock structure in a high volume Db2 data sharing environment.
How to Achieve Higher Availability and Lower Performance Costs with Asynchronous Db2 Lock Structure Duplexing
In this Cheryl Watson Tuning Letter Reprint, Todd Havekost and Frank Kyne detail the unexpected performance benefits gained after one site implemented Asynchronous duplexing.
Db2 Accounting Data: Customized Dashboard Recap
A survey of a sample customized dashboard where key Accounting data charts have been collected. These dashboards can promote collaboration across teams, as well as serving as a springboard for additional analysis as the views are applied to other time intervals.