From be1c7e50e1e8809ea56f2c9d472eccd8ffd73a97 Mon Sep 17 00:00:00 2001 From: Daniel Baumann Date: Fri, 19 Apr 2024 04:57:58 +0200 Subject: Adding upstream version 1.44.3. Signed-off-by: Daniel Baumann --- .../postgres_table_toast_cache_io_ratio.md | 39 ++++++++++++++++++++++ 1 file changed, 39 insertions(+) create mode 100644 health/guides/postgres/postgres_table_toast_cache_io_ratio.md (limited to 'health/guides/postgres/postgres_table_toast_cache_io_ratio.md') diff --git a/health/guides/postgres/postgres_table_toast_cache_io_ratio.md b/health/guides/postgres/postgres_table_toast_cache_io_ratio.md new file mode 100644 index 00000000..c33a2373 --- /dev/null +++ b/health/guides/postgres/postgres_table_toast_cache_io_ratio.md @@ -0,0 +1,39 @@ +### Understand the alert + +This alert monitors the TOAST hit ratio (i.e., cached I/O efficiency) of a specific table in a PostgreSQL database. If the hit ratio is low, it indicates that the database is performing more disk I/O operations than needed for the table, which may cause performance issues. + +### What is TOAST? + +TOAST (The Oversized-Attribute Storage Technique) is a mechanism in PostgreSQL to efficiently store large data items. It allows you to store large values (such as text or binary data) in a separate table, improving the overall performance of the database. + +### What does the hit ratio mean? + +The hit ratio is the percentage of cache hits (successful reads from the cache) compared to total cache requests (hits + misses). A high hit ratio indicates that the data frequently needed is stored in the cache, resulting in fewer disk I/O operations and better performance. + +### Troubleshoot the alert + +1. Verify if the alert is accurate by checking the TOAST hit ratio in the affected PostgreSQL system. You can use the following query to retrieve the hit ratio of a specific table: + + ```sql + SELECT CASE + WHEN blks_hit + blks_read = 0 THEN 0 + ELSE 100 * blks_hit / (blks_hit + blks_read) + END as cache_hit_ratio + FROM pg_statio_user_tables + WHERE schemaname = 'your_schema' AND relname = 'your_table'; + ``` + + Replace `your_schema` and `your_table` with the appropriate values. + +2. Examine the table's indexes, and consider creating new indexes to improve query performance. Be cautious when creating indexes, as too many can negatively impact performance. + +3. Analyze the table's read and write patterns to determine if you need to adjust the cache settings, such as increasing the `shared_buffers` configuration value. + +4. Inspect the application's queries to see if any can be optimized to improve performance. For example, use EXPLAIN ANALYZE to determine if the queries are using indexes effectively. + +5. Monitor overall PostgreSQL performance with tools like pg_stat_statements or pg_stat_activity to identify potential bottlenecks and areas for improvement. + +### Useful resources + +1. [PostgreSQL TOAST Overview](https://www.postgresql.org/docs/current/storage-toast.html) +2. [Tuning Your PostgreSQL Server](https://wiki.postgresql.org/wiki/Tuning_Your_PostgreSQL_Server) -- cgit v1.2.3