top of page
  • Writer's picturekyle Hailey

Postgres : monitoring active users in Dbeaver

Updated: Jun 24


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 1

NOTE 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)







165 views0 comments
bottom of page