August 19, 2014

An In-Depth Look at Buffer Pool Monitoring and Tuning

IBM recently released a Redpaper, "IBM DB2 11 for z/OS Buffer Pool Monitoring and Tuning."

While this document is intended for DB2 systems administrators, it could also be useful for those who specialize in z/OS system tuning. As someone who knows a bit about tuning buffer pools and the challenges this process entails, I'm excited to see IBM devote a Redpaper to this topic.

I've yet to read the whole thing, but the material I've been through is excellent. This isn't surprising given that the author, Jeff Berger, is one of IBM's top performance specialists.

Here's the chapter list:

            Chapter 1. Introduction
            Chapter 2. Prefetch and RID pool
            Chapter 3. Buffer pool writes
            Chapter 4. Data sharing
            Chapter 5. Workfiles
            Chapter 6. Assigning page sets to buffer pools
            Chapter 7. Improving CPU performance
            Chapter 8. Index and data organization

The first five chapters provide a great introduction. You'll learn what buffer pools are and how the tuning knobs are used to control their behavior. This information is very valuable for newcomers, or anyone in need of a buffer pool refresher course.

I believe that splitting up buffer pools is essential to effectively tune them. When I was consulting, I found that many customers solely rely upon the main buffer pool (BP0). My approach was to set up separate pools for random and sequential access -- a buffer pool for sort work files. However, in Chapter 6, Jeff makes a recommendation that I hadn't considered: assigning buffer pools based on sequential insert activity. As I said, he's the expert.

When I was tuning buffer pools, my focus was on reducing physical I/O, so I appreciated the information in Chapter 7, which covers the settings you should use to reduce CPU consumption. The final chapter examines when you should consider reorganizing index and data spaces.

I know from experience that setting up multiple buffer pools isn't simple, but the performance benefits it provides are worth the effort. This Redpaper offers valuable information to help you navigate this process.

Please post your experiences and/or questions on buffer pool tuning.