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
  | SOFT_CPU_LIMIT_PERCENTAGE = percent
  | MAX_CONCURRENCY = integer

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 memory 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 creating a resource pool.

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

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

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

Note: Resource limits also apply to any LOAD DATA queries, internal sampling queries, or stored procedures that are run by a user within a given 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.

Examples

Example 1: Creating a New Resource Pool

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.

CREATE RESOURCE POOL pool1 WITH MEMORY_PERCENTAGE = 50, SOFT_CPU_LIMIT_PERCENTAGE = 50, MAX_CONCURRENCY = 30;

SHOW RESOURCE POOLS;
****
+--------------+-------------------+---------------+-----------------+---------------------------+
| Pool_Name    | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage |
+--------------+-------------------+---------------+-----------------+---------------------------+
| default_pool |               100 |          NULL |            NULL |                       100 |
| pool1        |                50 |          NULL |               3 |                        50 |
+--------------+-------------------+---------------+-----------------+---------------------------+

Set the resource_pool session variable to test_pool.

SET resource_pool = test_pool;

SELECT @@resource_pool;
****
+-----------------+
| @@resource_pool |
+-----------------+
| test_pool       |
+-----------------+
1 row in set (0.00 sec)

Example 2: Setting query-timeout and Attempting to Exceed the Query Execution Time Limit

The following example shows the intended behavior of the query-timeout setting. It creates a new resource pool and sets the query execution time limit to 20 seconds. Then, an error is forced via the SELECT SLEEP(30) query.

CREATE RESOURCE POOL test_pool WITH QUERY_TIMEOUT = 20;
****
Query OK, 0 rows affected (0.00 sec)

SET resource_pool = test_pool;
****
Query OK, 0 rows affected (0.00 sec)

SELECT SLEEP(30);
****
ERROR 2293 (HY000): The query has reached the timeout set for this connection's resource pool.

SHOW RESOURCE POOLS;
****
+--------------+-------------------+---------------+-----------------+---------------------------+
| Pool_Name    | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage |
+--------------+-------------------+---------------+-----------------+---------------------------+
| default_pool |               100 |          NULL |            NULL |                       100 |
| test_pool    |               100 |            20 |            NULL |                       100 |
+--------------+-------------------+---------------+-----------------+---------------------------+
2 rows in set (0.01 sec)

Because the previous query attempts to execute for greater than the 20 second limit, it times out.

Was this Article Helpful?

Thanks For Your Feedback!