Db2 for z/OS: Clearing Up Some Matters Pertaining to PGSTEAL(NONE) Buffer Pools

I have written a few entries for this blog in which I provided information about Db2 for z/OS buffer pools defined with PGSTEAL(NONE), including one from a few years ago in which I differentiated the PGSTEAL and PGFIX buffer pool specifications, and another from just a few months ago in which I explained – among other things – why a PGSTEAL(NONE) buffer pools is referred to as a “contiguous buffer pool” in a Db2 12 environment. With z/OS LPAR real storage sizes getting larger and larger and Db2 people looking – rightly – for more ways to exploit “big memory” to gain performance advantages, PGSTEAL(NONE) buffer pools are getting more attention than ever. That said, I have noticed that there continues to be some misunderstanding among Db2 users about a couple of matters pertaining to PGSTEAL(NONE) pools: the use of such pools for read/write versus read-only tables, and the appropriate use of large real storage page frames for PGSTEAL(NONE) pools. In this blog entry I’ll try to clear things up on those two points.

First, a little background (for more background information you can check one or both of the blog entries for which I provided links, above). The PGSTEAL(NONE) specification does NOT mean that Db2 cannot steal buffers in such a pool – Db2 will always steal a buffer in a pool when it needs to, and it needs to when the pool’s buffers are all occupied and a page of an object assigned to the pool needs to be read from disk storage into memory. PGSTEAL(NONE), rather, is your way of telling Db2, “There should not be a need to steal buffers in this pool, because I have made it (or I think I have made it) large enough to hold all pages of all objects assigned to the pool.” A PGSTEAL(NONE) buffer pool, then, is intended for use as a “pinning” pool (i.e., as a buffer pool in which assigned database objects – table spaces and/or indexes – are “pinned” in memory, which is to say, cached in memory in their entirety). Of course, you could set up a pinning pool long before we got the PGSTEAL(NONE) buffer pool specification with Db2 10 for z/OS – all you had to do was make the pool large enough to completely cache the assigned object or objects in memory. Why, then, was the PGSTEAL(NONE) option provided?

PGSTEAL(NONE) was introduced so that Db2 would know that the pool in question is intended for the pinning of objects in memory. You see, when Db2 realizes that the pool is to be used for completely caching objects in memory, it will pitch in to help in achieving your no-read-I/O goal (no read I/Os after an object is fully in memory, that is). When an object assigned to a PGSTEAL(NONE) buffer pool is first accessed after the pool has been allocated, Db2 will immediately get for the associated process (e.g., an application program) the needed page or pages, so that the process can get on with whatever it’s doing; then, Db2 will asynchronously (i.e., via prefetch) read into the buffer pool all of the remaining pages of the object. Voila! The object is now completely in-memory. Db2 12 goes a step further and enhances the efficiency of access to an object’s pages in a PGSTEAL(NONE) buffer pool by arranging the pages in a contiguous fashion in the pool, as explained in the second of the two blog entries for which I provided links, above.

That is the gist of PGSTEAL(NONE) buffer pool functionality. On now to the two misunderstandings I want to clear up via this blog entry.

Read/write versus read-only tables

This misunderstanding regarding PGSTEAL(NONE) buffer pools has persisted for quite some time. Somehow, a lot of Db2 for z/OS people got the idea that a PGSTEAL(NONE) buffer pool is appropriate only for objects that are read-only (or very nearly so) in nature. WRONG. Read/write versus read-only access will generally not be an important factor when deciding whether an object would be a good candidate for a PGSTEAL(NONE) buffer pool. So what if a page of an object cached in a PGSTEAL(NONE) buffer pool gets updated? The updated page will be externalized (to disk, probably in an asynchronous fashion, or to a group buffer pool, likely in a synchronous manner as part of commit processing, when the Db2 subsystem is part of a data sharing group) as needed and in the usual way. No problem.

There are basically two things to consider when you’re looking for database objects (table spaces or indexes) that would be good candidates for a PGSTEAL(NONE) buffer pool: size and frequency of access. Let’s take a closer look at these two factors:

• Size – That this is a factor should be obvious. Suppose you have a table with a billion rows of data (not unusual – an organization with which I often work has more than 50 Db2 for z/OS tables that each hold more than 1 billion rows of data). That table’s rows might take up several hundred gigabytes of space. Could you cache that table in memory in its entirety? Sure you could – an individual Db2 buffer pool can be multiple terabytes in size. Do you want to use that much memory for a single database object? Probably not. With that said, when you think about size as a factor in evaluating candidates for a PGSTEAL(NONE) buffer pool, you don’t have to think, “Tiny.” As I mentioned in my opening paragraph, z/OS LPAR real storage sizes are getting larger all the time (I have seen plenty of LPARs with several hundred gigabytes of real storage – the largest real storage size for an LPAR that I’ve personally seen in the real world is 1.1 TB). Db2 buffer pool configurations are growing along with z/OS LPAR real storage sizes (I’ve seen a real-world Db2 for z/OS subsystem with an 879 GB buffer pool configuration; I’ve seen an individual buffer pool sized at 253 GB). With a large real storage resource available on a system, could you reasonably consider assigning to a PGSTEAL(NONE) buffer pool an object that takes up several hundred megabytes of space? Maybe a gigabyte or more? Yes, you could. The size-appropriateness, then, of an object for a PGSTEAL(NONE) buffer pool is going to depend in large part on the size of the relevant z/OS LPAR’s real storage resource and the degree to which that resource can be used for the Db2 subsystem (even a large real storage resource can be over-utilized – I like to see a z/OS LPAR’s demand paging rate at either zero or less than 1 per second).

[Something else to think about when considering the size of an object that you might assign to a PGSTEAL(NONE) buffer pool: online REORG. If you execute an online REORG for an object in a PGSTEAL(NONE) buffer pool, that buffer pool will also be used – understandably – for the object’s shadow data set(s). That, in turn, means that you’ll either size the PGSTEAL(NONE) buffer pool to accommodate the shadow data set(s), or you’ll be prepared for a lot of I/O activity for the buffer pool when the REORG job is running, or you’ll think about not REORGing the object (and that may not be so far-fetched – some objects truly do not have to be reorganized).]

• Frequency of access – Yes, z/OS LPAR real storage sizes are getting bigger and bigger, but mainframe memory isn’t free. You want to use it effectively – maximize its value. Can you put a lot of dinky objects in a PGSTEAL(NONE) pool? Yes. If those objects are not accessed particularly frequently, would that be a good use of mainframe memory? Probably not. What you want in a PGSTEAL(NONE) buffer pool are objects that are accessed very frequently – that will give you the biggest bang for your buck, as we say. You may already know which not-too-big (see the discussion of size, above) table spaces and/or indexes in your system get a great deal of access activity. If you don’t know that, information in the real-time statistics tables in the Db2 catalog can provide clarity (for example, the GETPAGES column of SYSTABLESPACESTATS and SYSINDEXSPACESTATS).

So, consider object size, and consider frequency of access when looking for good candidates for a PGSTEAL(NONE) buffer pool. Type of access, in the read/write versus read-only sense, is generally not going to be an important factor (yes, read/write access could impact the need for online REORG, and that has an implication that I noted in the [bracketed] paragraph above).

Large real storage page frames

I’ve often recommended to Db2 for z/OS-using organizations that they take steps to ensure that busy buffer pools – those, I’d say, for which the GETPAGE rate is greater than 1000 per second – are fully backed by large real storage page frames. As I explained in an entry posted to this blog a few years ago, large real storage page frames improve page-access performance by making the translation of virtual storage to real storage addresses more CPU-efficient.

It’s likely that a PGSTEAL(NONE) buffer pool in your system will be quite busy, in a GETPAGEs per second sense (I just got through explaining why frequency of access is an important consideration when evaluating candidate objects for a PGSTEAL(NONE) pool). That being the case, you should ensure that such a pool is backed by large real storage page frames, right? Well, maybe – maybe not.

Why the equivocation? Here’s the deal. As I explained in my blog entry covering the “contiguous” nature of a PGSTEAL(NONE) buffer pool in a Db2 12 for z/OS system, a given real storage page frame used for a PGSTEAL(NONE) pool will hold pages belonging to one and only one database object. Suppose you want to assign to a PGSTEAL(NONE) buffer pool a pretty good number of small, very frequently accessed table spaces and/or indexes. Fine. That could be a very good use of a PGSTEAL(NONE) pool. Should that pool be backed by 1 MB real storage page frames, for a performance boost? Maybe not. Consider that it takes 256 buffers of 4 KB each to exactly fill a single 1 MB page frame. What if you assign, to a 4K pool defined with PGSTEAL(NONE) and FRAMESIZE(1M), a frequently accessed “code” or “look-up” table whose table space consists of only, say, 20 pages? Those 20 pages will go in one 1 MB page frame, and none of the rest of the space in that page frame will be usable for any other object assigned to the pool. What if there are quite a few such small objects assigned to the pool? That could mean that the actual size of the pool will have to be quite a bit larger than you’d first thought, owing to the unusable space in the 1 MB frames that each are limited to holding the few pages of a single object. Are you OK with that? Maybe you’ll decide to just go with 4 KB page frames for this PGSTEAL(NONE) pool, to make more efficient use of memory, and I’d find that decision to be very much understandable. What if objects assigned to a PGSTEAL(NONE) pool have, on average, a few thousand pages apiece? Might you still have some “wasted” space in 1 MB page frames if it’s a FRAMESIZE(1M) pool? Yes, but in a proportionate sense this won’t be such a big deal. What if an object assigned to the pool has 5000 pages? Those pages will exactly fill nineteen 1 MB page frames, and the 136 pages left over will go in another 1 MB frame, and the remaining space in the 1 MB frame, which could hold another 120 pages of 4 KB each, will not be usable for another object. That would be nineteen 1 MB pages fully utilized, and about 47% of the twentieth 1 MB page frame “wasted,” or a little over 2% wastage when you consider all of the twenty 1 MB page frames used for the object. Personally, I wouldn’t lose any sleep over that.

Here’s something that follows from what I’ve just explained: if you have identified a set of objects that you want to assign to a PGSTEAL(NONE) pool, and a number of those objects are quite small (with several having maybe fewer than 50 pages apiece) and others are larger (maybe a few thousand pages apiece), use two different PGSTEAL(NONE) pools. Specify FRAMESIZE(1M) for one of the pools, and assign the larger objects to that pool, and assign the smaller objects to the pool for which you’re using the default frame size of 4K (assuming we’re talking about objects with 4 KB-sized pages). That will give you some performance boost from large frames for the one pool, while ensuring efficient use of memory for the other pool.

By the way, I’m not talking about FRAMESIZE(2G) for a PGSTEAL(NONE) pool because Db2 does not allow 2 GB page frames to be used for a PGSTEAL(NONE) pool. If you want to back a PGSTEAL(NONE) pool with large frames, go with FRAMESIZE(1M).

I hope that this blog entry will be helpful for you. Thanks for being a reader of the blog.

Originally published on Roberts Db2 Blog