PostgreSQLCacheHitRatio

PostgreSQL low cache hit rate on cluster. On Normal condition, database should have a cache hit rate of about 99%. This alarm will be raised if it is below 98%. This Cache Hit Ratio is available in Grafana Postgres Overview Dashboard.

Troubleshooting hints

See those articles for more information about PostgreSQL buffers and caches: * (https://www.craigkerstiens.com/2012/10/01/understanding-postgres-performance/) * (https://easyteam.fr/postgresql-tout-savoir-sur-le-shared_buffer/)

So we have only two ways to increase cache hit ratio: * increase shared_buffer size * increase effective_cache_size

The postgres Operator does not seem to offer ways to customize those two PostgreSQL settings. They are set respectively to 100MB and 4GB. So postgresql pods needs at least 100MB to run and assumes the node page cache size is about 4GB.

To optimize our node resource consumption, our postgresql pods are set to low memory and cpu consumption values. Some nodes have less than 4GB in page cache. You can try to mitigage the cache hit ratio issue by increasing postgresql memory request to 4GB max, this will result in a 4GB ram reservation on the node which can then use it for page caching.