CREATE RESOURCE POOL

CREATE RESOURCE POOL creates a resource pool.

Syntax

CREATE RESOURCE POOL <name>
WITH ( <parameter_name> [= <parameter_value>] [, ... ] )

• name is the name of the resource pool being created. It must be unique. Path notation is not allowed (i.e., it must not contain /).
• WITH ( <parameter_name> [= <parameter_value>] [, ... ] ) allows setting parameter values that define the behavior of the resource pool.

Parameters

• CONCURRENT_QUERY_LIMIT (Int32) — an optional field that sets the number of concurrently executing queries in the resource pool. If the value is -1, there is no limit. Default value: -1. Valid values: $-1, [0, 2^{31}-1]$.

• QUEUE_SIZE (Int32) — an optional field that defines the size of the waiting queue. The system can hold no more than $CONCURRENT_QUERY_LIMIT + QUEUE_SIZE$ queries at a time. If the value is -1, there is no limit. Default value: -1. Valid values: $-1, [0, 2^{31}-1]$.

• DATABASE_LOAD_CPU_THRESHOLD (Int32) — an optional field that sets the CPU load threshold for the entire database, after which queries are not sent for execution and remain in the queue. If the value is -1, there is no limit. Default value: -1. Valid values: $-1, [0, 100]$.

• TOTAL_MEMORY_LIMIT_PERCENT_PER_NODE (Double) is an optional field that defines the percentage of available memory on the node that all queries in this resource pool can use. If the value is -1, there are no limits. Default value: -1. Valid values: $-1, [0, 100]$.

  If a limit is set and the total memory consumption by queries in the pool reaches this limit:

  • New queries that require memory will fail with error OVERLOADED.
  • Already running queries that need additional memory will also fail with error OVERLOADED.

• TOTAL_CPU_LIMIT_PERCENT_PER_NODE (Double) — an optional field that sets the percentage of available CPU that all queries on the node in this resource pool can use. If the value is -1, there is no limit. Default value: -1. Valid values: $-1, [0, 100]$.

• QUERY_CPU_LIMIT_PERCENT_PER_NODE (Double) — an optional field that defines the percentage of available CPU on the node for a single query in the resource pool. If the value is -1, there is no limit. Default value: -1. Valid values: $-1, [0, 100]$.

• RESOURCE_WEIGHT (Int32) — an optional field that sets weights for distributing resources among pools. If the value is -1, weights are not used. Default value: -1. Valid values: $-1, [0, 2^{31}-1]$.

Notes

Queries are always executed in some resource pool. By default, all queries are sent to the resource pool default, which is created automatically and cannot be deleted — it is always present in the system.

If the value of parameter CONCURRENT_QUERY_LIMIT is set to 0, then all queries sent to this pool will be immediately terminated with status PRECONDITION_FAILED.

Permissions

Requires permission CREATE TABLE on the directory .metadata/workload_manager/pools, an example of granting such permission:

GRANT 'CREATE TABLE' ON `.metadata/workload_manager/pools` TO `user1@domain`;

CREATE RESOURCE POOL olap WITH (
    CONCURRENT_QUERY_LIMIT=20,
    QUEUE_SIZE=1000,
    DATABASE_LOAD_CPU_THRESHOLD=80,
    RESOURCE_WEIGHT=100,
    TOTAL_MEMORY_LIMIT_PERCENT_PER_NODE=80,
    TOTAL_CPU_LIMIT_PERCENT_PER_NODE=70
)

In the example above, a resource pool is created with the following limits:

• Maximum number of concurrent queries — 20.
• Maximum queue size — 1000.
• When the database load reaches 80%, queries stop running concurrently.
• All queries in the pool can consume no more than 80% of the available memory on the node.
• The total limit on available CPU for all queries in the pool on the node is 70%.
• The resource pool has a weight of 100, which only takes effect in case of oversubscription.

See also

• Workload Manager — resource consumption management
• ALTER RESOURCE POOL
• DROP RESOURCE POOL