DocsICP FilingConsole
Documentation
Home Page AnalyticDB AnalyticDB for MySQL User Guide Cluster management Resource group and load management Workload management Configure workload management rules

Configure workload management rules

Updated at:
Copy as MD
My Favorites

AnalyticDB for MySQL provides workload management to control database workloads. Use Workload Manager commands to configure rules for fine-grained control of different workloads and improve overall cluster performance. This topic describes how to configure workload management rules.

Prerequisites

AnalyticDB for MySQL clusters must run minor version 3.1.6.3 or later.

Note

In the AnalyticDB for MySQL console, go to the Cluster Information page. In the Configuration Information section, view and update the minor version.

Usage notes

  • Only clusters that run minor version 3.1.10.0 or later support modifying rules by using WLM commands.

  • AnalyticDB for MySQL uses a soft delete policy for rules and requires unique rule names. A new rule cannot use the name of a deleted 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>]
Note

Do not add spaces on either side of the equal sign (=).

Parameters

Parameter

Required

Description

name

Yes

The rule name.

Note

Rule names must be unique. A new rule cannot use the name of a deleted rule.

type

Yes

The rule type. Set this parameter to query.

action

Yes

The action that workload management performs when a query meets the matching conditions.

For more information about the supported actions, see Actions.

predicate

Yes

The predicate conditions.

Syntax of the predicate parameter:

  • Join matching conditions with && (logical AND).

  • Each condition consists of a property, an operator, and a value: <property><operator><value>.

  • Supported operators include >, <, >=, <=, =, !=, and in.

Note

  • For more information, see Properties and Operators.

  • Add spaces on both sides of the in operator so that it can be recognized. Other operators do not require surrounding spaces.

  • Enclose the value in single quotes.

attrs

No

The attrs parameter is required when action is RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES or BLOCK_WITH_PROB.

For information about configuring attrs, see Actions.

Note

Enclose the value in single quotes.

resource_group

No

The resource group name. Default: user_default.

Note

Log on to the AnalyticDB for MySQL console, click the target cluster ID, and view the resource groups on the Resource Management page.

description

No

The rule description.

Note

Enclose the value in single quotes.

compatible_group

No

The compatibility group.

By default, rules are evaluated in descending priority order, and evaluation stops at the first match. To apply multiple matching rules, place them in the same compatibility group. The highest-priority matching rule is selected first. If it belongs to a compatibility group, the other matching rules in that group are also applied.

enabled

No

Specifies whether to enable the rule. Valid values:

  • true (default): enables the rule.

  • false: disables the rule.

priority

No

The rule priority. Higher-priority rules take precedence. Default: 0.

By default, only the highest-priority matching rule is applied. If priorities are equal, the rule created earlier takes precedence.

Examples

  • Example 1: Create a rule named testRule1 to terminate queries submitted by user test when the historical 50th-percentile execution time for the query pattern exceeds 60000 ms. 

    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 for queries submitted by user test from 10.10.10.10, 192.168.0.1, or 192.0.2.1. Add the query_priority=low hint 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';

    Sample result:

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

  • Example 3: Create a rule named testRule3 with priority 5 for the testResourceGroup resource group. The rule terminates all SELECT queries submitted by user 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';

    Sample result:

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

Modify a rule

Important

Only clusters that run minor version 3.1.10.0 or later support modifying rules by using WLM commands.

In the AnalyticDB for MySQL console, go to the Cluster Information page. In the Configuration Information section, view and update the minor version.

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

Do not add spaces on either side of the equal sign (=).

Parameters

Parameter

Required

Description

id

No

The rule ID. The ID uniquely identifies a rule and cannot be changed.

Note

Run wlm list_rule to obtain the rule ID. For more information, see List rules.

name

No

The rule name. To rename a rule, identify it by its rule ID.

Note

Do not rename a rule to the name of an existing rule. Otherwise, the existing rule with that name is overwritten.

type

No

The rule type. Set this parameter to query.

action

No

The action that workload management performs when a query meets the matching conditions.

For more information about the supported actions, see Actions.

predicate

No

The predicate conditions.

Syntax of the predicate parameter:

  • Join matching conditions with && (logical AND).

  • Each condition consists of a property, an operator, and a value: <property><operator><value>.

  • Supported operators include >, <, >=, <=, =, !=, and in.

Note

  • For more information, see Properties and Operators.

  • Add spaces on both sides of the in operator so that it can be recognized. Other operators do not require surrounding spaces.

  • Enclose the value in single quotes.

attrs

No

The attrs parameter is required when action is RESUBMIT_RESOURCE_GROUP, ADD_PROPERTIES or BLOCK_WITH_PROB.

For information about configuring attrs, see Actions.

Enclose the value in single quotes.

resource_group

No

The resource group name. Default: user_default.

Note

Log on to the AnalyticDB for MySQL console, click the target cluster ID, and view the resource groups on the Resource Management page.

description

No

The rule description.

Note

Enclose the value in single quotes.

compatible_group

No

The compatibility group.

By default, rules are evaluated in descending priority order, and evaluation stops at the first match. To apply multiple matching rules, place them in the same compatibility group. The highest-priority matching rule is selected first. If it belongs to a compatibility group, the other matching rules in that group are also applied.

enabled

No

Specifies whether to enable the rule. Valid values:

  • true (default): enables the rule.

  • false: disables the rule.

priority

No

The rule priority. Higher-priority rules take precedence. Default: 0.

By default, only the highest-priority matching rule is applied. If priorities are equal, the rule created earlier takes precedence.

Examples

  • Example 1: Rename the rule with ID 2 to Rule. 

    wlm update_rule 
id=2
name=Rule;

    Sample result:

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

  • Example 2: Modify testRule1 to terminate queries submitted by user test when the historical 50th-percentile execution time for the query pattern exceeds 80000 ms. 

    wlm update_rule
name=testRule1
predicate='user=test && PATTERN_RT_P50>80000';

    Alternatively, modify testRule1 by its rule ID:

    wlm update_rule 
id=1
predicate='user=test && PATTERN_RT_P50>80000';

    Sample result:

    +-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+

  • Example 3: Resubmit queries that match testRule1 to the testgroup resource group to continue execution.

    Before the change, testRule1 terminates queries that meet its conditions:

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

    Modify testRule1 to resubmit matching queries from the current resource group to testgroup:

    wlm update_rule
name=testRule1
action=RESUBMIT_RESOURCE_GROUP
attrs='{
    "resubmit":{
    "resource_group": "testgroup"
    }
}'
    Note

    For information about configuring attrs, see Actions.

    Sample result:

    +-----------------+
| result          |
+-----------------+
| update rule 1   |
+-----------------+

List rules

Syntax

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

Parameters

Parameter

Required

Description

id

No

The rule ID.

Note

If neither id nor name is specified, all rules that have not been deleted are returned, including their IDs.

name

No

The rule name.

Note

  • Enclose the value in single quotes.

  • If neither id nor name is specified, all rules that have not been deleted are returned, including their names.

\G

No

Displays the result vertically, with each field on a separate line.

Examples

  • Example 1: Query the rule with ID 1. 

    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 rule with ID 1 and name 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 rule

Syntax

wlm disable_rule id=<ruleID>

Parameters

Parameter

Required

Description

id

Yes

The rule ID.

Note

Run wlm list_rule to obtain the rule ID. For more information, see List rules.

Examples

  • Example 1: Disable the rule with ID 1. 

    wlm disable_rule id=1;

    Sample result:

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

  • Example 2: Query the rule with ID 1. 

    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 rule

Syntax

wlm enable_rule id=<ruleID>

Parameters

Parameter

Required

Description

id

Yes

The rule ID.

Note

Run wlm list_rule to obtain the rule ID. For more information, see List rules.

Examples

  • Example 1: Enable the rule with ID 1. 

    wlm enable_rule id=1;

    Sample result:

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

  • Example 2: Query the rule with ID 1. 

    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 rule

Syntax

wlm delete_rule id=<ruleID>

Parameters

Parameter

Required

Description

id

Yes

The rule ID.

Note

Run wlm list_rule to obtain the rule ID. For more information, see List rules.

Examples

  • Example 1: Delete the rule with ID 1. 

    wlm delete_rule id=1;

    Sample result:

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

  • Example 2: Query the rule with ID 1. 

    wlm list_rule id=1\G;

    Sample result:

    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.

Examples

Calculate the pattern_hash 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 |
+-----------------------------------+----------------------+

Display WLM help

Run wlm without parameters to display help information. 

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 properties

user

String

The user who submitted the query.

source_ip

String

The IP address from which the query was submitted.

query_task_type

Numeric

The query type. Valid values:

  • 1: SELECT

  • 2: DELETE

  • 3: UPDATE

  • 4: INSERT INTO SELECT/INSERT OVERWRITE SELECT/REPLACE INTO SELECT

  • 5: CREATE VIEW

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, used to identify queries with the same pattern.

sql

String

The original SQL statement of the query.

Important

This property is supported only on clusters that run minor version 3.1.8.3 or later.

Query runtime metrics

Note

Query runtime metrics control queries after they enter the queue.

QUERY_PROCESS_TIME

Numeric

The sum of the time spent on all tasks in the query. This metric roughly measures the query workload. Unit: ms.

QUERY_EXECUTION_TIME

Numeric

The query execution time, excluding queueing time and execution plan generation time. Unit: ms.

Important

sleep() is evaluated during execution plan generation. Therefore, the query execution time excludes the time spent executing sleep().

QUERY_SUBMITTED_TIME

Numeric

The timestamp when the query was created.

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 shuffle data generated by the query. Unit: MB.

QUERY_OUTPUT_DATA_SIZE

Numeric

The amount of data output by 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 query execution plan.

Query pattern properties

Note

Query pattern properties reflect historical execution statistics for queries with the same pattern.

PATTERN_RT_P50

Numeric

The 50th percentile of historical execution times for queries with the same pattern. Unit: ms.

PATTERN_RT_P90

Numeric

The 90th percentile of historical execution times for queries with the same pattern. Unit: ms.

PATTERN_EXECUTION_TIME_P50

Numeric

The 50th percentile of historical execution times, excluding queueing time, for queries with the same pattern. Unit: ms.

PATTERN_EXECUTION_TIME_P90

Numeric

The 90th percentile of historical execution times, excluding queueing time, for queries with the same pattern. Unit: ms.

PATTERN_WALL_TIME_P50

Numeric

The 50th percentile of historical wall times for queries with the same pattern. Unit: ms.

PATTERN_WALL_TIME_P90

Numeric

The 90th percentile of historical wall times for queries with the same pattern. Unit: ms.

PATTERN_SHUFFLE_SIZE_AVG

Numeric

The average historical shuffle data size for queries with the same pattern. Unit: bytes.

PATTERN_PEAK_MEMORY_AVG

Numeric

The average historical peak memory usage for queries with the same pattern. Unit: bytes.

PATTERN_INPUT_POSITION_AVG

Numeric

The average historical number of input rows for queries with the same pattern. Unit: rows.

PATTERN_OUTPUT_POSITION_AVG

Numeric

The average historical number of output rows for queries with the same pattern. Unit: rows.

Appendix 2: Operators

Operators

Supported data types

Description

>

Numeric

None

<

Numeric

None

=

Numeric, String

None

>=

Numeric

None

<=

Numeric

None

!=

Numeric, String

None

in

String list

Checks whether two string lists intersect.

contains

String

Checks whether a string contains a specified substring. This operator is primarily used with the original SQL statement of a query.

Important

This operator is supported only on clusters that run minor version 3.1.8.3 or later.

Appendix 3: Actions

Actions

Description

attrs configuration

KILL

Terminates the query.

None

RESUBMIT_RESOURCE_GROUP

Stops matching queries in the current resource group and resubmits them to another resource group for execution.

{ "resubmit": {"resource_group":"<resource_group_name>"}}

resource_group_name: the resource group name.

Note

Log on to the AnalyticDB for MySQL console, click the target cluster ID, and view the resource groups on the Resource Management page.

ADD_PROPERTIES

Modifies query properties. This action is typically used to change query priority, but can also modify other query properties configured by hints.

{ "add_prop": {"query_priority": "<Priority level>", "force":"false"}}

  • Priority level: the query priority. Specify an integer or a string as follows:

    • For an integer, the valid range is 0 to 39. A larger value indicates a higher priority.

    • For a string, use one of the following values:

      • LOWEST: the lowest priority, equivalent to 5.

      • LOW: a low priority, equivalent to 15.

      • NORMAL: the normal priority, equivalent to 25.

      • HIGH: the highest priority, equivalent to 35.

    For more information, see Priority queue and concurrency control.

  • force: Specifies whether to overwrite query properties that are already configured.

    • true (default): overwrites existing properties.

    • false: does not overwrite existing properties.

    Note

    Clusters that run minor versions earlier than 3.1.9.4 do not support the force property and do not overwrite existing query properties by default. For information about viewing the cluster minor version, see View the minor version of a cluster.

BLOCK_WITH_PROB

Terminates queries with a specified probability before they enter the queue. This action is typically used for throttling.

{ "block_prob": {"prob":<prob_value>}}

prob_value: the probability of terminating a query. Valid values: 0 to 1.

Previous：Workload management overviewNext：Automatic identification and acceleration of small queries