the cache hit rate using pg_statio_user_tables

I read from this blog that a small 20% of the data in your database is getting the vast 80% of reads. That means a small portion of the data in the database is frequented. Hence, you want to ensure that 20% is accessed in the cache (main memory) as opposed to disk. Ultimately, you want to have a cache hit rate of 99%. The blog provides this command to find the cache hit rate:

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;

Ok, so I am expecting a number close to 99%, but now this is the number I got:

#   pg_statio_user_tables;
   heap_read   |   heap_hit    |         ratio          
---------------+---------------+------------------------
 2214811975866 | 1791164728361 | 0.44712310145763220493
(1 row)

0.44? That is not even close to 99%. That must mean that most of the hits are happening on disk and the cache is virtually nonexistant. What could be causing this issue?

The formula given only tells you what the hit rate was for hits within the PostgreSQL buffer cache (shared_buffers).

But maybe all of the misses were pulled from the OS's file cache, not actually from disk. PostgreSQL provides no direct way for you to know that, as it keeps out of the kernels business as much as possible. (Some other DBMS by-pass the OS's file cache, but PostgreSQL doesn't, it runs on top of it.)

One way to get a feel for it on recent versions if you run with track_io_timing turned on would be to look at

select sum(blks_read)/sum(blk_read_time) from pg_stat_database

For example, I get 110 blocks read per millisecond of read time. Since my disk are not nearly that fast, I know that almost all of that was read from the OS file cache, not actually from disk.

If you have shared_buffers set to a small fraction of the system RAM, as is usually recommended for large systems, then you can get better-looking hit ratios by increasing shared_buffers to be a large fraction of the RAM. Note that this is unlikely to give you better performance, it will just make some arbitrary numbers look spiffy.

In any case, hit ratios are part of "mythology-based administration". Maybe 20% of my data is responsible for 80% of my reads. Maybe 20% of the bloggers' data is responsible for 80% of his reads. Neither me nor the blogger know whether that applies to your database or not. Maybe it does, maybe is doesn't.

And even if it does, what if 20% of my data is still far larger than my available RAM? No amount of tinkering is going to make that fit in the cache. I would have to buy more RAM, which is expensive and involves down time, all to solve a "problem" that was not actually a problem.

If you have a performance problem, you should investigate what the problem is. If you don't have a performance problem, your time would be better spent practicing disaster recovery, or getting a test system and making a realistic load generator for it (so that you can predict what problems are likely to arise on your real system using data from the test system, rather than based on myths). Also, if you are running a version older than 9.2, you should spend time upgrading to at least that version if not newer. The tools available to diagnose problems once they do occur are getting better all the time, but if you are running an old version you can't use them.

StackExchange Database Administrators Q#103421, answer score: 10