diff options
Diffstat (limited to 'health/guides/postgres/postgres_db_cache_io_ratio.md')
| -rw-r--r-- | health/guides/postgres/postgres_db_cache_io_ratio.md | 51 |
1 files changed, 51 insertions, 0 deletions
diff --git a/health/guides/postgres/postgres_db_cache_io_ratio.md b/health/guides/postgres/postgres_db_cache_io_ratio.md new file mode 100644 index 000000000..d39329763 --- /dev/null +++ b/health/guides/postgres/postgres_db_cache_io_ratio.md @@ -0,0 +1,51 @@ +### Understand the alert + +The `postgres_db_cache_io_ratio` alert is related to PostgreSQL databases and measures the `cache hit ratio` in the last minute. If you receive this alert, it means that your database server cache is not as efficient as it should be, and your system is frequently reading data from disk instead of cache, causing possible slow performance and higher I/O workload. + +### What does cache hit ratio mean? + +Cache hit ratio is an indicator of how frequently the data required for a query is found in the cache instead of reading it directly from disk. Higher cache hit ratios mean increased query performance and less disk I/O, which can greatly impact your database performance. + +### Troubleshoot the alert + +1. Determine if the cache hit ratio issue is affecting your overall database performance using `htop`: + + ``` + htop + ``` + + Check the `Load average` gauge, if it's in the safe zone (green), the cache hit ratio issue might not be affecting overall performance. If it's in the yellow or red zone, further troubleshooting is necessary. + +2. Check per-database cache hit ratio: + + Run the following query to see cache hit ratios for each database: + ``` + SELECT dbname, (block_cache_hit_kb / (block_cache_miss_read_kb + block_cache_hit_kb)) * 100 AS cache_hit_ratio + FROM (SELECT datname as dbname, + sum(blks_read * 8.0 / 1024) as block_cache_miss_read_kb, + sum(blks_hit * 8.0 / 1024) as block_cache_hit_kb + FROM pg_stat_database + GROUP BY datname) T; + ``` + + Analyze the results to determine which databases have a low cache hit ratio. + +3. Analyze PostgreSQL cache settings: + + Check the cache settings in the `postgresql.conf` file. You may need to increase the `shared_buffers` parameter to allocate more memory for caching purposes, if there is available memory on the host. + + For example, set increased shared_buffers value: + ``` + shared_buffers = 2GB # Change the value according to your host's available memory. + ``` + + Restart the PostgreSQL service to apply the changes: + ``` + sudo systemctl restart postgresql + ``` + + Monitor the cache hit ratio to determine if the changes improved performance. It might take some time for the changes to take effect, so be patient and monitor the cache hit ratio and overall system health over time. + +### Useful resources + +1. [Tuning Your PostgreSQL Server](https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) |