A Note on DB2 for z/OS Page-Fixed Buffer Pools
I've recently had the occasion to give DB2 for z/OS page-fixed buffers a spin, and I've been pleased by the results I've seen. In this post I want to give you some information about this relatively new mainframe DB2 feature.
The ability to page-fix DB2 buffer pools was introduced with DB2 for z/OS V8, via the PGFIX option of the -ALTER BUFFERPOOL command. The default value of the parameter is NO, which gives you the business-as-usual situation with regard to the way in which z/OS treats real storage page frames occupied by DB2 buffers. [Page frames are like cubbyholes within the real storage resource managed by z/OS on a mainframe or a mainframe logical partition (LPAR). Real storage, in turn, is just a term for mainframe server memory -- so called to distinguish it from virtual storage.] Generally speaking, z/OS retains the right to move something out of a real storage page frame and into auxiliary storage (the latter likely being some files in the disk subsystem). z/OS does this so that it can support an amount of virtual storage, spread across multiple address spaces, that exceeds the real storage available on the system. Because they are usually referenced quite frequently, DB2 buffers in real storage page frames are not likely to be shipped off to auxiliary storage (z/OS tends to steal page frames occupied by pages that have gone a while without being referenced. That said, it's possible that a page frame occupied by a DB2 buffer could be stolen by z/OS.
Because a DB2 buffer could be moved out of real storage by z/OS, DB2 has to do something to ensure that the buffer will NOT be stolen in the midst of an I/O operation (i.e., while DB2 is in the process of reading a page into the buffer from the disk subsystem, or externalizing a previously updated page in the buffer to disk). DB2 does this by asking z/OS to fix (in other words, make non-stealable) the page frame holding the buffer, until the related I/O operation has completed. DB2 subsequently tells z/OS that the page frame can revert back to stealable status, and the previously fixed buffer becomes unfixed from a real storage perspective.
z/OS is very good at assigning to tasks (meaning, dispatchable pieces of work in the system) the costs associated with services that it performs on behalf of said tasks, and so it is that the CPU cost of DB2 buffer page fix and unfix requests is assigned to the DB2 database services address space (aka DBM1) for asynchronous (i.e., prefetch) read I/Os and database writes, and to allied address spaces (through which SQL statements get to DB2 -- examples include the DB2 DDF address space, CICS application-owning regions, and batch initiators) for synchronous (single-page) read I/Os. The CPU cost of fixing and unfixing a page frame is pretty small, but when disk I/O operations related to a buffer pool number in the hundreds per second, the cumulative cost adds up.
Enter buffer page-fixing. When PGFIX(YES) is specified for a buffer pool, the page frames that hold the pool's buffers will be fixed in real storage (this is sometimes referred to as V=R, which is short for virtual storage = real storage). Because these pages can't be stolen, there is no need for DB2 to issue fix and unfix requests, and CPU consumption is reduced (again, for both the DB2 DBM1 address space and allied address spaces associated with SQL statements that target objects assigned to the buffer pool in question).
Sounds good, eh? You might wonder why the option to page-fix buffer pools wasn't delivered before DB2 for z/OS V8. My thinking is that the capability would not have been very useful prior to DB2's exploitation of 64-bit virtual and real storage addressing, something else delivered with V8. When the maximum amount of real storage addressable by z/OS was 2GB, you probably wouldn't have wanted to page-fix DB2 buffer pools for fear of putting too much stress on other users of mainframe memory (CICS, batch, TSO, etc.). Now that people are running DB2 V8 and V9 on system Z servers with scores of gigabytes of real storage, using PGFIX(YES) to fix a chunk for the purpose of reducing the CPU consumption of a DB2 workload seems like a very reasonable proposition.
Now, do you want to page-fix all of your DB2 buffer pools? Probably not. I'd lean towards using it for pools that have the most associated I/O activity (you can check this out using your favorite DB2 monitor, or by way of the good old DB2 -DISPLAY BUFFERPOOL command (what I like to do is issue the command - DISPLAY BUFFERPOOL(ACTIVE) DETAIL(INTERVAL), and then issue the same command 60 minutes later to get an hour's worth of activity). Also, make sure that the z/OS LPAR on which the DB2 subsystem is running has a sufficiency of real storage.
One other thing: page-fixing a buffer pool has nothing to do with fixing the contents of the buffers. DB2 manages the moving of pages into buffers, and the replacement of pages already in buffers with other pages (buffer stealing), for a page-fixed buffer pool as it would for a non-fixed pool. Fixing a pool's buffers in mainframe memory and "pinning" an object, in its entirety, in a buffer pool are two very different things. Page-fixing a buffer pool isn't about keeping more data and index pages cached in server memory -- it's about caching the same amount of stuff in a more CPU-efficient manner.
So, take a look at your buffer pool configuration, and give serious consideration to page-fixing the pools that have the most associated I/O activity.
The ability to page-fix DB2 buffer pools was introduced with DB2 for z/OS V8, via the PGFIX option of the -ALTER BUFFERPOOL command. The default value of the parameter is NO, which gives you the business-as-usual situation with regard to the way in which z/OS treats real storage page frames occupied by DB2 buffers. [Page frames are like cubbyholes within the real storage resource managed by z/OS on a mainframe or a mainframe logical partition (LPAR). Real storage, in turn, is just a term for mainframe server memory -- so called to distinguish it from virtual storage.] Generally speaking, z/OS retains the right to move something out of a real storage page frame and into auxiliary storage (the latter likely being some files in the disk subsystem). z/OS does this so that it can support an amount of virtual storage, spread across multiple address spaces, that exceeds the real storage available on the system. Because they are usually referenced quite frequently, DB2 buffers in real storage page frames are not likely to be shipped off to auxiliary storage (z/OS tends to steal page frames occupied by pages that have gone a while without being referenced. That said, it's possible that a page frame occupied by a DB2 buffer could be stolen by z/OS.
Because a DB2 buffer could be moved out of real storage by z/OS, DB2 has to do something to ensure that the buffer will NOT be stolen in the midst of an I/O operation (i.e., while DB2 is in the process of reading a page into the buffer from the disk subsystem, or externalizing a previously updated page in the buffer to disk). DB2 does this by asking z/OS to fix (in other words, make non-stealable) the page frame holding the buffer, until the related I/O operation has completed. DB2 subsequently tells z/OS that the page frame can revert back to stealable status, and the previously fixed buffer becomes unfixed from a real storage perspective.
z/OS is very good at assigning to tasks (meaning, dispatchable pieces of work in the system) the costs associated with services that it performs on behalf of said tasks, and so it is that the CPU cost of DB2 buffer page fix and unfix requests is assigned to the DB2 database services address space (aka DBM1) for asynchronous (i.e., prefetch) read I/Os and database writes, and to allied address spaces (through which SQL statements get to DB2 -- examples include the DB2 DDF address space, CICS application-owning regions, and batch initiators) for synchronous (single-page) read I/Os. The CPU cost of fixing and unfixing a page frame is pretty small, but when disk I/O operations related to a buffer pool number in the hundreds per second, the cumulative cost adds up.
Enter buffer page-fixing. When PGFIX(YES) is specified for a buffer pool, the page frames that hold the pool's buffers will be fixed in real storage (this is sometimes referred to as V=R, which is short for virtual storage = real storage). Because these pages can't be stolen, there is no need for DB2 to issue fix and unfix requests, and CPU consumption is reduced (again, for both the DB2 DBM1 address space and allied address spaces associated with SQL statements that target objects assigned to the buffer pool in question).
Sounds good, eh? You might wonder why the option to page-fix buffer pools wasn't delivered before DB2 for z/OS V8. My thinking is that the capability would not have been very useful prior to DB2's exploitation of 64-bit virtual and real storage addressing, something else delivered with V8. When the maximum amount of real storage addressable by z/OS was 2GB, you probably wouldn't have wanted to page-fix DB2 buffer pools for fear of putting too much stress on other users of mainframe memory (CICS, batch, TSO, etc.). Now that people are running DB2 V8 and V9 on system Z servers with scores of gigabytes of real storage, using PGFIX(YES) to fix a chunk for the purpose of reducing the CPU consumption of a DB2 workload seems like a very reasonable proposition.
Now, do you want to page-fix all of your DB2 buffer pools? Probably not. I'd lean towards using it for pools that have the most associated I/O activity (you can check this out using your favorite DB2 monitor, or by way of the good old DB2 -DISPLAY BUFFERPOOL command (what I like to do is issue the command - DISPLAY BUFFERPOOL(ACTIVE) DETAIL(INTERVAL), and then issue the same command 60 minutes later to get an hour's worth of activity). Also, make sure that the z/OS LPAR on which the DB2 subsystem is running has a sufficiency of real storage.
One other thing: page-fixing a buffer pool has nothing to do with fixing the contents of the buffers. DB2 manages the moving of pages into buffers, and the replacement of pages already in buffers with other pages (buffer stealing), for a page-fixed buffer pool as it would for a non-fixed pool. Fixing a pool's buffers in mainframe memory and "pinning" an object, in its entirety, in a buffer pool are two very different things. Page-fixing a buffer pool isn't about keeping more data and index pages cached in server memory -- it's about caching the same amount of stuff in a more CPU-efficient manner.
So, take a look at your buffer pool configuration, and give serious consideration to page-fixing the pools that have the most associated I/O activity.
posted by Robert Catterall at
6:26 PM
11 Comments:
dear robert,
have you any empirical data to share ?
and benchmarks/test results ?
because in your post, you have said nothing that can't be found in the standard documentation...
t
The CPU efficiency (in terms of CPU consumption relative to the number of rows returned to requesters) of the DB2 for z/OS-based data warehouse with which I have been working (in a consulting capacity) has improved substantially, but the page-fixing of buffer pools is one of several tuning steps that have been taken in that environment (there have also been some database design changes), and we do not have figures that isolate the effect of page-fixed buffers.
In a presentation delivered at the May, 2008 International DB2 Users Group North American Conference by Dr. Jim Teng of IBM's DB2 for z/OS development organization, it is mentioned that page-fixing DB2 buffer pools can result in "up to 10% CPU saving."
This and other IDUG conference presentations can be accessed in the member area of the IDUG Web site at www.idug.org. Basic IDUG membership is free, and premier membership is available for a modest annual fee (or through attending an IDUG event).
Very useful post explaining background of the page fixing process that can't be found in the standard DB2 documentation.
Thanks!
My pleasure. Glad you found the information to be useful. Thanks for the positive feedback.
Robert
Just so noone misunderstand, V=R is Virtual addresse = Real addresse, not so much to do with page fixing.
Perhaps a misuse of the term V=R on my part. I did not mean to imply that virtual storage addresses are equal to real storage addresses for page-fixed buffer pools. I meant to point out that for a page-fixed buffer pool, the amount of real storage occupied by the pool (at least, by the pool's buffers) would be equal to the virtual storage consumed by the pool, because the contents of the page frames used by the pool would be fixed in server memory, as opposed to being eligible for page-out to auxiliary storage. So, a pool with 10,000 page-fixed 4K buffers would occupy 40 MB of real storage and 40 MB of virtual storage.
That said, you're right. I did use V=R in a way that does not agree with the technical meaning of the phrase. Good catch!
Is there any reason not to page fix sort pools?
By "sort pools," are you referring to a buffer pool dedicated to the DB2 work file table spaces in database DSNDB07?
Yes, that's correct.
Well, assuming that your z/OS LPAR's memory resource isn't under too much pressure (i.e., the demand paging rate during busy processing times is less than 10 per second), there is not a reason I can see for not page-fixing the buffers of a pool dedicated to the sort work table spaces. That said, this is usually not going to be a big win because (in my experience, at least) that pool is usually not going to have as high a level of disk I/O activity as some of the pools used for user table spaces and indexes (and the higher a pool's disk I/O rate, the greater the CPU benefit of buffer page-fixing). I'm usually inclined to recommend page-fixing the buffers of the pools with the highest levels of disk read and write I/O activity, whilst leaving the other buffer pools with non-page-fixed buffers (this to provide a "safety valve" should the z/OS LPAR's memory resource unexpectedly come under a lot of pressure, leaving z/OS with a need to do some page-out work).
Thanks for your response and explanation. Much appreciated!
Post a Comment
Subscribe to Post Comments [Atom]
<< Home