The core of performance monitoring on Postgres is pg_stat_activity.
While tools like RDS Performance Insights, Datadog DBM or GCP Query Insights (which is broken last I checked) have graphs of # of queries active and their wait states, there are times where I need faster more detailed information on what is happening now, so I run the following query in DBeaver:
select
COALESCE(wait_event,'cpu'),
round(EXTRACT(EPOCH FROM (
clock_timestamp() - query_start))::numeric, 5) as query_age ,
round(EXTRACT(EPOCH FROM
(clock_timestamp() - xact_start))::numeric, 5) as xact_age,
pid::text,
pg_blocking_pids(PID) as blocker,
substr(query , 1, 80)
, *
from pg_stat_activity a
where
state='active'
and pid != pg_backend_pid()
--and pid=xxxx
order by 2 desc
--\watch 1NOTE 1: if running it in psql, uncomment "--\watch 1" and it will run the query in a loop every second, which is pretty nice. I don't know how to do that in Dbeaver, anyone know?
NOTE 2: I leave "--and pid=xxxx" it the query so I can quickly uncomment and add a PID if I'm tracking one particular session.
The output looks like
I'm often interested in how long a query has been running or how long the transaction is open.
I've had to do a lot of partition operations lately and dropping a partition causes waits on all currently running queries on the table and all subsequent queries wait on my drop or alter, thus the lesser of two evils is to kill all the sessions blocking the partition DDL. There are generally less than have a dozen blockers where as there are immediately 100s of waiters who all timeout in seconds. I can look a the blocking sessions with:
select
round(EXTRACT(EPOCH FROM (clock_timestamp() - query_start))::numeric, 5) as query_age ,
round(EXTRACT(EPOCH FROM (clock_timestamp() - xact_start))::numeric, 5) as xact_age,
pid,
pg_blocking_pids(PID),
wait_event,
substr(query , 1, 80)
query ,
*
--, pg_terminate_backend(PID)
from pg_stat_activity a,
(select unnest( string_to_array(replace(replace(pg_blocking_pids(PID)::text,'{',''),'}',''), ',')) as bpids,
lower(query)
from pg_stat_activity
where lower(query) like '%drop%' or lower(query) like '%alter%'
) b
where
PID = b.bpids::integer ;If I want to kill all those blockers I uncomment "--, pg_terminate_backend(PID)"
YUGABYTE
For more reading checkou yugabyte blog a : https://dev.to/yugabyte/quick-on-active-sql-from-pgstatactivity-27hi
where they give the function:
drop table if exists yb_ash_plans;
drop function if exists yb_ash_sample;
create table if not exists yb_ash_plans(
plan_hash_value text primary key,
samples int, running_for interval,last_plan text,last_pg_stat_activity jsonb
);
create or replace function yb_ash_sample() returns int as
$$
declare
psa record;
plan_xml text;
plan_nofilter text;
plan_md5 text;
last_running_for interval;
count_updated int:=0;
begin
for psa in (select * from pg_stat_activity
where state='active' and pid!=pg_backend_pid()) loop
begin
--raise notice 'SQL %',psa.query;
execute format('explain (format xml, costs false, buffers false) %s',psa.query) into plan_xml;
exception when others then
if sqlstate not like '42%' then raise warning '% % %',sqlstate,sqlerrm,psa.query; end if;
end;
end loop;
if plan_xml is not null then
--raise notice 'XML %',plan_xml;
plan_nofilter:=regexp_replace(plan_xml,'(<Item>|<Filter>|<Index-Cond>|<Recheck-Cond>|<Join-Filter>|<Merge-Cond>|<Hash-Cond>)[^<]*(</)','\1\2','g');
plan_md5=md5(plan_nofilter);
--raise notice e'PHV %\n%\n%',plan_md5,plan_xml,plan_nofilter;
update yb_ash_plans set samples=samples+1 where plan_hash_value=plan_md5 returning running_for into last_running_for;
--raise notice e'LRF % <> %',last_running_for,clock_timestamp()-psa.query_start;
if last_running_for is null then
insert into yb_ash_plans(plan_hash_value,samples,running_for,last_plan,last_pg_stat_activity)
values(plan_md5,1,clock_timestamp()-psa.query_start,plan_xml,row_to_json(psa));
count_updated:=count_updated+1;
else
if last_running_for < clock_timestamp()-psa.query_start then
update yb_ash_plans
set running_for=clock_timestamp()-psa.query_start,
last_plan=plan_xml, last_pg_stat_activity=row_to_json(psa)
where plan_hash_value=plan_md5;
count_updated:=count_updated+1;
end if;
end if;
end if;
return count_updated;
end;
$$ language plpgsql;looping
select yb_ash_sample();
\watch 1
create or replace procedure yb_ash_loop(seconds int default 1)
as $$
declare n int;
begin loop select yb_ash_sample(), pg_sleep(seconds) into n;
raise notice '% Updated statements: %',clock_timestamp(),n;
commit;
end loop;
end;
$$ language plpgsql;
call yb_ash_loop(1);Example:
yugabyte=> select samples,running_for,last_pg_stat_activity->>'query' query
from yb_ash_plans
order by samples desc limit 10;
samples | running_for | query
---------+-----------------+----------------------------------------------------------------------------
54 | 00:00:59.299518 | select pg_sleep(60);
20 | 00:00:00.066935 | select count(*) from pgbench_history ;
1 | 00:00:00.087005 | UPDATE pgbench_accounts SET abalance = abalance + -2743 WHERE aid = 42216;
1 | 00:00:00.014626 | SELECT abalance FROM pgbench_accounts WHERE aid = 23344;
1 | 00:00:00.01092 | UPDATE pgbench_branches SET bbalance = bbalance + -4999 WHERE bid = 1;
(5 rows)
Comments