Workload Management

Starting in version 5.5, MemSQL automatically manages cluster workloads by limiting execution of queries that require fully distributed execution, to ensure that they are matched with available system resources. Workload management estimates the number of connections and threads needed to execute queries that require reshuffle and broadcast operations, and admits the query only if it can assign the necessary resources. Queries that are not immediately executed are put in a queue and are executed when system resources become available. Workload management improves overall query execution efficiency and prevents workload surges from overwhelming the system.

Configuration and System Variables

Workload management can be configured using system variables, and the default settings are sufficient for most users regardless of cluster size. However, these system variables can be tuned to fully utilize system resources for high concurrency workloads.

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 variables associated with workload management can be changed by using the SET GLOBAL statement. You can see the current variable settings by executing a SHOW VARIABLES statement:

memsql> SHOW VARIABLES LIKE '%workload%';
+----------------------------------------------+----------+
| Variable_name                                | Value    |
+----------------------------------------------+----------+
| workload_management                          | ON       |
| workload_management_expected_aggregators     | 1        |
| workload_management_max_connections_per_leaf | 1024     |
| workload_management_max_queue_depth          | 100      |
| workload_management_max_threads_per_leaf     | 8192     |
| workload_management_queue_time_warning_ratio | 0.500000 |
| workload_management_queue_timeout            | 3600     |
+----------------------------------------------+----------+
7 rows in set (0.00 sec)

workload_management: Specifies whether to enable workload management for the cluster. The default value is ON. If set to ON, the feature is enabled and the other workload management system variables will affect the way a query is executed. If set to OFF, the feature is disabled and no queueing or system resource optimization will occur.

workload_management_expected_aggregators: The expected number of aggregators that will be used to run a high volume of client queries which require fully distributed execution. The default value is 1. When this variable is set to 0, it is treated as the total number of aggregators in the cluster, i.e. all aggregators in the cluster are expected to be running a high volume of fully distributed queries.

workload_management_max_connections_per_leaf: The maximum number of connections to use per leaf node in the cluster. The default value is 1024.

workload_management_max_queue_depth: The maximum depth of the query queue, which is the maximum number of queries that can be queued. The default value is 100. If this number is reached, additional queries will not execute, and a ER_TOO_MANY_QUEUED_QUERIES error will appear.

workload_management_max_threads_per_leaf: The maximum number of threads to use per leaf. The default value is 8192. This number correlates with the max_connection_threads global variable, and they should generally be set to the same value.

workload_management_queue_time_warning_ratio: Specifies when a warning will appear based on the ratio of time spent by a query in the queue versus the actual execution time of the query. For example, if a query waits in the queue for one second and takes two seconds to execute, the ratio is 0.5. The default value is 0.500000. Once the specified ratio is reached for a query, a ER_QUERY_QUEUED_WARNING warning will appear.

workload_management_queue_timeout: The time duration in seconds after which a query times out and is removed from the queue without being executed. The default value is 3600.

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, MemSQL 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.

Was this Article Helpful?

Thanks For Your Feedback!