Enterprise Edition only
For the Community Edition equivalent, see Queue Control.
The Workload Management (WLM) feature provides the capability to manage cluster resources and workloads.
This is accomplished through defining a queue with specific characteristics (such as memory limits, CPU priority, and queueing and runtime timeouts) and then defining rules that specify which query is assigned to which queue.
This capability is particularly important in Dremio clusters that are deployed in multi-tenant environments with a variety of workloads ranging from exploratory queries to scheduled reporting queries. In particular, WLM provides the following:
Workload management works optimally in a homogeneous environment in terms of memory. That is, when each node in the Dremio cluster has the same amount of memory.
If you have a heterogeneous environment (nodes in a Dremio cluster have different amounts of memory), you should plan for the lowest common denominator (lowest amount of memory associated with a node).
To access WLM, navigate to the following UI location:
Admin > Workload Management > Queues | Rules
You set up workload management by:
The following diagram shows a basic WLM flow when a job query is submitted where Rule1 assigns job queries to Queue1, Rule2 to Queue2, Rule3 to Queue3, and so on. Rule4 indicates that any specified rule and/or all other queries can be rejected.
The Rule’s conditions that a job query matches, determines which queue the job query is sent to.
Dremio allows you to define job queues, to which queries can be assigned, based on defined rules.
You can add new queues, edit existing queues, or removed queues.
Are you sure you want to remove this queue?
popup message
displays prior to you explicitly removing it.Dremio provides the following generic queues as a starting point for customization:
Rule Name | Description |
---|---|
UI Previews | CPU priority: High Concurrency limit: 50 |
Low Cost User Queries | CPU priority: Medium Concurrency limit: 25 |
High Cost User Queries | CPU priority: Medium Concurrency limit: 5 |
Low Cost Reflections | CPU priority: Low Concurrency limit: 25 |
High Cost Reflections | CPU priority: Low Concurrency limit: 1 |
If you set up concurrency limits, ensure that you allocated job limits accordingly. For example, if you allow multiple concurrent jobs and if the total limit for each job is higher than the queue limit, then a job may fail if memory is consumed by other concurrent jobs.
Rules allow you to specify conditions that WLM uses to either assign a query to a queue or reject a query. Rules are applied in order, the first rule that a query matches determines the action taken.
You can create custom rules, use the provided rule templates, or modify the provided rule templates.
Dremio implementation of using rules for queue assignment may change in the future.
Category | Option | Description |
---|---|---|
Overview | Name | Name of the job queue. |
CPU Priority | Defines how much CPU time threads get respective to other threads.
Changing this attribute only affects new or enqueued queries.
Running queries continue using the previous setting until they complete. Priority levels:
| |
Concurrency Limit | Defines how many queries are allowed to run in parallel.
Changing this attribute only affects new queries.
For example, when the current limit is 5
and 10 long running queries have been submitted to the queue such that 5 of them are enqueued.
If the user changes the setting to be unlimited, those 5 enqueued queries will remain enqueued
until the running queries are done but new queries will start immediately. Enabled by default; set to 10 queries. | |
Memory Management | Queue memory Limit | Defines the total memory that all queries running in parallel in a given queue
can use per executor.
Changing this attribute is effective immediately and may cause some queries to fail
if the new value is smaller. Disabled by default. |
Job memory Limit | Sets a limit on the memory usage at the query level.
Changing this attribute only affects enqueued and new queries
but not the currently running queries. Disabled by default. | |
Time Limits | Enqueued Time Limit | Defines how long a query can wait in the queue before starting without being cancelled.
Changing the setting only affects new queries. Enqueued queries are not affected. Enabled by default; set to 5 minutes |
Query Runtime Limit | Defines how long a query can run before being cancelled.
Changing the setting affects enqueued and new queries but won’t affect queries that are already running. Disabled by default. | |
Session Tags | Tag Name | A JDBC connection paramater can be used to set a tag for rule processing. When the JDBC connection property "ROUTING_TAG = <Tag Name>" is set, the specified tag value is associated with all queries executed within that connection's session. Rules can check for the presense of a tag with the function "tag()" |
The following conditions, as well as Dremio SQL functions, can be used in combination when creating rules to target specific jobs.
Job Profile > Resource Allocation > Query Cost
Query Type | Description |
---|---|
JDBC | Jobs submitted from JDBC. |
ODBC | Jobs submitted from ODBC. |
REST | Jobs submitted from REST. |
Reflections | Reflection creation and maintenance jobs. |
UI Run | Full query runs in the UI. |
UI Preview | Preview queries in the UI. |
UI Download | Download queries in the UI. |
Internal Preview | Dataset formatting previews. Reflection recommender analysis queries. |
Internal Run | Node activity query. Data curation histogram and transformation suggestion queries. |
User Example
USER in ('JRyan','PDirk','CPhillips')
Group Membership Example
is_member('MarketingOps') OR is_member('Engineering')
Available Job Types Example
query_type() IN ('JDBC', 'ODBC', 'UI Run')
Query Plan Cost Example
query_cost() > 1000000
Tag Example
tag() = 'ProductionDashboardQueue'
Combined Conditions Example
The following example includes targeting a job by a combination of user, group membership, query type,
query cost, and time of day.
(
USER IN ('JRyan', 'PDirk', 'CPhillips')
OR is_member('superadmins')
)
AND query_type IN ( 'ODBC')
AND query_cost > 3000000
AND tag() = 'ExecutiveDashboard'
AND EXTRACT(HOUR FROM CURRENT_TIME)
BETWEEN 9 AND 18
Dremio provides the following generic rules as a starting point for customization.
Order | Rule Name | Available Query Types | Rule | Queue Name |
---|---|---|---|---|
1 | UI Previews | UI Preview | query_type() = 'UI Preview' |
UI Previews |
2 | Low Cost User Queries | JDBC, ODBC, REST, UI Run, UI Download, Internal Preview, Internal Run | query_cost() < 30000000 |
Low Cost User Queries |
3 | High Cost User Queries | JDBC, ODBC, REST, UI Run, UI Download, Internal Preview, Internal Run | query_cost() >= 30000000 |
High Cost User Queries |
4 | Low Cost Reflections | Reflections | query_type() = 'Reflections' AND query_cost() < 30000000 |
Low Cost Reflections |
5 | High Cost Reflections | Reflections | query_type() = 'Reflections' AND query_cost() >= 30000000 |
High Cost Reflections |
6 | All Other Queries | Reject |
The default setup covers all query types.
When setting your own custom rules, ensure that all query types are taken into account. Otherwise, you may experience unexpected behavior when using Dremio.
Direct Routing is used to specify the exact Queue and Execution Cluster to run queries on for a given ODBC or JDBC session. With Direct Routing WLM Rules are not considered and instead queries are routed directly to the specified Queue. Clients can be configured so that all queries run on a specific execution cluster or queries run on different execution clusters on a per-session basis.
To use Direct Routing add the Connection property ROUTING_QUEUE = <WLM Queue Name>
to the ODBC or JDBC session parameters when connecting to Dremio. When set all queries for the session are automatically routed to the specified WLM Queue and the execution cluster selected for that Queue.
To disable Tag Routing set the Dremio support key dremio.wlm.direct_routing
to false. By default Direct Routing is enabled.
Query Tags are configured by setting the ROUTING_TAG = <Tag Name>
parameter for a given session to the desired Tag Name.
Similarly, Direct Routing is configured by setting the ROUTING_QUEUE = <Queue Name>
parametor for a given session to the desired WLM Queue.
To configure JDBC sessions add the ROUTING_TAG
or ROUTING_QUEUE
parameter to the JDBC connection URL. For example as: jdbc:dremio:direct=localhost;ROUTING_TAG='TagA'
or jdbc:dremio:direct=localhost;ROUTING_Queue='PreviewQueue'
Configure ODBC sessions as follows:
Windows Sessions
Add the ROUTING_TAG
or ROUTING_QUEUE
parameter to the AdvancedProperties
parameter in the ODBC DSN field.
Mac OS Sessions
ROUTING_TAG
or ROUTING_QUEUE
parameter to the AdvancedProperties
parameter in the system odbc.ini
file located at /Library/ODBC/odbc.ini
. After adding the parameter an example Advanced Properties configuration is: AdvancedProperties=CastAnyToVarchar=true;HandshakeTimeout=5;QueryTimeout=180;TimestampTZDisplayTimezone=utc;NumberOfPrefetchBuffers=5;ROUTING_TAG='TagA';
ROUTING_TAG
or ROUTING_QUEUE
parameter to the AdvancedProperties
parameter in the User’s DSN located at ~/Library/ODBC/odbc.ini
Add the ROUTING_TAG
or ROUTING_QUEUE
parameter to the odbc-connect-string-extras
parameter in the .tds file. After adding an example configuration is: odbc-connect-string-extras="AUTHENTICATIONTYPE=Basic Authentication;CONNECTIONTYPE=Direct;HOST=automaster.drem.io;AdvancedProperties=ROUTING_QUEUE=PreviewQueue"
As of Dremio 4.0, multi-cluster isolation is available. Multi-cluster allows you to isolate your workload by grouping nodes into virtual clusters.
To isolate a job, setup YARN provisioning with a Cluster Name and Queue specified and configured:
Note: You can also configure for Multi-cluster Isolation in the dremio.conf via the services tag.