Help with Slow Query

[dfoley84]

Summary

A slow running query alert was triggered by pgwatch3 for one of our monitored databases, indicating a task running for 15 minutes. However, the metrics for average query runtime and longest running query over the same period do not show any anomalies (0.7ms avg, 0s max for 6 hours).

The Alert Query using Postgres Source.

The Query is taken from the Stat Activity Dashboard from pgwatch

SELECT 
  dbname,
  (data->>'query') as running_query,
  (data->>'duration_s')::int as duration_s
FROM stat_activity_realtime
WHERE
  dbname IN ('', '')
  AND time = (
    SELECT max(time)
    FROM stat_activity_realtime
    WHERE dbname IN ('', '')
      AND time >= NOW() - INTERVAL '1 minutes'
  )
  AND (data->>'duration_s')::int > 800
  AND NOT (
    data->>'ip' = 'local' OR data->>'ip' IS NULL
  );

Also, When i switch from Absolute time range to last 24hours, that query doesn't reappear.

How Every the pgwatch Dashboard for Health Checks is shown 0s for longest Query
max(max_over_time(pgwatch_backends_longest_query_seconds{dbname='$dbname'}[$__interval]))

shown 0.7ms Avg
Query runtime (avg.)

select
$__timeGroup(time, $online_interval),
avg(case when c = c_lag then 0 else (tt-tt_lag)::numeric / (c-c_lag) end) as "avg_query_runtime"
from (

        select 
          (data->>'total_time')::float8 as tt, lag((data->>'total_time')::float8) over w as tt_lag,
          (data->>'calls')::int8 as c, lag((data->>'calls')::int8) over w as c_lag,
          time
        from stat_statements_calls
        where dbname = '$dbname' and $__timeFilter(time)
        window w as (order by time)
) x
where c >= c_lag and tt >= tt_lag
group by 1
order by 1 desc
limit 1

[pashagolub]

Sometimes lags just happen. And the usual advice in these situations is to continue monitoring.

[dfoley84]

Thanks,,

Is the Query ok, or do you recommend a better approach, on alert rules for a long running query

SELECT 
  dbname,
  (data->>'query') as running_query,
  (data->>'duration_s')::int as duration_s
FROM stat_activity_realtime
WHERE
  dbname IN ('', '')
  AND time = (
    SELECT max(time)
    FROM stat_activity_realtime
    WHERE dbname IN ('', '')
      AND time >= NOW() - INTERVAL '1 minutes'
  )
  AND (data->>'duration_s')::int > 800
  AND NOT (
    data->>'ip' = 'local' OR data->>'ip' IS NULL
  );

[pashagolub]

Sorry, I cannot tell you. First of all, all realtime metrics will be deprecated in v4. And they were broken in v3 for a long time. That makes me think you're using pgwatch v2 which is outdated and not supported officially.

[dfoley84]

using pgwatch v3

FROM cybertecpostgresql/pgwatch:3

COPY Configurations/pgwatch/sources/ /config/pgwatch/sources/
COPY Configurations/pgwatch/metrics.yaml /config/pgwatch/metrics/
COPY Configurations/pgwatch/setup.sh /tmp/
RUN chmod +x /tmp/setup.sh
RUN chmod 777 /tmp/ 

EXPOSE 8080 9187
ENTRYPOINT [ "/bin/sh", "/tmp/setup.sh" ]