Resource Pool Basics
This page explains how to create a new resource pool, modify an existing resource pool, how to show resource pool statuses, and introduces some built-in resource pools.
Create a New Resource Pool
To create a new resource pool, run CREATE RESOURCE POOL
from the master aggregator. For information on how to use this command, query execution behavior, and an example, see the CREATE RESOURCE POOL topic.
Modify an Existing Resource Pool
To modify an existing resource pool, run ALTER RESOURCE POOL
from the master aggregator. For information on how to use this command and an example, see the ALTER RESOURCE POOL topic.
MV_RESOURCE_POOL_STATUS
You can also show resource pool status across your pools with the information_schema.MV_RESOURCE_POOL_STATUS information_schema table. The MV_RESOURCE_POOL_STATUS
view collects data from all nodes. It will give active details about queued queries for resource pools that had set a maximum concurrency, as well as some historical data (killed, finished queries and total and average queue time).
SELECT * FROM information_schema.MV_RESOURCE_POOL_STATUS; **** +---------+-----------+-----------------+------------------+----------------+------------------+---------------------+-----------------------+ | NODE_ID | POOL_NAME | RUNNING_QUERIES | QUEUEING_QUERIES | KILLED_QUERIES | FINISHED_QUERIES | TOTAL_QUEUE_TIME_US | AVERAGE_QUEUE_TIME_US | +---------+-----------+-----------------+------------------+----------------+------------------+---------------------+-----------------------+ | 3 | pool1 | 0 | 0 | 0 | 0 | 0 | 0 | | 1 | pool1 | 0 | 0 | 0 | 0 | 0 | 0 | | 2 | pool1 | 0 | 0 | 0 | 0 | 0 | 0 | +---------+-----------+-----------------+------------------+----------------+------------------+---------------------+-----------------------+
For more information, see ALTER RESOURCE POOL.
The Built-in Resource Pools
Every cluster contains the built-in resource pools system_optimizer_pool
, default_pool
, and system_auto
.
Note
For each user, default_pool
is used unless a user-created resource pool has been assigned to the user.
The system_optimizer_pool
runs when enable_background_statistics_collection
is set to ON
. If the system_optimizer_pool
is running and the resource_governor_cpu_limit_mode is set to HARD
, the pool reserves 5% of CPU utilization for collection of background statistics.
By default, client connections to SingleStoreDB use the default_pool
. To use a different pool, see the next section, Work with Resource Pool Engine Variables. Queries in the default_pool
can have up to 100% of available query execution memory, up to 100% CPU utilization (depending on the resource_governor_cpu_limit_mode setting), and do not have a timeout limit set or a defined number of concurrent queries.