Best practices for workload management - AnalyticDB for MySQL

This topic describes the best practices for workload management.

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.

Before execution: throttle queries

Before queries are executed, the system can determine the priority of each query based on workload management rules, allocate the queries into specific queues, and then execute the queries based on the maximum concurrency that you configured for each queue.

The following figure shows an example of how queries are allocated into queues based on priorities.

Throttling based on hash values of SQL patterns

AnalyticDB for MySQL can throttle queries based on SQL patterns. You can use wlm calc_pattern_hash statements to calculate the hash value of an SQL pattern and create a rule that assigns all queries in that SQL pattern to the low-priority queue. This way, you can configure the maximum concurrency of the queue to perform throttling.

Create a rule that assigns all queries in an SQL pattern to the low-priority queue.
```
wlm add_rule
name=confine_query
type=query
action=ADD_PROPERTIES
predicate='pattern_hash=XXXXXXXXXXXXXX'
attrs='{
  "add_prop": {
    "query_priority": "low"
  }
}'
```
Note
For more information about wlm calc_pattern_hash statements, see the "Obtain the pattern hash value of a query" section of the WLM topic.
Configure the maximum concurrency of the queue to perform throttling. The default value is 20.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;
```

Throttling based on historical executions

Create a rule that assigns all queries in SQL patterns that meet the following condition to the low-priority queue: P50 of the historical execution durations of the SQL pattern is longer than 2,000 ms.
```
wlm add_rule
name=confine_query
type=query
action=ADD_PROPERTIES
predicate='PATTERN_EXECUTION_TIME_P50>2000'
attrs='{
  "add_prop": {
    "query_priority": "low"
  }
}'
```
Configure the maximum concurrency of the queue to perform throttling. The default value is 20.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;
```

During execution: end queries to resolve performance issues

When a congestion occurs in an AnalyticDB for MySQL cluster, you can execute KILL ALL statements to end all executions. This operation also ends write operations, which may cause data inconsistency. In this case, you can create workload management rules to end only specific types of queries.

End all SELECT queries

Set predicate to query_task_type=1 to end SELECT queries. In this case, the system ends both the ongoing SELECT queries and subsequent SELECT queries. You need to manually delete or disable this rule when the system loads return to a low level. For information about how to delete or disable a rule, see WLM.

wlm add_rule
name=kill_select_query
type=query
action=KILL
predicate='query_task_type=1'

End queries from a specific requester

wlm add_rule
name=kill_query_from_user    
type=query       
action=KILL      
predicate='user=testuser1'

End queries from a specific IP address

wlm add_rule
name=kill_query_from_ip
type=query
action=KILL
predicate='source_ip=10.10.XX.XX'

End queries that consume more than 100 MB of memory

This rule ends both read and write operations.

wlm add_rule
name=kill_Big_query
type=query
action=KILL
predicate='QUERY_PEAK_MEMORY>=100'

End SELECT statements that consume more than 100 MB of memory

This rule ends only read operations.

wlm add_rule
name=kill_Big_query
type=query
action=KILL
predicate='QUERY_PEAK_MEMORY>100 && query_task_type=1'

Manage queries on specific databases or tables

You can use workload management rules to throttle the queries that involve specific databases or tables or increase their priorities. This section provides an example on how to throttle such queries.

Create the rule.

wlm add_rule
name=confine_query_in_table
type=query
action=ADD_PROPERTIES
predicate='query_table_list in database.table1, database.table2'
attrs='{
  "add_prop": {
    "query_priority": "low"
  }
}'

Configure the maximum concurrency of the queue to perform throttling. The default value is 20.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;
```