Skip to content

postgres monitoring metrics

postgres monitoring metrics published on 2 комментария к записи postgres monitoring metrics
postgres=#
SELECT COUNT(1) AS session_count,
  SUM(  CASE    WHEN state = 'active'    THEN 1    ELSE 0  END) AS active,
  SUM(  CASE    WHEN state LIKE 'idle in%'    THEN 1    ELSE 0  END) AS idle_in_transaction,
  SUM(  CASE    WHEN now()-query_start > INTERVAL '1s'    AND state  = 'active'    THEN 1    ELSE 0  END) AS slow_query_1s,
  SUM(  CASE    WHEN now()-query_start > INTERVAL '600s'    AND state = 'active'    THEN 1    ELSE 0 END)              AS slow_query_600s,
  SUM(waiting::INT) AS waiting_query,
  CASE pg_is_in_recovery()    WHEN true    THEN 1    ELSE 0  END AS is_in_recovery,
  CASE pg_is_in_recovery()    WHEN true    THEN extract(epoch FROM now() - COALESCE(pg_last_xact_replay_timestamp(),now()))    ELSE 0   END AS replication_delay
FROM pg_stat_activity ;
 session_count | active | idle_in_transaction | slow_query_1s | slow_query_600s | waiting_query | is_in_recovery | replication_delay
---------------+--------+---------------------+---------------+-----------------+---------------+----------------+-------------------
           177 |      3 |                   0 |             2 |               1 |             0 |              0 |                 0

2 комментария

SELECT now() as date,COUNT(1) AS session_count,
SUM( CASE WHEN state = ‘active’ THEN 1 ELSE 0 END) AS active,
SUM( CASE WHEN state LIKE ‘idle in%’ THEN 1 ELSE 0 END) AS idle_in_transaction,
SUM( CASE WHEN now()-query_start > INTERVAL ‘1s’ AND state = ‘active’ THEN 1 ELSE 0 END) AS slow_query_1s,
SUM( CASE WHEN now()-query_start > INTERVAL ‘600s’ AND state = ‘active’ THEN 1 ELSE 0 END) AS slow_query_600s,
SUM(waiting::INT) AS waiting_query,
CASE pg_is_in_recovery() WHEN true THEN 1 ELSE 0 END AS is_in_recovery,
CASE pg_is_in_recovery() WHEN false THEN ( select pg_xlog_location_diff(sent_location, replay_location) from pg_stat_replication) else 0 end as byte_lag,
CASE pg_is_in_recovery() WHEN true THEN extract(epoch FROM now() — COALESCE(pg_last_xact_replay_timestamp(),now())) ELSE 0 END AS replication_delay
FROM pg_stat_activity ;

Добавить комментарий

Ваш e-mail не будет опубликован. Обязательные поля помечены *

Primary Sidebar

Яндекс.Метрика