One Hat Cyber Team

Your IP: 216.73.216.23
Server IP: 178.33.27.10
Server: Linux cpanel.dev-unit.com 3.10.0-1160.108.1.el7.x86_64 #1 SMP Thu Jan 25 16:17:31 UTC 2024 x86_64
Server Software: Apache/2.4.57 (Unix) OpenSSL/1.0.2k-fips
PHP Version: 8.2.11
Buat File | Buat Folder

Dir : ~/usr/local/src/netdata/health/guides/postgres/

Edit File: postgres_table_toast_index_cache_io_ratio.md

### Understand the alert

This alert monitors the `PostgreSQL` TOAST index cache hit ratio for a specific table in a database. A low hit ratio indicates a potential performance issue, as it means that a high number of cache misses are occurring. If you receive this alert, it suggests that your system is experiencing higher cache miss rates, which may lead to increased I/O load and reduced query performance.

### What is TOAST?

TOAST (The Oversized-Attribute Storage Technique) is a technique used by PostgreSQL to handle large data values. It allows PostgreSQL to store large records more efficiently by compressing and storing them separately from the main table. The TOAST index cache helps PostgreSQL efficiently access large data values, and a high cache hit ratio is desired for better performance.

### Troubleshoot the alert

- Check the current cache hit ratio

Run the following query in the PostgreSQL prompt to see the current hit ratio:

```
  SELECT schemaname, relname, toastidx_scan, toastidx_fetch, 100 * (1 - (toastidx_fetch / toastidx_scan)) as hit_ratio
  FROM pg_stat_all_tables
  WHERE toastidx_scan > 0 and relname='${label:table}' and schemaname='${label:database}';
  ```

- Investigate the workload on the database

Inspect the queries running on the database to determine if any specific queries are causing excessive cache misses. Use [`pg_stat_statements`](https://www.postgresql.org/docs/current/pgstatstatements.html) module to gather information on query performance.

- Increase `work_mem` configuration value

If the issue persists, consider increasing the `work_mem` value in the PostgreSQL configuration file (`postgresql.conf`). This parameter determines the amount of memory PostgreSQL can use for internal sort operations and hash tables, which may help reduce cache misses.

Remember to restart the PostgreSQL server after making changes to the configuration file for the changes to take effect.

- Optimize table structure

Assess if the table design can be optimized to reduce the number of large data values or if additional indexes can be created to improve cache hit ratio.

- Monitor the effect of increased cache miss ratios

Keep an eye on overall database performance metrics, such as query execution times and I/O load, to determine the impact of increased cache miss ratios on database performance.

### Useful resources

1. [PostgreSQL: The TOAST Technique](https://www.postgresql.org/docs/current/storage-toast.html)