AnalyticDB:Best practices for workload management

Throttle queries by SQL pattern

Use this approach when you can identify a specific query pattern that is too expensive to run at full concurrency. WLM matches queries by their SQL pattern hash, so structurally identical queries are grouped and throttled together regardless of their literal parameter values.

1. Calculate the hash value of the SQL pattern you want to throttle:

   wlm calc_pattern_hash

   For command details, see Calculate a pattern_hash value.

2. Create a rule that routes queries with that pattern hash 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"
     }
   }'

   Replace XXXXXXXXXXXXXX with the hash value from step 1.

3. Set the maximum concurrency for the low-priority queue. The default is 20:

   SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;

   Note

   For more information about how to modify the queue concurrency, see Priority queues and concurrency of an Interactive resource group.

   Lower this value to apply stricter throttling.

Throttle queries based on historical execution time

Use this approach when you want to automatically catch slow queries without identifying specific patterns upfront. The predicate PATTERN_EXECUTION_TIME_P50>2000 matches queries whose historical median (P50) execution time for that SQL pattern exceeds 2,000 milliseconds — a reliable signal that the query is expensive at scale.

1. Create a rule that routes historically slow queries 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"
     }
   }'

2. Set the maximum concurrency for the low-priority queue:

   SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE=20;

Stop all SELECT queries

Use this when SELECT queries are blocking critical writes and you need to stop them immediately. Setting query_task_type=1 targets SELECT queries only.

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

Stop queries from a specific user

Use this when queries from a single user account are causing cluster instability.

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

Replace testuser1 with the actual username. Delete this rule after the situation is resolved.

Stop queries from a specific IP address

Use this when you have traced the problematic queries to a single IP address.

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

Replace 10.10.XX.XX with the actual IP address. Delete this rule after the situation is resolved.

Stop all queries exceeding 100 MB of memory

Use this when memory-intensive queries are destabilizing the cluster and you need to stop all of them, including write operations.

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

This rule applies to both SELECT queries and write operations. Delete this rule after the cluster stabilizes.

Stop SELECT queries exceeding 100 MB of memory

Use this when large SELECT queries are causing instability but you want to keep write operations running. The predicate combines a memory threshold with query_task_type=1 to target only SELECT queries.

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

Delete this rule after the cluster stabilizes.