When multiple workloads compete for the same Interactive resource group, high-priority queries—like real-time dashboards or latency-sensitive analytics—can get stuck behind long-running extract, transform, and load (ETL) batch jobs. AnalyticDB for MySQL solves this by routing queries through four priority queues (LOWEST, LOW, NORMAL, HIGH) and letting you control exactly how many queries each queue can run and hold at any time.
Prerequisites
Before you begin, ensure that you have:
An AnalyticDB for MySQL cluster running version 3.1.6.3 or later. To view and update the minor version, log on to the AnalyticDB for MySQL console and go to the Configuration Information section of the Cluster Information page.
An Interactive resource group that uses the XIHE engine
Feature availability by version
| Feature | 3.1.6.3–earlier than 3.1.8.2 | 3.1.8.2 and later |
|---|---|---|
| Priority queues | Supported (manual enable required) | Supported (enabled by default) |
| Per-resource-group concurrency limits | Supported | Supported |
| Per-resource-group concurrency on Data Warehouse clusters in reserved mode | Not supported | Not supported |
How priority queues work
Each Interactive resource group has four priority queues. AnalyticDB for MySQL routes queries to a queue based on query type and any explicit priority you assign.
Default routing:
| Query type | Default queue | Default priority value |
|---|---|---|
| Regular SELECT queries | NORMAL | 25 |
ETL queries (INSERT INTO SELECT, DELETE SELECT, INSERT OVERWRITE SELECT) | LOWEST | 5 |
Within the same queue, queries run in priority-value order: a query with query_priority=8 runs before one with query_priority=6. Queries with equal priority values run in first-in, first-out (FIFO) order.
When a queue fills up:
Once running queries reach the maximum concurrency limit, incoming queries are queued.
Once queued queries reach the maximum queue size, incoming queries are rejected.
When a running query finishes, the queued query with the highest priority starts next.
Priority value ranges:
Each queue accepts a range of integer values (0–39):
| Queue | String alias | Integer value | Default for |
|---|---|---|---|
| HIGH | HIGH | 35 | — |
| NORMAL | NORMAL | 25 | Regular SELECT queries |
| LOW | LOW | 15 | — |
| LOWEST | LOWEST | 5 | ETL queries |
Enable query priority
For cluster versions 3.1.8.2 and later, query priority is enabled by default. No action required.
For cluster versions 3.1.6.3 to a version earlier than 3.1.8.2, run the following statement to enable query priority:
SET ADB_CONFIG XIHE_ENV_QUERY_PRIORITY_QUEUE_ENABLE=true;Set a query priority
Assign a priority using an integer from 0 to 39, or a string alias: LOWEST, LOW, NORMAL, or HIGH.
INSERT INTO ... VALUES(...) statements do not support query priority.Method 1: Use Workload Manager (recommended)
Use Workload Manager to automatically apply priority hints based on rules. This is the preferred approach when managing query priorities at scale across a production cluster.
Method 2: Add a HINT inline
Add a priority hint directly before the query statement:
/*+ query_priority=<priority_value>*/ <select_statement>Replace <priority_value> with an integer (0–39) or a string alias.
Examples:
-- Route to the HIGH queue using the string alias
/*+ query_priority=HIGH*/ SELECT * FROM test_table;
-- Route to the HIGH queue using the integer value
/*+ query_priority=35*/ SELECT * FROM test_table;Configure queue concurrency
Set the maximum number of concurrent queries and the maximum queue size for each priority queue. Configure limits globally (affecting all resource groups) or for a specific resource group.
Per-resource-group concurrency configuration is not supported for Data Warehouse clusters in reserved mode.
Concurrency parameters
All parameter values apply to a single frontend node, not the entire cluster.
To calculate the cluster-wide limit:
Total cluster limit = (per-node limit) × (number of frontend nodes)For example, if the NORMAL queue has XIHE_ENV_QUERY_NORMAL_MAX_CONCURRENT_SIZE = 20 and the cluster has 3 frontend nodes, the cluster can run up to 60 concurrent NORMAL-queue queries.
A frontend node is a FrontNode resource. For details, see Access layer high availability. Configuration changes take effect on all frontend nodes simultaneously.
| Queue | Parameter | Default (per frontend node) |
|---|---|---|
| LOWEST (ETL) | XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE | 20 |
| LOWEST (ETL) | XIHE_ENV_QUERY_ETL_MAX_QUEUED_SIZE | 200 |
| LOW | XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE | 20 |
| LOW | XIHE_ENV_QUERY_LOW_PRIORITY_MAX_QUEUED_SIZE | 200 |
| NORMAL | XIHE_ENV_QUERY_NORMAL_MAX_CONCURRENT_SIZE | 20 |
| NORMAL | XIHE_ENV_QUERY_NORMAL_MAX_QUEUED_SIZE | 200 |
| HIGH | XIHE_ENV_QUERY_HIGH_MAX_CONCURRENT_SIZE | 40 |
| HIGH | XIHE_ENV_QUERY_HIGH_MAX_QUEUED_SIZE | 400 |
Set global concurrency
To apply a concurrency limit across all resource groups:
SET ADB_CONFIG <parameter> = <value>;Set concurrency for a specific resource group
To apply a concurrency limit to one resource group only:
SET ADB_CONFIG <resource_group_name>.<parameter> = <value>;Example: Separating ETL, analytics, and time-sensitive queries
The following example configures a cluster with 2 frontend nodes to isolate ETL batch jobs from interactive queries. It throttles the LOWEST queue to cap ETL resource usage, increases NORMAL queue capacity for routine analytics, and restricts the HIGH queue to a single resource group (rg1) for service-level agreement (SLA)-critical queries.
Throttle ETL throughput to prevent batch jobs from starving interactive queries:
-- Limit LOWEST queue to 10 concurrent ETL queries per frontend node (20 cluster-wide)
SET ADB_CONFIG XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE = 10;Increase NORMAL queue capacity to handle more concurrent analytics queries:
-- Allow up to 30 concurrent NORMAL queries per frontend node (60 cluster-wide)
SET ADB_CONFIG XIHE_ENV_QUERY_NORMAL_MAX_CONCURRENT_SIZE = 30;Reserve HIGH queue capacity for a single resource group to protect SLA-critical queries from cluster-wide contention:
-- Limit HIGH queue to 5 concurrent queries per frontend node for rg1 only
SET ADB_CONFIG rg1.XIHE_ENV_QUERY_HIGH_MAX_CONCURRENT_SIZE = 5;With this setup, ETL jobs in the LOWEST queue are throttled cluster-wide, freeing capacity for interactive queries in the NORMAL queue. The HIGH queue limit applies only to rg1, leaving other resource groups unaffected.
View queue concurrency settings
Check the current configured limits for any queue parameter.
View a global setting:
SHOW ADB_CONFIG KEY=<parameter>;View the setting for a specific resource group:
SHOW ADB_CONFIG KEY='<resource_group_name>.<parameter>';Example: Check the global concurrency limit for the LOW queue:
SHOW ADB_CONFIG KEY=XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE;