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.
NoteFor 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>]
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:
Note
|
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:
|
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
|
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
|
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
|
\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 |
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 |
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 |
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:
| |
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.
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. |