ProTop monitors the efficiency of your database buffer pool, aka Hit%, hit ratio, or buffer hit rate. It also lets you know when there is room for improvement. Learn how to optimize your "blocks in database buffers (-B)" setting.
Introduction
What is "Hit ratio" or "Hit%"? In short, it is a measure of how often a database block containing a queried record is in already memory rather than having to be read from the disk. As we know, reading from memory is orders of magnitude faster than performing IO operations. Ideally, your database buffer pool (-B) is large enough to contain nearly all active database blocks required by your Online Transaction Processing (OLTP) application. Hit ratio tells you if this is the case.
So, how do you keep an eye on your Hit%? How do you improve it?
Progress' take
- Here is a Knowledge article on Monitoring the Buffer Hit Ratio.
- And here is a short and sweet Progres video introducing OpenEdge: Tuning Database Buffers (online).
ProTop's take: aim higher!
The video suggests 95% as a best practice target. But wait, isn't 95% five times worse than 99%? Think about it: with a 95% buffer hit ratio, 1 in 20 reads is coming from the disk. With a 99% buffer hit ratio, only 1 in 100 reads is coming from disk!
ProTop's recommendation: keep buffer hit ratio above 99%!
Automate hit ratio reporting!
Rather than manually running and configuring promon each time you want to sample and report the current "Primary Hits" percentage, use ProTop RT!
It shows Hit% in the upper left corner by simply running "protop mydb":
ProTop sends alerts to the ProTop Portal by default if your Hit ratio is less than 98% during the sample period:
And it sends Hit ratio data to the ProTop Portal so you can see it over time in the ProTop Trends dashboard:
To tune or not to tune ...
We see in the image above that the Hit ratio for this database is nearly 100% the vast majority of the time. However, we observe the ratio drop drastically every day to below 80% for a brief period around 2:30 in the morning. So you have to ask yourself, DBA:
- Is this happening during regular business transaction hours, when many users are trying to add data? No, it's happening daily around 2:30 in the morning.
- Are there high volume, read-intensive operations like reports or database maintenance running at that time? Given the timing, it is likely.
Looking at the DB Reads chart at that time:
We see a high volume of OS reads going on, which likely means some sort of report or database maintenance is running and pulling large numbers of database blocks from the disk.
This scenario is not likely a candidate for tuning unless you can fit your entire database into memory without affecting other processes. If so, great, more memory to you!
A different scenario
This chart shows frequent Hit ratios of less than 94% in the middle of the business day:
And here we see the non-trivial OS reads per second that correspond with the low hit rates above:
In this case, yes, do tune!
Tuning requires LARGE increases to -B to see small increases in buffer hit ratio.
For an idea as to what value to test, start out with a look at the BigB Guesstimator in ProTop.
Steps:
- Evaluate available memory, do you even have any?
- Determine a safe number to set -B to. We are using 100000 as an example below.
- Make a change. As indicated in the video above you can do so with the database online:
proenv> protuil mydb -C increaseto -B 100000
- Monitor the Hit% in ProTop RT for any immediate effect.
- Check ProTop Portal Trends for the effect on the Hit ratio and OS reads over time, and see if these measures are heading in the right direction.
- Study the effect on available memory.
CAUTION: Always keep in mind the amount of memory you have on the server and the amount the db, the OS, and other processes are currently using before committing additional chunks to database buffers. You don't want to replace database block IO with the IO involved in swapping memory to and from disk because you ran out. - Rinse and repeat until you see that your Hit ratio is as close to 99% as you can get given your other memory constraints. Consider adding memory if necessary.