Craig S. Mullins |
|
| Vol. 10, No. 1 (May 2003) |
|
 |
The Buffer Pool
Dynamic Buffer Changes and Partitioned
Access 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.
© 2006
Craig S. Mullins, All rights reserved. |