First, I need to give credit to Martin Packer, my UK-based colleague and one of IBM's leading System z experts, for the "be bold" directive. Martin is one of a growing number of people in the worldwide DB2 for z/OS community who have seen that, with the possible exception of WebSphere Application Server for z/OS, no subsystem responds positively to increased memory resources as does DB2: the more memory you provide to a DB2 for z/OS subsystem, the better your DB2-accessing applications will perform, and the more CPU-efficient your DB2 workload will be. Martin likes to tell his clients to "be bold" in boosting the real storage resources with which a DB2 subsystem can work because he knows (and he's right) that incremental increases aren't the way to move the needle significantly with regard to DB2 performance and CPU efficiency. Big impact comes from big moves. Don't be thinking 5% or 10% more memory. Think 100% or more.
Right here, some folks might be thinking, "Maybe Catterall gets a commission on IBM sales of mainframe memory." Not so. I'm paid to help organizations succeed with DB2 for z/OS, and Big Memory is a big part of that. In multiple ways over the past decade or so, IBM has plowed the field, removing barriers that formerly would have gotten in the way of going big with respect to provisioning mainframe memory:
- Software -- About 10 years ago, z/OS V1.2 delivered 64-bit storage addressing, allowing for 16 exabytes (that's 16 million terabytes) of virtual storage addressability (versus the 2 gigabytes we had with the old 31-bit addressing scheme). DB2 Version 8 exploited that technology by moving most DB2 storage pools above the 2 GB "bar" in the DB2 database services address space (aka DBM1).
- Hardware -- The old zSeries line of servers (z990, z900, z800, and z890) enabled byte-addressable use of more than 2 GB of real storage back in the early 2000s. Today, an EC12 mainframe can be configured with up to 3 terabytes of real storage.
- Economics -- System z memory costs much less now than it did just a few years ago.
Buffer pools
The poster child of Big Memory exploiters, DB2 buffer pools have been above the 2 GB bar in DBM1 since Version 8. Growing your DB2 buffer pool configuration can boost performance in two ways:
- Response time and throughput -- Take a look at an accounting long report generated by your DB2 monitor. Chances are, the largest components of in-DB2 elapsed time for your applications are read I/O-related: wait for synchronous read (on-demand reads of individual table space or index pages) and wait for "other" read (this has to do with prefetch reads: if a program requests a page that is currently scheduled for read-in via dynamic, list, or sequential prefetch, the program will wait for the prefetch read to complete); thus, these wait-for-read occurrences tend to be your primary leverage point for improving response time and throughput for DB2-accessing applications. The bigger your buffer pools, the lower the incidence of wait-for-read situations.
- CPU efficiency -- Some System z people are under the mistaken impression that a mainframe's I/O assist processors take on all of the CPU load associated with I/O operations. Not true. The I/O assist processors certainly help, and they are one reason for System z's preeminence when it comes to handling I/O-heavy workloads, but make no mistake: every I/O operation consumes cycles of a general-purpose mainframe engine (or zIIP engine cycles, if we're talking about a zIIP-eligible workload). Synchronous read I/Os eliminated via bigger buffer pools lead to reduced in-DB2 CPU time (also known as "class 2" CPU time) for DB2-accessing applications, and avoided prefetch reads and database writes reduce the CPU time consumed by the DB2 database services address space (with DB2 10, prefetch reads and database writes became 100% zIIP-eligible, so savings here can help to avoid zIIP contention issues).
Does the thought of a single 20 GB buffer pool seem really "out there" to you? It might, given that most DB2 shops I've seen have entire buffer pool configurations (by which I mean the aggregate size of all buffer pools associated with one DB2 subsystem) that are well under 20 GB in size. Here's my message for you: it's time to start wrapping your brain around the idea of really big buffer pool configurations, and getting the ball rolling in that direction at your site. Let me give you a data point for your consideration: at an organization with which I've worked for a number of years (a large supplier to many firms in the manufacturing industry), the size of the buffer pool configuration for the production DB2 subsystem is 90 GB. And get this: every one of the buffer pools in this configuration is defined with PGFIX(YES). Are these people crazy? Are they thrashing horribly? No. Their demand paging rate (a z/OS monitor-reported metric, and my preferred indicator of pressure on an LPAR's memory resource) is ZERO. It's zero because the LPAR in question is configured with 212 GB of real storage -- there's plenty of memory for use beyond DB2 page caching. Oh, and this company also runs WebSphere Application Server, another Big Memory exploiter, in that same z/OS LPAR. It's a client-server application system in a box (the WebSphere apps access DB2 for z/OS data), and performance-wise, it screams.
I think that it won't be long before we see DB2 buffer pool configurations north of 100 GB.
Thread storage
CICS-DB2 people, in particular, have long known of the CPU efficiency gains to be had when persistent threads (those being threads that persist through commits, such as CICS-DB2 protected entry threads) are paired with DB2 packages bound with RELEASE(DEALLOCATE) (organizations using IMS Transaction Manager with DB2 get the same benefit with WFI and pseudo-WFI regions, and DB2 10 extended this performance tuning possibility to DDF-using applications via high-performance DBATs). The thing is, RELEASE(DEALLOCATE) packages executed via persistent threads makes for "fatter" threads, from a virtual and real storage standpoint (fellow IBMer John Campbell's term -- I like it). That required, in the past, very selective use of RELEASE(DEALLOCATE), not only because virtual and real storage were at a premium, but because the fatter threads consumed space in the part of the DB2 EDM pool used for packages allocated to threads (if you ran out space in that section of the EDM pool, you'd have programs failing). Starting with DB2 10 for z/OS, that situation changed: for packages bound (or rebound) in a DB2 10 system, almost all of the virtual storage used for copies of the packages when they were allocated to threads went above the 2 GB bar in DBM1; furthermore, those packages were copied to agent local pool storage when allocated to threads, as opposed to taking up EDM pool space. The upshot? DB2 10 provided much more virtual storage "head room" (John Campbell, again) for the pairing of RELEASE(DEALLOCATE) packages and persistent threads. Being able to use more virtual storage to gain greater CPU efficiency is nice, but only if the extra virtual storage utilization is backed by a sufficient real storage resource. Getting a bigger bang from RELEASE(DEALLOCATE) packages and persistent threads is, then, another reason to go big with memory when it comes to DB2.
Other ways to exploit Big Memory for better DB2 performance
How about a bigger dynamic statement cache? Especially for quick-running dynamic SQL statements (such as those that tend to dominate for many high-volume DB2 DDF-using applications), the CPU cost of statement preparation can be several times the cost of statement execution. The bigger the DB2 dynamic statement cache (set via a ZPARM parameter), the bigger your cache hit ratio is likely to be (the percentage of statement "finds" for cache look-ups), and that will result in CPU savings. I regularly see, these days, dynamic statement cache hit ratios (as reported by a DB2 monitor) that exceed 90%. If your hit ratio is less than that, consider enlarging your dynamic statement cache. You have all the virtual storage you'd want for that (the space has been above the 2 GB bar in DBM1 since DB2 Version 8) -- you just need to back that virtual storage with real storage.
Often getting less attention than the dynamic statement cache, the DB2 sort pool presents another opportunity to use more memory to get more done with less CPU. The sort pool is the in-memory work space available to DB2 for processing a SQL-related (as opposed to utility-related) sort operation (such as one that might be required to satisfy an ORDER BY, GROUP BY, or DISTINCT specification). The more of the processing for a SQL sort that can be accomplished in the sort pool (versus using space in the DB2 work file database), the better, in terms of CPU efficiency. The default size of the sort pool (as determined by the value of the SRTPOOL parameter in ZPARM) is 10 MB (it was 2 MB before DB2 10). The largest sort pool size I've seen at a DB2 site is 48 MB. The max size is 128 MB, but don't go crazy here -- keep in mind that this value is the maximum in-memory sort work area that DB2 can use per concurrent SQL sort; so, if you had a 40 MB sort pool and 10 big SQL sort operations were going at one time, you could see 400 MB (10 X 40 MB) of virtual storage used for sort work space. Of course, lots of virtual storage for SQL sort processing is plenty do-able if (same song, nth verse) you back that virtual storage with a large real storage resource.
And don't forget the RID pool, used by DB2 for sorting and otherwise processing lists of RIDs (row IDs, obtained from indexes) that are used for certain data access paths (such as list prefetch, and index ANDing and ORing). DB2 10 raised the default size of this pool (see your MAXRBLK setting in ZPARM) to 400 MB from the former default of 8 MB. DB2 10 also changed things so that running out of space in the RID pool for a RID list processing operation would result in that processing being continued using work file database space (versus the previous out-of-RID-pool-space action, which was to abandon RID list processing in favor of a table space scan). That was a good change, but if you see some work file spill-over for RID list processing (your DB2 monitor can show you this), you might consider making your RID pool larger -- completing a RID list processing operation using a combination of RID pool and work file space is probably better than going to a table space scan, but getting all the RID list work done in the RID pool should deliver optimal CPU efficiency.
Closing thoughts
From the start, DB2 for z/OS was architected to make good use of large server memory resources (it was one of the first exploiters of the old MVS/XA operating system, which first gave us access to 2 GB -- versus 16 MB -- of byte-addressable space on a mainframe server), and that focus continues. If you want to take the performance and CPU efficiency of your DB2 for z/OS system to the next level, go big on memory. Are you looking to have a z/OS environment in which CPU capacity is well balanced by real storage? I'd think in terms of 20 GB or more of memory for each engine (zIIP as well as general-purpose) in an LPAR.
Keep in mind that even a large real storage resource can be over-committed. As previously noted, I like to use the demand paging rate for an LPAR as the indicator of pressure on the LPAR's real storage. In expanding DB2's use of memory (larger buffer pools, more thread storage, etc.), I would aim to keep the demand paging rate from getting out of hand. My take? If that rate is in the low single digits per second, on average, during busy processing times, it's not out of hand. Mid-single digits per second is what I see as "yellow light" territory. If I saw a demand paging rate in the higher single digits per second (or more), I'd want to either make more memory available to the LPAR (my preference) or reduce virtual storage utilization. Of course, a zero demand paging rate is great, but don't let a very small, non-zero rate get you overly bothered.
More and more organizations are seeing first hand what Big Memory can do for a DB2 subsystem and the applications that access that subsystem, and they like what they see. So, in 2015, if you haven't already done so, do what my friend Martin recommends: on the DB2 memory front, go bold and go big.
No comments:
Post a Comment