ALTER RESOURCE POOL
Changes the resource settings in an existing resource pool.
Syntax
ALTER RESOURCE POOL pool_name SET (resource_setting [, ...n]) resource_setting: MEMORY_PERCENTAGE = percent | QUERY_TIMEOUT = seconds | SOFT_CPU_LIMIT_PERCENTAGE = percent | HARD_CPU_LIMIT_PERCENTAGE = percent | MAX_CONCURRENCY = integer | MAX_QUEUE_DEPTH = integer
For information on each resource setting, see CREATE RESOURCE POOL.
Remarks
You cannot alter the built-in resource pools.
CREATE POOL
orDROP POOL
privileges are required for altering a resource pool.Changes made to a resource pool take effect immediately, but running queries in the pool won’t be killed if the pool currently exceeds its new memory limit.
The resource pool settings
MEMORY_PERCENTAGE
,QUERY_TIMEOUT
,SOFT_CPU_LIMIT_PERCENTAGE
,HARD_CPU_LIMIT_PERCENTAGE
,MAX_CONCURRENCY
, andMAX_QUEUE_DEPTH
in the resource poolsdefault_pool
,system_auto
, andsystem_optimizer_pool
are fixed. These settings cannot be changed.This command can be run on a master, or child aggregator (see Node Requirements for SingleStoreDB Commands).
See the Permission Matrix for the required permission.
Example
The following example alters resource limits in two resource pools.
Before altering the current limits for the resource pools, it is advisable to set the current limits to a lower value, to create headroom.
ALTER RESOURCE POOL executive SET HARD_CPU_LIMIT_PERCENTAGE = 10; ALTER RESOURCE POOL general SET HARD_CPU_LIMIT_PERCENTAGE = 10; ALTER RESOURCE POOL executive SET MEMORY_PERCENTAGE = 10; ALTER RESOURCE POOL general SET MEMORY_PERCENTAGE = 10;
Now set the new resource limits, as required:
ALTER RESOURCE POOL executive SET HARD_CPU_LIMIT_PERCENTAGE = 75; ALTER RESOURCE POOL general SET HARD_CPU_LIMIT_PERCENTAGE = 25; ALTER RESOURCE POOL executive SET MEMORY_PERCENTAGE = 55; ALTER RESOURCE POOL general SET MEMORY_PERCENTAGE = 45;