Work with Resource Pool Engine Variables

Set the engine variable resource_pool to specify the resource pool to be used by an existing or new client connection. For an existing connection, use SET resource_pool = <pool_name>;. For all new connections, use SET GLOBAL resource_pool = <pool_name>;. By default, resource_pool is set to default_pool, unless a default resource pool has been set for the user who is connected.

For an example of how resource pools can be used together, consider the following example. Two resource groups, executive and general, have been created on a cluster

CREATE RESOURCE POOL executive WITH MEMORY_PERCENTAGE = 60;
CREATE RESOURCE POOL general WITH MEMORY_PERCENTAGE = 40;

At any point in time, executive can use at most 60% of query execution memory and general can use at most 40% of query execution memory. This allows concurrent queries in both pools to consume all available query execution memory. CPU limits using SOFT_CPU_LIMIT_PERCENTAGE can be set the same way.

The resource_pool variable is set to general so that all new connections are assigned to that pool by default. The following command requires the SUPER permission. Refer Permissions Matrix

SET GLOBAL resource_pool = general;

When a new user connects to the cluster, it will be part of the general resource pool. You can then change its resource pool by changing the current value to executive. This will enable usage of a larger portion of the available query execution memory.

SET resource_pool = executive;

Thus an application controlling client connections would give priority to “executive” users by setting their resource_pool to executive after those users connect. This prevents an accidental memory-intensive query from a user in the general resource pool from consuming memory that should be kept available for users in the executive pool.

You can determine the current resource pool by querying for the resource_pool variable.

SHOW VARIABLES like '%resource_pool%';
****
+---------------+------------+
| Variable_name | Value      |
+---------------+------------+
| resource_pool | test_pool  |
+---------------+------------+

SELECT @@resource_pool;
****
+-----------------+
| @@resource_pool |
+-----------------+
| test_pool       |
+-----------------+

To query for the complete list of available resource pools, use the SHOW RESOURCE POOLS command.

SHOW RESOURCE POOLS;
****
+--------------+-------------------+---------------+-----------------+---------------------------+
| Pool_Name    | Memory_Percentage | Query_Timeout | Max_Concurrency | Soft_CPU_Limit_Percentage |
+--------------+-------------------+---------------+-----------------+---------------------------+
| default_pool |               100 |          NULL |            NULL |                       100 |
| executive    |                60 |          NULL |            NULL |                        50 |
| general      |                40 |          NULL |            40   |                        50 |
+--------------+-------------------+---------------+-----------------+---------------------------+

For more information, see the Non-Sync Variables List, and SHOW RESOURCE POOLS.