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. This allows you to prevent non-critical workloads from overloading the system. You can use resource pools to prevent a user’s query from making the system unusable for others connected to the database.

Resource limits are for all databases in a workspace. It is not possible to set limits at the database level.

The maximum number of resource pools allowed is 10.

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:

  1. SOFT_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.

  2. HARD_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 resource_governor_cpu_limit_mode. To display it, execute:

  SQL

  Copy

  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. Which queries get killed due to lack of memory is determined by the values set for QUERY_MEMORY_PERCENTAGE and MEMORY_PERCENTAGE at the time of the resource pool creation. Any single query that uses more memory than is allocated via QUERY_MEMORY_PERCENTAGE will be killed. Further, the total memory of all queries running in the resource pool cannot exceed the value of MEMORY_PERCENTAGE. Any queries that exceed that limit will be killed.

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

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 MEMORY_BS in INFORMATION_SCHEMA.MV_ACTIVITIES.

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. Workload management is a component of SingleStore Helios that automatically manages workspace workloads to ensure that they are matched with available system resources. For more information, see Workload Management.