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.
| 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.
| 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.
| 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.
| 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.
| 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. |