What is cache hit ratio and how to improve it

Through this article, we are going to understand what is cache hit ratio and how to improve it.

If the page is found in the buffer then that is called a page hit and if the page needs to be retrieved from memory then we call this a page hit or page read (read - page read from memory, I/O happens here).

Postgres generally tries to keep the data you access most often in the cache. The cache hit ratio measures how many content requests a cache can handle compared to how many requests it receives. A cache hit is a request that is successfully handled and a miss is not. A miss will go beyond the cache to the base machine to fulfil the request.

The cache hit ratio is calculated below:

                                number of cache hit
Cache hit ratio  =  --------------------------------------------
                     number of cache hit + number of cache miss     

So if you have 100 cache hits and 2 misses, you’ll have a cache hit ratio of 100/102 which equals 98%.

We can increase the cache hit ratio by reducing the number of cache misses. This can be achieved by making the database system bloat-free and by keeping the stats (pg_stat_all_tables) up-to-date. So alternatively we are suggesting a vacuum here. Find the tables that are getting skipped by the autovacuum or vacuum process, you can check this from logs and vacuum command output.

Sometimes when the system is highly transactional and autovacuum is not helping to clear the bloat then you can also set a more aggressive vacuum for a particular table after investigating such a list of tables.

The commands are as below:

vacuum verbose analyze;

Or:

vacuum full verbose analyze;

Or from the OS level by the database superuser:

vacuumdb -p 5444 -d edb -zv

Or:

vacuumdb -p 5444 -d edb -zvf

Please remember that VACUUM FULL causes an exclusive lock, so if you need to do it, do it at a time when the database is less active.

Sometimes we see the cache hit ratio too low in the database, to calculate the ratio you use the below SQLs.

SELECT sum(heap_blks_read) AS heap_read,
       sum(heap_blks_hit) AS heap_hit,
       sum(heap_blks_hit) / (sum(heap_blks_hit) + sum(heap_blks_read)) AS ratio
FROM pg_statio_user_tables;

For indexes:

SELECT sum(idx_blks_read) AS idx_read,
       sum(idx_blks_hit) AS idx_hit,
       (sum(idx_blks_hit) - sum(idx_blks_read)) / sum(idx_blks_hit) AS ratio
FROM pg_statio_user_indexes;

For normal operations and for best performance, you want to have your Postgres cache hit a ratio of about 99%. If you see your cache hit ratio below that and the vacuum is not helping you out, you probably need to look at moving to an instance with larger memory.