I love pg_stats_activity. It'sthe heart of all my postgres performance monitoring. It's about the only place I go. But one thing that had been super frustrating, especially when the house is burning down, is that when I saw blockers mucking up the system, I didn't know what they were doing and/or if I could kill them. So newest modification is to include them in the output even if they are "idle".
Output looks like:
script
select
case when b.bpids::integer = a.pid
then a.pid
else null end as blocker,
COALESCE(wait_event,'cpu'),
COALESCE(round(EXTRACT(EPOCH FROM (clock_timestamp() -
query_start))::numeric, 5), -1) as query_age ,
CASE when lower(substr(query , 1, 5)) = 'alter'
then 999999
else
COALESCE(round(EXTRACT(EPOCH FROM (clock_timestamp() -
xact_start ))::numeric, 5), -1) end as xact_age,
pid::text,
pg_blocking_pids(PID)::text as blocker,
state,
substr(query , 1, 80)
-- , query
-- , *
from pg_stat_activity a,
(SELECT COALESCE(
(SELECT unnest(string_to_array(replace(replace(pg_blocking_pids(PID)::text, '{', ''), '}', ''), ','))::int
FROM pg_stat_activity ), -1 ) bpids
) b
where
( state='active'
or pid=3379309
or PID = b.bpids::integer
)
--and
-- pid != pg_backend_pid()
-- pid=3379309
-- pid in (3533517,3651820,3651819)
order by 3 desc, 5 , 2 desc
-- \watch 1
;
I live the comments in because these I things I often modify on the fly.
Like restricting it to one PID or making sure to include a specific PID
Commentaires