ALTER RESOURCE POOL

Changes values of 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
  | MAX_CONCURRENCY = integer

Remarks

• MEMORY_PERCENTAGE: An integer between 5 and 100. This is the percentage of resources allocated to the pool (maximum_memory - memory not available for query execution).
  Info

  MEMORY_PERCENTAGE is the percentage of maximium_memory - (total_server_memory - alloc_query_execution - buffer_manager_cached_memory) on leaves. See Identifying and Reducing Memory Usage for more information on summary variables.

• QUERY_TIMEOUT: The number of seconds (in multiples of five) specifying the time after which a query running in the pool will be automatically terminated.
• SOFT_CPU_LIMIT_PERCENTAGE: An integer between 1 to 100. This is the percentage of CPU resources allocated to the pool.
• MAX_CONCURRENCY: The maximum number of concurrent SQL statements (queries) that are allowed to run cluster-wide across all aggregators. To un-set this limit, set it to zero.
• SUPER privileges are required for altering a resource pool.

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

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

Example

The following example changes the MEMORY_PERCENTAGE value from 40 to 80. Note: The percentages for all non-default pools should total 100. The default_pool value is always set to 100.

mysql> SHOW RESOURCE POOLS;
+--------------+-------------------+---------------+-----------------+---------------------------+
| Pool_Name    | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage |
+--------------+-------------------+---------------+-----------------+---------------------------+
| default_pool |               100 |          NULL |            NULL |                       100 |
| test_pool    |                40 |          NULL |            40   |                       100 |
+--------------+-------------------+---------------+-----------------+---------------------------+

mysql> ALTER RESOURCE POOL test_pool SET MEMORY_PERCENTAGE = 80;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW RESOURCE POOLS;
+--------------+-------------------+---------------+-----------------+---------------------------+
| Pool_Name    | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage |
+--------------+-------------------+---------------+-----------------+---------------------------+
| default_pool |               100 |          NULL |            NULL |                       100 |
| test_pool    |                80 |          NULL |            40   |                       100 |
+--------------+-------------------+---------------+-----------------+---------------------------+

Was this Article Helpful?

Thanks For Your Feedback!