Use the Workload Manager

Workload Management Queue Enhancements

Incoming queries are filtered into queues based on their size (large, medium, or small). If a large number of queries are all filtered into the same queue, that queue could become oversaturated and this could lead to slower processing. SingleStore has two engine variables workload_management_queue_size_allow_upgrade (specifies how many large queries can be waiting for execution in the LARGE queue when we want to move to that queue a MEDIUM query from the MEDIUM queue) and workload_management_dynamic_resource_allocation (enables/disables the dynamic reuse of WM queues feature) for dynamic query handling. These variables work together to dynamically move a query from a saturated queue to another queue if there are available resources. For example, if the medium queue is saturated and the large queue is not, the medium query will be moved into the large queue. To utilize dynamic query handling, set workload_management_dynamic_resource_allocation variable to ON.

Detailed information about these engine variables may be obtained in the Workload Management Sync Variables section of the engine variable list.

Small Queries

Small queries do not exceed the workload_management_memory_queue_threshold, which is a small amount of memory relative the total amount of leaf memory available to queries.

Small queries also do not use distributed joins. For this reason, they do not require threads or connections on the leaves.

Medium Queries

Medium queries are queries whose estimated resource usage per leaf (threads, connections, and memory) falls between a lower and an upper bound that Workload Management (WM) maintains internally:

• Lower bound (small vs. medium)

  • Threads per leaf ≥ 1

  • Connections per leaf ≥ 1

  • Memory per leaf ≥ total_leaf_query_memory * workload_management_memory_queue_threshold

• Queries below lower bound limits are treated as small and are never queued by WM.

• Upper bound (medium vs. large)

  • Threads per leaf ≤ 0.5 * workload_management_max_threads_per_leaf / workload_management_expected_aggregators

  • Connections per leaf ≤ 0.5 * workload_management_max_connections_per_leaf / workload_management_expected_aggregators

  • Memory per leaf ≤ total_leaf_query_memory / workload_management_expected_aggregators

Queries whose estimated usage is between these lower and upper bounds are classified as medium. Queries above the upper bound are classified as large and use the global queue.

Thresholds are internal estimates per leaf, not numbers a user manually plugs in.

Workload Management computes “threads used per leaf” and “connections used per leaf” internally from the query plan and runtime settings (for example workload_management_max_threads_per_leaf, flexible parallelism, join shape, etc.). It then uses them internally to decide whether a query is classified as small, medium, or large and if it must be queued.

Hence in practice:

• You should treat the thresholds conceptually (small / medium / large) rather than trying to hand-calculate the exact threads-per-leaf for every statement.

• To understand why queries are being queued, you use:

  • SHOW WORKLOAD MANAGEMENT STATUS command or  MV_WORKLOAD_MANAGEMENT_STATUS view to see aggregate running and queued queries and global thresholds. This gives an idea which queue is hot and which resource is tight.

  • WORKLOAD_MANAGEMENT_QUEUE information_schema view to see which queries are queued and whether they are medium or large (QUERY_TYPE) and which resource (threads, connections, memory) is limiting them.

Large queries

Queries that exceed the upper threshold for medium queries are classified as large queries.

Example: Checking Why Queries are Queued

1. Check overall WM status

   SQL

   Copy

   SHOW WORKLOAD MANAGEMENT STATUS;

   Key details to observe:

   • Running Threads Per Leaf (from local queue) – total threads per leaf currently used by medium queries.

   • Running Connections Per Leaf (from local queue) – total connections per leaf for medium queries.

   • Memory Threshold (MB) to Queue Locally – memory level above which medium queries start queuing locally.

   • Memory / Connections / Threads Threshold to Queue Globally – limits above which queries are considered large and use the global queue.

   • Queued Queries (from local queue) vs Queued Queries (from global queue) – how many medium vs large queries are waiting.

   This helps find out whether you are hitting medium limits (local queue) or large limits (global queue).

2. Find out which specific queries are queued and why:

   SQL

   Copy

   SELECT QUERY, QUERY_TYPE, THREADS_PER_LEAF, THREADS_PER_LEAF_LIMIT, CONNECTIONS_PER_LEAF, CONNECTIONS_PER_LEAF_LIMIT, LEAF_MEMORY, LEAF_MEMORY_LIMIT
     FROM information_schema.WORKLOAD_MANAGEMENT_QUEUE;

   Key details to observe:

   • QUERY_TYPE is Medium or Large as per WM’s classification.

   • For any queued row, at least one *_USED (internal) is close to or above its corresponding *_LIMIT:

     • If THREADS_PER_LEAF is close to THREADS_PER_LEAF_LIMIT, threads are the bottleneck.

     • If CONNECTIONS_PER_LEAF is close to CONNECTIONS_PER_LEAF_LIMIT, connections are limiting.

     • If LEAF_MEMORY is close to LEAF_MEMORY_LIMIT, memory is limiting.

   You can then correlate this with the PROCESSLIST view, if needed:

   Copy

   SELECT ID, USER, HOST, DB, INFO
     FROM information_schema.PROCESSLIST;

   and match the text of INFO to the QUERY column from WORKLOAD_MANAGEMENT_QUEUE.

Small Queries

Small queries are never queued. They use a negligible amount of leaf resources, which are not tracked.

Medium Queries

At regular intervals, the master aggregator collects statistics from the child aggregators. These statistics include the maximum resources used (threads per leaf, connections per leaf, and memory per leaf) during the interval and a rolling average of the maximum resources required during the previous intervals.

At the end of each interval, the master aggregator uses the statistics gathered from the child aggregators to reallocate resources to each aggregator, if necessary.

If the resource requirements for a new medium query exceed the total amount of resources allocated to all aggregators for all medium queries, the new query is queued in a local queue that is maintained by an individual aggregator. Each aggregator decides when to dequeue the queries in its local queue.

Fifty percent of the total leaf resources are reserved for medium queries.

Large Queries

Large queries are ones whose estimated resource needs (threads, connections, or memory per leaf) exceed the upper thresholds for medium queries.Up to four large queries in the cluster will run concurrently. Any additional queries are queued in the global queue.

Fifty percent of the total leaf resources are reserved for large queries.

Local Queue vs Global Queue

Workload Management uses two queues to control distributed queries.

The local queue holds medium queries.

The global queue is for large queries. Global queues ensure that a few heavy, fully distributed queries cannot monopolize leaf resources and starve the rest of the workload.

Dynamic Reuse of WM Queues

There are two queues created by Workload Manager: one for Medium queries and one for Large queries. WM dynamically moves queries from the Medium to the Large Queue when the Medium Queue is saturated and the Large Queue is not.

There are two global variables to control this behavior: workload_management_dynamic_resource_allocation and workload_management_queue_size_allow_upgrade