Greenplum database resource queues - way to limit available resources to database users

posted Apr 28, 2017, 12:02 PM by Sachchida Ojha
1. List all resource type that can be configured in a greenplum resource queues
2. See resource queue parameter settings
3. Resource queue catalog tables and views
4. Create new RQ or update existing RQ settings


There are 6 tables/views that can be used to get most of the information about resource queue. 4 of them are in pg_catalog schema and 1 is in gp_toolkitt schema.
  • pg_catalog.pg_resourcetype
  • pg_catalog.pg_resqueue
  • pg_catalog.pg_resqueue_attributes
  • pg_catalog.pg_resqueuecapability
  • pg_catalog.pg_stat_resqueues
  • gp_toolkit.gp_resqueue_status
Functions.

  • pg_catalog.pg_stat_get_queue_elapsed_exec | bigint | oid                 | normal
  • pg_catalog.pg_stat_get_queue_elapsed_wait | bigint | oid                 | normal
  • pg_catalog.pg_stat_get_queue_num_exec     | bigint | oid                 | normal
  • pg_catalog.pg_stat_get_queue_num_wait     | bigint  | oid                 | normal

sachi=# select pg_resqueue.oid, rsqname from pg_catalog.pg_resqueue;
  oid  |  rsqname   
-------+------------
 16995 | perf_test
  6055 | pg_default
 33478 | myque1
 33481 | myque2
 33484 | myque3
 52269 | highrq
 52275 | cdr_test
(7 rows)


1. pg_catalog.pg_resourcetype

sachi=# \d pg_catalog.pg_resourcetype
Table "pg_catalog.pg_resourcetype"
Column | Type | Modifiers 
--------------------+----------+-----------
resname | name | not null
restypid | smallint | not null
resrequired | boolean | not null
reshasdefault | boolean | not null
reshasdisable | boolean | not null
resdefaultsetting | text | 
resdisabledsetting | text | 
Indexes:
"pg_resourcetype_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
"pg_resourcetype_resname_index" UNIQUE, btree (resname), tablespace "pg_global"
"pg_resourcetype_restypid_index" UNIQUE, btree (restypid), tablespace "pg_global"

Tablespace: "pg_global"

sachi=# select * from pg_catalog.pg_resourcetype;
      resname          | restypid | resrequired | reshasdefault | reshasdisable | resdefaultsetting | resdisabledsetting 
-------------------+----------+-------------+---------------+---------------+-------------------+--------------------
 active_statements |        1    | f               | t                    | t                    | -1                      | -1
 max_cost             |        2    | f               | t                    | t                    | -1                      | -1
 min_cost              |        3    | f               | t                   | t                    | -1                       | 0
 cost_overcommit   |        4    | f              | t                    | t                    | -1                       | -1
 priority                  |        5    | f              | t                    | f                    | medium               | 
 memory_limit        |        6    | f              | t                   | t                     | -1                          | -1


pg_catalog.pg_resourcetype
COLUMNTYPEREFERENCESDESCRIPTION
restypidsmallint The resource type ID.
resnamename The name of the resource type.
resrequiredboolean Whether the resource type is required for a valid resource queue.
reshasdefaultboolean Whether the resource type has a default value. When true, the default value is specified in reshasdefaultsetting.
rescandisableboolean Whether the type can be removed or disabled. When true, the default value is specified in resdisabledsetting.
resdefaultsettingtext Default setting for the resource type, when applicable.
resdisabledsettingtext The value that disables this resource type (when allowed).

2. pg_catalog.pg_resqueue

sachi=# \d pg_catalog.pg_resqueue
      Table "pg_catalog.pg_resqueue"
       Column       |  Type   | Modifiers 
--------------------+---------+-----------
 rsqname            | name    | not null
 rsqcountlimit      | real    | not null
 rsqcostlimit       | real    | not null
 rsqovercommit      | boolean | not null
 rsqignorecostlimit | real    | not null
Indexes:
    "pg_resqueue_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_resqueue_rsqname_index" UNIQUE, btree (rsqname), tablespace "pg_global"

Tablespace: "pg_global"

sachi=# select * from pg_catalog.pg_resqueue;
  rsqname   | rsqcountlimit | rsqcostlimit | rsqovercommit | rsqignorecostlimit 
------------+---------------+--------------+---------------+--------------------
 perf_test  |             2 |        1e+08          | t             |             100000
 pg_default |            20 |           -1           | f             |                  0
 myque1     |            20 |           -1          | f             |                  0
 myque2     |            20 |        1e+08      | f             |             100000
 myque3     |            -1 |        1e+08      | f             |             100000
 highrq     |            20 |           -1            | f             |                  0
 cdr_test   |            25 |           -1           | t             |             100000
(7 rows)

pg_catalog.pg_resqueue column description
COLUMNTYPEREFERENCESDESCRIPTION
rsqnamename The name of the resource queue.
rsqcountlimitreal The active query threshold of the resource queue.
rsqcostlimitreal The query cost threshold of the resource queue.
rsqovercommitboolean Allows queries that exceed the cost threshold to run when the system is idle.
rsqignorecostlimitreal The query cost limit of what is considered a 'small query'. Queries with a cost under this limit will not be queued and run immediately.


3. pg_catalog.pg_resqueue_attributes

sachi=# \d pg_catalog.pg_resqueue_attributes
View "pg_catalog.pg_resqueue_attributes"
   Column   |  Type   | Modifiers 
------------+---------+-----------
 rsqname    | name    | 
 resname    | text    | 
 ressetting | text    | 
 restypid   | integer | 
View definition:
((( SELECT pg_resqueue.rsqname, 'active_statements' AS resname, pg_resqueue.rsqcountlimit::text AS ressetting, 1 AS restypid
   FROM pg_resqueue
UNION 
 SELECT pg_resqueue.rsqname, 'max_cost' AS resname, pg_resqueue.rsqcostlimit::text AS ressetting, 2 AS restypid
   FROM pg_resqueue)
UNION 
 SELECT pg_resqueue.rsqname, 'cost_overcommit' AS resname, 
        CASE
            WHEN pg_resqueue.rsqovercommit THEN '1'::text
            ELSE '0'::text
        END AS ressetting, 4 AS restypid
   FROM pg_resqueue)
UNION 
 SELECT pg_resqueue.rsqname, 'min_cost' AS resname, pg_resqueue.rsqignorecostlimit::text AS ressetting, 3 AS restypid
   FROM pg_resqueue)
UNION 
 SELECT rq.rsqname, rt.resname, rc.ressetting, rt.restypid
   FROM pg_resqueue rq, pg_resourcetype rt, pg_resqueuecapability rc
  WHERE rq.oid = rc.resqueueid AND rc.restypid = rt.restypid
  ORDER BY 1, 4;


sachi=# select * from pg_catalog.pg_resqueue_attributes;
  rsqname   |      resname      | ressetting | restypid 
------------+-------------------+------------+----------
 cdr_test   | active_statements | 25         |        1
 cdr_test   | max_cost          | -1         |        2
 cdr_test   | min_cost          | 100000     |        3
 cdr_test   | cost_overcommit   | 1          |        4
 cdr_test   | priority          | medium     |        5
 cdr_test   | memory_limit      | -1         |        6
 highrq     | active_statements | 20         |        1
 highrq     | max_cost          | -1         |        2
 highrq     | min_cost          | 0          |        3
 highrq     | cost_overcommit   | 0          |        4
 highrq     | priority          | medium     |        5
 highrq     | memory_limit      | 4000MB     |        6
 myque1     | active_statements | 20         |        1
 myque1     | max_cost          | -1         |        2
 myque1     | min_cost          | 0          |        3
 myque1     | cost_overcommit   | 0          |        4
 myque1     | priority          | medium     |        5
 myque1     | memory_limit      | -1         |        6
 myque2     | active_statements | 20         |        1
 myque2     | max_cost          | 1e+08      |        2
 myque2     | min_cost          | 100000     |        3
 myque2     | cost_overcommit   | 0          |        4
 myque2     | priority          | medium     |        5
 myque2     | memory_limit      | -1         |        6
 myque3     | active_statements | -1         |        1
 myque3     | max_cost          | 1e+08      |        2
 myque3     | min_cost          | 100000     |        3
 myque3     | cost_overcommit   | 0          |        4
 myque3     | priority          | medium     |        5
 myque3     | memory_limit      | -1         |        6
 perf_test  | active_statements | 2          |        1
 perf_test  | max_cost          | 1e+08      |        2
 perf_test  | min_cost          | 100000     |        3
 perf_test  | cost_overcommit   | 1          |        4
 perf_test  | priority          | medium     |        5
 perf_test  | memory_limit      | -1         |        6
 pg_default | active_statements | 20         |        1
 pg_default | max_cost          | -1         |        2
 pg_default | min_cost          | 0          |        3
 pg_default | cost_overcommit   | 0          |        4
 pg_default | priority          | medium     |        5
 pg_default | memory_limit      | -1         |        6
(42 rows)

Here restypid represents System assigned resource type id. Other fields are self explanatory. 


4. pg_catalog.pg_resqueuecapability

sachi-# \d pg_catalog.pg_resqueuecapability
Table "pg_catalog.pg_resqueuecapability"
   Column   |   Type   | Modifiers 
------------+----------+-----------
 resqueueid | oid      | not null
 restypid   | smallint | not null
 ressetting | text     | 
Indexes:
    "pg_resqueuecapability_oid_index" UNIQUE, btree (oid), tablespace "pg_global"
    "pg_resqueuecapability_resqueueid_index" btree (resqueueid), tablespace "pg_global"
    "pg_resqueuecapability_restypid_index" btree (restypid), tablespace "pg_global"

Tablespace: "pg_global"

sachi-# 

sachi=# select * from pg_catalog.pg_resqueuecapability;
 resqueueid | restypid | ressetting 
------------+----------+------------
       6055 |        5 | medium
       6055 |        6 | -1
      16995 |        5 | medium
      16995 |        6 | -1
      33478 |        5 | medium
      33478 |        6 | -1
      33481 |        5 | medium
      33481 |        6 | -1
      33484 |        5 | medium
      33484 |        6 | -1
      52269 |        6 | 4000MB
      52269 |        5 | medium
      52275 |        5 | medium
      52275 |        6 | -1
(14 rows)

sachi=# 

pg_catalog.pg_resqueuecapability
COLUMNTYPEREFERENCESDESCRIPTION
rsqueueidoidpg_resqueue.oidThe object ID of the associated resource queue.
restypidsmallintpg_resourcetype. restypeidThe resource type, derived from the pg_resqueuecapability system table.
ressettingopaque type The specific value set for the capability referenced in this record. Depending on the actual resource type, this value may have different data types.


sachi=# select pg_resqueue.oid, rsqname from pg_catalog.pg_resqueue;
  oid  |  rsqname   
-------+------------
 16995 | perf_test
  6055 | pg_default
 33478 | myque1
 33481 | myque2
 33484 | myque3
 52269 | highrq
 52275 | cdr_test
(7 rows)

5. pg_catalog.pg_stat_resqueues

sachi=# \d pg_catalog.pg_stat_resqueues
 View "pg_catalog.pg_stat_resqueues"
     Column     |  Type  | Modifiers 
----------------+--------+-----------
 queueid        | oid    | 
 queuename      | name   | 
 n_queries_exec | bigint | 
 n_queries_wait | bigint | 
 elapsed_exec   | bigint | 
 elapsed_wait   | bigint | 
View definition:
SELECT q.oid AS queueid, q.rsqname AS queuename, pg_stat_get_queue_num_exec(q.oid) AS n_queries_execpg_stat_get_queue_num_wait(q.oid) AS n_queries_waitpg_stat_get_queue_elapsed_exec(q.oid) AS elapsed_execpg_stat_get_queue_elapsed_wait(q.oid) AS elapsed_wait
 FROM pg_resqueue q;

sachi=# \df pg_catalog.pg_stat_get_queue*
                                       List of functions
   Schema   |              Name              | Result data type | Argument data types |  Type  
------------+--------------------------------+------------------+---------------------+--------
 pg_catalog | pg_stat_get_queue_elapsed_exec | bigint           | oid                 | normal
 pg_catalog | pg_stat_get_queue_elapsed_wait | bigint           | oid                 | normal
 pg_catalog | pg_stat_get_queue_num_exec     | bigint           | oid                 | normal
 pg_catalog | pg_stat_get_queue_num_wait     | bigint           | oid                 | normal
(4 rows)

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)

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.

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.

6. gp_toolkit.gp_resqueue_status

sachi=# \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;


sachi=# \df pg_resqueue_status_kv()
                                  List of functions
   Schema   |         Name          | Result data type | Argument data types |  Type  
------------+-----------------------+------------------+---------------------+--------
 pg_catalog | pg_resqueue_status_kv | SETOF record     |                     | normal
(1 row)

sachi=# \df+ pg_resqueue_status_kv()
                                                                                 List of functions
   Schema   |         Name          | Result data type | Argument data types |  Type  | Volatility |  Owner  | Language |      Source code      |            
Description            
------------+-----------------------+------------------+---------------------+--------+------------+---------+----------+-----------------------+------------
-----------------------
 pg_catalog | pg_resqueue_status_kv | SETOF record     |                     | normal | volatile   | gpadmin | internal | pg_resqueue_status_kv | Return reso
urce queue information
(1 row)

sachi=# 


sachi=# select pg_resqueue_status_kv();
        pg_resqueue_status_kv         
--------------------------------------
 (16995,rsqcountlimit,2)
 (16995,rsqcountvalue,0)
 (16995,rsqcostlimit,100000000.00)
 (16995,rsqcostvalue,0.00)
 (16995,rsqmemorylimit,-1.00)
 (16995,rsqmemoryvalue,0.00)
 (16995,rsqwaiters,0)
 (16995,rsqholders,0)
 (33484,rsqcountlimit,-1)
 (33484,rsqcountvalue,0)
 (33484,rsqcostlimit,100000000.00)
 (33484,rsqcostvalue,0.00)
 (33484,rsqmemorylimit,-1.00)
 (33484,rsqmemoryvalue,0.00)
 (33484,rsqwaiters,0)
 (33484,rsqholders,0)
 (52275,rsqcountlimit,25)
 (52275,rsqcountvalue,0)
 (52275,rsqcostlimit,-1.00)
 (52275,rsqcostvalue,0.00)
 (52275,rsqmemorylimit,-1.00)
 (52275,rsqmemoryvalue,0.00)
 (52275,rsqwaiters,0)
 (52275,rsqholders,0)
 (33478,rsqcountlimit,20)
 (33478,rsqcountvalue,0)
 (33478,rsqcostlimit,-1.00)
 (33478,rsqcostvalue,0.00)
 (33478,rsqmemorylimit,-1.00)
 (33478,rsqmemoryvalue,0.00)
 (33478,rsqwaiters,0)
 (33478,rsqholders,0)
 (52269,rsqcountlimit,20)
 (52269,rsqcountvalue,0)
 (52269,rsqcostlimit,-1.00)
 (52269,rsqcostvalue,0.00)
 (52269,rsqmemorylimit,4194304000.00)
 (52269,rsqmemoryvalue,0.00)
 (52269,rsqwaiters,0)
 (52269,rsqholders,0)
 (33481,rsqcountlimit,20)
 (33481,rsqcountvalue,0)
 (33481,rsqcostlimit,100000000.00)
 (33481,rsqcostvalue,0.00)
 (33481,rsqmemorylimit,-1.00)
 (33481,rsqmemoryvalue,0.00)
 (33481,rsqwaiters,0)
 (33481,rsqholders,0)
 (6055,rsqcountlimit,20)
 (6055,rsqcountvalue,0)
 (6055,rsqcostlimit,-1.00)
 (6055,rsqcostvalue,0.00)
 (6055,rsqmemorylimit,-1.00)
 (6055,rsqmemoryvalue,0.00)
 (6055,rsqwaiters,0)
 (6055,rsqholders,0)


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)

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