Using the Workload Manager

Notice

SingleStoreDB Cloud does not support setting the engine variables that are discussed in this topic. Please log into your account at support.singlestore.com and request support for this feature.

SingleStoreDB Cloud’s Workload Manager (WM) automatically manages workspace workloads by limiting execution of queries that require fully distributed execution, to ensure that they are matched with available system resources. It improves overall query execution efficiency and prevents workload surges from overwhelming the system. When the system is low on connections, threads, or memory, queries are queued so they can run later when adequate resources are available.

WM divides leaf resources among multiple aggregators. Assume a system with one leaf node and two aggregators. The  workload_management_max_threads_per_leaf variable is set to 1000 implying the total number of threads on the leaf node at any point of time is limited to 1K. Queries running on both aggregators will try to use the threads on the leaf and the WM will attempt to split this limit of 1K threads between the two aggregators based on the requirement of the queries on those two aggregators. If Aggregator1 is heavily loaded, the WM may decide to give 900 threads quota to Aggregator1 and 100 threads quota to Aggregator2.

To see these quotas, which change with time, on the specific node run:

SHOW WORKLOAD MANAGEMENT STATUS;

WM is concerned with

  • number of threads on leaves specified by the workload_management_max_threads_per_leaf variable,

  • number of connections on each leaf specified by the workload_management_max_connections_per_leaf variable and

  • leaf memory.

In addition to splitting leaf resources among aggregators, WM also throttles the amount of queries that can run on any aggregator. This is to make sure the current running queries do not exceed the quota on the aggregator. WM does this throttling by queuing queries. WM queues queries after Resource Governance.

Related to workload management is the concept of resource governance. Unlike the Workload Manager which is built-in and enabled by default you have to enable the Resource Governance. For more information, see Setting Resource Limits.