Creates a resource pool.

Syntax

CREATE RESOURCE POOL pool_name
    [ WITH 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

Remarks

• pool_name: The name of a user-defined resource pool. The value follows the same naming convention as a database column name. Not case sensitive.

Resource Pool Settings

When you write a CREATE RESOURCE POOL or an ALTER RESOURCE POOL statement, you can specify zero or more of the following limits.

• MEMORY_PERCENTAGE: An integer between 5 and 100. Memory usage for users can be limited from 5% to 100% of the query execution memory resources available in the system. This is a hard limit for each resource pool, which means the pool cannot utilize memory above this limit.

  Info

  MEMORY_PERCENTAGE is set as the percentage of the memory currently available for query execution, which is maximum_memory - memory in use by everything except query execution. This includes memory used by tables and metadata, for example. “Memory in use by everything except query execution” can be calculated as total_server_memory - alloc_query_execution - buffer_manager_cached_memory. So MEMORY_PERCENTAGE is the percentage of maximium_memory - (total_server_memory - alloc_query_execution - buffer_manager_cached_memory).

  See Identifying and Reducing Memory Usage for more information on summary variables.

• SOFT_CPU_LIMIT_PERCENTAGE: An integer between 1 to 100. CPU usage for users can be limited from 1% - 100% of available processing capacity. This is a soft limit that allows CPU usage for a given pool to temporarily burst above the limit during a period of low/idle CPU usage; however, if other pools need CPU resources, then any pools above their soft limit will be immediately pushed down to their assigned limit. To set SOFT_CPU_LIMIT_PERCENTAGE when creating or altering a resource pool, the resource_governor_cpu_limit_mode engine variable must be set to SOFT. A soft CPU limit is recommended for most applications, as they typically want to utilize CPU resources from other pools, if such resources are available.

• HARD_CPU_LIMIT_PERCENTAGE: An integer between 1 to 100. CPU usage for users can be limited from 1% - 100% of available processing capacity. This is a hard limit that reserves the specified CPU for a given pool. Once a resource pool’s hard limit is set, that share of the CPU is reserved for the resource pool and no other resource pool can use the reserved share. The sum of hard CPU limits of all resource pools must be <=100. To set HARD_CPU_LIMIT_PERCENTAGE when creating or altering a resource pool, the resource_governor_cpu_limit_mode engine variable must be set to HARD. A hard CPU limit is useful when you want to ensure maximum predictability of CPU usage, at the expense of not utilizing extra CPU resources that may be available.

• QUERY_TIMEOUT: A number of seconds (in multiples of five). To control query execution time, you can define a query execution time limit that cancels a running query after that timeout value has been reached.

• MAX_CONCURRENCY: If you specify this setting, at most MAX_CONCURRENCY concurrent SQL statements (queries) will run across all aggregators at any time. This number is approximate. Precisely, at most MAX (1, FLOOR (MAX_CONCURRENCY / <number of aggregators>)) concurrent SQL statements (queries) will run on one aggregator at a time. To un-set this limit, set it to zero.

• MAX_QUEUE_DEPTH: The maximum number of queries that will be queued when MAX_CONCURRENCY is exceeded. If you set the MAX_QUEUE_DEPTH, its value must be 1 or greater. If you do not set MAX_QUEUE_DEPTH, the resource pool will not queue any queries. The sum of MAX_QUEUE_DEPTH for all user-defined resource pools cannot exceed the value of the engine variable max_connection_threads * 0.8, when you create a resource pool or alter MAX_QUEUE_DEPTH. To understand why you may want to set MAX_QUEUE_DEPTH, see the Resource Pool Interaction with Workload Management section.

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

The CPU Limit Mode

The resource governor runs in either hard CPU limit mode or soft CPU limit mode. To specify the mode to use, set the engine variable resource_governor_cpu_limit_mode to HARD or SOFT. The default setting is SOFT. This variable’s setting applies to all resource pools in the cluster.

The following notes provide further information about resource_governor_cpu_limit_mode, referenced as “the current mode”:

• If the current mode is HARD, the HARD_CPU_LIMIT_PERCENTAGE of a resource pool must be set while creating the pool.
• If the current mode is SOFT, the HARD limits of a resource pool cannot be set, and vice-versa.
• Setting the current mode to HARD copies all the existing SOFT limits to HARD and sets the soft limits to NULL, and vice-versa.
• While querying the current resource limits, if the current mode is SOFT, all HARD_CPU_LIMIT_PERCENTAGE values will be displayed as NULL, and vice-versa.
• You will not be able to change the current mode from one mode to another if the sum of the current SOFT_CPU_LIMIT_PERCENTAGE is greater than 100.
• If the current mode is HARD and enable_background_statistics_collection is set to ON, the built-in system_optimizer_pool reserves 5% of the CPU utilization for collection of background statistics.

Resource Pool Interaction with Workload Management

Suppose your cluster has two user-defined resource pools, pool1 and pool2. pool1 is a lightly loaded pool. You submit a large number of queries to pool2. This number of queries is greater than pool2's MAX_CONCURRENCY setting . Without MAX_QUEUE_DEPTH (the maximum number of queries allowed to be queued in the resource pool) set appropriately in pool2, these queries cause the workload_management_max_queue_depth to be exceeded, causing the workload manager to cancel the queued queries in pool1.

See Workload Management for a discussion on how the workload manager operates.

Other Remarks

• You can create at most ten resource pools, not including the built-in resource pools.

• SUPER privileges are required for creating a resource pool.

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

Examples

Example 1: Creating Two Resource Pools

The following example creates two resource pools. It assumes the resource_governor_cpu_limit_mode is set to HARD and enable_background_statistics_collection is set to ON. It also assumes that no other resource pools exist, except the built-in pools.

CREATE RESOURCE POOL executive WITH MEMORY_PERCENTAGE = 60,
HARD_CPU_LIMIT_PERCENTAGE = 65, MAX_CONCURRENCY = 40;

CREATE RESOURCE POOL general WITH MEMORY_PERCENTAGE = 40,
HARD_CPU_LIMIT_PERCENTAGE = 30, MAX_CONCURRENCY = 20;

The HARD_CPU_LIMIT_PERCENTAGE of the two resource pools sums to 95%. The built-in pool system_optimizer_pool reserves 5% of the hard CPU utilization.

Display the resource pool configuration. Scroll to the right to see the HARD_CPU_LIMIT_PERCENTAGE column.

SHOW RESOURCE POOLS;
****
+-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
| Pool_Name             | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage | Hard_CPU_Limit_Percentage | Max_Queue_Depth |
+-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+
| default_pool          |               100 |          NULL |            NULL |                      NULL |                      NULL |            NULL |
| system_auto           |               100 |          NULL |            NULL |                      NULL |                      NULL |            NULL |
| system_optimizer_pool |               100 |          NULL |            NULL |                      NULL |                         5 |            NULL |
| executive             |                60 |          NULL |              40 |                      NULL |                        65 |              20 |
| general               |                40 |          NULL |              20 |                      NULL |                        30 |              20 |
+-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+

Example 2: Attempting to Exceed the Hard CPU Limit when the system_optimizer_pool is Running

The following example attempts to create two resource pools. It assumes the resource_governor_cpu_limit_mode is set to HARD and enable_background_statistics_collection is set to ON. It also assumes that no other resource pools exist, except the built-in pools.

CREATE RESOURCE POOL executive WITH MEMORY_PERCENTAGE = 60,
HARD_CPU_LIMIT_PERCENTAGE = 65, MAX_CONCURRENCY = 40;

CREATE RESOURCE POOL general WITH MEMORY_PERCENTAGE = 40,
HARD_CPU_LIMIT_PERCENTAGE = 35, MAX_CONCURRENCY = 20;

Because 5% of CPU utilization is reserved for collection of background statistics, the CREATE RESOURCE POOL general ... command fails, because the HARD_CPU_LIMIT_PERCENTAGE would exceed 100%.

Example 3: 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.

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!