Buffer Efficiency from Integrating IFCID 199 and SMF 42 Data

Integrating IFCID 199 and SMF 42 data provides a view of buffer efficiency enabling buffer misses (I/Os) to be further subdivided into cache hits and misses, which can be leveraged to further refine 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

Another powerful way to leverage this integration of the Db2 IFCID 1 99 and SMF 42 data is this view of overall Db2 get page efficiency. It indicates the percentage of get pages resolved with a buffer pool hit, the ideal situation in red. This is the random buffer hit ratio, just as we discussed earlier. But the additional insight here is that for those get pages not satisfied from a buffer and thus that generate an I/O, what percentage of those I/Os result in a disk cache hit in yellow, and what’s the remaining percentage that generate an actual disk I/O in blue.

So, let’s drill into this data initially by buffer pool size. And then let’s go ahead and look at the 4K buffer pools by buffer pool number. So we’ll compare the efficiencies across the buffer pools and we’ll capture this in our dashboard.

So, we select a buffer pool with a lower percentage of hits here, like buffer pool 16. We might be interested in seeing how many I/Os are occurring. So in this case, it’s in the range of about 1500 to 2000 per second, except in the evening. Or another way we can look at this data is by the database, the buffer efficiency by database. Let’s go ahead and compare that across the various databases. And again, let’s capture that in our dashboard. Selecting this database with a lower buffer hit percentage, we see an I/O rate approaching 20,000 per second, but that are fortunately almost all cash hits. So that I/O rate led me to be curious about the buffer pool connection. So let’s go ahead and just hang on to those cache hits and now look at it by buffer pool. And when we do that, we see that almost all the I/Os to this database are coming out of buffer pool 20 for the data and 21 for the index.

And so that leads us to the topic we mentioned earlier, the potential application of this cache hit data to enhance a buffer pool tuning methodology. Disk cache hits reflect a second tier of access. The page did not reside in the Db2 buffer pool long enough to be accessed there, but the page was accessed soon enough thereafter, such that it was still present in the disk cache. So cache hit volumes as seen here can be an indicator of buffer pools that could potentially benefit from being enlarged, indicating a likelihood that the page may be accessed in the buffer if it’s able to remain resident a bit longer. Again, not surprisingly we see buffer pool 21 at the top of this list of disk cache hits again, suggesting it as a prime candidate to benefit from additional memory.

So more broadly we’ve seen how integrating the Db2 IFCID 1 99 data with SMF 42 dataset I/O performance data provides extensive insights into the I/O cache and response time characteristics at the Db2 buffer pool and Db2 database levels.

Learn More About Analyzing Db2 Statistics and Accounting Data