Configure workload management rules - AnalyticDB - Alibaba Cloud Documentation Center

AnalyticDB for MySQL provides workload management mechanisms that can be used to configure workload management rules, implement finer-grained cluster management, and improve cluster performance. This topic describes how to configure workload management rules.

Prerequisites

Before you configure workload management rules in AnalyticDB for MySQL, make sure that the following requirements are met:

An AnalyticDB for MySQL Data Warehouse Edition cluster is created.
The minor version of the cluster is 3.1.6.3 or later.
Note
For information about how to view and update the minor version of an AnalyticDB for MySQL cluster, see Update the minor version of a cluster.

Usage notes

You can use the WLM syntax to modify workload management rules only for AnalyticDB for MySQL clusters of V3.1.10.0 or later.
AnalyticDB for MySQL adopts a soft delete policy for workload management rules. New rules cannot use the same names as the rules that have been deleted.

Create a workload management 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

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

Parameters

Parameter	Required	Description
name	Yes	The name of the rule. Note The rule name must be unique among the existing rules and deleted rules.
type	Yes	The type of the rule. Set the parameter to query.
action	Yes	The actions that are performed when a query meets the conditions of the rule. For more information about actions, see the "Appendix 3: Actions" section of this topic.
predicate	Yes	The predicate conditions of the rule. Syntax: Conditions in a predicate have logical AND relations. Specify the elements of a condition in the following format: $Property $Operator $Value. Operators such as >, <, >=, <=, =, !=, and in are supported. Note For more information, see the "Appendix 1: Properties" and "Appendix 2: Operators" sections of this topic. Spaces must be added on both sides of an in operator and cannot be added for the other operators. Enclose the value of this parameter with single quotation marks (').
attrs	No	The attributes of the rule. You must specify this parameter when the value of the action parameter is RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES, or BLOCK_WITH_PROB. For information about how to specify the attrs parameter, see the "Appendix 3: Actions" section of this topic. Note Enclose the value of this parameter with single quotation marks (').
resource_group	No	The name of the resource group. The default value is user_default. Note To obtain the resource group name of a cluster, log on to the AnalyticDB for MySQL console, click the cluster ID, and then go to the Resource Groups page.
description	No	The description of the rule. Note Enclose the value of this parameter with single quotation marks (').
compatible_group	No	The compatibility groups. The system matches a query against workload management rules one by one in descending order of rule priority. When a rule is matched, the system stops matching and performs the actions specified in the rule. If you want multiple rules to be matched, you can add the rules to a compatibility group. When a rule is matched, the system also checks the other rules in the same compatibility group and takes the actions specified in the other matching rules regardless of their priorities.
enabled	No	Specifies whether to enable the rule. Valid values: true (default) false
priority	No	The priority of the rule. Default value: 0. The system matches a query against workload management rules one by one in descending order of rule priority. When a rule is matched, the system stops matching and performs the actions specified in the rule. If multiple rules with the same priority are matched, the one that was created the earliest takes effect.

Examples

Example 1: Create a rule named testRule1. If the username is test and the 50th percentile of the historical execution duration of the SQL pattern is longer than 60,000 ms, the query is ended.

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

Sample result:

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

Example 2: Create a rule named testRule2. If the username is test and the user IP address is 10.10.10.10, 192.168.0.1, or 192.0.2.1, the query_priority=low hint is added to the query.

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

Sample result:

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

Example 3: Create a rule named testRule3 whose priority is 5 for the testResourceGroup resource group. If the username is test and the query type is SELECT, the query is ended.

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

Sample result:

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

Modify a workload management rule

Important

You can use the WLM syntax to modify workload management rules only for AnalyticDB for MySQL clusters of V3.1.10.0 or later.

For information about how to view the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster? To update the minor version of a cluster, contact technical support.

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

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

Parameters

Parameter	Required	Description
id	No	The ID of the rule. The rule ID is unique and is used to match the corresponding rule. The rule ID cannot be changed. Note You can execute the `WLM list_rule` statement to query rule IDs. For more information, see the "Query the information about workload management rules" section of this topic.
name	No	The name of the rule. When you change the rule name, you must use the rule ID to match the corresponding rule. Note The new rule name cannot be the same as the name of an existing rule. Otherwise, the existing rule is overwritten.
type	No	The type of the rule. Set the parameter to query.
action	No	The actions that are performed when a query meets the conditions of the rule. For more information about actions, see the "Appendix 3: Actions" section of this topic.
predicate	No	The predicate conditions of the rule. Syntax: Conditions in a predicate have logical AND relations. Specify the elements of a condition in the following format: $Property $Operator $Value. Operators such as >, <, >=, <=, =, !=, and in are supported. Note For more information, see the "Appendix 1: Properties" and "Appendix 2: Operators" sections of this topic. Spaces must be added on both sides of an in operator and cannot be added for the other operators. Enclose the value of this parameter with single quotation marks (').
attrs	No	The attributes of the rule. You must specify this parameter when the value of the action parameter is RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES, or BLOCK_WITH_PROB. For information about how to specify the attrs parameter, see the "Appendix 3: Actions" section of this topic. Enclose the value of this parameter with single quotation marks (').
resource_group	No	The name of the resource group. The default value is user_default. Note To obtain the resource group name of a cluster, log on to the AnalyticDB for MySQL console, click the cluster ID, and then go to the Resource Groups page.
description	No	The description of the rule. Note Enclose the value of this parameter with single quotation marks (').
compatible_group	No	The compatibility groups. The system matches a query against workload management rules one by one in descending order of rule priority. When a rule is matched, the system stops matching and performs the actions specified in the rule. If you want multiple rules to be matched, you can add the rules to a compatibility group. When a rule is matched, the system also checks the other rules in the same compatibility group and takes the actions specified in the other matching rules regardless of their priorities.
enabled	No	Specifies whether to enable the rule. Valid values: true (default) false
priority	No	The priority of the rule. Default value: 0. The system matches a query against workload management rules one by one in descending order of rule priority. When a rule is matched, the system stops matching and performs the actions specified in the rule. If multiple rules with the same priority are matched, the one that was created the earliest takes effect.

Examples

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

wlm update_rule 
id=2
name=Rule;

Sample result:

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

Example 2: Modify the conditions of the testRule1 rule. If the username is test and the 50th percentile of the historical execution duration of the SQL pattern is longer than 80,000 ms, the query is ended.
```
wlm update_rule
name=testRule1
predicate='user=test && PATTERN_RT_P50>80000';
```
Alternatively, use the corresponding rule ID to modify the testRule1 rule.
```
wlm update_rule 
id=1
predicate='user=test && PATTERN_RT_P50>80000';
```
Sample result:
```
+-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+
```
Example 3: Resubmit all queries that meet the requirements of the testRule1 rule to the testgroup resource group for execution.
Create a rule named testRule1 whose action is set to KILL.
```
wlm add_rule 
name=testRule1
type=query 
action=kill 
predicate='user=test && PATTERN_RT_P50>60000';
```
Modify the testRule1 rule to resubmit queries from the current resource group to the testgroup resource group for execution.
```
wlm update_rule
name=testRule1
action=RESUBMIT_RESOURCE_GROUP
attrs='{
    "resubmit":{
    "resource_group": "testgroup"
    }
}'
```
Note
For information about how to specify the attrs parameter, see the "Appendix 3: Actions" section of this topic.
Sample result:
```
+-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+
```

Query the information about workload management rules

Syntax

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

Parameters

Parameter	Required	Description
id	No	The ID of the rule. Note If you do not specify the id and name parameters, information about all existing rules, including their rule IDs, is returned.
name	No	The name of the rule. Note Enclose the value of this parameter with single quotation marks ('). If you do not specify the id and name parameters, information about all existing rules, including their rule names, is returned.
\G	No	Displays each returned parameter in a separate line.

Examples

Example 1: Query the information about the rule whose ID is testID1.

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"}}
1 row in set (0.11 sec)

Example 2: Query the information about the rule whose ID is testID1 and name is testRule4.

mysql> wlm list_rule 
id=1 
name='testRule4' \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"}}
1 row in set (0.09 sec)

Disable a workload management rule

Syntax

wlm disable_rule id=<ruleID>

Parameters

Parameter	Required	Description
id	Yes	The ID of the rule. Note You can execute the `WLM list_rule` statement to query rule IDs. For more information, see the "Query the information about workload management rules" section of this topic.

Examples

Example 1: Disable the rule whose ID is testID1.

wlm disable_rule id=1;

Sample result:

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

Example 2: Query the information about the rule whose ID is testID1.

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 workload management rule

Syntax

wlm enable_rule id=<ruleID>

Parameters

Parameter	Required	Description
id	Yes	The ID of the rule. Note You can execute the `WLM list_rule` statement to query rule IDs. For more information, see the "Query the information about workload management rules" section of this topic.

Examples

Example 1: Enable the rule whose ID is testID1.

wlm enable_rule id=1;

Sample result:

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

Example 2: Query the information about the rule whose ID is testID1.

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 workload management rule

Syntax

wlm delete_rule id=<ruleID>

Parameters

Parameter	Required	Description
id	Yes	The ID of the rule. Note You can execute the `WLM list_rule` statement to query rule IDs. For more information, see the "Query the information about workload management rules" section of this topic.

Examples

Example 1: Delete the rule whose ID is testID1.

wlm delete_rule id=1;

Sample result:

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

Example 2: Query the information about the rule whose ID is testID1.
```
wlm list_rule id=1\G;
```
Sample result:
```
Empty set
```

Obtain the pattern hash value of a query

Syntax

wlm calc_pattern_hash <SQL>

Parameters

Parameter	Required	Description
SQL	Yes	A SELECT query or a write query that contains SELECT statements.

Examples

Obtain the pattern hash value 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 |
+-----------------------------------+----------------------+

Query a list of WLM statements

Execute the WLM statement without parameters.

wlm;

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

Appendixes

Appendix 1: Properties

Category	Property	Data type	Description
Query	user	String	The user that submits the query.
	source_ip	String	The IP address from which the query is sent.
	query_task_type	Numeric	The type of the query. 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 that are involved in the query.
	query_db_list	String list	The databases that are involved in the query.
	pattern_hash	Numeric	The hash value of the SQL pattern to which the query belongs.
	sql	String	The original SQL statement of the query. Important This property is supported only for AnalyticDB for MySQL clusters of V3.1.8.3 or later.
Runtime Note Properties of this category are used to identify queries based on their execution statistics.	QUERY_PROCESS_TIME	Numeric	The total amount of CPU time consumed by all tasks of the query. The value can indicate an approximate amount of computing resources that are required by the query. Unit: milliseconds.
	QUERY_EXECUTION_TIME	Numeric	The execution duration of the query, excluding the queuing time. Unit: milliseconds.
	QUERY_SUBMITTED_TIME	Numeric	The time when the query is submitted.
	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.
Pattern Note Properties of this category are used to identify queries based on their historical execution.	PATTERN_RT_P50	Numeric	The 50th percentile of the historical execution duration for queries of the SQL pattern. Unit: milliseconds.
	PATTERN_RT_P90	Numeric	The 90th percentile of the historical execution duration for queries of the SQL pattern. Unit: milliseconds.
	PATTERN_EXECUTION_TIME_P50	Numeric	The 50th percentile of the historical execution duration for queries of the SQL pattern, excluding the queuing time. Unit: milliseconds.
	PATTERN_EXECUTION_TIME_P90	Numeric	The 90th percentile of the historical execution duration for queries of the SQL pattern, excluding the queuing time. Unit: milliseconds.
	PATTERN_WALL_TIME_P50	Numeric	The 50th percentile of the wall time for queries of the SQL pattern. Unit: milliseconds.
	PATTERN_WALL_TIME_P90	Numeric	The 90th percentile of the wall time for queries of the SQL pattern. Unit: milliseconds.
	PATTERN_SHUFFLE_SIZE_AVG	Numeric	The average amount of shuffled data for queries of the SQL pattern. Unit: bytes.
	PATTERN_PEAK_MEMORY_AVG	Numeric	The average peak memory usage for queries of the SQL pattern. Unit: bytes.
	PATTERN_INPUT_POSITION_AVG	Numeric	The average number of input rows of data for queries of the SQL pattern.
	PATTERN_OUTPUT_POSITION_AVG	Numeric	The average number of output rows of data for queries of the SQL pattern.

Appendix 2: Operators

Operator	Data type	Description
>	Numeric	N/A
<	Numeric	N/A
=	Numeric and string	N/A
>=	Numeric	N/A
<=	Numeric	N/A
!=	Numeric and string	N/A
in	String list	Determines whether two string lists intersect.
contains	String	Determines whether a string contains another string. This operator is used to query the original SQL statement. Important This operator is supported only for AnalyticDB for MySQL clusters of V3.1.8.3 or later.

Appendix 3: Actions

Action	Description	Configuration method of the attrs parameter
KILL	Ends the query.	N/A
RESUBMIT_RESOURCE_GROUP	Stops the execution of the query that meets requirements in the current resource group and resubmits the query to another resource group for execution.	{ "resubmit": {"resource_group":"<resource_group_name>"}} resource_group_name: the name of the destination resource group. Note To obtain the resource group name of a cluster, log on to the AnalyticDB for MySQL console, click the cluster ID, and then go to the Resource Groups page.
ADD_PROPERTIES	Modifies properties of the query. It can be used to change the priority of the query or modify other query properties of hints.	{ "add_prop": {"query_priority": "<Priority level>", "force":"false"}} Priority level: the query priority. The value of this parameter can be an integer or a string. Valid values when the value is an integer: 0 to 39. A greater value specifies a higher priority. Valid values when the value is a string: LOWEST: the lowest priority (5). LOW: the low priority (15). NORMAL: the normal priority (25). HIGH: the highest priority (35). For more information, see Priority queue and concurrency of interactive resource groups. force: specifies whether to forcibly overwrite existing query properties. Valid values: true (default) false Note By default, the value of the force field is set to false for AnalyticDB for MySQL clusters of versions earlier than 3.1.9.4. For information about how to query the minor version of an AnalyticDB for MySQL cluster, see How do I view the minor version of a cluster?
BLOCK_WITH_PROB	Ends the query at a specific probability before the query enters the queue. It can be used for throttling.	{ "block_prob": {"prob":<prob_value>}} prob_value: the probability. Valid values: 0 to 1.