pg_stat_resqueues

posted Apr 28, 2017, 12:02 PM by Sachchida Ojha
The pg_stat_resqueues view allows administrators to view metrics about a resource queue's workload over time. To allow statistics to be collected for this view, you must enable the stats_queue_level server configuration parameter on the Greenplum Database master instance. Enabling the collection of these metrics does incur a small performance penalty, as each statement submitted through a resource queue must be logged in the system catalog tables.

sachi=# select * from pg_catalog.pg_stat_resqueues;
 queueid | queuename  | n_queries_exec | n_queries_wait | elapsed_exec | elapsed_wait 
---------+------------+----------------+----------------+--------------+--------------
   16995 | perf_test  |              0 |              0 |            0 |            0
    6055 | pg_default |              0 |              0 |            0 |            0
   33478 | myque1     |              0 |              0 |            0 |            0
   33481 | myque2     |              0 |              0 |            0 |            0
   33484 | myque3     |              0 |              0 |            0 |            0
   52269 | highrq     |              0 |              0 |            0 |            0
   52275 | cdr_test   |              0 |              0 |            0 |            0
(7 rows)

sachi=# 
pg_catalog.pg_stat_resqueues
COLUMNTYPEREFERENCESDESCRIPTION
queueoidoid The OID of the resource queue.
queuenamename The name of the resource queue.
n_queries_execbigint Number of queries submitted for execution from this resource queue.
n_queries_waitbigint Number of queries submitted to this resource queue that had to wait before they could execute.
elapsed_execbigint Total elapsed execution time for statements submitted through this resource queue.
elapsed_waitbigint Total elapsed time that statements submitted through this resource queue had to wait before they were executed.



gp_resqueue_status


The gp_toolkit.gp_resqueue_status 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.

sachi=# select * from gp_toolkit.gp_resqueue_status;
 queueid |  rsqname   | rsqcountlimit | rsqcountvalue | rsqcostlimit | rsqcostvalue | rsqmemorylimit | rsqmemoryvalue | rsqwaiters | rsqholders 
---------+------------+---------------+---------------+--------------+--------------+----------------+----------------+------------+------------
   16995 | perf_test  |             2 |             0 |        1e+08 |            0 |             -1 |              0 |          0 |          0
   33484 | myque3     |            -1 |             0 |        1e+08 |            0 |             -1 |              0 |          0 |          0
   52275 | cdr_test   |            25 |             0 |           -1 |            0 |             -1 |              0 |          0 |          0
   33478 | myque1     |            20 |             0 |           -1 |            0 |             -1 |              0 |          0 |          0
   52269 | highrq     |            20 |             0 |           -1 |            0 |     4.1943e+09 |              0 |          0 |          0
   33481 | myque2     |            20 |             0 |        1e+08 |            0 |             -1 |              0 |          0 |          0
    6055 | pg_default |            20 |             0 |           -1 |            0 |             -1 |              0 |          0 |          0
(7 rows)

gp_toolkit.gp_resqueue_status
COLUMNTYPEREFERENCESDESCRIPTION
queueidoidgp_toolkit.gp_resqueue_ queueidThe ID of the resource queue.
rsqnamenamegp_toolkit.gp_resqueue_ rsqnameThe name of the resource queue.
rsqcountlimitrealgp_toolkit.gp_resqueue_ rsqcountlimitThe active query threshold of the resource queue. A value of -1 means no limit.
rsqcountvaluerealgp_toolkit.gp_resqueue_ rsqcountvalueThe number of active query slots currently being used in the resource queue.
rsqcostlimitrealgp_toolkit.gp_resqueue_ rsqcostlimitThe query cost threshold of the resource queue. A value of -1 means no limit.
rsqcostvaluerealgp_toolkit.gp_resqueue_ rsqcostvalueThe total cost of all statements currently in the resource queue.
rsqmemorylimitrealgp_toolkit.gp_resqueue_ rsqmemorylimitThe memory limit for the resource queue.
rsqmemoryvaluerealgp_toolkit.gp_resqueue_ rsqmemoryvalueThe total memory used by all statements currently in the resource queue.
rsqwaitersintegergp_toolkit.gp_resqueue_ rsqwaiterThe number of statements currently waiting in the resource queue.
rsqholdersintegergp_toolkit.gp_resqueue_ rsqholdersThe number of statements currently running on the system from this resource queue.
Comments