For as long as I've been involved with IDUG, performance tuning has always been one of the most popular subject areas for conference presentations and technical articles. From the wide range of possible topics in this area, two perennial favourites are buffer pool tuning and indexing strategies. At my very first IDUG, I attended a presentation by David Cohen where I was introduced to the various buffer pool thresholds on DB2 for z/OS for the first time. I think you'll find that many DB2 for z/OS shops will be using a methodology similar to what David taught us right to this day. I was also privileged to attend a seminar by the late Richard Yevich on “Designing for Performance” where I learnt a huge amount that is still relevant today. Looking at the agenda for the most recent conferences, there still is an appetite for this information; for example, there was a full day DB2 for LUW performance and tuning seminar by Scott Hayes at the recent IDUG DB2 Tech Conference in Denver.
One of the great things about being involved in the IDUG community is that you learn not only the basics, but also about less well known features of DB2 that can have significant positive effects. What I'm going to highlight in this article is one such feature: the block-based area of the buffer pools in DB2 for LUW. I'm indebted to Martin Hubel for pointing this feature out to me a number of years ago and for providing assistance in the writing of this article.
To provide a practical example of the many benefits this feature can give, I'll describe a recent personal experience. I was asked to look at an implementation of IBM Information Server (MDM Server) that was experiencing a very slow processing rate for “evergreening” (the process of ingesting and cleansing data from legacy systems into the Master Data Management model format). Regular index tuning had been done and db2advis was giving the captured workload a clean bill of health, but performance was still not acceptable. We noticed that the buffer pool hit ratios were low and that there was a large amount of page stealing taking place. The workload was a typical example of a mixture of random and sequential access: large data scans to satisfy extremely fuzzy search criteria coupled with a lot of keyed access to both lookup tables and fact tables once matches had been found. Since this is a package solution, we were not at liberty to change the SQL. It would have been possible, but very time-consuming to go through each of the many tables categorizing them as RAMOS or SAMOS and allocating them to individual buffer pools accordingly (the default implementation places most of the data into three tablespaces all supported by a single buffer pool). As an alternative approach, I recommended trying a small block-based area (initially NUMBLOCKPAGES 1000). The effect was startling. Throughput jumped tenfold, the regular buffer pool hit ratios climbed to over 99% and the page stealing totally stopped.
Let's set the scene with a brief review of the history of buffer pool tuning within the DB2 family. In DB2 for z/OS, buffer pool tuning involves adjusting a number of threshold parameters to favour either sequential or random IO and then allocate individual tables via their respective tablespaces to the most suitable buffer pool for the expected IO profile for that table. This approach has a number of issues, in particular that there are very few tables which will be exclusively randomly or sequentially accessed. The best we can hope for is that the tables will either be “RAMOS” (Random Access MOStly) or “SAMOS” (Sequential Access MOStly), and your choice of thresholds will provide maximum benefit, most of the time. Increasingly this RAMOS or SAMOS balance is becoming harder to determine as workloads become more “mixed.” Gone are the days when our workloads were all statically bound packages. Now, you are likely to see a mixture of static and dynamic SQL with a mixture of OLTP and BI workloads running against a single database. Improvements in optimisation techniques have helped to offset this increasingly diverse workload, but not in all cases.
When I first came to DB2 for LUW, I was distressed to find that this fine-grained control over buffer pools was not available, and indeed, I remember submitting enhancement requests for something to be done about it. All that could be done in those days was to change the buffer pool size. In Version 8.1, a single new option was provided: NUMBLOCKPAGES; defining a “block-based area.” During the beta program, Martin Hubel pointed this out to me and I quickly realised the simple elegance of the approach. This single parameter sets aside a part of a buffer pool for sequential access, thus protecting (in most cases) the rest of the buffer pool from being flushed out by a large sequential scan. Because of the nature of sequential access, this block-based area does not have to be large since it only has to accommodate the pages currently being used, rather than having to preserve pages for later. So, allocating a small block-based area can have a huge benefit when there is a mixture of random and sequential IO against a table.
For quite some time now I have allocated approximately 2% of the overall allocation of each buffer pool to a block-based area. For anything other than totally random workloads, this has yielded huge benefits time and time again. Even on resource constrained systems, I have found that allocating a block-based area gives benefits, and in many cases, significantly reduces the resource consumption, since often a high percentage of the CPU burn on these systems is supporting IO and eliminating physical IOs due to having to reread randomly accessed pages flushed from buffer by sequential processes.
The value of NUMBLOCKPAGES is set to 0 (i.e. unused) by default. It can be set at buffer pool creation time via “CREATE BUFFERPOOL” statement or applied to existing buffer pools using the “ALTER BUFFERPOOL” statement. Note that the parameter name is slightly misleading in that it is not the number of pages that are allocated to the block-based area, but the number of extents. The actual number of pages allocated is (NUMBLOCKPAGES * EXTENTSIZE). Additionally, although there is a restriction that NUMBLOCKPAGES cannot be more than 98% of the overall buffer pool size, in practice, this normally isn't a problem apart from a potential issue with STMM (Self Tuning Memory Management) on databases with very variable workloads (q.v.).
DB2 monitor outputs will allow you to see how much use is being made of the block based area. There are four monitoring elements specifically related to the block-based area: block_ios, pages_from_block_ios, vectored_ios and pages_from_vectored_ios. These statistics are available in snapshots at buffer pool, tablespace and tablespace container granularity. To assess whether the block-based area is providing maximum benefit, check the ratio (pages_from_block_ios/block_ios). This should be as close as possible to the extentsize. The vectored IO elements give information about sequential accesses which are using the main (page-based rather than block-based) part of the buffer pool and should be as small as possible, preferably zero, although, this may not always be achievable. If there are significant vectored IOs taking place, and especially if the block_ios ratio is significantly less than the extentsize, an increase in the value of NUMBLOCKPAGES should be considered. As well as these monitoring elements, you should also see a decrease in the corresponding traditional buffer pool monitoring elements. The normal pattern here is that the buffer pool hit ratio, measuring the percentage of total IO satisfied by logical reads, would increase. If you are experiencing page stealing, this should also be significantly reduced or even eliminated altogether by allocation of a block-based area.
Use of the block-based area is so effective that I have been advocating that it should be set to a non-zero value by default for some time. Even simply setting it to a default value of 1,000 would be beneficial - even better if it were set by default to (perhaps) 2% of the initial buffer allocation, and better still if STMM would maintain that percentage as the buffer pool size changes. Indeed, another parameter to set the percentage of the overall buffer pool to give over to the block-based area, rather than a literal value, might make more sense in these increasingly autonomic days.
Which brings me to the only significant issue I've found with the block-based area: STMM does not seem to be aware of its existence. The most severe impact I have seen is on DB2 for Solaris, which seems less effective in its autonomic efforts than other platforms for some reason, where a very dynamic workload, with bursts of high activity and periods of idleness, saw STMM attempt to shrink the total buffer pool size down to the minimum allowable size which just happened to mean that over 98% of the buffer pool was then going to be allocated to the block-based area. This would then throw errors into the diagnostic log on a regular basis until STMM increased the size of the buffer pool again. This impact was found to be even more severe on the standby member of an HADR cluster where these STMM issues were interfering with the HADR log apply processes. On platforms other than Solaris, I have not seen this behaviour, but it may be that these systems have a more consistent workload profile.
The fact, that STMM seems unaware of the block-based area is a concern as more and more of DB2 becomes autonomic. A while ago, a number of us actually had to mount a “save NUMBLOCKPAGES” campaign, since there did not seem to be an awareness throughout the development team of what this little gem did and how effective it was. Thankfully, that campaign seems to have been successful, and we now are turning our attention to ensuring that NUMBLOCKPAGES is fully supported in the autonomic memory management process, perhaps even with the enhancements I described above.
If you haven't looked into this option before, I'd suggest that you add it to your list of things to do. It could be a very “quick win” for you. If it does help in your shop, then please share the good news with the rest of us, and consider joining our efforts to make the benefits of using the block-based area more widely known both within IBM and in the wider DB2 community. You can even consider joining our campaign to introduce NUMBLOCKPAGES and STMM to each other.
Until next time …
Don't just do it, DB2 it!!!
Philip Nelson, IDUG Content Committee DB2 for LUW Team Leader
Philip.Nelson@scottishwidows.co.uk