Checking Resource Queue Activity and Status

Checking Resource Queue Activity and Status

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. 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 gp_resq_* family of views can be used to check the status of statements currently submitted to the system through their respective resource queue. Note that statements issued by superusers are exempt from resource queuing.

gp_resq_activity

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.

Table 1. gp_resq_activity view
Column Description
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.

gp_resq_activity_by_queue

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

Table 2. gp_resq_activity_by_queue Column
Column Description
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.

gp_resq_priority_statement

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.

Table 3. gp_resq_priority_statement view
Column Description
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.

gp_resq_role

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

Table 4. gp_resq_role view
Column Description
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).

gp_resqueue_status

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.

Table 5. gp_resqueue_status view
Column Description
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.