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| COLUMN | TYPE | REFERENCES | DESCRIPTION |
|---|
| restypid | smallint | | The resource type ID. | | resname | name | | The name of the resource type. | | resrequired | boolean | | Whether the resource type is required for a valid resource queue. | | reshasdefault | boolean | | Whether the resource type has a default value. When true, the default value is specified in reshasdefaultsetting. | | rescandisable | boolean | | Whether the type can be removed or disabled. When true, the default value is specified in resdisabledsetting. | | resdefaultsetting | text | | Default setting for the resource type, when applicable. | | resdisabledsetting | text | | 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| COLUMN | TYPE | REFERENCES | DESCRIPTION |
|---|
| rsqname | name | | The name of the resource queue. | | rsqcountlimit | real | | The active query threshold of the resource queue. | | rsqcostlimit | real | | The query cost threshold of the resource queue. | | rsqovercommit | boolean | | Allows queries that exceed the cost threshold to run when the system is idle. | | rsqignorecostlimit | real | | 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| COLUMN | TYPE | REFERENCES | DESCRIPTION |
|---|
| rsqueueid | oid | pg_resqueue.oid | The object ID of the associated resource queue. | | restypid | smallint | pg_resourcetype. restypeid | The resource type, derived from the pg_resqueuecapability system table. | | ressetting | opaque 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_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| COLUMN | TYPE | REFERENCES | DESCRIPTION |
|---|
| queueoid | oid | | The OID of the resource queue. | | queuename | name | | The name of the resource queue. | | n_queries_exec | bigint | | Number of queries submitted for execution from this resource queue. | | n_queries_wait | bigint | | Number of queries submitted to this resource queue that had to wait before they could execute. | | elapsed_exec | bigint | | Total elapsed execution time for statements submitted through this resource queue. | | elapsed_wait | bigint | | 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| COLUMN | TYPE | REFERENCES | DESCRIPTION |
|---|
| queueid | oid | gp_toolkit.gp_resqueue_ queueid | The ID of the resource queue. | | rsqname | name | gp_toolkit.gp_resqueue_ rsqname | The name of the resource queue. | | rsqcountlimit | real | gp_toolkit.gp_resqueue_ rsqcountlimit | The active query threshold of the resource queue. A value of -1 means no limit. | | rsqcountvalue | real | gp_toolkit.gp_resqueue_ rsqcountvalue | The number of active query slots currently being used in the resource queue. | | rsqcostlimit | real | gp_toolkit.gp_resqueue_ rsqcostlimit | The query cost threshold of the resource queue. A value of -1 means no limit. | | rsqcostvalue | real | gp_toolkit.gp_resqueue_ rsqcostvalue | The total cost of all statements currently in the resource queue. | | rsqmemorylimit | real | gp_toolkit.gp_resqueue_ rsqmemorylimit | The memory limit for the resource queue. | | rsqmemoryvalue | real | gp_toolkit.gp_resqueue_ rsqmemoryvalue | The total memory used by all statements currently in the resource queue. | | rsqwaiters | integer | gp_toolkit.gp_resqueue_ rsqwaiter | The number of statements currently waiting in the resource queue. | | rsqholders | integer | gp_toolkit.gp_resqueue_ rsqholders | The number of statements currently running on the system from this resource queue. |
|
|
|
