Pivotal Knowledge Base

Follow

Script - Resource queue information

Environment

  • Pivotal Greenplum
  • Please, don't run this on Pivotal HDB , it will cause a PANIC

Goal

Is to provide a simple scripts to help administrator identify Resource queue level information.

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

Solution

Resource Queue Information and its 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;

List of username and its associated resource queue.

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

Resource Queue Activity.

-- Current activity of the Resource queue.

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

-- Current activity of the Resource queue w.r.t specific users.

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 ;

-- Resource queue usage status w.r.t to current activity.

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 Priority

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 ;

Comments

  • Avatar
    Yadukula Chengappa

    Hi Faisal,

    Thanks for the informatiom. Could you please also explain what needs to be done when we see lot of rsqholders and rsqwaiters resource queue? We have observed huge performance issues when we see lot of rsqholders and rsqwaiters resource queue.

    Regards,
    Yadu

  • Avatar
    Faisal Ali

    Hi Yadu,

    rsqholders refers to the number of users currently holding the RQ's.
    rsqwaiters refers to numbers of users waiting on slots on RQ's due the rsqholders currently holding them

    So based on the above description rsqwaiters are symptoms and they are not the cause , now to check what is causing so many rsqwaiter i.e which part of the RQ's are exhausted use the script

    -- Resource queue usage status w.r.t to current activity.
    
    select 
       rsqname as "RQname",
       rsqcountlimit as "RQActiveLimit",
       rsqcountvalue as "RQCurrentActive",
       rsqcostlimit as "RQCostLimit",
       rsqcostvalue as "RQCurrentCost",
       rsqmemorylimit::integer as "RQMemoryLimit",
       rsqmemoryvalue::integer "RQCurrentMemory",
       rsqholders as "RQHolders",
       rsqwaiters as "RQWaiters"
    from gp_toolkit.gp_resqueue_status;
    
    

    Where

    RQActiveLimit -> is the current or max slots that can be allocated.
    RQCurrentActive > is the current usage of slots
    etc

    once you identify the cause for eg.s if its due to active slots getting all used up , check on what users are under that queue why are they taking so much time , if you need to increase the slot etc

    Let me know if you have any questions.

  • Avatar
    Brendan Stephens

    Note the defect MPP-26078 may cause these statements to return incorrect values between 4.3.5.x (Logged) and 4.3.8.x (Fixed)

Powered by Zendesk