Set Resource Limits
The Resource Governance (RG) feature restricts the resources that queries can use by grouping the queries into “pools” and then assigning quotas of resources to these pools.
Resource limits are for all databases in a workspace.
The resources controlled by resource pools are:
Concurrency: Limits the total queries that can run simultaneously in any given pool.
For example, if you define a pool with a max concurrency of 25 and there are 4 aggregators in the workspace, each aggregator will only allow FLOOR(25/4)=6 queries to run at a time in that pool. The rest of the queries will be queued in FIFO order. Only queries are queued to limit concurrency.
CPU: Limits the percentage of total cores available to a pool.
It includes both aggregator and leaf node CPUs. For example, if your leaves have 100 cores, and Pool A has 20% as its cpu limit, 20 cores will be assigned to Pool A on each leaf node. All queries running in Pool A will share the same 20 cores between themselves. The OS will manage context switching between the queries. There are two variants:
CPU_ LIMIT_ PERCENTAGE: Pool B can temporarily use cores from Pool A when Pool A is underutilizing its CPU reservation. As a result, Pool A might temporarily have less than 20 cores at its disposal but there will be overall better performance.
CPU_ LIMIT_ PERCENTAGE: Pool B can’t use cores from Pool A and hence the performance is more deterministic.
Each pool can have both SOFT_
CPU_ LIMIT_ PERCENTAGE and HARD_ CPU_ LIMIT_ PERCENTAGE set. Which mode the resource governor is running in is determined by the variable
governor_ cpu_ limit_ mode To display it, execute:SELECT @@resource_governor_cpu_limit_mode;
All pools run in either the hard or soft mode, i.
e. all pools have the same mode.
Memory: Limits the total leaf node memory that all queries running in a specific pool can together use.
Once this limit is reached, the first query to request more memory will be killed and result in an Out Of Memory (OOM) error. It is not possible to control exactly which query will be killed. It is best to make sure queries don’t reach this limit by controlling the concurrency of the pool.
Query Timeout: This can be specified to kill queries if they have been running or queued for a long period of time.
For more information on the above settings, see CREATE RESOURCE POOL.
Each client connection is assigned to a resource pool that specifies some (or all) of these limits.
Resource limits also apply to any
LOAD DATA queries, internal sampling queries, or stored procedures that are run by a user within a given resource pool.
To see the status of queries running in pools run:
SELECT * FROM INFORMATION_SCHEMA.MV_RESOURCE_POOL_STATUS;
To see the details about properties of pools run:
SELECT * FROM INFORMATION_SCHEMA.RESOURCE_POOLS;
To see how much memory a query is using, check
To see the total memory allocated (not reserved) by a node currently use:
SHOW STATUS EXTENDED LIKE '%TOTAL_SERVER_MEMORY%';
Related to setting resource limits through governance is the concept of workload management.
In this section
Last modified: April 26, 2023