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)
- Db2 I/O Cache Insights from IFCID 199 and SMF 42 Data
- Buffer Efficiency from IFCID 199 and SMF 42
- Measuring Benefits of Db2 Buffer Pool Tuning
- Exploring Other Db2 Statistics Metrics
- Walkthrough of Db2 Statistics Dashboard for Buffer Pool Tuning
- 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:
Integrating Dataset Performance (SMF 42.6) and Db2 Buffer Pools & Databases (SMF 102/IFCID 199) Data
Dataset performance data from SMF 42.6 records provides disk cache and response time data at the Db2 buffer pool and Db2 database levels when integrated with Db2 Statistics IFCID 199 data.
Db2 GETPAGE Efficiency Integrating Dataset Performance (42.6) and Db2 IFCID 199 Data
Integrating Db2 IFCID 199 and SMF 42 data can be leveraged to create a view of overall Db2 GETPAGE efficiency along with several other key GETPAGE views and data.
Evaluating and Tuning Db2 Subsystem Wait (Suspension) Times
Understanding what causes wait (suspension) time can be instrumental in improving your subsystem and application performance.