This is the blog of Robert Catterall, an IBM Db2 for z/OS specialist. The opinions expressed herein are the author's, and should not be construed as reflecting official positions of the IBM Corporation.

Sunday, February 26, 2017

Recently, a DB2 for z/OS professional I’ve known for some
years sent to me a question about the relationship between the PGSTEAL and
PGFIX options of the DB2 command -ALTER BUFFERPOOL. It took a few iterations of
question and answer to get things straightened out, and I thought, “Hmm. If
this person, who has lots of DB2 for z/OS experience and knowledge, needed a
little help in getting PGSTEAL and PGFIX straightened out in his mind, it's likely that other
DB2 people are in the same boat.” And so I’m writing this blog entry, in
the hope that it will be helpful to people who have some uncertainty about the dependencies
– if any – between PGSTEAL and PGFIX.

OK, the “if any” in the preceding sentence might suggest to
you that maybe there isn’t anything
in the way of interdependency with regard to PGSTEAL and PGFIX. In fact, there
really isn't. Yes, there’s a recommended combination of PGSTEAL and PGFIX
settings, and I’ll get to that, but the suggested combination is about the way
in which PGSTEAL and PGFIX can both support a particular performance objective,
as opposed to having anything to do with the way these two buffer pool
specifications affect each other, because
they don't affect each other.

“What?” you might ask. “How is it that two buffer pool
configuration options that both have 'page' in their long names (‘PG’ is short
for ‘page’) don’t really have anything to do with each other?” This is so
because PGSTEAL and PGFIX have very different functions. This functional
difference might have been more readily apparent to people if PGSTEAL had
instead been labeled something like BSTEAL – short for “buffer steal,” because
PGSTEAL is about management of buffers, which is a DB2 responsibility, and
PGFIX is about management of real storage – a responsibility of the z/OS
operating system.

Let me make this distinction even more clear: PGSTEAL is
related to virtual storage management
– DB2’s management of virtual storage space that belongs to DB2 – and PGFIX is
related to real storage management –
something in z/OS’s bailiwick.

Consider the possible values of PGSTEAL: LRU, FIFO, and
NONE. The first two are specified when you anticipate that there will be some
buffer-steal activity for the pool in question (i.e., there are more pages in
objects assigned to the pool than there are buffers in the pool). You’d go with
LRU (least recently used) if you anticipate that there will be quite a bit of
buffer-steal activity for the pool (“OK, DB2, you’re probably going to have to
frequently steal buffers for this pool. When you do that – when you have to
replace a table or index page currently in a buffer with another page – steal
the buffer holding the page that’s gone the longest time without being referenced.”).
You’d choose FIFO (first in, first out) when you expect some – but very little
– buffer-stealing for the pool (“Hey, DB2. You’re going to have to do a very
small amount of buffer stealing for this pool. That being the case, don’t waste
CPU cycles keeping track of which buffers hold the pages that have gone the
longest time without being referenced. Just steal the buffer holding the page
brought into memory the longest time ago.”).

How about PGSTEAL(NONE)? When would you go with that option?
You’d go this route if you anticipate that there will be NO buffer-steal
activity for a pool – in other words, the pool has more buffers than there are pages
belonging to objects assigned to the pool (“Hey, DB2. I’m planning on using
this pool to cache one or more table spaces and/or indexes in memory in their
entirety. I’m doing that because I want maximum performance for these babies.
Help me out.”). And, given the message you’ve sent to DB2, DB2 will help
you out: it will asynchronously read into the pool every page belonging to an
object assigned to the PGSTEAL(NONE) pool when the object is first referenced
after a DB2 start-up, and in optimizing SQL statements targeting objects
assigned to the pool, it will perform access path selection with the assumption
that read I/O costs will be zero (not counting possible reads associated with
use of work file table spaces).

Next, PGFIX settings. There are two: YES and NO. When NO (the
default) is in effect, a real storage page frame occupied by a DB2 buffer can
be stolen by z/OS (i.e., the contents of the page frame can be moved to
auxiliary storage, also known as the page data sets) in order to make room for
some other page that has to be brought into real storage (in performing such
page steal actions, z/OS utilizes a least-recently-used algorithm). To prevent
a z/OS real storage page frame steal action from interfering with a buffer read
I/O (DB2 copies a table space or index page from disk – or maybe from a group
buffer pool, in a data sharing system – into a buffer) or write I/O operation (DB2
copies a page in a buffer to disk or to a group buffer pool), a buffer will be
fixed in memory (i.e., made non-page-able) prior to the I/O action, and
released (made page-able again) after the I/O action. When PGFIX(YES) is in
effect for a pool, buffers belonging to the pool are fixed in memory (made non-page-able)
as soon as the pool is allocated, and they remain fixed in memory as long as
the pool is allocated. Because the buffers are always in a fixed-in-memory
state, the page-fix/page-release actions required for all buffer read and write
I/O operations when PGFIX(NO) is used are not necessary. That makes buffer read
and write I/Os more CPU-efficient when PGFIX is set to YES, and that makes
PGFIX(YES) particularly beneficial for pools with high rates of read and write
I/Os.

PGFIX(YES) is also a prerequisite if you want a DB2 buffer
pool to be backed by large real storage page frames (1 MB or, starting with DB2
11 for z/OS, 2 GB frames, versus the traditional 4 KB frames). When a pool’s
buffers are located in large real storage page frames, further CPU savings (beyond
less-expensive I/O operations) are realized, because large real storage page
frames make virtual-storage-to-real-storage address translation more efficient.

If we take a look at some combinations of PGSTEAL and PGFIX
settings, you’ll see what I meant about there being essentially nothing in the
way of interdependency. Take PGSTEAL(LRU) and PGFIX(YES). Will the fact that
the pool’s buffers are fixed in memory have any impact on the level of buffer
stealing done by DB2 for this pool? NO! REGARDLESS of whether a pool’s buffers
are fixed in memory or not, DB2 will ALWAYS steal a buffer in the pool WHENEVER
IT HAS TO, and a buffer HAS TO BE STOLEN whenever all the pool’s buffers are
occupied (by table space and/or index pages) and a page has to be read into the
pool from disk (or from a group buffer pool in a coupling facility). The PGFIX(YES)
setting for this pool simply means that z/OS will not move any of the pool’s
buffers out of real storage to auxiliary storage – it in no way restricts DB2’s
ability to steal buffers so that pages not yet in the pool can be brought in
from disk (or from a coupling facility).

Change the combination of PGSTEAL and PGFIX settings to NONE
and NO, respectively, and again you’ll see that there is nothing in the way of
an interdependency. PGSTEAL(NONE) means that DB2 does not EXPECT to have to do
any buffer stealing for the pool, because there is an assumption that the
number of pages belonging to objects assigned to the pool does not exceed the quantity
of buffers in the pool (i.e., does not exceed the pool’s VPSIZE). Of course, if
the number of pages in objects assigned to the pool does exceed the
pool’s VPSIZE (perhaps VPSIZE was too small to begin with, or it was OK
initially but objects assigned to the pool have gotten larger), and a buffer
has to be stolen because all buffers are occupied and a page has to be brought
into the pool, a buffer WILL BE STOLEN because (as noted previously) a buffer
will ALWAYS BE STOLEN when a buffer HAS TO BE STOLEN (when PGSTEAL is set to
NONE for a pool and some buffer stealing has to be performed, it will be done
on a FIFO basis – first in, first out). What about the PGFIX(NO) setting for
this pool? Will the pool’s PGSTEAL(NONE) setting in any way restrict the
ability of z/OS to move buffers belonging to the pool out of real storage to
auxiliary storage, as necessary, to free up real storage page frames so that
new pages can be brought into real storage? NO! With PGFIX(NO) in effect, a
pool’s buffers are absolutely fair game for being paged out to auxiliary
storage, REGARDLESS of the pool’s PGSTEAL setting (that said, buffers in
PGFIX(NO) pools are not often paged out of real storage, because z/OS, as
noted, steals real storage page frames on a least-recently-used basis, and DB2
tends to “touch” its buffers with a frequency that makes them unlikely to be
among the least-recently-used pages in a z/OS LPAR’s real storage).

DB2, then, manages its buffers as it needs to (with some
guidance provided by you via a pool’s PGSTEAL setting), and z/OS manages its
real storage resource as it needs to (with the understanding that buffers in a
PGFIX(YES) pool are off-limits with regard to page-out actions), and the DBMS
and the OS attend to these respective tasks pretty much independently. PGSTEAL
settings do not impact z/OS page-frame-stealing, and PGFIX settings do not
impact DB2 buffet-stealing.

Now, early on in this post I mentioned that while there
essentially aren’t any interdependencies between the various settings of
PGSTEAL and PGFIX for a buffer pool, there is a recommendation I have
concerning a particular combination of PGSTEAL and PGFIX values. Here it is: if
you are going to specify PGSTEAL(NONE) for a buffer pool, specify PGFIX(YES)
for that same pool, unless the demand paging rate of the z/OS LPAR is higher
than it should be (if the demand paging rate – available by way of a z/OS
monitor – is in the low single digits or less per second, it’s OK). Why I make
this recommendation: presumably, you assign objects to a PGSTEAL(NONE) buffer
pool because you in fact want them to be cached in memory in their entirety. You
would do this, I imagine, for objects for which maximum performance is really
important. If that’s the case, why not really max out performance as it
pertains to accessing these objects? Make the pool PGFIX(YES) as well as
PGSTEAL(NONE), so that you can get the CPU efficiency benefit of large real
storage page frames (of course, to get that benefit you need to have
large page frames available in the LPAR to back the PGFIX(YES) pool –
information about that can be found in an entry I posted to this blog a fewmonths ago).

And there you have it. Just remember that PGSTEAL is related
to DB2’s management of its buffers, while PGFIX is related to z/OS’s management
of the LPAR’s real storage resource. Two different buffer pool configuration
settings, for two different aspects of DB2 performance.