I'm administering a server where a tool using a PostgreSQL runs. The tool takes care of most PostgreSQL configurations by itself, but I'm observing some performance problems. I could confirm at OS level that a lot of I/O is taking place, therefore I suspect that a lot of cache misses are taking place.
If you look for "cache miss" or "cache miss postgresql" or similar searches in the Internet, you will find a lot of references to "cache_miss statistics". But nowhere is explained how to get them! I kind of understood, that that value has to be calculated subtracting hits from fetches. But since I'm no experienced DB admin, I don't really understand with values are meant :-S
I found the PostgreSQL – Monitoring Database Activity documentation, but I'm not sure if following formula is all I need:
cache_miss = "result_of" pg_stat_get_db_blocks_fetched(oid) - "result_of" pg_stat_get_db_blocks_hit(oid)
An explanation for dummies would be highly appreciated.
Thank you in advance!
Best Answer
I use this query to show
disk x cache
hits: