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.

• SUPER 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, and MAX_QUEUE_DEPTH in the resource pools default_pool, system_auto, and system_optimizer_pool are fixed. These settings cannot be changed.

• This command must be run on the master aggregator (see Node Requirements for MemSQL Commands).

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;

Related Topics

• For more information about using resource pools to specify resource limits, see Setting Resource Limits.

Was this Article Helpful?

Thanks For Your Feedback!