Workload Management enterprise

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, navigate to the following UI location:

Admin > Workload Management > Queues | Rules

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 add new queues, edit existing queues, or removed 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, the Are you sure you want to remove this queue? popup message displays prior to you explicitly removing it.

Default Queues

Dremio provides the following generic queues as a starting point for customization:

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.

Rule Configuration Options

Conditions Used in Rules

The following conditions, as well as Dremio SQL functions, can be used in combination when creating rules to target specific jobs.

• Users

• Group membership (members)

• Query type

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

• Session Tag

• Other functions that are supported in sql: such as date/time

Query Types

Rule Examples

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

Default Rules

Dremio provides the following generic rules as a starting point for customization.

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.

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.

Setup and Configuration

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

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

2. Setup 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.

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.