Bookmark and Share

Recent Posts

Best Practices for Tuning Local and Group Bufferpools

June 28, 2016

During May's IDUG NA conference, I attended a session by IBM's John Campbell. A question came up on how to size and configure local and group bufferpools. John mentioned that a presentation he'd given on this in 2013 is available as a webcast.

Having heard John myself, I encourage you to listen to him. The webcast is tremendously helpful for anyone who wants to understand the basics of DB2 bufferpool tuning.

One quick point of clarification, though. In the webcast (which is three years old), John talks about setting VPSEQT to 100 for sort workfile bufferpools (see slide 4). His recommendation at the time was to set it very high -- say 98, 99 or 100 -- and many customers have been hearing this message and responding by essentially making 100 their new default. However, IBM has since determined that DB2 will still have some random access in this type of bufferpool. For this reason, it's best to set the value to 95 or even 90 and monitor sync I/Os.
On slide 10 John talks about the use of multiple bufferpools. The minimum would be 6 BPs, Catalog/Directory (4K and 8K), User Index (4K), User Data (4K), Work File (4K and 32K). As someone who's tuned bufferpools myself, I would add that if you have a good handle on the type of processing (random or sequential), you may want to split user data into two bufferpools -- one with settings for random and another for sequential. However, John does warn that creating too many bufferpools (a BP party, as he calls it) could fragment your bufferpool requirement and cost you efficiency.

If you're in a data sharing environment, you'll especially like the second half of the webcast, which starts with slide 13. John notes that you should first tune the directory entry reclaims, adding that the general recommendation is to set the “cross invalidations due to directory reclaim” option to 0. Slide 14 discusses how to use the autonomic feature set in the Coupling Facility Resource Manager (CFRM) policy to help avoid both structure full and directory entry reclaim conditions.

John has much more to say, so again, check out the webcast.

Posted June 28, 2016| Permalink

comments powered by Disqus