diff options
author | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-19 02:57:58 +0000 |
---|---|---|
committer | Daniel Baumann <daniel.baumann@progress-linux.org> | 2024-04-19 02:57:58 +0000 |
commit | be1c7e50e1e8809ea56f2c9d472eccd8ffd73a97 (patch) | |
tree | 9754ff1ca740f6346cf8483ec915d4054bc5da2d /health/guides/postgres/postgres_db_transactions_rollback_ratio.md | |
parent | Initial commit. (diff) | |
download | netdata-upstream.tar.xz netdata-upstream.zip |
Adding upstream version 1.44.3.upstream/1.44.3upstream
Signed-off-by: Daniel Baumann <daniel.baumann@progress-linux.org>
Diffstat (limited to '')
-rw-r--r-- | health/guides/postgres/postgres_db_transactions_rollback_ratio.md | 55 |
1 files changed, 55 insertions, 0 deletions
diff --git a/health/guides/postgres/postgres_db_transactions_rollback_ratio.md b/health/guides/postgres/postgres_db_transactions_rollback_ratio.md new file mode 100644 index 00000000..b2f94fed --- /dev/null +++ b/health/guides/postgres/postgres_db_transactions_rollback_ratio.md @@ -0,0 +1,55 @@ +### Understand the alert + +This alert calculates the `PostgreSQL database transactions rollback ratio` for the last five minutes. If you receive this alert, it means that the percentage of `aborted transactions` in the specified PostgreSQL database is higher than the defined threshold. + +### What does transactions rollback ratio mean? + +In a PostgreSQL database, the transactions rollback ratio represents the proportion of aborted transactions (those that roll back) in relation to the total number of transactions processed. A high rollback ratio may indicate issues with the application logic, database performance or excessive `deadlocks` causing transactions to be aborted frequently. + +### Troubleshoot the alert + +1. Check the PostgreSQL logs for any error messages or unusual activities related to transactions that might help identify the cause of the high rollback ratio. + + ``` + vi /var/log/postgresql/postgresql.log + ``` + + Replace `/var/log/postgresql/postgresql.log` with the appropriate path to your PostgreSQL log file. + +2. Investigate recent database changes or application code modifications that might have led to the increased rollback ratio. + +3. Examine the PostgreSQL database table and index statistics to identify potential performance bottlenecks. + + ``` + SELECT relname, seq_scan, idx_scan, n_tup_ins, n_tup_upd, n_tup_del, n_tup_hot_upd, n_live_tup, n_dead_tup, last_vacuum, last_analyze + FROM pg_stat_all_tables + WHERE schemaname = 'your_schema_name'; + ``` + + Replace `your_schema_name` with the appropriate schema name. + +4. Identify the most frequent queries that cause transaction rollbacks using pg_stat_statements view: + + ``` + SELECT substring(query, 1, 50) as short_query, calls, total_time, rows, 100.0 * shared_blks_hit/nullif(shared_blks_hit + shared_blks_read, 0) AS hit_percent + FROM pg_stat_statements + WHERE calls > 50 + ORDER BY (total_time / calls) DESC; + ``` + +5. Investigate database locks and deadlocks using pg_locks: + + ``` + SELECT database, relation::regclass, mode, transactionid AS tid, virtualtransaction AS vtid, pid, granted + FROM pg_catalog.pg_locks; + ``` + +6. Make necessary changes in the application logic or database configuration to resolve the issues causing a high rollback ratio. Consult a PostgreSQL expert, if needed. + +### Useful resources + +1. [Monitoring PostgreSQL - rollback ratio](https://www.postgresql.org/docs/current/monitoring-stats.html#MONITORING-STATS-VIEWS) +2. [PostgreSQL: Database Indexes](https://www.postgresql.org/docs/current/indexes.html) +3. [PostgreSQL: Deadlocks](https://www.postgresql.org/docs/current/explicit-locking.html#LOCK-BUILT-IN-DEADLOCK-AVOIDANCE) +4. [PostgreSQL: Log files](https://www.postgresql.org/docs/current/runtime-config-logging.html) +5. [PostgreSQL: pg_stat_statements module](https://www.postgresql.org/docs/current/pgstatstatements.html)
\ No newline at end of file |