Configure workload management rules with WLM commands-AnalyticDB(AnalyticDB)-阿里云帮助中心

Prerequisites

Your AnalyticDB for MySQL cluster must run kernel version 3.1.6.3 or later.

Note

To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Notes

You can use WLM syntax to modify rules only on clusters running kernel version 3.1.10.0 or later.
AnalyticDB for MySQL uses a soft delete policy for rules. This policy requires each rule name to be unique, so you cannot reuse the name of a deleted rule.

Create a rule

Syntax

wlm add_rule
name=<ruleName>
type=query      
action=<ruleAtion>    
predicate='<property><operator><value> && <property><operator><value>'
[attrs='<ruleAttrs>']
[resource_group=<ruleResource_group>]
[description='<ruleDescription>']
[compatible_group=<compatible_group_name>]
[enabled=<ruleEnabled>]
[priority=<rulePriority>]

Note

Do not add spaces on either side of the equal sign (=).

Parameters

Parameter	Required	Description
name	Yes	The name of the rule. Note Rule names must be unique, and the names of deleted rules cannot be reused.
type	Yes	The rule type. Must be `query`.
action	Yes	The action performed when the predicate condition is met. For more information about actions, see Actions.
predicate	Yes	The predicate condition. Predicate syntax: Multiple conditions are joined by a logical AND operator (`&&`). The format for a single condition is: `$property $operator $value`. Supported operators include `>`, `<`, `>=`, `<=`, `=`, `!=`, and `in`. Note For more information about properties and operators, see Properties and Operators. The `in` operator requires spaces on both sides. Other operators do not require spaces. Enclose the parameter value in single quotation marks (').
attrs	No	This parameter is required when the `action` parameter is set to RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES, or BLOCK_WITH_PROB. For configuration details, see Actions. Note Enclose the parameter value in single quotation marks (').
resource_group	No	The name of the resource group. The default value is `user_default`. Note To view the resource groups for a cluster, log on to the AnalyticDB for MySQL console, click the target cluster ID, and then go to the Resource Groups page.
description	No	The description of the rule. Note Enclose the parameter value in single quotation marks (').
compatible_group	No	The compatibility group. By default, the system evaluates rules in descending order of priority and stops after the first match. To evaluate multiple rules for a single query, assign those rules to the same compatibility group. When a query matches a rule in a compatibility group, the system continues to evaluate other rules within that group and applies all that match.
enabled	No	Specifies whether to enable the rule. Valid values: `true` (default): The rule is enabled. `false`: The rule is disabled.
priority	No	The priority of the rule. The default value is 0. The system evaluates rules in descending order of priority. By default, it applies only the first rule that matches a query. If multiple matching rules have the same priority, the one created earliest is applied.

Examples

Example 1: Create a rule named testRule1. This rule terminates a query if the user is test and the 50th percentile of the historical execution time of the query pattern exceeds 60,000 ms.

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

The following result is returned:

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

Example 2: Create a rule named testRule2. This rule adds the query_priority=low hint to set the query priority to low for queries submitted by the user test from the IP addresses 10.10.10.10, 192.168.0.1, or 192.0.2.1.

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';

The following result is returned:

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

Example 3: Create a rule named testRule3 with a priority of 5 for the testResourceGroup resource group. This rule terminates all SELECT queries from the 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';

The following result is returned:

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

Modify a rule

Important

You can use WLM commands to modify rules only on AnalyticDB for MySQL clusters running kernel version 3.1.10.0 or later.

To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Syntax

wlm update_rule
id=<ruleId>
name=<ruleName>
type=query 
action=<ruleAtion> 
predicate='<property><operator><value> && <property><operator><value>'
[attrs='<ruleAttrs>']
[resource_group=<ruleResource_group>]
[description='<ruleDescription>']
[compatible_group=<compatible_group_name>]
[enabled=<ruleEnabled>]
[priority=<rulePriority>]

Note

Do not add spaces on either side of the equal sign (=).

Parameters

Parameter	Required	Description
id	No	The unique ID of the rule. This ID is used to identify the rule and cannot be modified. Note You can run the`wlm list_rule` statement to find rule IDs. For more information, see Query rules.
name	No	The rule name. To modify the name, you must identify the rule by its ID. Note The new rule name cannot be the same as the name of an existing rule. Otherwise, the existing rule with that name will be overwritten.
type	No	The rule type. Must be `query`.
action	No	The action performed when the predicate condition is met. For more information about actions, see Actions.
predicate	No	The predicate condition. Predicate syntax: Multiple conditions are joined by a logical AND operator (`&&`). The format for a single condition is: `$property $operator $value`. Supported operators include `>`, `<`, `>=`, `<=`, `=`, `!=`, and `in`. Note For more information about properties and operators, see Properties and Operators. The `in` operator requires spaces on both sides. Other operators do not require spaces. Enclose the parameter value in single quotation marks (').
attrs	No	This parameter is required when the `action` parameter is set to RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES, or BLOCK_WITH_PROB. For configuration details, see Actions. Enclose the parameter value in single quotation marks (').
resource_group	No	The name of the resource group. The default value is `user_default`. Note To view the resource groups for a cluster, log on to the AnalyticDB for MySQL console, click the target cluster ID, and then go to the Resource Groups page.
description	No	The description of the rule. Note Enclose the parameter value in single quotation marks (').
compatible_group	No	The compatibility group. By default, the system evaluates rules in descending order of priority and stops after the first match. To evaluate multiple rules for a single query, assign those rules to the same compatibility group. When a query matches a rule in a compatibility group, the system continues to evaluate other rules within that group and applies all that match.
enabled	No	Specifies whether to enable the rule. Valid values: `true` (default): The rule is enabled. `false`: The rule is disabled.
priority	No	The priority of the rule. The default value is 0. The system evaluates rules in descending order of priority. By default, it applies only the first rule that matches a query. If multiple matching rules have the same priority, the one created earliest is applied.

Examples

Example 1: Change the name of the rule with ID 2 to Rule.

wlm update_rule 
id=2
name=Rule;

The following result is returned:

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

Example 2: Modify the predicate of the testRule1 rule. This change terminates a query if the user is test and the 50th percentile of the historical execution time of the query pattern exceeds 80,000 ms.
```
wlm update_rule
name=testRule1
predicate='user=test && PATTERN_RT_P50>80000';
```
Alternatively, you can modify the rule by using its ID:
```
wlm update_rule 
id=1
predicate='user=test && PATTERN_RT_P50>80000';
```
The following result is returned:
```
+-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+
```
Example 3: Resubmit queries that match the testRule1 rule to the testgroup resource group for execution.

Before modification, the testRule1 rule terminates queries that match its predicate:
```
wlm add_rule 
name=testRule1
type=query 
action=kill 
predicate='user=test && PATTERN_RT_P50>60000';
```
Modify the testRule1 rule to resubmit matching queries to the testgroup resource group:
```
wlm update_rule
name=testRule1
action=RESUBMIT_RESOURCE_GROUP
attrs='{
    "resubmit":{
    "resource_group": "testgroup"
    }
}'
```
Note
For configuration details, see Actions.

The following result is returned:
```
+-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+
```

Query rules

Syntax

wlm list_rule
[id=<ruleID>]
[name='<ruleName>'\G]

Parameters

Parameter	Required	Description
id	No	The rule ID. Note If you do not specify the `id` or `name` parameter, the command returns all existing rules.
name	No	The rule name. Note Enclose the value in single quotation marks ('). If you do not specify the `id` or `name` parameter, the command returns all existing rules.
\G	No	Displays the query result in vertical format, with each field on a separate line.

Examples

Example 1: Query the rule with ID 1.

wlm list_rule 
id=1 \G;

Output:

            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)

Example 2: Query the rule with ID 1 and the name 'testRule4'.

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

Output:

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

Disable a workload rule

Syntax

wlm disable_rule id=<ruleID>

Parameters

Parameter	Required	Description
id	Yes	Specifies the rule ID. Note Use the `wlm list_rule` command to query rule IDs. For more information, see Query rules.

Examples

Example 1: Disable the rule with ID 1.

wlm disable_rule id=1;

Sample result:

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

Example 2: Verify that the rule is disabled.

wlm list_rule id=1\G;

Sample result:

            id: 1
            name: testRule4
     description: NULL
            type: QUERY
  resource_group: user_default
         enabled: 0
        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"}}

Enable a rule

Syntax

wlm enable_rule id=<ruleID>

Parameters

Parameter	Required	Description
id	Yes	Specifies the rule ID. Note Use the `wlm list_rule` command to query rule IDs. For more information, see Query rules.

Examples

Example 1: Enable rule 1.

wlm enable_rule id=1;

Sample result:

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

Example 2: Query rule 1.

wlm list_rule id=1\G;

Sample result:

            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"}}

Delete a rule

Syntax

wlm delete_rule id=<ruleID>

Parameters

Parameter	Required	Description
id	Yes	Specifies the rule ID. Note Use the `wlm list_rule` command to query rule IDs. For more information, see Query rules.

Examples

Example 1: Delete the rule with the ID 1.

wlm delete_rule id=1;

Example output:

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

Example 2: Verify that the rule with ID 1 is deleted.
```
wlm list_rule id=1\G;
```
Example output:
```
Empty set
```

Calculate pattern_hash

Syntax

wlm calc_pattern_hash <SQL>

Parameters

Parameter	Required	Description
SQL	Yes	A SELECT statement, either standalone or within a write query.

Examples

Calculating the pattern_hash of a query.

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

Sample result:

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

WLM help

Run the wlm command without any parameters to display the help information.

wlm;

The command returns the following result:

+---------------------------+-------------------------------------+
| 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                 |
+---------------------------+-------------------------------------+

Appendices

Appendix 1: Properties

Category	Parameter	Type	Description
Query properties	user	string	The user who submitted the query.
	source_ip	string	The source IP address of the query.
	query_task_type	numeric	The query type. Valid values: 1: SELECT 2: DELETE 3: UPDATE 4: INSERT INTO SELECT, INSERT OVERWRITE SELECT, or REPLACE INTO SELECT 5: CREATE VIEW
	query_table_list	string list	The tables scanned by the query.
	query_db_list	string list	The databases scanned by the query.
	pattern_hash	numeric	The hash value of the SQL pattern. This value identifies queries that belong to the same pattern.
	sql	string	The original SQL statement of the query. Important Supported only on clusters that run minor version 3.1.8.3 or later.
Query runtime metrics Note Use query runtime metrics to control queries after they are queued.	QUERY_PROCESS_TIME	numeric	The total time consumed by all query tasks. This metric provides an approximate measure of the computational workload of the query. Unit: ms.
	QUERY_EXECUTION_TIME	numeric	The query's execution time, excluding queuing time and the time for generating the execution plan. Unit: ms. Important The `sleep()` function runs during execution plan generation. Therefore, this metric does not include the execution time of the `sleep()` function.
	QUERY_SUBMITTED_TIME	numeric	The time when the query was created.
	QUERY_TOTAL_TASK	numeric	The total number of tasks in the physical execution plan.
	QUERY_INPUT_DATA_SIZE	numeric	The amount of data read by the query. Unit: MB.
	QUERY_SHUFFLE_DATA_SIZE	numeric	The amount of shuffled data for the query. Unit: MB.
	QUERY_OUTPUT_DATA_SIZE	numeric	The amount of output data for the query. Unit: MB.
	QUERY_PEAK_MEMORY	numeric	The peak memory usage of the query. Unit: MB.
	QUERY_TOTAL_STAGES	numeric	The total number of stages in the execution plan.
SQL pattern properties Note SQL pattern properties show the historical execution performance of queries.	PATTERN_RT_P50	numeric	The 50th percentile of the historical execution time for queries of this pattern. Unit: ms.
	PATTERN_RT_P90	numeric	The 90th percentile of the historical execution time for queries of this pattern. Unit: ms.
	PATTERN_EXECUTION_TIME_P50	numeric	The 50th percentile of the historical execution time for queries of this pattern, excluding the queuing time. Unit: ms.
	PATTERN_EXECUTION_TIME_P90	numeric	The 90th percentile of the historical execution time for queries of this pattern, excluding the queuing time. Unit: ms.
	PATTERN_WALL_TIME_P50	numeric	The 50th percentile of the historical wall time for queries of this pattern. Unit: ms.
	PATTERN_WALL_TIME_P90	numeric	The 90th percentile of the historical wall time for queries of this pattern. Unit: ms.
	PATTERN_SHUFFLE_SIZE_AVG	numeric	The average amount of shuffled data for queries of this pattern. Unit: bytes.
	PATTERN_PEAK_MEMORY_AVG	numeric	The average peak memory usage for queries of this pattern. Unit: bytes.
	PATTERN_INPUT_POSITION_AVG	numeric	The average number of input rows for queries of this pattern. Unit: rows.
	PATTERN_OUTPUT_POSITION_AVG	numeric	The average number of output rows for queries of this pattern. Unit: rows.

Appendix 2: Operators

Operator	Type	Description
>	numeric	N/A
<	numeric	N/A
=	numeric, string	N/A
>=	numeric	N/A
<=	numeric	N/A
!=	numeric, string	N/A
in	string list	Determines whether string lists intersect.
contains	string	Determines whether a string contains a specific substring. This operator is primarily used for querying original SQL statements. Important Supported only on clusters that run minor version 3.1.8.3 or later.

Appendix 3: Actions

Action	Description	Configuration
KILL	Terminates the query.	N/A
RESUBMIT_RESOURCE_GROUP	Stops a matching query in the current resource group and resubmits it to another resource group.	{ "resubmit": {"resource_group":"<resource_group_name>"}} resource_group_name: the name of the destination resource group. Note You can log in to the AnalyticDB for MySQL console, click the ID of the target cluster, and go to the Resource Groups page to view the resource groups of the cluster.
ADD_PROPERTIES	Modifies a query's properties. This action typically changes the query priority but can also modify other query properties set with hints.	{ "add_prop": {"query_priority": "<priority_level>", "force":"false"}} priority_level: The query priority. The value can be an integer or a string. If the value is an integer, the valid range is 0 to 39. A larger value indicates a higher priority. If the value is a string, the valid values are: LOWEST: The lowest priority. The corresponding integer value is 5. LOW: A low priority. The corresponding integer value is 15. NORMAL: A normal priority. The corresponding integer value is 25. HIGH: The highest priority. The corresponding integer value is 35. For more information, see Priority queue and concurrency control. force: Specifies whether to forcibly overwrite existing query properties. true (default): Overwrites existing properties. false: Does not overwrite existing properties. Note Clusters that run a minor version earlier than 3.1.9.4 do not support the `force` property. On these clusters, existing query properties are not overwritten by default. To check the minor version of your cluster, see View cluster versions.
BLOCK_WITH_PROB	Terminates a query with a specified probability before it is queued. This action is typically used for throttling.	{ "block_prob": {"prob":<prob_value>}} prob_value: The termination probability. The value must be between 0 and 1.