netdata/health/guides/postgres/postgres_table_index_cache_io_ratio.md

Understand the alert

This alert monitors the PostgreSQL table index cache hit ratio, specifically the average index cache hit ratio over the last minute, for a specific database and table. If you receive this alert, it means that your table index caching is not efficient and might result in slow database performance.

What does cache hit ratio mean?

Cache hit ratio is the percentage of cache accesses to an existing item in the cache, compared to cache accesses to a non-existing item. A higher cache hit ratio means that your database entries are found in the cache more often, reducing the need to access the disk and consequently speeding up the execution times for database operations.

Troubleshoot the alert

1. Check cache configuration settings

• shared_buffers: This parameter sets the amount of shared memory used for the buffer pool, which is the most common caching mechanism. You can check its current value by running the following query:

  SHOW shared_buffers;
  

• effective_cache_size: This parameter is used by the PostgreSQL query planner to estimate how much of the buffer pool data will be cached in the operating system's page cache. To check its current value, run:

  SHOW effective_cache_size;
  

1. Analyze the query workload

• Queries using inefficient indexes or not using indexes properly might contribute to a higher cache miss ratio. To find the most expensive queries, you can run:

  SELECT * FROM pg_stat_statements ORDER BY total_time DESC LIMIT 10;
  

• Check if your database is using proper indexes. You can create a missing index based on your query plan or modify existing indexes to cover more cases.

1. Increase cache size

• If the cache settings are low and disk I/O is high, you might need to increase the cache size. Remember that increasing the cache size may also impact system memory usage, so monitor the changes and adjust the settings accordingly.

1. Optimize storage performance

• Verify that the underlying storage system performs well by monitoring disk latency and throughput rates. If required, consider upgrading the disk subsystem or using faster disks.

Useful resources

1. PostgreSQL Performance Tuning Guide