Resource Queue activity and status check using gp_toolkit schema views

Post date: Jan 21, 2014 3:14:16 PM

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.