All Products
Search
Document Center

AnalyticDB:Configure workload management rules

Last Updated:Sep 03, 2024

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.