ALTER RESOURCE POOL

ALTER RESOURCE POOL changes the definition of a resource pool.

Syntax

Modifying parameters

The syntax for modifying any resource pool parameter is as follows:

ALTER RESOURCE POOL <name> SET (<key> = <value>);

<key> is the parameter name, <value> is its new value.

For example, the following command enables a limit of 100 concurrent queries:

ALTER RESOURCE POOL olap SET (CONCURRENT_QUERY_LIMIT = "100");

Resetting parameters

The command to reset a resource pool parameter is as follows:

ALTER RESOURCE POOL <name> RESET (<key>);

<key> is the parameter name.

For example, the following command resets the TOTAL_CPU_LIMIT_PERCENT_PER_NODE settings for a resource pool:

ALTER RESOURCE POOL olap RESET (TOTAL_CPU_LIMIT_PERCENT_PER_NODE);

Permissions

A permission ALTER SCHEMA on the resource pool in the .metadata/workload_manager/pools directory is required. Example of granting such a permission:

GRANT 'ALTER SCHEMA' ON `.metadata/workload_manager/pools/olap_pool` TO `user1@domain`;

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) — 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 is no limit. 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]$.

See also

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