How to Measure the Benefits of Db2 Buffer Pool Tuning

How to approach measuring the benefits of buffer pool tuning initiatives, which are likely to be primarily realized in this small set of average elapsed time metrics.

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

If a buffer pool tuning exercise succeeds in achieving its goal of satisfying more get pages from buffers and reducing sync read I/Os, we’ll expect to see improvements in these three unit of work level metrics. Synchronous database read wait time because fewer I/Os for the same workload will reduce the average time spent waiting for I/Os. In “Other” read wait time, which primarily reflects wait for prefetch read, and in Class 2 CPU time, in Db2 CPU time, because memory access is used less CPU than the many operations required to execute an I/O. And we’ll look at CPU time for both general-purpose and zIIP CPs.

That reference to Class 2 times suggests it might be helpful to have a brief refresher for how Db2 classifies the components of elapsed time. So, Class 1 time includes application and Db2 elapsed times beginning from when the Db2 thread is created. Then Class 2 is the elapsed time within Db2. It includes CPU, both GCP, and zIIP, and wait times. Then those wait times are broken out in the Class 3 times into approximately 30 components in the Db2 accounting records. So, for the metrics of primary interest to our buffer pool tuning analysis, we’ll find the CPU time and the Class 2 data and those wait times in the Class 3 data. Now two notes as we prepare to look at these, 1) is the common recommendation among DB two performance experts to view this data aggregated by connection type because different drivers of Db2 work often have very different workload profiles. And then 2) the material and the rest of this buffer pool section, both the timing metrics and the ability to view them by connection type all require the level of detail found in the accounting data. So even though this is the statistics session, we’ll be taking a sneak peek ahead into how much you can learn exploring Db2 accounting data, which is available as a separate on demand session. So, you can consider this bonus material.

Viewing accounting metrics aggregated by connection type makes great sense. That’s apparent when we begin looking at these timing metrics where the transaction and elapsed time profiles are completely different between the work coming in a Db2, for example, from CICS here, the first bar and that coming in through IMS batch BMPs, the second bar. All right, now we’re going to make use of the dashboard that we’ve been creating throughout this session and go back to the report of read disk I/O by buffer pools and reminds us that buffer pool 20 I had by far the highest volume of read disk I/Os. So now let’s look at that, now that we’re looking ahead into the accounting data, let’s look at that by connection type. And when we do that, we see the DDF is by far the largest driver of the sync read I/Os for buffer pool 20. So, let’s go ahead now and view those over time.

When we do that by connection type indicates in this environment, the DDF work is the dominant driver of read activity across almost the entire interval. So let’s go ahead and capture that in our dashboard. We indicated earlier that the Db2 accounting data captures approximately 30 types of weight events along with the elapsed times for each. So this view of average elapsed time profile by connection type contains what are typically the primary contributors. And then the others are grouped into other Db2 wait time. So as we said earlier, this work is primarily coming from DDF.

So let’s view the time of day profile for that work. That’s the primary driver of sync read I/Os for buffer pool 20. And again, we said we want to focus on four particular elements of that. So, let’s go ahead and remove the rest of the elements and we’ll get down to the two types of CPU, the sync read I/Os, and the other read I/Os. We’ll make this a line chart so we can compare the components. All right. So let’s go ahead and add this over time view onto our dashboard.

Now in an actual situation where we made buffer pool changes, we would want to compare the before and after time intervals to analyze the impact of the change. Data in my demo database doesn’t have a capture, a buffer pool change. So here I’m just using as by example, comparing with the previous day. And there was no intervening change that took place. Now, if we want here, we can also just kind of look across the entire week of data that I’ve got in this demo database. And when we do that, we see increases in the sync I/O wait time in the evening hours earlier in the week. And then that decreases later in the week. And that was actually driven by changes not relating to buffer pool tuning. And we’re going to be discussing those in the upcoming accounting session.

All right, let’s go ahead and capture this in the dashboard. So our simulated, buffer pool tuning scenario here comes to an end. So no matter what buffer pool tuning methodology you use, no matter what key metrics drive your analysis, your process will be greatly enhanced by having easy accessibility into those metrics, both the initial analysis and then when you come back to revisit it. And at the end of today’s session, we’ll see how we can leverage the customized dashboard we’ve been building throughout the session to show how the analysis can be quickly revisited for any new time period, to determine the next set of buffer pool size changes. That dashboard enables all team members involved in the process to have a common view of all the key metrics at their fingertips. And they can, again, simply update the date selection intervals to re-execute the process.

Learn More About Analyzing Db2 Statistics and Accounting Data