# CREATE RESOURCE POOL The `CREATE RESOURCE POOL` command creates a [resource pool](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits.md). ## Syntax ```sql CREATE RESOURCE POOL [IF NOT EXISTS] pool_name [ WITH resource_setting [, ...n] ] resource_setting: MEMORY_PERCENTAGE = percent | QUERY_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. * The memory percentages the resource governor can assign are on a per leaf basis, not the whole cluster. For example, if your allocation is 50% of memory, that will be on a per leaf basis, so you can’t exceed the 50% limit at any time per leaf. * Similarly, the resource governor assigns the CPU usage on a per leaf basis, not the whole cluster. On bare‑metal or VM‑based deployments, the per‑leaf CPU capacity is based on the host CPU resources visible to that node. On Kubernetes‑based deployments, resource governance is cgroup‑aware. The effective CPU capacity used for enforcing pool CPU limits on each node is the minimum of: * The host CPU capacity visible to the node, and * The cgroup CPU limit configured for the container. All pool CPU percentage limits (`SOFT_CPU_LIMIT_PERCENTAGE` and `HARD_CPU_LIMIT_PERCENTAGE`) are applied relative to this effective capacity. * The maximum number of resource pools allowed is 10. * Refer to the [Permissions Matrix](https://docs.singlestore.com/db/v9.1/reference/sql-reference/security-management-commands/permissions-matrix.md) for the required permissions. ## 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`  defines the maximum percentage of the overall memory usage by all queries within that pool for any node (including aggregator and leaf nodes). It is set as the percentage of the memory currently available for query execution, including the memory used by tables and metadata. Hence, `MEMORY_PERCENTAGE`  is the percentage of  `maximium_memory` - non-execution-mem where non-execution-mem = (`total_server_memory` - `alloc_query_execution` - `buffer_manager_cached_memory` ). For example, if an aggregator’s `MAXIMUM_MEMORY` is 8GB and a leaf node’s `MAXIMUM_MEMORY` is 100GB, and the `MEMORY_PERCENTAGE` of one pool is 10%, then all queries executing under that pool can consume at most 10% \* (8GB - non-execution-mem) in the aggregator node and at most 10% \* (100GB - non-execution-mem) in the leaf node, any query that goes above this threshold will get the Out Of Memory error. See [Identifying and Reducing Memory Usage](https://docs.singlestore.com/db/v9.1/reference/troubleshooting-reference/identifying-and-reducing-memory-usage.md) 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: ``` 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](https://docs.singlestore.com/db/v7.5/en/reference/configuration-reference/engine-variables/list-of-engine-variables.html#sync-variables-list) 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](https://docs.singlestore.com/db/v7.5/en/reference/configuration-reference/engine-variables/list-of-engine-variables.html#sync-variables-list) 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 / ))` 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](https://docs.singlestore.com/#UUID-d527fdca-5ce3-1cc4-3a42-b7d6b8097279.md). 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. In Kubernetes‑based clusters, both `HARD` and `SOFT` CPU limits are enforced using a `cgroup‑aware` CPU capacity. The engine uses the minimum of host cores and the `cgroup` CPU limit when interpreting pool CPU percentages for Resource Governance. 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`. > **📝 Note**: 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.`MAX_CONCURRENCY``0`, the following logic executes: If the number of queries currently running in the cluster has reached `MAX_CONCURRENCY`, SingleStore will attempt to queue the query. However, if the queue is full then the query returns an error. See [Using the Workload Manager](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/use-the-workload-manager.md) 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](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits/resource-pool-basics/#section-idm4510351909488033483313104461.md). * `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](https://docs.singlestore.com/db/v9.1/reference/sql-reference/cluster-management-commands.md)). ## 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. ```sql 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. ```sql SHOW RESOURCE POOLS; ``` ```output +-----------------------+-------------------+---------------+-----------------+---------------------------+---------------------------+-----------------+ | 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. ```sql 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. ```sql CREATE RESOURCE POOL test_pool WITH QUERY_TIMEOUT = 20; ``` ```output Query OK, 0 rows affected (0.00 sec) ``` ```sql SET resource_pool = test_pool; ``` ```output Query OK, 0 rows affected (0.00 sec) ``` ```sql SELECT SLEEP(30); ``` ```output ERROR 2293 (HY000): The query has reached the timeout set for this connection's resource pool. ``` ```sql SHOW RESOURCE POOLS; ``` ```output +--------------+-------------------+---------------+-----------------+---------------------------+ | 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](https://docs.singlestore.com/db/v9.1/user-and-cluster-administration/use-the-workload-manager-and-set-resource-limits/set-resource-limits.md). *** Modified at: June 11, 2026 Source: [/db/v9.1/reference/sql-reference/resource-pool-commands/create-resource-pool/](https://docs.singlestore.com/db/v9.1/reference/sql-reference/resource-pool-commands/create-resource-pool/) (An index of the documentation is available at /llms.txt)