Resource Queue activity and status check using gp_toolkit schema views

posted Apr 28, 2017, 3:41 PM by Sachchida Ojha
All database users are assigned to a resource queue, and every statement submitted by a user is first evaluated against the resource queue limits before it can run.

The purpose of resource queues is to limit the number of active queries in the system at any given time in order to avoid exhausting system resources such as memory, CPU, and disk I/O.

Following gp_toolkit schema views can be used to check the status of the resource queue activity.

1. gp_resq_activity
2. gp_resq_activity_by_queue
3. gp_resq_priority_statement
4. gp_resq_role
5. gp_resqueue_status

1. gp_resq_activity

gpadmin=# \d gp_toolkit.gp_resq_activity
View "gp_toolkit.gp_resq_activity"
Column | Type | Modifiers 
-------------+--------------------------+-----------
resqprocpid | integer | 
resqrole | name | 
resqoid | oid | 
resqname | name | 
resqstart | timestamp with time zone | 
resqstatus | text | 
View definition:
SELECT psa.procpid AS resqprocpid, psa.usename AS resqrole, resq.resqoid, resq.rsqname AS resqname, psa.query_start AS resqstart, 
CASE
WHEN resq.resqgranted = false THEN 'waiting'::text
ELSE 'running'::text
END AS resqstatus
FROM pg_stat_activity psa
JOIN ( SELECT pgrq.oid AS resqoid, pgrq.rsqname, pgl.pid AS resqprocid, pgl.granted AS resqgranted
FROM pg_resqueue pgrq, pg_locks pgl
WHERE pgl.objid = pgrq.oid) resq ON resq.resqprocid = psa.procpid
WHERE psa.current_query <> '<IDLE>'::text
ORDER BY psa.query_start;

For the resource queues that have active workload, this view shows one row for each active statement submitted through a resource queue. This view is accessible to all users.

resqprocpid=>Process ID assigned to this statement (on the master).
resqrole=>User name.
resqoid=>Resource queue object id.
resqname=>Resource queue name.
resqstart=>Time statement was issued to the system.
resqstatus=>Status of statement: running, waiting or cancelled.

2. gp_resq_activity_by_queue

gpadmin=# \d gp_toolkit.gp_resq_activity_by_queue
View "gp_toolkit.gp_resq_activity_by_queue"
Column | Type | Modifiers 
------------+--------------------------+-----------
resqoid | oid | 
resqname | name | 
resqlast | timestamp with time zone | 
resqstatus | text | 
resqtotal | bigint | 
View definition:
SELECT gp_resq_activity.resqoid, gp_resq_activity.resqname, max(gp_resq_activity.resqstart) AS resqlast, gp_resq_activity.resqstatus, count(*) AS resqtotal
FROM gp_toolkit.gp_resq_activity
GROUP BY gp_resq_activity.resqoid, gp_resq_activity.resqname, gp_resq_activity.resqstatus
ORDER BY gp_resq_activity.resqoid, max(gp_resq_activity.resqstart);

For the resource queues that have active workload, this view shows a summary of queue activity. This view is accessible to all users.

resqoid=>Resource queue object id.
resqname=>Resource queue name.
resqlast=>Time of the last statement issued to the queue.
resqstatus=>Status of last statement: running, waiting or cancelled.
resqtotal=>Total statements in this queue.


3. gp_resq_priority_statement

gpadmin=# \d gp_toolkit.gp_resq_priority_statement
View "gp_toolkit.gp_resq_priority_statement"
Column | Type | Modifiers 
-------------+---------+-----------
rqpdatname | name | 
rqpusename | name | 
rqpsession | integer | 
rqpcommand | integer | 
rqppriority | text | 
rqpweight | integer | 
rqpquery | text | 
View definition:
SELECT psa.datname AS rqpdatname, psa.usename AS rqpusename, rpb.rqpsession, rpb.rqpcommand, rpb.rqppriority, rpb.rqpweight, psa.current_query AS rqpquery
FROM gp_toolkit.gp_resq_priority_backend rpb
JOIN pg_stat_activity psa ON rpb.rqpsession = psa.sess_id
WHERE psa.current_query <> '<IDLE>'::text;

This view shows the resource queue priority, session ID, and other information for all statements currently running in the Greenplum Database system. This view is accessible to all users.

rqpdatname=>The database name that the session is connected to.
rqpusename=>The user who issued the statement.
rqpsession=>The session ID.
rqpcommand=>The number of the statement within this session (the command id and session id uniquely identify a statement).
rqppriority=>The resource queue priority for this statement (MAX, HIGH, MEDIUM, LOW).
rqpweight=>An integer value associated with the priority of this statement.
rqpquery=>The query text of the statement.

4. gp_resq_role

gpadmin=# \d gp_toolkit.gp_resq_role
View "gp_toolkit.gp_resq_role"
Column | Type | Modifiers 
-----------+------+-----------
rrrolname | name | 
rrrsqname | name | 
View definition:
SELECT pgr.rolname AS rrrolname, pgrq.rsqname AS rrrsqname
FROM pg_roles pgr
LEFT JOIN pg_resqueue pgrq ON pgr.rolresqueue = pgrq.oid;

This view shows the resource queues associated with a role. This view is accessible to all users.

rrrolname=>Role (user) name.
rrrsqname=>The resource queue name assigned to this role. If a role has not been explicitly assigned to a resource queue, it will be in the default resource queue (pg_default).


5. gp_resqueue_status

gpadmin=# \d gp_toolkit.gp_resqueue_status
View "gp_toolkit.gp_resqueue_status"
Column | Type | Modifiers 
----------------+---------+-----------
queueid | oid | 
rsqname | name | 
rsqcountlimit | integer | 
rsqcountvalue | integer | 
rsqcostlimit | real | 
rsqcostvalue | real | 
rsqmemorylimit | real | 
rsqmemoryvalue | real | 
rsqwaiters | integer | 
rsqholders | integer | 
View definition:
SELECT q.oid AS queueid, q.rsqname, t1.value::integer AS rsqcountlimit, t2.value::integer AS rsqcountvalue, t3.value::real AS rsqcostlimit, t4.value::real AS rsqcostvalue, t5.value::real AS rsqmemorylimit, t6.value::real AS rsqmemoryvalue, t7.value::integer AS rsqwaiters, t8.value::integer AS rsqholders
FROM pg_resqueue q, pg_resqueue_status_kv() t1(queueid oid, key text, value text), pg_resqueue_status_kv() t2(queueid oid, key text, value text), pg_resqueue_status_kv() t3(queueid oid, key text, value text), pg_resqueue_status_kv() t4(queueid oid, key text, value text), pg_resqueue_status_kv() t5(queueid oid, key text, value text), pg_resqueue_status_kv() t6(queueid oid, key text, value text), pg_resqueue_status_kv() t7(queueid oid, key text, value text), pg_resqueue_status_kv() t8(queueid oid, key text, value text)
WHERE q.oid = t1.queueid AND t1.queueid = t2.queueid AND t2.queueid = t3.queueid AND t3.queueid = t4.queueid AND t4.queueid = t5.queueid AND t5.queueid = t6.queueid AND t6.queueid = t7.queueid AND t7.queueid = t8.queueid AND t1.key = 'rsqcountlimit'::text AND t2.key = 'rsqcountvalue'::text AND t3.key = 'rsqcostlimit'::text AND t4.key = 'rsqcostvalue'::text AND t5.key = 'rsqmemorylimit'::text AND t6.key = 'rsqmemoryvalue'::text AND t7.key = 'rsqwaiters'::text AND t8.key = 'rsqholders'::text;

This view allows administrators to see status and activity for a workload management resource queue. It shows how many queries are waiting to run and how many queries are currently active in the system from a particular resource queue.

queueid=>The ID of the resource queue.
rsqname=>The name of the resource queue.
rsqcountlimit=>The active query threshold of the resource queue. A value of -1 means no limit.
rsqcountvalue=>The number of active query slots currently being used in the resource queue.
rsqcostlimit=>The query cost threshold of the resource queue. A value of -1 means no limit.
rsqcostvalue=>The total cost of all statements currently in the resource queue.
rsqmemorylimit=>The memory limit for the resource queue.
rsqmemoryvalue=>The total memory used by all statements currently in the resource queue.
rsqwaiters=>The number of statements currently waiting in the resource queue.
rsqholders=>The number of statements currently running on the system from this resource queue.
Comments