AnalyticDB: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 view the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, see How do I view the minor version of a 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.

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.

1. 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.

2. 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

1. 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"
     }
   }'

2. 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.

1. 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"
     }
   }'

2. 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;