Use the Workload Manager

Configure Workload Management

The workload manager can be configured using engine variables, and the default settings are sufficient for most users regardless of cluster size. However, these engine variables can be tuned to fully utilize system resources for high concurrency workloads. These variables are discussed in the sections that follow.

Before changing any of these values, ensure that you understand each of these variables and have evaluated their impact on your workload.

All of the workload management engine variables are sync variables. To configure these variables, first read about Sync Variables to learn how to set them. Then, refer to Workload Management Sync Variables for information on specific variables.

Query Classification

The workload manager classifies queries by their size: small, medium, or large. Queries are classified to determine their Queueing Policy.

Queueing Policy

Queries are queued based on their size, as determined by the thresholds in the previous section, Query Classification.

Errors and Warnings

Queries return configurable errors if too many queries are queued or a query has been queued for too long. Queries also return configurable warnings when they were queued for a significant time relative to their actual execution time. This allows users to identify when their cluster resources are insufficient to meet the load of incoming queries.

These errors and warnings are:

ER_TOO_MANY_QUEUED_QUERIES: When the workload_management_max_queue_depth value is reached, SingleStore will return this error instead of adding a new query to the queue. If you encounter this error, the maximum queue depth may need to be increased to accommodate the load. This error may also indicate that your cluster’s resources need to be increased or the load of incoming queries needs to be reduced.

ER_QUERY_QUEUE_TIMEOUT: When a query has been queued longer than the workload_management_queue_timeout value, it will return this error and will be removed from the queue. This error indicates that your cluster’s resources or the load of incoming queries need to be adjusted to successfully process incoming queries.

ER_QUERY_QUEUED_WARNING: When the ratio of time spent by a query in the queue versus the actual execution time of the query exceeds the workload_management_queue_time_warning_ratio value, the query will return this warning. The warning indicates the amount of time spent queued and the amount of time spent executing, to help you understand the sources of query latency. Encountering this warning is normal in many workloads, but if query latency is too high, your cluster’s resources may not be sufficient to process incoming queries in a timely manner.

Observe the Status of Workload Management

To see the current state of the workload management system, including the number of running and queued queries, and resource usage and thresholds, you can run the SHOW WORKLOAD MANAGEMENT STATUS command. You can also run the query SELECT * FROM information_schema.MV_WORKLOAD_MANAGEMENT_STATUS;, which will return the same information as the previous command, but per aggregator.

The following example shows sample output from a small, lightly-loaded system. The units for the memory thresholds are in megabytes.

SHOW WORKLOAD MANAGEMENT STATUS;

+-------------------------------------------------------+-------+
| Stat                                                  | Value |
+-------------------------------------------------------+-------+
| Queued Queries (from global queue)                    |     0 |
| Queued Queries (from local queue)                     |     0 |
| Total Queued Queries Since Startup                    |     0 |
| Running Queries (from global queue)                   |     0 |
| Running Queries (from local queue)                    |     0 |
| Running Memory (MB) On Leaves (from global queue)     |     0 |
| Running Memory (MB) On Leaves (from local queue)      |     0 |
| Allocated Memory (MB) On Leaves (for local queue)     |  6673 |
| Required Memory (MB) On Leaves (from local queue)     |     0 |
| Avg Required Memory (MB) On Leaves (from local queue) |     0 |
| Running Threads Per Leaf (from local queue)           |     0 |
| Allocated Threads Per Leaf (for local queue)          |  2048 |
| Required Threads Per Leaf (from local queue)          |     0 |
| Avg Required Threads Per Leaf (from local queue)      |     0 |
| Running Connections Per Leaf (from local queue)       |     0 |
| Allocated Connections Per Leaf (for local queue)      |  2500 |
| Required Connections Per Leaf (from local queue)      |     0 |
| Avg Required Connections Per Leaf (from local queue)  |     0 |
| Memory Threshold (MB) to Queue Locally                |    66 |
| Memory Threshold (MB) to Queue Globally               |  3337 |
| Connections Threshold to Queue Globally               |  2500 |
| Threads Threshold to Queue Globally                   |  2048 |
+-------------------------------------------------------+-------+

The following table provides a general description of some of the status fields. For information on the local queue and the global queue, see the Query Classification section.

In addition, the SHOW PROCESSLIST command can be executed on any SingleStore node to obtain details about currently running queries. Also, information_schema.processlist or information_schema.mv_processlist can be queried to provide the same information but can be filtered for more focused results.

The processlist provides information such as the user, host, database, command type, execution time, state, and additional information about each process. The information provided can help identify long-running or hanging queries, monitor query execution, and analyze query behavior.

If you haven't received any of the errors listed above and you still have available memory or CPU, see the Troubleshooting Poorly Performing Queries page for potential steps to take to resolve the issue.