Craig S. Mullins 

Return to Home Page

Vol. 10, No. 1 (May 2003)

 

The Buffer Pool

Dynamic Buffer Changes and Partitioned Access
By Craig S. Mullins

 
Continuing with the theme we introduced in the last issue of the IDUG Solutions Journal, we will examine areas where there seems to be some confusion – making dynamic changes to buffer pools, and accessing data in partitioned table spaces.

There is a degree of confusion out there as to what happens when a buffer pool is dynamically altered. I have heard all kinds of apocryphal stories about the horrors that can ensue when you change the size of a buffer pool. One of the enduring myths is that any time you change buffer pool size DB2 will invalidate the data in the pool, thereby causing an I/O spike as you have to re-read all of the data back into the buffer pool. This is not true.

We conducted a series of tests and the results disprove this fallacious notion. We started with a DB2 for z/OS subsystem (Version 6) that was started fresh with no other workload running. The test was set up to use three different DB2 objects having 1 row per page, each of which resided in a single buffer pool (BP25) that was set to 1100 pages. All of the thresholds for BP25 were set to the maximum so that none would be tripped during the test. Furthermore, checkpoint frequency was modified so as not to allow a system checkpoint to occur during the test.

Five threads were run simultaneously against three different DB2 objects. This resulted in an I/O rate of approximately 60 GETPAGEs/second. The result set for each query was able to reside in BP25. So, once pages were retrieved by the first query, no I/O to disk was needed for the subsequent queries.

An ALTER BUFFERPOOL command was issued during the workload to change the size of BP25 from 1100 pages to 1300 pages. There was no change in the I/O rate. The last accounting record cut before BP25 was increased showed a 100% hit ratio (at time 10:24:39.852631). After BP25 was increased, the overall statistics showed (at time 10:25:33.791945) that the number of GETPAGEs had increased, hit ratio was still 100%, and the buffer pool had indeed been increased in size from 1100 to 1300.

So what? Well, this shows that you can increase the size of a buffer pool without taking a performance hit or invalidating buffer pool pages. What about decreasing the size of a buffer pool? Of course, if DB2 has to get rid of a page that contains data, there is no getting around the fact that the particular data in that page will have to be re-read the next time it is accessed. But will all of it, as some strange stories I’ve heard suggest?

Of course, the answer is “no!” Under similar conditions we set our happy little test BP25 buffer pool to 5000 pages – which is very overallocated for the amount of data we were going to use in our test. We ran our workload to prime the buffer pages. We ran our workload again and examined the accounting detail to assure that there were no synchronous or asynchronous reads; and there were 2400 GETPAGE requests. Satisfied that the buffers were populated with our data we shaved 100 pages from BP25 – that is, we used ALTER BUFFERPOOL to set the VPSIZE to 4900. Then we ran our workload again. This time our accounting detail showed 2400 GETPAGEs with 6 synchronous pages read and 91 asynchronous pages read. So, obviously DB2 did not invalidate all of the data in BP25 – but it seems like it did need to re-read some data. Our results on this run, 97 pages, were less than the amount of pages that were removed from BP25 (100). The DB2 reclaim manager evidently assumes a fully utilized buffer pool (which would normally be a safe assumption) and discards pages from the LRU queue until the desired size reduction has been achieved.  By repeating the test while running the IFCID 198 “getpage” trace, we were able to easily observe the effects of this process.  I think it is safe to say that the only pages read from disk were those that DB2 removed from the buffer pool when we reduced its size.

Just to be sure, we ran the test again. First step: re-run the workload at 4900 pages and validate that we get 0 pages read – synchronous or asynchronous. Done. Then shave off another 100 pages and re-run our workload. This time we got 100 asynchronous pages read; still 2400 GETPAGE requests. So, consistently our results show that reducing the size of a buffer pool will not cause DB2 to re-read all of the data again. Of course, doing so will cause DB2 to re-read data that was on pages that were removed. But this should be the only impact.

So, if you need to dynamically alter buffer pool sizes, or you have a product that dynamically alters buffer pool sizes, rest assured. You are not ruining  your performance. In fact, it is more likely that you may be improving performance (if you are making the changes wisely – that is, when your buffer pools are stressed).

Accessing Partitioned Data

Another area that tends to confuse DB2 developers until they gain some experience is how DB2 partitioning works. A common question I get goes something like this: “If a table is in a partitioned table space, say four partitions, for example, then to process the table in batch can I run four instances of the batch program in parallel, one against each partition. What do I code to do this?”

Well, the short and sweet answer to this question is “Yes, you can run four instances of a batch program in parallel if you so desire.” But there is a nuance to this misconception that might be missed here. The question lurking beneath the question is this: “How can I make sure I am accessing only data in one partition in each of the batch programs?”

To do this requires some programming work. The program will need to have a means of identifying which partition it should run against. So, you might code the program to accept an input parameter of 1, 2, 3, or 4. The program would read the parameter and translate it into the key range of values that should be read by the program. This is the LIMITKEY value for the particular partition as found in the partitioning index. You can retrieve this value from the DB2 catalog using the following query:

SELECT PARTITION, LIMITKEY

FROM   SYSIBM.SYSINDEXPART

WHERE  IXNAME = ?

AND    IXCREATOR = ?

ORDER BY PARTITION;

 

Supply the index name and creator and this query will return the partition number and LIMITKEY for that partition. (If you include this query in the program you probably will want to include the PARTITION column in the WHERE clause and return only a single row.) The LIMITKEY is the high key value for that partition. Using this information you will need to write the queries in the program such that only values from the partition being processes will be retrieved. As long as the program adheres to that key range you should only process data from the one partition that holds that data.  

Of course, none of this is necessary to get DB2 to operate in parallel. The best approach uses DB2 query parallelism because it minimizes the amount of programming work and has the potential to maximize performance. To signal DB2 to turn on parallelism you will need to BIND your program specifying the DEGREE(ANY) parameter. Then DB2 will choose the degree of parallelism for the batch program. You will only need to run the program once (instead of 4 times as in our example); DB2 will figure out how many tasks it can run in parallel for each query in the program. And there is no need to modify the program at all! This is far simpler than any alternate approach because DB2 handles chunking up the work into parallel tasks for you.

Summary

DB2 is very powerful, but very complex. This complexity translates into confusion. It is easy to get confused when using a product as powerful and feature-laden as DB2. So, don’t despair. And keep reading “The Buffer Pool” column in the IDUG Solutions Journal to help clear up some of the many DB2 misconceptions out there.

 


From IDUG Solutions Journal,
May 2003.
 
 

© 2006 Craig S. Mullins, All rights reserved.
Home.