Outdated Version

You are viewing an older version of this section. View current production version.

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

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.
  • SUPER privileges are required for altering a resource pool.

You cannot ALTER the default_pool values.

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_Percent | Query_Timeout|
+--------------+----------------+--------------+
| default_pool |            100 |         NULL |
| test_pool    |             40 |         NULL |
+--------------+----------------+--------------+
2 rows in set (0.00 sec)

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_Percent | Query_Timeout|
+--------------+----------------+--------------+
| default_pool |            100 |         NULL |
| test_pool    |             80 |         NULL |
+--------------+----------------+--------------+
2 rows in set (0.00 sec)