Team with the best
Db2® LUW Performance Tools
company in the World

DB2 LUW Performance: Bufferpool Hit Ratios and Folly

April 7, 2009, 9:11 pm
Posted by Scott in General
It's difficult to talk about DB2 performance and not have the subject of Bufferpool Hit Ratios come up. It's as if high bufferpool hit ratios are somehow capable of saving the planet from global warming. Yes, bufferpool performance is relevant, but we need to evaluate these with a dose of reality. Performance Heroes will spend much more time on workload analysis and physical design than twiddling memory bits.
The (SQL) overall bufferpool hit ratio (OHR)

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!!!


PHYSICAL DESIGN FLAWS OBFUSCATED BY LARGE MEMORY POOLS ARE THE NUMBER ONE CPU KILLER IN AMERICA AND AROUND THE GLOBE

DB2 9 Automatic Memory Tuning, and other manual memory tuning, AT BEST can ONLY HOPE to COMPENSATE for PHYSICAL DESIGN INADEQUACIES.

So, if you observe very high bufferpool hit ratios, don't get out the champagne and start celebrating just yet. Check your other key cost metrics (IREF and BPLRTX) and make sure that large bufferpools are not obfuscating physical design flaws.

Here's a tip worth the price of at least 8 CPUs...

Many organizations run development and test databases with the "same" memory configurations as found in their production database. Bad idea. Instead, development and test databases should use very small bufferpools in the range of 4-16MB. If you can get your application to perform well with only a 16MB bufferpool, the chances are pretty good that your physical design (indexes, MQTs, MDCs) will adequately support your production workload and your production environment will soar at breakthrough speeds with full size large bufferpools. If you can't get decent response time in a development/test environment with a 16MB bufferpool, then you are not ready for production yet! QA environment databases should test workloads with bufferpool sizes configured at 25%, 50%, and 100% of production database bufferpool sizes.

The Shameless Marketing Moment...

Brother-Panther™ for DB2 LUW has only been generally available for 22 days and already has several licensed and PAID customers. The value of this breakthrough performance analysis and tuning solution is apparently extremely self evident! Some 38 trial downloads have been completed by organizations located in Australia, the US, Canada, Germany and Sweden. Our new Performance Hero Guide has been downloaded 83 times! Got your FREE trial of Brother-Panther yet? Become a Performance Hero! Want a quick introduction and training? - Register for our Webinar "A Preview to DBI Performance Solutions: Become a Performance Hero" this coming August 28th at 10am CDT.

Just for fun...

Do you ever wonder just how fast your high speed internet connection really is? Were you "sold" a certain capacity/speed level by your high speed vendor, but you're not sure if you are really getting what you've paid for? Check out this cool web site that will perform a FREE, EASY, and PAINLESS connection speed test for you: http://www.speakeasy.net/speedtest/

Until next time,
Cheers,
Scott

Scott Hayes
President & CEO, DBI
IBM GOLD Consultant