Configure workload management rules - AnalyticDB - Alibaba Cloud Documentation Center

Prerequisites

Make sure you have:

An AnalyticDB for MySQL Data Warehouse Edition cluster
A cluster minor version of 3.1.6.3 or later

Note

To check or update your minor version, see Update the minor version of a cluster.

Usage notes

Modifying rules with WLM syntax requires cluster version V3.1.10.0 or later.
AnalyticDB for MySQL uses a soft delete policy for rules. New rules cannot reuse names of previously deleted rules.

Create a rule

Syntax

wlm add_rule
name=<rule_name>
type=query
action=<action>
predicate='<property><operator><value> && <property><operator><value>'
[attrs='<rule_attrs>']
[resource_group=<resource_group>]
[description='<description>']
[compatible_group=<compatible_group_name>]
[enabled=<true|false>]
[priority=<priority>]

Note

No spaces are allowed on either side of the equal sign (=).

Parameters

Parameter	Required	Description
`name`	Yes	Rule name. Must be unique among all existing and deleted rules.
`type`	Yes	Rule type. Set to `query`.
`action`	Yes	Action to perform when a query matches. See Actions.
`predicate`	Yes	Match conditions joined by `&&` (logical AND). Format: `<property><operator><value>`. Enclose the entire value in single quotes. See Properties and Operators.
`attrs`	No	Action attributes. Required when `action` is `RESUBMIT_RESOURCE_GROUP`, `ADD_PROPERTIES`, or `BLOCK_WITH_PROB`. Enclose in single quotes. See Actions.
`resource_group`	No	Resource group name. Default: `user_default`. To find resource group names, open the AnalyticDB for MySQL console, click the cluster ID, and go to the Resource Management page.
`description`	No	Rule description. Enclose in single quotes.
`compatible_group`	No	Compatibility group name. Rules in the same group are all evaluated when any one matches, regardless of priority order.
`enabled`	No	Whether the rule is active. Values: `true` (default), `false`.
`priority`	No	Rule priority. Default: `0`. Rules are matched in descending priority order. When a rule matches, evaluation stops. If multiple rules share the same priority, the earliest-created rule takes effect.

Note

In predicate values, add spaces on both sides of the in operator only. Do not add spaces around other operators.

Examples

End queries by user and pattern latency

End queries from user test where the P50 historical execution duration exceeds 60,000 ms:

wlm add_rule
name=testRule1
type=query
action=kill
predicate='user=test && PATTERN_RT_P50>60000';

+---------------------------------+
| result                          |
+---------------------------------+
| insert rule 'testRule1' success |
+---------------------------------+

Add query priority hints by user and IP

Lower the priority of queries from user test originating from specific IP addresses:

wlm add_rule
name=testRule2
type=query
action=ADD_PROPERTIES
attrs='{"add_prop":{"query_priority":"low"}}'
predicate='user=test && source_ip in 10.10.10.10,192.168.0.1,192.0.2.1';

+---------------------------------+
| result                          |
+---------------------------------+
| insert rule 'testRule2' success |
+---------------------------------+

End SELECT queries for a specific resource group

Create a priority-5 rule for testResourceGroup that ends SELECT queries from user test:

wlm add_rule
name=testRule3
type=query
resource_group=testResourceGroup
description='just a test'
priority=5
action=kill
predicate='user=test && query_task_type=1';

+---------------------------------+
| result                          |
+---------------------------------+
| insert rule 'testRule3' success |
+---------------------------------+

Modify a rule

Important

Modifying rules requires cluster version V3.1.10.0 or later. To check your version, see How do I view the minor version of a cluster?. To update the version, contact technical support.

Syntax

wlm update_rule
id=<rule_id>
name=<rule_name>
type=query
action=<action>
predicate='<property><operator><value> && <property><operator><value>'
[attrs='<rule_attrs>']
[resource_group=<resource_group>]
[description='<description>']
[compatible_group=<compatible_group_name>]
[enabled=<true|false>]
[priority=<priority>]

Note

No spaces are allowed on either side of the equal sign (=).

Parameters

All parameters are optional for update_rule. Specify id or name to identify the target rule.

Parameter	Description
`id`	Rule ID (unique, immutable). Run `wlm list_rule` to find rule IDs.
`name`	Rule name. To rename a rule, specify the rule by `id`. If the new name matches an existing rule, that rule is overwritten.
`type`	Rule type. Set to `query`.
`action`	Action to perform. See Actions.
`predicate`	Updated match conditions. See Properties and Operators.
`attrs`	Updated action attributes. Required when `action` is `RESUBMIT_RESOURCE_GROUP`, `ADD_PROPERTIES`, or `BLOCK_WITH_PROB`.
`resource_group`	Resource group name. Default: `user_default`.
`description`	Rule description.
`compatible_group`	Compatibility group name.
`enabled`	`true` (default) or `false`.
`priority`	Rule priority. Default: `0`.

Examples

Rename a rule

wlm update_rule
id=2
name=Rule;

+-----------------+
| result          |
+-----------------+
| update rule 2   |
+-----------------+

Update predicate conditions

Change the P50 latency threshold of testRule1 from 60,000 ms to 80,000 ms:

wlm update_rule
name=testRule1
predicate='user=test && PATTERN_RT_P50>80000';

Or use the rule ID:

wlm update_rule
id=1
predicate='user=test && PATTERN_RT_P50>80000';

+-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+

Change a KILL action to RESUBMIT_RESOURCE_GROUP

Reroute matching queries to a different resource group instead of ending them:

wlm update_rule
name=testRule1
action=RESUBMIT_RESOURCE_GROUP
attrs='{
    "resubmit":{
    "resource_group": "testgroup"
    }
}'

Note

For attrs format details, see Actions.

+-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+

List rules

Syntax

wlm list_rule
[id=<rule_id>]
[name='<rule_name>'\G]

Parameters

Parameter	Required	Description
`id`	No	Filter by rule ID. Omit `id` and `name` to list all rules.
`name`	No	Filter by rule name. Enclose in single quotes.
`\G`	No	Display each field on a separate line.

Examples

List a rule by ID

wlm list_rule
id=1 \G;

id: 1
            name: testRule4
     description: NULL
            type: QUERY
  resource_group: user_default
         enabled: 1
        priority: 0
         version: 1
      life_cycle: BEFORE_QUEUEING
compatible_group:
     valid_begin: null
       valid_end: null
         creator: kepler
     update_user: kepler
     create_time: 2022-09-16 14:00:18
     update_time: 2022-09-16 14:00:18
       predicate: [{"property":"${session.user}","value":"test","operator":"equal"},{"property":"${session.source_ip}","value":"1,2,3","operator":"in"}]
          action: ADD_PROPERTIES
           attrs: {"add_prop":{"query_priority":"low"}}
1 row in set (0.11 sec)

List a rule by ID and name

wlm list_rule
id=1
name='testRule4' \G;

Disable a rule

wlm disable_rule id=<rule_id>

Run wlm list_rule to find rule IDs.

Example

wlm disable_rule id=1;

+------------------+
| result           |
+------------------+
| disable rule 1   |
+------------------+

Verify that the rule is disabled (enabled: 0):

wlm list_rule id=1\G;

Enable a rule

wlm enable_rule id=<rule_id>

Run wlm list_rule to find rule IDs.

Example

wlm enable_rule id=1;

+----------------+
| result         |
+----------------+
| enable rule 1  |
+----------------+

Verify that the rule is enabled (enabled: 1):

wlm list_rule id=1\G;

Delete a rule

wlm delete_rule id=<rule_id>

Run wlm list_rule to find rule IDs. Deleted rule names cannot be reused.

Example

wlm delete_rule id=1;

+----------------+
| result         |
+----------------+
| delete rule 1  |
+----------------+

Verify deletion:

wlm list_rule id=1\G;

Empty set

Get the pattern hash of a query

Use calc_pattern_hash to get the hash value of an SQL pattern. You can then use this value with the pattern_hash property in predicates to target specific query patterns.

Syntax

wlm calc_pattern_hash <SQL>

The <SQL> parameter accepts a SELECT query or a write query that contains SELECT statements.

Example

wlm calc_pattern_hash select * from t where a=1 and b=2;

+-----------------------------------+----------------------+
| raw_sql                           | pattern_hash         |
+-----------------------------------+----------------------+
| select * from t where a=1 and b=2 | -4759960226441980963 |
+-----------------------------------+----------------------+

List all WLM commands

wlm;

+---------------------------+-------------------------------------+
| command                   | description                         |
+---------------------------+-------------------------------------+
| calc_pattern_hash         | calculate pattern_hash of query     |
| add_rule                  | add a new rule                      |
| list_rule id=[x]|name=[x] | list all rules OR filter by id/name |
| disable_rule id=[x]       | disable a rule by id                |
| enable_rule id=[x]        | enable a rule by id                 |
| delete_rule id=[x]        | delete a rule by id                 |
| update_rule id=[x]        | update a rule by id                 |
+---------------------------+-------------------------------------+

Reference

Properties

Properties specify which aspects of a query to evaluate in rule predicates.

Query properties

Property	Data type	Description
`user`	String	User who submitted the query.
`source_ip`	String	Client IP address.
`query_task_type`	Numeric	Query type: `1` (SELECT), `2` (DELETE), `3` (UPDATE), `4` (INSERT INTO SELECT / INSERT OVERWRITE SELECT / REPLACE INTO SELECT), `5` (CREATE VIEW).
`query_table_list`	String list	Tables involved in the query.
`query_db_list`	String list	Databases involved in the query.
`pattern_hash`	Numeric	Hash value of the SQL pattern.
`sql`	String	Original SQL statement. Requires V3.1.8.3 or later.

Runtime properties

Runtime properties evaluate queries based on current execution statistics.

Property	Data type	Unit	Description
`QUERY_PROCESS_TIME`	Numeric	ms	Total CPU time consumed by all tasks.
`QUERY_EXECUTION_TIME`	Numeric	ms	Execution duration, excluding queue time.
`QUERY_SUBMITTED_TIME`	Numeric	-	Submission timestamp.
`QUERY_TOTAL_TASK`	Numeric	-	Total tasks in the physical execution plan.
`QUERY_INPUT_DATA_SIZE`	Numeric	MB	Data read by the query.
`QUERY_SHUFFLE_DATA_SIZE`	Numeric	MB	Data shuffled during execution.
`QUERY_OUTPUT_DATA_SIZE`	Numeric	MB	Data output by the query.
`QUERY_PEAK_MEMORY`	Numeric	MB	Peak memory usage.
`QUERY_TOTAL_STAGES`	Numeric	-	Total stages in the execution plan.

Pattern properties

Pattern properties evaluate queries based on historical execution data for a given SQL pattern.

Property	Data type	Unit	Description
`PATTERN_RT_P50`	Numeric	ms	50th percentile execution duration.
`PATTERN_RT_P90`	Numeric	ms	90th percentile execution duration.
`PATTERN_EXECUTION_TIME_P50`	Numeric	ms	50th percentile execution duration, excluding queue time.
`PATTERN_EXECUTION_TIME_P90`	Numeric	ms	90th percentile execution duration, excluding queue time.
`PATTERN_WALL_TIME_P50`	Numeric	ms	50th percentile wall time.
`PATTERN_WALL_TIME_P90`	Numeric	ms	90th percentile wall time.
`PATTERN_SHUFFLE_SIZE_AVG`	Numeric	bytes	Average shuffle data size.
`PATTERN_PEAK_MEMORY_AVG`	Numeric	bytes	Average peak memory usage.
`PATTERN_INPUT_POSITION_AVG`	Numeric	-	Average number of input rows.
`PATTERN_OUTPUT_POSITION_AVG`	Numeric	-	Average number of output rows.

Operators

Operator	Supported types	Description
`>`	Numeric	Greater than.
`<`	Numeric	Less than.
`=`	Numeric, String	Equal to.
`>=`	Numeric	Greater than or equal to.
`<=`	Numeric	Less than or equal to.
`!=`	Numeric, String	Not equal to.
`in`	String list	Checks whether two string lists intersect. Add spaces on both sides of this operator.
`contains`	String	Checks whether a string contains another string. Use with the `sql` property. Requires V3.1.8.3 or later.

Actions

Action	Description	`attrs` format
`KILL`	Ends the query.	Not required.
`RESUBMIT_RESOURCE_GROUP`	Stops execution in the current resource group and resubmits the query to a different resource group.	`{"resubmit":{"resource_group":"<resource_group_name>"}}`
`ADD_PROPERTIES`	Modifies query properties such as priority.	`{"add_prop":{"query_priority":"<priority>","force":"false"}}`
`BLOCK_WITH_PROB`	Ends the query at a specified probability before it enters the queue. Use for throttling.	`{"block_prob":{"prob":<probability>}}`

ADD_PROPERTIES details:

query_priority: Set as an integer (0--39, higher is higher priority) or a string: For more information, see Priority queue and concurrency of interactive resource groups.

String value	Numeric equivalent
`LOWEST`	5
`LOW`	15
`NORMAL`	25
`HIGH`	35

force: Whether to overwrite existing query properties. Values: true (default for V3.1.9.4 and later) or false (default for versions earlier than V3.1.9.4). See How do I view the minor version of a cluster?.

BLOCK_WITH_PROB details:

prob: Probability of ending the query. Valid values: 0 to 1.