Work with Resource Pool Engine Variables
This page explains how to set and find current resource pool values.
Set the resource_pool
Value
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.
Find the Current resource_pool
Value
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.