Configure workload management rules - AnalyticDB for MySQL

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

Your AnalyticDB for MySQL cluster is of the Data Warehouse Edition (V3.0).
The engine version of the cluster is V3.1.6.3 or later.
Note
For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.

Usage notes

Workload management rules cannot be modified by using WLM statements. If you want to modify a rule, you must delete the rule and create another one with the desired configurations.
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>]
[valid_begin=<ruleValid_begin>]
[valid_end=<ruleValid_end>]

Note

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

Parameters

Parameter	Required	Description
name	Yes	The rule name. Note The rule name must be unique among the existing rules and deleted rules.
type	Yes	The rule type. Set the parameter to query.
action	Yes	The operations 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 >, <, >=, <=, =, !=, in, and equal 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	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 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. Once 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. Once 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.
valid_begin	No	The point in time when the rule takes effect. Specify the value in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss format. Note If you specify this parameter and not the valid_end parameter, the rule takes effect at the time specified by the valid_begin parameter and is permanently effective. If you specify neither this parameter nor the valid_end parameter, the rule takes effect immediately and is permanently effective.
valid_end	No	The point in time when the rule becomes ineffective. Specify the value in the yyyy-MM-dd or yyyy-MM-dd HH:mm:ss format. Note If you specify this parameter and not the valid_begin parameter, the rule takes effect immediately and becomes ineffective at the time specified by the valid_end parameter. If you specify neither this parameter nor the valid_end parameter, the rule takes effect immediately and is permanently effective.

Examples

Example 1: Create a rule named testRule1. If the username is test and P50 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 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 && 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 for the testResourceGroup resource group. If the username is test and the query type is SELECT, the query is ended. The priority of the rule is 5, and the rule is effective from 2023-01-01 08:00:00 to 2023-01-02 08:00:00.

wlm add_rule
name=testRule3
type=query
resource_group=testResourceGroup
description='just a test'
priority=5
action=kill
valid_begin='2023-01-01 08:00:00'
valid_end='2023-01-02 08:00:00'
predicate='user=test && query_task_type=1';

Sample result:

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

Obtain information of workload management rules

Syntax

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

Parameters

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

Examples

Example 1: Obtain the information of a workload management rule whose ID is testID1.

wlm list_rule 
id=testID1 \G;

Sample result:

            id: testID1
            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.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: Obtain the information of a workload management rule whose ID is testID1 and name is testRule4.

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

Sample result:

            id: testID1
            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.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 rule ID. Note You can execute `wlm list_rule` statements to query rule IDs. For more information, see the "Obtain information of workload management rules" section of this topic.

Examples

Example 1: Disable a rule whose ID is testID1.

wlm disable_rule id=testID1;

Sample result:

+----------------------+
| result               |
+----------------------+
| disable rule testID1 |
+----------------------+

Example 2: Obtain the information of a rule whose ID is testID1.

wlm list_rule id=testID1\G;

Sample result:

            id: testID1
            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.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 rule ID. Note You can execute `wlm list_rule` statements to query rule IDs. For more information, see the "Obtain information of workload management rules" section of this topic.

Examples

Example 1: Enable the rule whose ID is testID1.

wlm enable_rule id=testID1;

Sample result:

+----------------------+
| result               |
+----------------------+
| enable rule testID1  |
+----------------------+

Example 2:

Obtain the information of a rule whose ID is testID1.

wlm list_rule id=testID1\G;

Sample result:

            id: testID1
            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.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 rule ID. Note You can execute `wlm list_rule` statements to query rule IDs. For more information, see the "Obtain information of workload management rules" section of this topic.

Examples

Example 1: Delete the rule whose ID is testID1.

wlm delete_rule id=testID1;

Sample result:

+----------------------+
| result               |
+----------------------+
| delete rule testID1  |
+----------------------+

Example 2: Obtain the information of a rule whose ID is testID1.
```
wlm list_rule id=testID1\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 methods

Execute WLM queries without any parameters.

wlm;

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

Appendixes

Appendix 1: Properties

Category	Property	Data type	Description
Query	user	String	The user that initiated the query.
	source_ip	String	The IP address from which the query is sent.
	query_task_type	Numeric	The query type. Valid values: 1: SELECT 2: DELETE 3: UPDATE 4: INSERT INTO SELECT or INSERT OVERWRITE SELECT 5: CREATE VIEW
	query_table_list	String list	The tables that the query involves.
	query_db_list	String list	The databases that the query involves.
	pattern_hash	Numeric	The hash value of the pattern to which the query belongs.
	sql	String	The original SQL statement of the query. Important This property is supported only for 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. 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 input data for 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	P50 of the historical execution duration of the SQL pattern. Unit: milliseconds.
	PATTERN_RT_P90	Numeric	P90 of the historical execution duration of the SQL pattern. Unit: milliseconds.
	PATTERN_EXECUTION_TIME_P50	Numeric	P50 of the historical execution duration of the SQL pattern, excluding queue time. Unit: milliseconds.
	PATTERN_EXECUTION_TIME_P90	Numeric	P90 of the historical execution duration of the SQL pattern, excluding queue time. Unit: milliseconds.
	PATTERN_WALL_TIME_P50	Numeric	P50 of the wall time of the SQL pattern. Unit: milliseconds.
	PATTERN_WALL_TIME_P90	Numeric	P90 of the wall time of the SQL pattern. Unit: milliseconds.
	PATTERN_SHUFFLE_SIZE_AVG	Numeric	The average amount of shuffled data for the SQL pattern. Unit: bytes.
	PATTERN_PEAK_MEMORY_AVG	Numeric	The average peak memory usage of the SQL pattern. Unit: bytes.
	PATTERN_INPUT_POSITION_AVG	Numeric	The average number of input rows of data for the SQL pattern.
	PATTERN_OUTPUT_POSITION_AVG	Numeric	The average number of output rows of data for the SQL pattern.

Appendix 2: Operators

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

Appendix 3: Actions

Action	Description	Configuration
KILL	Ends the query.	N/A
LOG	Records the query.	N/A
RESUBMIT_RESOURCE_GROUP	Stops the execution of the query in the current resource group and executes the query in another resource group.	{ "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	Adds properties to the query. It can be used to change the priority of the query or add hints to the query.	{ "add_prop": {"query_priority": "<Priority level>"}} 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 queues and concurrency control.
BLOCK_WITH_PROB	Ends the query at a specified 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.