Script for Resource Queue information in Tanzu Greenplum

Products

• VMware Tanzu Greenplum

Issue/Introduction

This article provides a simple script to help an administrator identify Resource queue level information.

Note: Please verify the script on a test cluster before running it on production.

Environment

Product Version: 6.18

Resolution

Resource Queue Information and its attributes

List Resource Queues and their attributes:

select a.rsqname as "RQname", 
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='active_statements') as "ActiveStatment",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='max_cost') as "MaxCost",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='min_cost') as "MinCost",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='cost_overcommit') as "CostOvercommit",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='memory_limit') as "MemoryLimit",
	(select ressetting from pg_resqueue_attributes b 
	 where a.rsqname=b.rsqname and resname='priority') as "Priority",
	(select count(*) from pg_resqueue x,pg_roles y 
	 where x.oid=y.rolresqueue and a.rsqname=x.rsqname) as "RQAssignedUsers"
from ( select distinct rsqname from pg_resqueue_attributes ) a
order by 1;

To see usernames and their associated Resource Queues, run this query:

select rrrsqname as "RQueueName", rrrolname as "Username" from gp_toolkit.gp_resq_role order by 1,2;

Resource Queue Activity

To see current activity of the Resource Queue, run this query:

select 
    resqname as "RQueueName",
    resqlast as "RQueueLastRequest",
    resqstatus as "RQueueStatus",
    resqtotal as "TotalRQUsers"
from
    gp_toolkit.gp_resq_activity_by_queue;

To see the current activity of the Resource Queue w.r.t specific users:

Greenplum 5x:

select 
    resqprocpid as "Pid",
    sess_id as "SessionID",
    resqrole as "Username",
    resqname as "RQueueName",
    resqstart as "RQueueStartTime",
    now() - resqstart as " WaitDuration",
    resqstatus as "RQueueStatus",
    substring(current_query from 1 for 40) as "Query"
from
    gp_toolkit.gp_resq_activity a,pg_stat_activity b
where resqrole='<Username>' and a.resqprocpid=b.procpid ;

Greenplum 6x:

select 
    resqprocpid as "Pid",
    sess_id as "SessionID",
    resqrole as "Username",
    resqname as "RQueueName",
    resqstart as "RQueueStartTime",
    now() - resqstart as " WaitDuration",
    resqstatus as "RQueueStatus",
    substring(query from 1 for 40) as "Query"
from
    gp_toolkit.gp_resq_activity a,pg_stat_activity b
where resqrole='<Username>' and a.resqprocpid=b.pid;

To see the Resource Queue usage status w.r.t to current activity, run this query:

select 
   rsqname as "RQname",
   rsqcountlimit as "RQActiveLimit",
   rsqcountvalue as "RQCurrentActive",
   rsqcostlimit::bigint as "RQCostLimit",
   rsqcostvalue::bigint as "RQCurrentCost",
   rsqmemorylimit::bigint as "RQMemoryLimit",
   rsqmemoryvalue::bigint "RQCurrentMemory",
   rsqholders as "RQHolders",
   rsqwaiters as "RQWaiters"
from gp_toolkit.gp_resqueue_status;

List of Resource queue Priorities

select 
   rqpdatname as "DBname",
   rqpusename as "Username",
   rqpsession as "SessionID",
   rqpcommand as "CommandExec",
   rqppriority as "RQPriority",
   substring(rqpquery from 1 for 40) as "Query"
from gp_toolkit.gp_resq_priority_statement ;

Additional Information

+ Environment:

Pivotal Greenplum Database (GPDB) all versions

Note: Do not run this on Pivotal HAWQ Database (HDB), it will cause a PANIC.