DB2 LUW Performance: Bufferpool Hit Ratios and Folly

Accessing data from memory can take nanoseconds, whereas a trip to disk can take milliseconds. Few would disagree that memory is significantly faster than disk, so it is desirable to satisfy as many data and index Logical Read requests from memory without a trip to disk as possible.

If DB2 desires to read a particular page with a logical I/O, and this page is already in the bufferpool, this is a "hit". If the required page isn't in bufferpool memory, then this is a "miss" and a trip to disk with a Physical Read is required to satisfy the Logical Read request. The more "hits" you get, the less physical I/O your database does, and the happier users will be - generally.

The bufferpool hit ratio can, and should be, computed for the database, its bufferpools, and each of its tablespaces - with increasing granularity through each object level. From the class preparation materials, use 'dbsnap2.txt', 'bpsnap2.txt', and 'tssnap2.txt' from database, bufferpool, and tablespace snapshots respectively.

The formula:

OHR = 100 – (((Buffer pool index physical reads + Buffer pool data physical reads) * 100 ) / (Buffer pool index logical reads + Buffer pool data logical reads))

Physical Reads divided by Logical Reads actually produces the "miss" ratio. The miss ratio is subtracted from 100 to find the "hit" ratio. It is, after all, a hit or miss binary world we live in. Also be careful of division by zero, most computers and software are not fond of this. Adding an extra 1 to the total number of logical reads (to avoid division by zero) probably won't significantly alter your results.

In a similar manner, the (SQL) Index Bufferpool Hit Ratio can also be computed to determine the effectiveness of avoiding Physical I/O to INDEX pages:

IHR = 100 – ((Buffer pool index physical reads * 100 ) / (Buffer pool index logical reads + 1)

Yes, I concede, that having high bufferpool hit ratios is certainly better than having low bufferpool hit ratios. To improve hit ratios, DBAs will commonly throw more and more memory at the bufferpool sizes until they can't get the bufferpool hit ratios to go any higher. There is a point of diminishing returns. DB2 9 autonomic tuning may even help you find the point of diminishing returns more quickly and efficiently than you could discover it on your own.

THE BIG PROBLEM WITH BIG BUFFERPOOLS AND HIGH HIT RATIOS

Let's say you've got a 1 GB bufferpool (BIG1GBBP). Several tablespaces are assigned to this bufferpool, and each tablespace has one or more tables defined within it. It just so happens that one of the tables is about 100 MB in size, or maybe 200 MB (10-20% of the BIG1GBP size), with about 1 million rows (a small-medium table by today's standards). This is a popular table - lots of transactions query it. One of the queries has a WHERE predicate for which there are no indexes defined, so the access method chosen to retrieve an anticipated result set size of 2 rows is a full table scan.

Conveniently, this 100-200MB table fits completely within the 1GB bufferpool. Since the query is executed very frequently, this table is scanned over and over and over again. The data pages easily stay resident in the bufferpool, and they are "hit" each time DB2 returns to Logically read them again. The result is a 100% Bufferpool Hit Ratio! Whooo hoooo! Yay! Get out the Champagne!

Ooooops. Wait a minute. Reading 1,000,000 rows to find a result set of 2 Rows gives us an outrageously high Index Read Efficiency (IREF) ratio of 500,000 Rows Read to retrieve 1 Row! Ouch!

Meanwhile, Bufferpool Logical Reads is quickly climbing faster than the number of hamburgers sold at McDonalds (McDonalds used to boast the number of burgers served in millions on its outdoor signs, but in the last few years decided that 'billions and billions' served would lower their sign maintenance costs). More math shows that Bufferpool Logical Reads per Transaction (BPLRTX - The Most Important Cost) is an astronomically high 25,000 or so! High BPLRTX = High CPU Consumption!!!