CREATE RESOURCE POOL

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

• QUERY_MEMORY_PERCENTAGE: this option can be used to restrict the maximum memory that a single query can use in the resource pool. It is applied together with MEMORY_PERCENTAGE.

  For example, setting MEMORY_PERCENTAGE to 60% and QUERY_MEMORY_PERCENTAGE to 50% would configure the system so that all queries running within the specified resource pool should together use a maximum of 60% of system memory, and any single query running within the pool should use, at most, 50% of system memory. Example syntax:

  Copy

  CREATE RESOURCE POOL rpoolmain WITH   
  MEMORY_PERCENTAGE = 60,   
  QUERY_MEMORY_PERCENTAGE = 50,    
  SOFT_CPU_LIMIT_PERCENTAGE = 65,   
  MAX_CONCURRENCY = 40;

  When setting QUERY_MEMORY_PERCENTAGE, the memory limit set for a pool will take effect immediately; however, queries already running in the pool will not be killed if the pool is currently over its new memory limit.

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

  There is a background thread on the aggregator scanning the list of running queries periodically, and this thread kills any query it finds that has been running longer than the timeout. This thread wakes up roughly once every 5 seconds. However, it can miss the 5-second window to kill that query, for example, if the query has a SLEEP(10).

• The engine variable max_async_compilation_concurrency controls the number of compilations that can run asynchronously at a given time on each node when interpreter_mode is set to interpret_first. By decreasing the value of max_async_compilation_concurrency, you can decrease the likelihood that asynchronous compilations will cause the SOFT_CPU_LIMIT_PERCENTAGE or HARD_CPU_LIMIT_PERCENTAGE limits to be reached.

• 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. The sum of soft CPU limits of all resource pools must be <=100. 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. SingleStore recommends a soft CPU limit 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.

• 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 Resource Pool Interaction with Workload Management.

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) which results in no limit being set.

If QUERY_MEMORY_PERCENTAGE is not initially set, there is no memory limit on a per-query basis (default behavior).

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.

You can view the setting of this engine variable by using:

SHOW VARIABLES LIKE 'resource_governor%';

or

SELECT @@resource_governor_cpu_limit_mode;

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.

• 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 Using the Workload Manager 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.

• CREATE POOL privilege is required to create a resource pool.

• This command can be run on a master, or child aggregator node (see Node Requirements for SingleStore Commands).

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.