Know what these numbers have in common? They indicate the number of 4K buffer pools allocated for four different production DB2 for z/OS subsystems that I analyzed over the past couple of years. I believe that there are quite a few sites where a plethora of 4K buffer pools have been defined for a given DB2 subsystem. That's not illegal or anything (DB2 allows you to have as many as 50 different 4K pools for a single subsystem), but it does make the task of managing and monitoring a buffer pool configuration more complex and time-consuming. When I'm in a meeting with an organization's DB2 for z/OS DBAs, and we're looking over information for a DB2 environment, and I see lots of 4K buffer pools defined and I ask about that, I might see sheepish grins and a few eye-rolls, and hear words like, "Well, you know, we just added a buffer pool here and a buffer pool there over the years, for this reason and that, and we ended up with what you see." Recently, I've been pleased to see DBAs at more than one company engaged in consolidation of 4K buffer pools (the number of 8K, 16K, and 32K buffer pools is usually not a matter of concern -- you can only have as many as 10 of each, and most systems I've seen have fewer than that number allocated). These DBAs are reassigning database objects (table spaces and indexes) from lots of smaller 4K pools to a smaller number of larger 4K pools (moving objects from one buffer pool to another got easier with DB2 10, especially for organizations running DB2 in data sharing mode, as I pointed out in a blog entry on the topic I wrote a few years ago). At one site I visited earlier this year, they've taken an interesting approach to managing the size of some of the larger 4K pools they are using for consolidation purposes: they are allocating buffers for these pools in chunks of 524,288. Why? Because that's the number of 4K buffers that can fit into a 2 GB page frame (DB2 11 allows 2 GB page frames to be requested for pools defined with PGFIX(YES), with those very large frames being made available through a specification for the LFAREA parameter in the IEASYSxx member of a z/OS system's SYS1.PARMLIB data set). When the DBAs enlarge one of these pools, it will be enlarged by 524,288 buffers (or a multiple thereof), so as to get one more 2 GB page frame for the pool (or a few more, depending on the size increase).
So, if you're looking to rationalize and simplify the 4K buffer pool arrangement for a DB2 subsystem (or if your organization is just getting started with DB2 for z/OS and you're looking for a reasonable initial set-up -- I got this question a few days ago from a DBA at such a site), what kind of 4K buffer pool configuration might make sense for you? Here are my thoughts on the subject:
- Ideally, the only database objects (table spaces and indexes) in buffer pool BP0 will be those associated with the DB2 catalog and directory.
- You should dedicate a 4K buffer pool to the 4K table spaces in the work file database (just as you should dedicate a 32K buffer pool to the 32K table spaces in the work file database). Organizations very often use BP7 for this purpose, because (in a non-data sharing DB2 system) the work file database is named DSNDB07. The "7" in BP7 is then a handy reminder of the role of this buffer pool.
- You should have a default 4K buffer pool for table spaces used by applications, and a default pool for indexes defined on those table spaces.
- You might want to have a 4K buffer pool that you would use to "pin" objects in memory (i.e., cache objects in memory in their entirety). You might decide to do this for certain table spaces and indexes that a) are accessed very frequently by important programs and b) are not particularly large (it would take a lot of buffers to pin a really big table space or index in memory). Note that a "pinning" buffer pool should be defined with PGSTEAL(NONE), so as to let DB2 know that you want to use the pool to completely cache objects assigned to it.
- You might want to have a 4K pool that you would use for monitoring and diagnostic purposes. Suppose, for example, that this pool is BP12, and you have a table space for which you want access activity information. You could then assign that table space temporarily to BP12 and know that the associated information provided by your DB2 monitor or via the DB2 command -DISPLAY BUFFERPOOL(BP12) DETAIL pertains to that one table space. That is a cheaper (in terms of CPU cost) and easier way to get pretty detailed object-specific access activity information versus turning on one or more DB2 performance trace classes.
- Beyond this basic set-up, you could also consider assigning table spaces that are accessed in a mostly random fashion to a buffer pool that's different from a pool used for table spaces that see a lot of large page scans (and associated indexes would likewise go in two different buffer pools). This would depend, of course, on your being able to determine this division of objects based on predominant access patterns.
All told, you might end up with something like 6 to 8 different 4K buffer pools. I'd prefer that to having 15 or 20 or 25 or more 4K pools. There is often goodness in simplicity.
Hi Robert,
ReplyDeletethank you very much for interesting article.
I have a couple of questions regarding in-memory bufferpool.
1) if I specify PGSTEAL(NONE) , what VPSEQT threshold I should use ? since prefetch is not a factor for in-memory bufferpool
2) If bufferpool is not big enough to keep assigned objects , are pages gets swapped based on FIFO algorithm ? And pages will be brought into buffer thru Sync I/O operation ?
regards Ilya
1) For a PGSTEAL(NONE) buffer pool, I don't see a need to change VPSEQT from its default value of 80. Prefetch won't be used anyway for that pool, aside from the initial prefetching into memory of pages belonging to objects assigned to the pool.
Delete2) Yes, if buffers must be stolen, the FIFO algorithm will be used. And yes, subsequent read I/Os will be synchronous. That's why you want to have enough buffers in a PGSTEAL(NONE) pool to hold all the pages of objects assigned to the pool.
Robert
Hi Robert,
DeleteOne more question, regarding bufferpool for in-memory objects. I have 8-way sharing , and want to re-define one bufferpool for in-memory objects. This bufferpool is defined equally on 7 subs - with 30,000 pages, but on one subs , it's defined as 5,000 buffers. Would it create an issue to have an imbalance ? Any potential issues on subs with smaller number of buffers?
Regards Ilya
Not really a technical issue. More of a systems management issue. My preference is to have identical buffer pool configurations on all members of a data sharing group, because I think this makes the system easier to manage, and it also supports an "anything can run anywhere" approach to workload distribution, which I like.
DeleteThat said, sometimes reality intrudes on one's objectives. If a particular LPAR in a Sysplex has insufficient memory to support a buffer pool configuration that is do-able on other LPARs, you may have to adjust buffer pool sizes downward for the DB2 member(s) on that LPAR. You don't want a z/OS LPAR's demand paging rate to get out of hand (as long as that demand paging rate is in the low single digits per second or less, it is not out of hand).
One more thing: if you have a buffer pool that is 5,000 buffers on one member and 30,000 buffers on other members, and you intend to use that pool for caching objects in memory in their entirety, you'll either waste a lot of buffers in the larger pools (by assigning objects totaling only 5000 pages to those pools) or you'll have a lot of page stealing in the smaller pools (because the 30,000 pages of stuff you assigned to the pool won't fit in the 5000 buffers available in the member with the smaller pool). Either way, not a great situation. If you want to use PGSTEAL(NONE) for a buffer pool in a data sharing group, my recommendation would be to make the size of that pool the same on all members. If that means it has to be smaller than you'd like on some members, so be it. In that case, assign less stuff to the pool to avoid buffer stealing and get maximum performance benefit from PGSTEAL(NONE).
Robert
Hi Robert,
ReplyDeleteI defined bufferpool in my Prod environment , and used PGSTEAL(NONE).
my bufferpool size is 30,000 pages, and I assigned objects with total number of around 10,000 pages for now, however, I still see some Sync I/Os against objects which completely cashed ?
Would you tell me what can cause that ?
Regards Ilya Kurepa
I assume that you're referring to synchronous read I/Os, as opposed to synchronous write I/Os.
DeleteIs the Db2 subsystem in question running in standalone mode, or is it a member of a data sharing group on a Parallel Sysplex?
Robert
yes, Robert , I'm talking about Sync I/Os and we have 8 way data sharing , and on each subs this buffer defined with PGSTEAL(NONE).
DeleteOK, where you indicate that you're "talking about sync I/Os," I'll assume that you're referring to sync READ I/Os, as opposed to sync WRITE I/Os.
DeleteYou are seeing read I/Os for this PGSTEAL(NONE) buffer pool because you're running Db2 in data sharing mode. Consider this scenario: page 123 of table space XYZ is cached locally, on DB2A and DB2B (members of a data sharing group), in buffer pool BP5, which is defined with PGSTEAL(NONE) and has more than enough buffers to hold all pages of all objects assigned to the pool. An application process on DB2B updates page 123, causing the copy of the page cached in BP5 on DB2A to be marked invalid. The updated version of the page is written to the group buffer pool GBP5 when that change is committed, and the updated page in GBP5 is subsequently cast out to disk and is removed from GBP5 when the data entry in which it's been sitting is stolen to hold some other page. Now, an application process on DB2A needs a row in page 123. DB2A sees that the copy of that page cached in BP5 has been marked invalid. DB2A issues a read request to GBP5, hoping that the current version of the page will be found there, but the request returns no data because, as mentioned, the copy of page 123 written to GBP5 was later cast out to disk and removed from GBP5. That being the case, DB2A gets the current copy of page 123 from disk; thus, a sync read I/O.
It seems to me that if a GBP associated with a PGSTEAL(NONE) buffer pool has enough data entries to hold all pages of all objects assigned to the pool, you might not see sync read I/Os because updated pages written to the GBP would not have to be removed from the GBP to make room for other pages.
Robert
Thank you very much Robert for explanation. Great example you provided. I'm trying to assign read only objects to pgsteal(none) buffer, but some of them have updates, and since objects used across all subs, situation you described is very likely. I will also check my GBP definition for number of data entries.
ReplyDeleteAs always , thank you for all articles you're posting.
Regards Ilya.