Resource Pool Basics

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.

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