On this page

    Workload Management enterprise

    note:

    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:

    • Provides workload isolation and predictability for users and groups.

    • Ease of configuration for SLA and workload management.

    • Predictable and efficient utilization of cluster resources.

    Homogeneous Environment

    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).

    How Workload Management Works

    To access WLM, click the Settings (gear) icon near the bottom of the side navigation bar, and then click Queues.

    You set up workload management by:

    1. Creating queues with different criteria depending on how you want to manage your jobs.

    2. Creating rules that do the following:

    • Establish conditions that target specific queries.

    • Assign queries that fit the conditions to specific queues.

    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.

    Queues

    Dremio allows you to define job queues, to which queries can be assigned, based on defined rules. You can also add new queues, edit existing queues, or remove queues.

    • If you add a new queue, you can customize the queue with the following configuration options.

    • If you edit an existing queue, all of the options are available for modification as when you add a new queue.

    • If you delete a queue, Dremio prompts you to confirm your choice before deleting the queue.

    Default Queues

    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

    Queue and Job Limits

    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

    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.

    note:

    Dremio implementation of using rules for queue assignment may change in the future.

    Rule Configuration Options

    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:

    • Critical
    • High
    • Medium (default)
    • Low
    • Background
    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 parameter 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 presence of a tag with the function "tag()".

    Session tags are distinct from the tags used for version control in the API and the tags you can add to datasets as labels that allow sorting.

    Rule Examples

    You can use Dremio SQL syntax to specify rules to target particular jobs.

    The following are the types of rules that can be created along with examples.

    User

    Create a rule that identifies the user that triggers the job.

    USER in ('JRyan','PDirk','CPhillips')
    

    Group Membership

    Create a rule that identifies if the user that triggers the job is part of a particular group.

    is_member('MarketingOps') OR is_member('Engineering')
    

    Job Type

    Create a rule depending on the type of job. The types of jobs can be identified by the following categories:

    • Flight
    • JDBC
    • Internal Preview
    • Internal Run
    • Metadata Refresh
    • ODBC
    • Reflections
    • Rest
    • UI Download
    • UI Preview
    • UI Run
    query_type() IN ('JDBC', 'ODBC', 'UI Run')
    

    Query Label

    Create a rule that routes queries to specific queues if they are running the COPY INTO <table> or OPTIMIZE SQL command.

    query_label() IN ('COPY')
    

    Here is an example of a rule in the New Rule dialog: The condition is query_label() = 'COPY', and the queue is Low Cost User Queries.

    The allowed values are COPY for queries that run the COPY INTO <table> SQL command and OPTIMIZATION for queries that run the OPTIMIZE SQL command.

    Query Type

    Query Type Description
    Flight Jobs submitted from Arrow Flight and Arrow Flight SQL, including from the ODBC driver for Arrow Flight SQL.
    JDBC Jobs submitted from JDBC.
    ODBC Jobs submitted from the legacy Dremio ODBC driver.
    Metadata Refresh Refreshes pick up the latest metadata changes from external sources. The refresh can be run on specific engines rather than all engines.
    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.

    Query Plan Cost

    To find the cost of a query, navigate to: Job Profile > Raw Profile > Resource Allocation > Query Cost

    query_cost() > 1000000
    

    Tag

    tag() = 'ProductionDashboardQueue'
    

    Date and Time

    Create a rule that routes a job based on the time it was triggered. Use Dremio SQL Functions.

    EXTRACT(HOUR FROM CURRENT_TIME)
    BETWEEN 9 AND 18
    

    Combined Conditions

    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
    

    Default Rules

    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

    note:

    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.

    Viewing All Rules

    To view all rules:

    1. Click the Settings (gear) icon from the side navigation bar.
    2. Click Engine Routing from the sidebar menu to see the list of engine routing rules.

    Adding a Rule

    To add a rule:

    1. On the Engine Routing page, click + Add Rule in the top left.

    2. In the New Rule dialog, for Rule Name, enter a name.

    3. For Conditions, enter the routing condition. See Rules for supported conditions.

    4. For Action, complete one of the following options:

      a. If you want to route the jobs that meet the conditions to a particular queue, select the Route to queue option. Then use the queue selector to choose the queue.

      b. If you want to reject the jobs that meet the conditions, select the Reject option.

    5. Click Add.

    Editing a Rule

    To edit a rule:

    1. On the Engine Routing page, click the Edit Rule (pencil) icon that appears next to the rule.

    2. In the Edit Rule dialog, for Rule Name, enter a name.

    3. For Conditions, enter the routing condition. See Rules for supported conditions.

    4. For Action, complete one of the following options:

      a. If you want to route the jobs that meet the conditions to a particular queue, select the Route to queue option. Then use the queue selector to choose the queue.

      b. If you want to reject the jobs that meet the conditions, select the Reject option.

    5. Click Save.

    Deleting a Rule

    To delete a rule:

    1. On the Engine Routing page, click the Delete Rule (trash) icon that appears next to the rule.

    warning:

    You must have at least one rule to route queries to a particular queue.

    1. In the Delete Rule dialog, click Delete to confirm.

    Direct Routing

    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.

    note:

    You can use an SQL command to route jobs for refreshing reflections directly to specified queues. See Queue Routing in the SQL reference.

    Query Tagging & Direct Routing Configuration

    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.

    JDBC Session Configuration

    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'

    ODBC Session Configuration

    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

    1. Add the 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';

    2. Add the ROUTING_TAG or ROUTING_QUEUE parameter to the AdvancedProperties parameter in the User’s DSN located at ~/Library/ODBC/odbc.ini

    Tableau .tds File Configuration

    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=localhost;AdvancedProperties=ROUTING_QUEUE=PreviewQueue"

    Multi-Cluster Isolation

    Multi-cluster isolation allows you to isolate your workload by grouping nodes into virtual clusters.

    Setup and Configuration

    To isolate a job, set up YARN provisioning with a Cluster Name and Queue specified and configured:

    1. Navigate to Admin > Cluster > Provisioning, click Add New, and click the YARN icon.

    2. Set up your provisioned node in addition to specifying the following:

      a. Cluster Name

      b. Queue

      c. Workers

      • Cores per worker (Default: 4)

      • Memory per Worker (Default: 16GB)

    3. Navigate to Admin > Workload Management > Queue.

    4. Select Add New or Edit an existing Queue and specify the Cluster Name.

    note:

    You can also configure Multi-cluster Isolation in dremio.conf using the services tag.

    Viewing and Assigning

    • To view the activity, navigate to Node Activity.

    • To assign or un-assign a node as an executor node, navigate to Node Activity > Actions.