SingleStore DB

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

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.