Define a new resource queue.
CREATE RESOURCE QUEUE name WITH (queue_attribute=value [, ... ])where queue_attribute is:
ACTIVE_STATEMENTS=integer
[ MAX_COST=float [COST_OVERCOMMIT={TRUE|FALSE}] ]
[ MIN_COST=float ]
[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
[ MEMORY_LIMIT='memory_units' ]
| MAX_COST=float [ COST_OVERCOMMIT={TRUE|FALSE} ]
[ ACTIVE_STATEMENTS=integer ]
[ MIN_COST=float ]
[ PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX} ]
[ MEMORY_LIMIT='memory_units' ]Create a new resource queue for YMatrix database resource management. The resource queue must have an ACTIVE_STATEMENTS or MAX_COST value (or may have both). Only superusers can create resource queues.
A resource queue with an ACTIVE_STATEMENTS threshold sets a maximum limit on the number of queries that the role assigned to the queue can perform. It controls the number of active queries that are allowed to run simultaneously. The value of ACTIVE_STATEMENTS should be an integer greater than 0.
A resource queue with a MAX_COST threshold sets a maximum limit on the total cost of queries that can be performed by the role assigned to the queue. Cost is measured as the estimated total cost of a query determined by the YMatrix database query optimizer (as shown in the EXPLAIN output of the query). Therefore, administrators must be familiar with queries that are usually executed on the system in order to set appropriate cost thresholds for queues. Cost is measured in units of obtaining disk pages; 1.0 is equal to reading a continuous disk page. The value of MAX_COST is specified as a floating point number (e.g. 100.0) or as an exponent (e.g. 1e + 2). If the resource queue is limited based on the cost threshold, the administrator can allow COST_OVERCOMMIT=TRUE (default value). This means that queries that exceed the allowed cost threshold will be allowed to run, but will only be allowed to run when the system is idle. If COST_OVERCOMMIT=FALSE is specified, queries that exceed the cost limit will always be denied and will never be allowed to run. By specifying a value for MIN_COST, the administrator can define fees for small queries that will be exempt from resource queuing.
If a value is not defined for ACTIVE_STATEMENTS or MAX_COST, it is set to -1 by default (indicating no limit). After defining a resource queue, you must assign roles to the queue using the ALTER ROLE or CREATE ROLE command.
You can choose to assign PRIORITY to a resource queue to control the relative share of available CPU resources used by the queries associated with that queue relative to the other resource queues. If no value is defined for PRIORITY, the default priority for the query associated with the queue is MEDIUM.
The resource queue with an optional MEMORY_LIMIT threshold sets a limit on the maximum amount of memory that can be used on the segment host for all queries submitted through the resource queue. This determines the total amount of memory that all worker processes of a query can consume on the segment host during query execution. YMatrix recommends using MEMORY_LIMIT with ACTIVE_STATEMENTS, not with MAX_COST. On a statement-based queue, the default amount of memory allocated for each query is: MEMORY_LIMIT / ACTIVE_STATEMENTS. On a cost-based queue, the default amount of memory allocated for each query is: MEMORY_LIMIT * (query_cost / MAX_COST).
If MEMORY_LIMIT or statement_mem is not exceeded, the default memory allocation can be overridden on a per-query basis using the statement_mem server configuration parameter. For example, allocate more memory for a specific query:
=> SET statement_mem='2GB';
=> SELECT * FROM my_big_table WHERE column='value' ORDER BY id;
=> RESET statement_mem;The MEMORY_LIMIT value of all your resource queues should not exceed the physical memory of the segment host. If the workload is spread across multiple queues, memory allocations may be overscheduled. However, if the segment host memory limit specified in gp_vmem_protect_limit is exceeded, the query can be canceled during execution.
name
ACTIVE_STATEMENTS integer
MEMORY_LIMIT 'memory_units'
MAX_COST float
COST_OVERCOMMIT boolean
MIN_COST float
PRIORITY={MIN|LOW|MEDIUM|HIGH|MAX}
Use the gp_toolkit.gp_resqueue_status system view to view the limit settings and current status of the resource queue:
SELECT * from gp_toolkit.gp_resqueue_status WHERE
rsqname='queue_name';There is another system view called pg_stat_resqueues, which displays statistical metrics for resource queues over a period of time. However, to use this view, the stats_queue_level server configuration parameter must be enabled.
CREATE RESOURCE QUEUE cannot run in a transaction.
Additionally, SQL statements that are run during the execution of the EXPLAIN ANALYZE command will be excluded from the resource queue.
Create a resource queue with an active query limit of 20:
CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20);Create a resource queue with an active query limit of 20 and a total memory limit of 2000MB (each query will allocate 100MB of segment host memory when executed):
CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=20,
MEMORY_LIMIT='2000MB');Create a resource queue with a query fee limit of 3000.0:
CREATE RESOURCE QUEUE myqueue WITH (MAX_COST=3000.0);Create a resource queue with a query cost limit of 3^10 (or 3000000000.0) and does not allow excessive use. Allow small queries with a cost of less than 500 to run immediately:
CREATE RESOURCE QUEUE myqueue WITH (MAX_COST=3e+10,
COST_OVERCOMMIT=FALSE, MIN_COST=500.0);Create a resource queue with both active query limits and query cost limits:
CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=30,
MAX_COST=5000.00);Create a resource queue with an active query limit of 5 and maximum priority:
CREATE RESOURCE QUEUE myqueue WITH (ACTIVE_STATEMENTS=5,
PRIORITY=MAX);CREATE RESOURCE QUEUE is a YMatrix database extension. There is no provision for resource queues or resource management in the SQL standard.