AnalyticDB for MySQL provides a workload management module. You can use Workload Management (WLM) commands to configure rules that provide fine-grained control over different workloads and improve the overall running status of your cluster. This topic describes how to use WLM commands to create, modify, enable, disable, and delete workload management rules.
Prerequisites
The kernel version of your AnalyticDB for MySQL cluster must be 3.1.6.3 or later.
To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.
Usage notes
You can use WLM commands to modify rules only on clusters whose kernel version is 3.1.10.0 or later.
AnalyticDB for MySQL uses a soft delete policy for workload management rules. This means that you cannot reuse the name of a deleted rule for a new 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>]Do not add spaces around 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 value to query. |
action | Yes | The actions that are performed when a query meets the conditions of the rule. For more information about actions, see Actions. |
predicate | Yes | The predicate condition. Syntax of predicate:
Note
|
attrs | No | The attrs parameter is required when action is set to RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES, or BLOCK_WITH_PROB. For information about how to specify attrs, see the configuration method in Actions. Note You must enclose the value of this parameter in single quotation marks ('). |
resource_group | No | The name of the resource group. The default value is user_default. Note You can log on to the AnalyticDB for MySQL console, click the ID of the cluster, and then go to the Resource Group Management page to view the resource groups of the cluster. |
description | No | The description. Note You must enclose the value of this parameter in single quotation marks ('). |
compatible_group | No | The compatible group. By default, rules are matched in descending order of priority. When a rule is matched, the matching process stops. You can also set a compatible group for rules that need to be matched at the same time. When rules are matched, the rule with the highest priority is matched first. If the matched rule has a compatible group, the remaining rules in the compatible group are matched at the same time if they can be matched. |
enabled | No | Specifies whether to enable the rule. Valid values:
|
priority | No | The priority of the rule. The default value is 0. Each time rules are matched, only the rule with the highest priority is matched. If multiple rules have the same priority, the earliest created rule is matched first. |
Examples
Example 1: Create a rule named testRule1. This rule terminates a query if the username is test and the 50th percentile of the historical running time for 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. For queries where the username is test and the user IP address is 10.10.10.10, 192.168.0.1, or 192.0.2.1, this rule adds the
query_priority=lowhint to set the query priority to low.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 for the testResourceGroup resource group with a priority of 5. This rule terminates all select queries where the username is 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
You can use WLM commands to modify rules only on clusters whose kernel version is 3.1.10.0 or later.
To view and update the minor version of an AnalyticDB for MySQL cluster, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.
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>]Do not add spaces around the equal sign (=).
Parameters
Parameter | Required | Description |
id | No | The ID of the rule. The rule ID is unique, is used to match the corresponding rule, and cannot be modified. Note You can execute the |
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 modified rule cannot have the same name as a created rule. Otherwise, the created rule with the same name is overwritten. |
type | No | The type of the rule. Set the value to query. |
action | No | The actions that are performed when a query meets the conditions of the rule. For more information about actions, see Actions. |
predicate | No | The predicate condition. Syntax of predicate:
Note
|
attrs | No | The attrs parameter is required when action is set to RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES, or BLOCK_WITH_PROB. For information about how to specify attrs, see the configuration method in Actions. You must enclose the value of this parameter in single quotation marks ('). |
resource_group | No | The name of the resource group. The default value is user_default. Note You can log on to the AnalyticDB for MySQL console, click the ID of the cluster, and then go to the Resource Group Management page to view the resource groups of the cluster. |
description | No | The description. Note You must enclose the value of this parameter in single quotation marks ('). |
compatible_group | No | The compatible group. By default, rules are matched in descending order of priority. When a rule is matched, the matching process stops. You can also set a compatible group for rules that need to be matched at the same time. When rules are matched, the rule with the highest priority is matched first. If the matched rule has a compatible group, the remaining rules in the compatible group are matched at the same time if they can be matched. |
enabled | No | Specifies whether to enable the rule. Valid values:
|
priority | No | The priority of the rule. The default value is 0. Each time rules are matched, only the rule with the highest priority is matched. If multiple rules have the same priority, the earliest created rule is matched first. |
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 conditions of the testRule1 rule. The rule now terminates a query if the username is test and the 50th percentile of the historical running time for the query pattern exceeds 80,000 ms.
wlm update_rule name=testRule1 predicate='user=test && PATTERN_RT_P50>80000';Alternatively, you can use the ID of the testRule1 rule to modify the rule:
wlm update_rule id=1 predicate='user=test && PATTERN_RT_P50>80000';The following result is returned:
+-----------------+ | result | +-----------------+ | update rule 1 | +-----------------+Example 3: Deliver queries that meet the conditions of the testRule1 rule to the testgroup resource group for execution.
Before the modification, the testRule1 rule terminates queries that meet the conditions:
wlm add_rule name=testRule1 type=query action=kill predicate='user=test && PATTERN_RT_P50>60000';Modify the testRule1 rule to deliver all 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" } }'NoteFor information about how to specify attrs, see the configuration method in 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 ID of the rule. Note If you do not specify the ID and name, all rules that are not deleted are queried, including their rule IDs. |
name | No | The name of the rule. Note
|
\G | No | Prints the query result by column. Each field is printed on a separate line. |
Examples
Example 1: Query the rule with the ID testID1.
wlm list_rule id=1 \G;The following result is returned:
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 the ID testID1 and the name testRule4.
mysql> wlm list_rule id=1 name='testRule4' \G;The following result is returned:
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 rule
Syntax
wlm disable_rule id=<ruleID>Parameters
Parameter | Required | Description |
id | Yes | The ID of the rule. Note You can execute the |
Examples
Example 1: Disable the rule with the ID testID1.
wlm disable_rule id=1;The following result is returned:
+------------------+ | result | +------------------+ | disable rule 1 | +------------------+Example 2: Query the rule with the ID testID1.
wlm list_rule id=1\G;The following result is returned:
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 | The ID of the rule. Note You can execute the |
Examples
Example 1: Enable the rule with the ID testID1.
wlm enable_rule id=1;The following result is returned:
+----------------+ | result | +----------------+ | enable rule 1 | +----------------+Example 2: Query the rule with the ID testID1.
wlm list_rule id=1\G;The following result is returned:
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 | The ID of the rule. Note You can execute the |
Examples
Example 1: Delete the rule with the ID testID1.
wlm delete_rule id=1;The following result is returned:
+----------------+ | result | +----------------+ | delete rule 1 | +----------------+Example 2: Query the rule with the ID testID1.
wlm list_rule id=1\G;The following result is returned:
Empty set
Calculate pattern_hash
Syntax
wlm calc_pattern_hash <SQL>Parameters
Parameter | Required | Description |
SQL | Yes | A SELECT statement or a write statement that contains SELECT. |
Example
Calculate the pattern_hash for a query statement.
wlm calc_pattern_hash select * from t where a=1 and b=2;The following result is returned:
+-----------------------------------+----------------------+
| raw_sql | pattern_hash |
+-----------------------------------+----------------------+
| select * from t where a=1 and b=2 | -4759960226441980963 |
+-----------------------------------+----------------------+Print WLM help information
If you execute the wlm command without any parameters, the help information is displayed.
wlm;The following result is returned:
+---------------------------+-------------------------------------+
| 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 | Field | Data type | Description |
Query properties | user | String | The user who sends 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:
| |
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 query pattern, which is used to identify queries of the same pattern. | |
sql | String | The original SQL statement of the query. Important This property is supported only for clusters with a kernel version of 3.1.8.3 or later. | |
Query runtime metrics Note Query runtime metrics are used to control queries after they enter a queue. | QUERY_PROCESS_TIME | Numeric | The total time consumed by all tasks in the query process. This metric can be used to roughly measure the computing workload of a query. Unit: ms. |
QUERY_EXECUTION_TIME | Numeric | The running time of the query, excluding the queuing time and the time consumed to generate the execution plan. Unit: ms. Important The | |
QUERY_SUBMITTED_TIME | Numeric | The timestamp when the query is created. | |
QUERY_TOTAL_TASK | Numeric | The total number of tasks in the physical execution plan of the query. | |
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 of the query. Unit: MB. | |
QUERY_TOTAL_STAGES | Numeric | The total number of stages in the execution plan of the query. | |
Query pattern properties Note Query pattern properties reflect the historical execution of queries. | PATTERN_RT_P50 | Numeric | The 50th percentile of the historical running time for queries of the same pattern. Unit: ms. |
PATTERN_RT_P90 | Numeric | The 90th percentile of the historical running time for queries of the same pattern. Unit: ms. | |
PATTERN_EXECUTION_TIME_P50 | Numeric | The 50th percentile of the historical running time (excluding queuing time) for queries of the same pattern. Unit: ms. | |
PATTERN_EXECUTION_TIME_P90 | Numeric | The 90th percentile of the historical running time (excluding queuing time) for queries of the same pattern. Unit: ms. | |
PATTERN_WALL_TIME_P50 | Numeric | The 50th percentile of the historical wall time for queries of the same pattern. Unit: ms. | |
PATTERN_WALL_TIME_P90 | Numeric | The 90th percentile of the historical wall time for queries of the same pattern. Unit: ms. | |
PATTERN_SHUFFLE_SIZE_AVG | Numeric | The average amount of historical shuffled data for queries of the same pattern. Unit: byte. | |
PATTERN_PEAK_MEMORY_AVG | Numeric | The average historical peak memory for queries of the same pattern. Unit: byte. | |
PATTERN_INPUT_POSITION_AVG | Numeric | The average number of historical input records for queries of the same pattern. Unit: row. | |
PATTERN_OUTPUT_POSITION_AVG | Numeric | The average number of historical output records for queries of the same pattern. Unit: row. |
Appendix 2: Operators
Operator | Applicable data type | Description |
> | Numeric | None |
< | Numeric | None |
= | Numeric, string | None |
>= | Numeric | None |
<= | Numeric | None |
!= | Numeric, string | None |
in | String list | Checks whether string lists have an intersection. |
contains | String | Checks whether a string contains specific strings. This is mainly used to query the original SQL statement. Important This operator is supported only for clusters with a kernel version of 3.1.8.3 or later. |
Appendix 3: Actions
Control Measures | Description | attrs configuration method |
KILL | Terminates the query. | None |
RESUBMIT_RESOURCE_GROUP | Stops the query that meets the conditions in the current resource group and submits it to another resource group for execution. | { "resubmit": {"resource_group":"<resource_group_name>"}} resource_group_name: the name of the resource group. Note You can log on to the AnalyticDB for MySQL console, click the ID of the cluster, and then go to the Resource Group Management page to view the resource groups of the cluster. |
ADD_PROPERTIES | Modifies the properties of the query. This is usually used to modify the query priority. You can also modify other query properties configured by hints. | { "add_prop": {"query_priority": "<Priority level>", "force":"false"}}
|
BLOCK_WITH_PROB | Terminates the query with a certain probability before the query enters the queue. This is usually used for throttling. | { "block_prob": {"prob":<prob_value>}} prob_value: the probability of terminating the query. The value ranges from 0 to 1. |