You are viewing an older version of this section. View current production version.
ALTER RESOURCE POOL
MemSQL Helios does not support this command.
Changes the resource settings in an existing resource pool.
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.
You cannot alter the built-in resource pools.
SUPERprivileges 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
MAX_QUEUE_DEPTHin the resource pools
system_optimizer_poolare fixed. These settings cannot be changed.
This command must be run on the master aggregator (see Node Requirements for MemSQL Commands).
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;
- For more information about using resource pools to specify resource limits, see Setting Resource Limits.