When do you Add a New DB2 Buffer Pool?

I've written a number of entries, here and in my old Catterall Consulting blog, on various topics related to DB2 buffer pools (I posted the most recent of these just a few weeks ago). Often, when I write about DB2 buffer pools (or talk about them in presentations or when working with DB2 users), I have sizing in mind -- this because 1) undersized buffer pools are the DB2 performance inhibitor that I most frequently encounter, and 2) many DB2 people haven't yet thought about leveraging 64-bit addressing and the large amounts of memory commonly available on DB2 servers. What I'm thinking about today is related to buffer pool sizing, but is more of a layout consideration -- namely, when should you think about adding a new pool to your configuration?

There is an interesting history behind this question. Way back in the 1980s, when DB2 for the mainframe was new and DB2 for Linux, UNIX, and Windows wasn't yet on the scene, I and other DB2 specialists regularly counseled users to "put everything in BP0" (speaking, obviously, of objects with 4K pages). You didn't have a lot of choices back then (besides BP0, there were BP1, BP2, and BP3 for data sets with 4K pages, and BP32K for objects with larger pages), and we thought early on that it was best to just let DB2 figure out how to best manage the caching of pages for all tablespaces of all types (and associated indexes). In the 1990s we got a DB2 release (I'm thinking that it might have been Version 4) that gave us a LOT more choices with respect to the buffer pool configuration: you could have (and it's the case today) up to FIFTY different pools for objects with 4K pages, and ten different buffer pools each for data sets with 8K, 16K, and 32K pages. Around that time, we DB2 specialists started to sing a different buffer pool configuration tune: reserve BP0 for the catalog and directory objects, and assign the other tablespaces and indexes in your database to several other buffer pools. Aside from the obvious need to assign objects with different page sizes to pools with corresponding buffer sizes, we felt that assigning different categories of objects to different pools provided advantages in terms of performance and monitoring. I'll elaborate on this as I go along.

By the late 1990s, what I'd call the basic buffer pool configuration became pretty well established. That configuration tends to look pretty much like this (I'm talking about 4K buffer pools here, but the same categories could apply to 8K, 16K, and 32K pools -- except that the catalog and directory have to go in BP0, and work file tablespaces can have 4K or 16K pages, but not 8K or 16K pages):

• Catalog and directory: BP0
• User tablespaces: BP1 (doesn't have to be BP1 -- could be any 4K pool other than BP0)
• Indexes on user tablespaces: BP2 (again, doesn't have to be BP2 -- just something other than BP0 and the pool to which user tablespaces are assigned).
• Work file tablespaces: BP7 (doesn't have to be BP7, but that's a popular choice because the name of the work file database in a non-data sharing environment is DSNDBB07).

The size of these pools will vary from one installation to another, but often BP0 will have between 5000 and 10,000 buffers, and BP7 will have between 10,000 and 20,000 buffers (maybe more). BP1 and BP2 should probably have -- depending on the server memory resource available to the operating system -- at least 40,000 buffers each (it's not uncommon for these pools to have in excess of 100,000 buffers apiece). In addition to size differences, there can be differences in parameter specifications for the various pools. For example, the virtual pool sequential threshold (VPSEQT) for BP7 (the work file buffer pool) might be set to 90 or 95 versus the default setting of 80. This parameter indicates the percentage of the pool's buffers that can hold pages brought into memory from disk via prefetch reads. Because almost all DSNDB07 reads should be of the prefetch variety, it makes sense to allow a higher percentage of the buffers in the associated pool to hold prefetched pages.

Another fairly common BP7 parameter specification difference is a higher value for the vertical and horizontal deferred write thresholds (VDWQT and DWQT). The default values for VDWQT and DWQT are 5 and 30, respectively. For BP7, they might both be set to 50 or even higher. Why? Because for the pools that hold the catalog and directory and user tablespaces and indexes, lower deferred write thresholds trigger more-frequent externalization of changed pages to disk, and that leads to faster restart times in the event of an abnormal DB2 subsystem termination (there will be fewer pending writes to re-create during the roll-forward phase of subsystem restart). For the work file tablespaces, we don't care about recovery in case of a DB2 failure -- they just hold temporary work files used for sorts and other query-processing operations; therefore, the BP7 deferred write thresholds just need to be low enough to prevent a thrashing situation. [Note: use caution in setting there thresholds above 50 -- if they are too high, you could have a situation in which the data manager critical threshold (DMTH) is hit, and that is really bad for performance (in one situation, I saw a BP7 for which VDWQT and DWQT were both above 90, and DMTH was hit frequently for that pool). If you go above 50 for VDWQT and DWQT for your BP7, ensure that DMTH is not being hit -- you can check this out using your DB2 monitor or the output of the DB2 command -DISPLAY BUFFERPOOL (BP7) DETAIL(*).]

Next on the list of specifications that might differ from one pool to another is the PGFIX option. PGFIX is the parameter that indicates whether or not z/OS will fix a pool's buffers in memory (I blogged on buffer pool page-fixing a few months ago). Because page-fixing tends to deliver the greatest benefit for pools that have the highest rates of disk I/O operations, I tend to see PGFIX(YES) specified for pools that hold user tablespaces and indexes (BP0 and BP7 usually have the default specification of PGFIX(NO)).

The basic buffer pool configuration I've just described has been extended at many DB2 sites to include additional pools beyond BP1 and BP2 for user tablespaces and indexes. Why might you allocate an additional pool or pools for user objects? A couple of scenarios come immediately to my mind:

• As part of a plan for growing the overall size of the buffer pool configuration. Suppose you have the basic configuration, with user tablespaces and associated indexes assigned to buffer pools BP1 and BP2 (or whatever). You check the total read I/O rate for these pools and find that it's on the high side -- over 1000 per second, let's say (see my blog entry on buffer pool sizing, posted a few weeks ago, for information on performing this read I/O rate analysis). You make the pools considerably larger -- maybe doubling them in size, to well over 100,000 buffers apiece -- and still the read I/O rate is higher than you'd like it to be. At this point, you could just keep growing these pools (assuming adequate real storage to back the larger pools), but you could also consider the option of enlarging the overall buffer pool configuration by creating a new pool for some of your tablespaces (and another new one for the indexes defined on tables in those tablespaces). Which objects might you reassign from BP1 to BP3 (if that's your choice for the new pool for tablespaces)? One alternative would be to reassign tablespaces of a certain category -- those holding "history-type" tables, for example, so as to separate these from "current-activity" tables from a page-caching perspective. Another possibility would be a reassignment of some tablespaces based on their I/O counts (some DB2 monitoring tools provide this information, and it's also available via the DB2 command -DISPLAY BUFFERPOOL with the LSTATS option specified). In any case, I'm talking about net new buffers in the new pool -- not a reduction in BP1 buffers to offset the buffers allocated to the new pool. How big might the new pool be? For starters, I might think in terms of BP3 being half the size of BP1, with the possibility of getting larger still based on observed I/O rates. What I've mentioned for BP3 applies also to BP4 (or whatever you decide to use for the indexes associated with the tablespaces reassigned from BP1 to BP3). When you've reassigned the tablespaces (and indexes), monitor I/O activity for the new and "legacy" pools, and grow them (or leave them alone) based on what you see.  
• To create a pool or pools to be used for "pinning" some objects in memory. This is a specialized case of overall buffer pool configuration size enlargement (again, I'm talking about new pools having net new buffers -- assuming, as always, that you have enough server memory to back the new buffers). I blogged about pinning objects in pools a couple of years ago. The concept is simple: you assign an object or objects (tablespaces and/or indexes) to a pool (or pools) that has enough buffers to hold all of the pages of the objects assigned to the pool. Once the objects are in the pool, there is no more disk read I/O activity associated with the pool. Generally speaking, the best candidates for pinning are objects that are a) relatively small (maybe a few thousand pages or less) and b) accessed very frequently. If you do decide to have a pool (or pools) for object-pinning, consider specifying PGSTEAL(FIFO) for the pool, instead of going with the default of PGSTEAL(LRU). This has the effect of making the buffer-stealing algorithm for the pool first in / first out (FIFO) instead of least-recently-used (LRU). The rationale: FIFO, being simpler, is more CPU-efficient than LRU. It's less sophisticated, but with no need for buffer stealing (remember, the pool has enough buffers to hold all pages of all objects assigned to the pool), why not go with the cheapest page stealing algorithm available?

If you do move some objects to a new pool, know that this is done by issuing an ALTER TABLESPACE (or ALTER INDEX) statement with the new buffer pool specified for the object, followed by a stop and a start of the object (via the commands -STOP DATABASE and -START DATABASE) to put the change into effect (in a data sharing environment, the object must be in the stopped state when the ALTER TABLESPACE or ALTER INDEX statement is executed). With the STOP and START being necessary, you'll want to make the change during a period when the object can very briefly be made unavailable to application processes. If you can temporarily shut off the flow of application requests for the target object, the stop and start should complete in a few seconds (issuing the STOP DATABASE command with the AT COMMIT option can enable you to "break in" on an application process bound with RELEASE(DEALLOCATE) to get the target object stopped).

As you consider adding more pools to your DB2 buffer pool configuration, keep these primary advantages in mind: 1) you can better fine-tune the configuration by setting buffer pool parameters in a way that boosts performance and efficiency for accesses to certain classes of database objects, and 2) you can get more fine-grained buffer pool monitoring data, enabling you to allocate additional buffer resources where they'll do the most good. This has worked well at a lot of DB2 sites. It could be a winning approach for yours.