Best practices for workload management - AnalyticDB - Alibaba Cloud Documentation Center

This topic describes the best practices for workload management.

Prerequisites

The kernel version of your AnalyticDB for MySQL cluster must be 3.1.6.3 or later.

Note

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.

Before execution: throttle queries

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

Priority queues:

Throttling based on hash values of SQL patterns

AnalyticDB for MySQL can throttle queries based on SQL patterns. You can use the wlm calc_pattern_hash command to calculate the hash value of an SQL pattern and configure a rule to assign all queries that have the same pattern hash value to the low-priority queue. Then, you can modify the concurrency of the queue to perform throttling.

Create the following rule to assign the queries of the specified 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 the wlm calc_pattern_hash command, see Calculate a pattern_hash value.
Modify the concurrency of the low-priority queue to perform throttling. The default concurrency is 20.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;
```

Throttling large queries based on historical execution information

Create the following rule to assign queries with a historical P50 execution time greater than 2,000 for the pattern to the low-priority queue.

wlm add_rule
name=confine_query
type=query
action=ADD_PROPERTIES
predicate='PATTERN_EXECUTION_TIME_P50>2000'
attrs='{
  "add_prop": {
    "query_priority": "low"
  }
}'

Modify the concurrency of the low-priority queue to perform throttling. The default concurrency is 20.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;
```

During execution: control abnormal queries

If a critical blockage occurs in an AnalyticDB for MySQL cluster, you can configure rules to terminate specific types of queries. This prevents write job failures that can be caused using the KILL ALL statement to terminate all queries.

End all SELECT queries

To terminate SELECT queries, you can set QUERY_TASK_TYPE to 1. After this setting is applied, all active SELECT queries are terminated. However, this also terminates all subsequent queries. Therefore, you must delete or disable this rule after the system load decreases. For more 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 user

If you identify that queries from a specific user are causing the AnalyticDB for MySQL cluster to become unavailable, you can terminate all queries from that user.

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

End queries from a specific IP address

If you trace the queries that are causing the AnalyticDB for MySQL cluster to become unavailable to a specific IP address, you can terminate all queries from that IP address.

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

End all queries that use more than 100 MB of memory

If large queries are causing instability in the AnalyticDB for MySQL cluster, you can configure a rule to terminate all queries that use more than 100 MB of memory. This includes SELECT queries and write operations.

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

End SELECT queries that use more than 100 MB of memory

If large queries are causing instability in the AnalyticDB for MySQL cluster, you can configure a rule to terminate SELECT queries that use more than 100 MB of memory. This approach limits large queries without affecting write jobs.

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

Routine throttling (throttling queries on databases or tables)

You can throttle queries that scan specific tables or databases by assigning them to a low-priority queue.

Create the following 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"
  }
}'

Modify the concurrency of the low-priority queue to perform throttling. The default concurrency is 20.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;
```