CREATE RESOURCE POOL

Create a resource pool with a specified name.

Syntax

CREATE RESOURCE POOL pool_name
    [ WITH resource_setting [, ...n] ]

resource_setting:
    MEMORY_PERCENTAGE = percent
  | QUERY_TIMEOUT = seconds

Remarks

• pool_name: The name of a resource pool. The value follows the same naming convention as a database column name. Not case sensitive.
• 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 creating a resource pool.

There can be at most 10 named resource pools, not including default_pool.

If MEMORY_PERCENTAGE or QUERY_TIMEOUT are not initially set, they will default to 100% and 0 (which is represented as NULL in SHOW RESOURCE POOLS), respectively.

Resource limits are for all databases in a cluster. It is not possible to set limits at the database level.

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 creates a new resource pool, test_pool, and sets it as the resource pool for the current session. Note: The percentages for all non-default pools should total 100. The default_pool value is always set to 100.

mysql> CREATE RESOURCE POOL test_pool WITH MEMORY_PERCENTAGE = 75, QUERY_TIMEOUT = 10;
Query OK, 0 rows affected (0.01 sec)

mysql> SHOW RESOURCE POOLS;
+--------------+----------------+--------------+
| Pool_name    | Memory_Percent | Query_Timeout|
+--------------+----------------+--------------+
| default_pool |            100 |         NULL |
| test_pool    |             75 |           10 |
+--------------+----------------+--------------+
2 rows in set (0.00 sec)

-- Set the resource_pool session variable to test_pool.
mysql> SET resource_pool = test_pool;
Query OK, 0 rows affected (0.00 sec)

mysql> select @@resource_pool;
+-----------------+
| @@resource_pool |
+-----------------+
| test_pool       |
+-----------------+
1 row in set (0.00 sec)

Was this Article Helpful?

Thanks For Your Feedback!