Return
-
Title
"High Percentage of Queries Waiting" alarm in PostgreSQL -
Description
How is the High Percentage of Queries Waiting alarm in PostgreSQL calculated? -
Resolution
The collection checks the wait_event for all of the processes from pg_stat_activity to determine if they are NULL or NOT NULL. This is done inside the collection where the total number of NOT NULL rows as assigned as "t" and the NULL rows are assigned as "f".The rule then counts the total backends and the number of NOT NULL events "t" (waitNum).
The wait percent is calculated, 100*(waitNum/total)
The rule is fired based on the wait percent (default 10 warning, 20 critical, 30 fatal)
There isn't a specific query to determine why the alarm is triggered, however running a select statement from the table pg_stat_activity can obtain backend information to filter out the queries that are waiting.
An example might be this
SELECT datname, usename, wait_event_type, wait_event, backend_type, query
FROM pg_stat_activity
WHERE wait_event_type IS NOT NULL
AND wait_event_type NOT IN ('Activity', 'Client');
For more information refer to: https://www.postgresql.org/docs/current/monitoring-stats.html