November 16, 2010

The Art of Bufferpool Tuning

I've been asked to write about bufferpool tuning. Though it's been years since I've had the responsibility of tuning DB2 bufferpools, I'm happy to oblige.

First off, I call bufferpool tuning an art, and I do so seriously. Artists and musicians spend untold hours perfecting their craft, and I believe a similar level of practice and dedication is needed for DB2 systems programmers to master bufferpool tuning.

Of course, the reason we need to tune bufferpools is to limit the number of times DB2 must read disk storage as opposed to having the data already in memory. In the early days we'd make one primary bufferpool (BP0) and put everything into it. Logical as this approach seemed, we eventually learned this wasn't the way to go. In fact, creating a large bufferpool can hurt performance by causing z/OS system paging. So IBM has come to recommend the use of multiple, smaller bufferpools (BP0, BP1, BP2, etc.).

Even though I haven't tuned recently, the basics remain the same. When tuning applications, try to figure out if a tables or index space is being accessed randomly or sequentially. One way to do this is to isolate the table or index space in a separate bufferpool that's dedicated to tuning. Once the object is isolated, see if doubling the memory will reduce disk I/O. It may surprise you to learn the answer is often no. Some access is purely sequential, and having more and more memory simply doesn't help.

A starting point is to separate everything except the catalog from BP0. Move table spaces to BP1, indexes to BP2 and DSNDB07 work files to BP7. The next step is to group objects based on access. Put randomly accessed objects into one pool and sequentially accessed objects in another. This allows you to set some of the performance knobs for sequential versus random access. So as you can see, you'll end up with around six to eight bufferpools to tune your system.

While detailed technical information on bufferpool tuning is hard to find, a good source is a bufferpool tuning specialist named Joel Goldstein. Some of his whitepapers are availabe online.

And for the latest information on tuning from IBM, see the DB2 10 Managing Performance manual. A couple of the sections are specifically devoted to bufferpool tuning. In the "Improving the use of real and virtual storage" section, some of the tuning knobs are explained. Also check out the "Tuning DB2 buffer, EDM, RID, and sort pools" section.

If you're a bufferpool tuning artist, please tell us about your work in the Comments section below.