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