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 ;