CREATE RESOURCE QUEUE

Defines a new resource queue.

Synopsis

CREATE RESOURCE QUEUE <name> WITH (<queue_attribute>=<value> [, ... ])

where <queue_attribute> is:

    PARENT=<queue_name>
    MEMORY_LIMIT_CLUSTER=<percentage>
    CORE_LIMIT_CLUSTER=<percentage>
    [ACTIVE_STATEMENTS=<integer>]
    [ALLOCATION_POLICY='even']
    [VSEG_RESOURCE_QUOTA='mem:<memory_units>']
    [RESOURCE_OVERCOMMIT_FACTOR=<double>]
    [NVSEG_UPPER_LIMIT=<integer>]
    [NVSEG_LOWER_LIMIT=<integer>]
    [NVSEG_UPPER_LIMIT_PERSEG=<double>]
    [NVSEG_LOWER_LIMIT_PERSEG=<double>]

    <memory_units> ::= {128mb|256mb|512mb|1024mb|2048mb|4096mb|
                        8192mb|16384mb|1gb|2gb|4gb|8gb|16gb}
    <percentage> ::= <integer>%

Description

Creates a new resource queue for HAWQ workload management. A resource queue must specify a parent queue. Only a superuser can create a resource queue.

Resource queues with an ACTIVE_STATEMENTS threshold set a maximum limit on the number of queries that can be executed by roles assigned to that queue. It controls the number of active queries that are allowed to run at the same time. The value for ACTIVE_STATEMENTS should be an integer greater than 0. If not specified, the default value is 20.

When creating the resource queue, use the MEMORY_LIMIT_CLUSTER and CORE_LIMIT_CLUSTER queue attributes to tune the allowed resource usage of the resource queue. MEMORY_LIMIT_CLUSTER and CORE_LIMIT_CLUSTER must set to the same value for a resource queue. In addition the sum of the percentages of MEMORY_LIMIT_CLUSTER (and CORE_LIMIT_CLUSTER) for resource queues that share the same parent cannot exceed 100%.

Note: The MEMORY_LIMIT of a resource queue assigns the quota to operators in a query plan during planning, before actual execution. Actual memory consumption during query might exceed the limit.

You can optionally configure the maximum or minimum number of virtual segments to use when executing a query by setting NVSEG_UPPER_LIMIT/NVSEG_LOWER_LIMIT or NVSEG_UPPER_LIMIT_PERSEG/NVSEG_LOWER_LIMIT_PERSEG attributes for the resource queue.

After defining a resource queue, you can assign a role to the queue by using the ALTER ROLE or CREATE ROLE command. You can only assign roles to the leaf-level resource queues (resource queues that do not have any children.)

See also Best Practices for Using Resource Queues.

Parameters

<name>
Required. The name of the resource queue. The name must not already be in use and must not be pg_default or pg_root.

PARENT=<queue_name>
Required. The parent queue of the new resource queue. The parent queue must already exist. This attribute is used to organize resource queues into a tree structure. You cannot specify pg_default as a parent queue. Resource queues that are parents to other resource queues are also called branch queues. Resource queues without any children are also called leaf queues. If you do not have any existing resource queues, use pg_root as the starting point for new resource queues.

The parent queue cannot have any roles assigned.

MEMORY_LIMIT_CLUSTER=<percentage>
Required. Defines how much memory a resource queue can consume from its parent resource queue and consequently dispatch to the execution of parallel statements. Since a resource queue obtains its memory from its parent, the actual memory limit is based from its parent queue. The valid values are 1% to 100%. The value of MEMORY_ LIMIT_CLUSTER must be identical to the value of CORE_LIMIT_CLUSTER. The sum of values for MEMORY_LIMIT_CLUSTER of this queue plus other queues that share the same parent cannot exceed 100%. The HAWQ resource manager periodically validates this restriction. Despite its name, the actual memory consumed by a resource queue may exceed a limit inferred from this value.

CORE_LIMIT_CLUSTER=<percentage>
Required. The percentage of consumable CPU (virtual core) resources that the resource queue can take from its parent resource queue. The valid values are 1% to 100%. The value of MEMORY_ LIMIT_CLUSTER must be identical to the value of CORE_LIMIT_CLUSTER. The sum of values for CORE_LIMIT_CLUSTER of this queue and queues that share the same parent cannot exceed 100%.

ACTIVE_STATEMENTS=<integer>
Optional. Defines the limit of the number of parallel active statements in one leaf queue. The maximum number of connections cannot exceed this limit. If this limit is reached, the HAWQ resource manager queues more query allocation requests. Note that a single session can have several concurrent statement executions that occupy multiple connection resources. The value for ACTIVE_STATEMENTS should be an integer greater than 0. The default value is 20.

ALLOCATION_POLICY=<string>
Optional. Defines the resource allocation policy for parallel statement execution. The default value is even.

Note: This release only supports an even allocation policy. Even if you do not specify this attribute, the resource queue still applies an even allocation policy. Future releases will support alternative allocation policies.

Setting the allocation policy to even means resources are always evenly dispatched based on current concurrency. When multiple query resource allocation requests are queued, the resource queue tries to evenly dispatch resources to queued requests until one of the following conditions are encountered:

  • There are no more allocated resources in this queue to dispatch, or
  • The ACTIVE_STATEMENTS limit has been reached

For each query resource allocation request, the HAWQ resource manager determines the minimum and maximum size of a virtual segment based on multiple factors including query cost, user configuration, table properties, and so on. For example, a hash distributed table requires fixed size of virtual segments. With an even allocation policy, the HAWQ resource manager uses the minimum virtual segment size requirement and evenly dispatches resources to each query resource allocation request in the resource queue.

VSEG_RESOURCE_QUOTA=‘mem:{128mb | 256mb | 512mb | 1024mb | 2048mb | 4096mb | 8192mb | 16384mb | 1gb | 2gb | 4gb | 8gb | 16gb}’
Optional. This quota defines how resources are split across multiple virtual segments. For example, when the HAWQ resource manager determines that 256GB memory and 128 vcores should be allocated to the current resource queue, there are multiple solutions on how to divide the resources across virtual segments. For example, you could use a) 2GB/1 vcore * 128 virtual segments or b) 1GB/0.5 vcore * 256 virtual segments. Therefore, you can use this attribute to make the HAWQ resource manager calculate the number of virtual segments based on how to divide the memory. For example, if VSEG_RESOURCE_QUOTA='mem:512mb', then the resource queue will use 512MB/0.25 vcore * 512 virtual segments. The default value is ’mem:256mb’.

Note: To avoid resource fragmentation, make sure that the segment resource capacity configured for HAWQ (in HAWQ Standalone mode: hawq_rm_memory_limit_perseg; in YARN mode: yarn.nodemanager.resource.memory-mb must be a multiple of the resource quotas for all virtual segments and CPU to memory ratio must be a multiple of the amount configured for yarn.scheduler.minimum-allocation-mb.

RESOURCE_OVERCOMMIT_FACTOR=<double>
Optional. This factor defines how much a resource can be overcommitted. For example, if RESOURCE_OVERCOMMIT_FACTOR is set to 3.0 and MEMORY_LIMIT_CLUSTER is set to 30%, then the maximum possible resource allocation in this queue is 90% (30% x 3.0). If the resulting maximum is bigger than 100%, then 100% is adopted. The minimum value that this attribute can be set to is 1.0. The default value is 2.0.

NVSEG_UPPER_LIMIT=<integer> / NVSEG_UPPER_LIMIT_PERSEG=<double>
Optional. These limits restrict the range of number of virtual segments allocated in this resource queue for executing one query statement. NVSEG_UPPER_LIMIT defines an upper limit of virtual segments for one statement execution regardless of actual cluster size, while NVSEG_UPPER_LIMIT_PERSEG defines the same limit by using the average number of virtual segments in one physical segment. Therefore, the limit defined by NVSEG_UPPER_LIMIT_PERSEG varies dynamically according to the changing size of the HAWQ cluster.

For example, if you set NVSEG_UPPER_LIMIT=10 all query resource requests are strictly allocated no more than 10 virtual segments. If you set NVSEG_UPPER_LIMIT_PERSEG=2 and assume that currently there are 5 available HAWQ segments in the cluster, query resource requests are allocated 10 virtual segments at the most.

NVSEG_UPPER_LIMIT cannot be set to a lower value than NVSEG_LOWER_LIMIT if both limits are enabled. In addition, the upper limit cannot be set to a value larger than the value set in global configuration parameter hawq_rm_nvseg_perquery_limit and hawq_rm_nvseg_perquery_perseg_limit.

By default, both limits are set to -1, which means the limits are disabled. NVSEG_UPPER_LIMIT has higher priority than NVSEG_UPPER_LIMIT_PERSEG. If both limits are set, then NVSEG_UPPER_LIMIT_PERSEG is ignored. If you have enabled resource quotas for the query statement, then these limits are ignored.

Note: If the actual lower limit of the number of virtual segments becomes greater than the upper limit, then the lower limit is automatically reduced to be equal to the upper limit. This situation is possible when user sets both NVSEG_UPPER_LIMITand NVSEG_LOWER_LIMIT_PERSEG. After expanding the cluster, the dynamic lower limit may become greater than the value set for the fixed upper limit.

NVSEG_LOWER_LIMIT=<integer> / NVSEG_LOWER_LIMIT_PERSEG=<double>
Optional. These limits specify the minimum number of virtual segments allocated for one statement execution in order to guarantee query performance. NVSEG_LOWER_LIMIT defines the lower limit of virtual segments for one statement execution regardless the actual cluster size, while NVSEG_LOWER_LIMIT_PERSEG defines the same limit by the average virtual segment number in one segment. Therefore, the limit defined by NVSEG_LOWER_LIMIT_PERSEG varies dynamically along with the size of HAWQ cluster.

NVSEG_UPPER_LIMIT_PERSEG cannot be less than NVSEG_LOWER_LIMIT_PERSEG if both limits are set enabled.

For example, if you set NVSEG_LOWER_LIMIT=10, and one statement execution potentially needs no fewer than 10 virtual segments, then this request has at least 10 virtual segments allocated. If you set NVSEG_UPPER_LIMIT_PERSEG=2, assuming there are currently 5 available HAWQ segments in the cluster, and one statement execution potentially needs no fewer than 10 virtual segments, then the query resource request will be allocated at least 10 virtual segments. If one statement execution needs at most 4 virtual segments, the resource manager will allocate at most 4 virtual segments instead of 10 since this resource request does not need more than 9 virtual segments.

By default, both limits are set to -1, which means the limits are disabled. NVSEG_LOWER_LIMIT has higher priority than NVSEG_LOWER_LIMIT_PERSEG. If both limits are set, then NVSEG_LOWER_LIMIT_PERSEG is ignored. If you have enabled resource quotas for the query statement, then these limits are ignored.

Note: If the actual lower limit of the number of virtual segments becomes greater than the upper limit, then the lower limit is automatically reduced to be equal to the upper limit. This situation is possible when user sets both NVSEG_UPPER_LIMITand NVSEG_LOWER_LIMIT_PERSEG. After expanding the cluster, the dynamic lower limit may become greater than the value set for the fixed upper limit.

Notes

To check on the configuration of a resource queue, you can query the pg_resqueue catalog table. To see the runtime status of all resource queues, you can use the pg_resqueue_status. See Checking Existing Resource Queues.

CREATE RESOURCE QUEUE cannot be run within a transaction.

To see the status of a resource queue, see Checking Existing Resource Queues.

Examples

Create a resource queue as a child of pg_root with an active query limit of 20 and memory and core limits of 50%:

CREATE RESOURCE QUEUE myqueue WITH (PARENT='pg_root', ACTIVE_STATEMENTS=20,
MEMORY_LIMIT_CLUSTER=50%, CORE_LIMIT_CLUSTER=50%);

Create a resource queue as a child of pg_root with memory and CPU limits and a resource overcommit factor:

CREATE RESOURCE QUEUE test_queue_1 WITH (PARENT='pg_root', 
MEMORY_LIMIT_CLUSTER=50%, CORE_LIMIT_CLUSTER=50%, RESOURCE_OVERCOMMIT_FACTOR=2);

Compatibility

CREATE RESOURCE QUEUE is a HAWQ extension. There is no provision for resource queues or workload management in the SQL standard.

See Also

ALTER RESOURCE QUEUEALTER ROLE, CREATE ROLE, DROP RESOURCE QUEUE