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

Post date: Oct 12, 2014 12:56:32 PM

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

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_exec, pg_stat_get_queue_num_wait(q.oid) AS n_queries_wait, pg_stat_get_queue_elapsed_exec(q.oid) AS elapsed_exec, pg_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

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