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

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.

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 pool1 WITH MEMORY_PERCENTAGE = 50, SOFT_CPU_LIMIT_PERCENTAGE = 50, MAX_CONCURRENCY = 30;
Query OK, 0 rows affected (0.01 sec)

mysql> 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.
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 useful?