The notorious database cache hit ratio

Gather round kids, and I’ll tell you a story about database tuning from way back when I was your age.

I’m talking about way back before MySQL or SQL Server even. Back when Oracle was the coolest database tech out there. Back in the dim, dark ages of the early 1990s.

In these dark ages before the web, widespread internet or smartphones, database tuning was a lonely business. It was hit and (forgive the pun) miss. The art and science of database performance tuning has come a long way in the intervening years but a some things are surprisingly still exactly the same. In particular:

• Disks are SLOW; avoiding disk IO is essential if you want to make things go faster.
• More memory means less IO — let’s try and get data in memory!

From these simple principles, the most infamous database tuning ratio was invented: The Cache Hit Ratio (sometimes called the Buffer Cache Hit Ratio). The ratio was defined as:

Cache Hit Ratio=Number of IO requests that were satisfied in the cache/Total IO requests

The Cache Hit Ratio represents the proportion of block requests that are satisfied by the database cache without requiring a disk read. Each “hit” — when a block is found in memory — is a Good Thing, since it avoids a time consuming disk IO. Therefore it seems intuitively obvious that a high Buffer Cache Hit Ratio is also a Good Thing. For most of the 1990s and 2000s DBAs attempted to tune their database by achieving an increase in the Cache Hit Ratio (or decreasing the inverse Cache Miss Ratio).

Unfortunately, while the Cache Hit Ratio clearly measures something, it’s not necessarily or even usually true that a high Cache Hit Ratio is indicative of a well tuned database. In particular, poorly tuned workloads often read the same data blocks over and over again; these blocks are almost certainly in memory so the most grossly inefficient operations ironically tend to generate very high Cache Hit Ratios. An Oracle DBA Connor McDonald famously created a script that could generate any desired hit ratio , essentially by reading the same blocks over and over again. Connor’s script performs no useful work but can achieve an almost perfect hit ratio.

There is no “correct” value for the Cache Hit Ratio — high values are just as likely to be the result of poorly tuned wrokloads as the result of well tuned memory configuration.

That having all been said, for a well tuned workload (one with a sound schema design, appropriate indexes, and well tuned aggregation pipelines) observing the Wired Tiger hit ratio can give you an idea of how well your WiredTiger cache is sustaining your MongoDB workload demand.

Here’s a script to calculate the miss rate (the opposite of the hit rate):

You can also see the miss ratio on the dbKoda performance lab home page (dbKoda is our free and open source MongoDB IDE):

Is it worth paying attention to this metric?

In general the hit or miss rate is a useful indication of how well the cache is doing. You can if you want see what happens to the metric if you resize the wiredTiger cache. The wiredTiger cache can be modified with the following command (in this example setting it to 500M):

db.getSiblingDB("admin").runCommand({ 
     setParameter: 1, 
     wiredTigerEngineRuntimeConfig: "cache_size=500M" 
});

If we play with the cache size and monitor the hit rate we can get a sense of how it affects performance. Here’s some data from a little test on my laptop, showing how various cache sizes affected the miss rate and throughput:

wiredTiger Hit rate vs throughput on MongoDB

As we can see, as we increase the size of the cache, we might see a decrease in the miss rate (or an increase in the hit rate) and an increase in throughput. The key word in the last sentence is might: some workloads will see little or no benefit from an increased cache size, either because all the data needed is already in memory, or because some of the data is never re-read and therefore can’t benefit from caching.

As imperfect as it is, the wiredTiger miss rate is a key health metric for many mongoDB databases.

To quote the Mongodb manual:

Performance issues may indicate that the database is operating at capacity and that it is time to add additional capacity to the database. In particular, the application’s working set should fit in the available physical memory.

A low cache miss ratio is the best indicator we have that the working set does in fact fit into memory.

dbKoda is a totally free, open source (AGPL) product made by a small group of wanna be coding stallions in Melbourne, Australia. It contains a rich code editing environment for MongoDB, query builders, code optimisers and the cool performance dashboard you can see above. Check it out and let us know what you think at dbKoda.com!