CREATE RESOURCE POOL

Info

MemSQL Helios does not support this command.

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 (maximum_memory minus persistent memory for tables, databases, indexes, etc.) in the system. This is a hard limit for each resource pool, which means the pool cannot utilize memory above this limit. For more information on maximum_memory, see Memory Management.

  • 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.

Info

When you submit a new query to a resource pool and the pool’s MAX_CONCURRENCY is 0, the workload manager will check if resources are available to immediately run the query. If resources are not available, the workload manager will attempt to queue the query.

When you submit a new query to a resource pool and the pool’s MAX_CONCURRENCY is greater than 0, the following logic executes: If the number of queries currently running in the cluster has reached MAX_CONCURRENCY, MemSQL will attempt to queue the query. However, if the queue is full then the query returns an error.

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

Other Remarks

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%.

Related Topics