Configure priority queues and queue concurrency of interactive resource groups - AnalyticDB

AnalyticDB for MySQL provides the priority queue feature for interactive resource groups to allow you to accurately control the number of concurrent queries. Each resource group has a set of priority queues, which are LOWEST, LOW, NORMAL, and HIGH queues. You can configure query priorities to allow queries to enter different priority queues. You can also configure the number of concurrent queries for queues. This topic describes how to configure query priorities and the number of concurrent queries for queues.

Prerequisites

An AnalyticDB for MySQL cluster of V3.1.6.3 or later is created.

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.
For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.

Overview

Query priorities

Each interactive resource group has a set of priority queues, which are LOWEST, LOW, NORMAL, and HIGH queues. By default, common SELECT queries enter the NORMAL queue, and extract, transform, load (ETL) queries, such as INSERT INTO SELECT, DELETE SELECT, and INSERT OVERWRITE SELECT queries, enter the LOWEST queue. You can configure query priorities to allow queries to enter different priority queues. In each priority queue, queries that have higher priorities are preferentially executed. For example, a query with the query_priority parameter set to 8 is preferentially executed than a query with the query_priority parameter set to 6.

The following figure shows the priority range of each priority queue. 优先级队列

Queue concurrency

You can configure the maximum number of queries that can be executed and the maximum number of queries that can be queued for each priority queue.

If the number of queries that are being executed in a priority queue is greater than or equal to the maximum number of queries that can be executed, new queries enter the queuing state.
If the number of queued queries in a priority queue is greater than or equal to the sum of the maximum number of queries that can be queued and the maximum number of queries that can be executed, new queries are rejected.
After a query is executed, the queued query that has the highest priority enters the executing state. If two queries have the same priority, they are executed in first-in first-out (FIFO) order.

Enable the priority queue feature for queries

By default, the priority queue feature is enabled for AnalyticDB for MySQL clusters of V3.1.8.2 or later.
To enable the priority queue feature for AnalyticDB for MySQL clusters of V3.1.6.3 to V3.1.8.1, execute the following statement:
```
SET ADB_CONFIG XIHE_ENV_QUERY_PRIORITY_QUEUE_ENABLE=true;
```

Configure query priorities

By default, the priority level value is LOWEST (5) for ETL queries and NORMAL (25) for common SELECT queries. The following section describes how to configure the query priorities.

Usage notes

You cannot configure priorities for INSERT INTO ... VALUES(...) statements.

Configuration methods

(Recommended) Execute a WLM statement to add a hint to a query. For more information, see the configuration method of the ADD_PROPERTIES parameter of the WLM topic.
Add a hint before a query statement. Syntax:
```
/*+ query_priority=<priority level>*/ select_statement
```
priority level: The value can be an integer from 0 to 39. A greater value specifies a higher priority. The value can also be a string, including LOWEST, LOW, NORMAL, and HIGH, which are equivalent to integers 5, 15, 25, and 35.

Examples

Set the priority of a query to HIGH to allow the query to enter the HIGH queue.
```
/*+ query_priority=HIGH*/ SELECT * FROM test_table;
```
Set the priority of a query to 35 to allow the query to enter the HIGH queue.
```
/*+ query_priority=35*/ SELECT * FROM test_table;
```

Configure the number of concurrent queries for queues

You can configure the number of concurrent queries and the maximum number of queries that can be queued for priority queues. You can configure the number of concurrent queries for queues in a cluster or a resource group.

Configuration methods

Execute the following statement to configure the number of concurrent queries for queues in a cluster: SET ADB_CONFIG <concurrency parameter> = <value>.
Execute the following statement to configure the number of concurrent queries for queues in a resource group: SET ADB_CONFIG <resource group name>.<concurrency parameter> = <value>.

Important

You cannot configure the number of concurrent queries for queues in a resource group for Data Warehouse Edition clusters in reserved mode.

Concurrency parameters

Note

The value of a priority queue parameter specifies the queue size of a frontend node. You can calculate the total queue size of a cluster by using the following formula: Total queue size of the cluster = Size of a queue × Number of frontend nodes.
The configuration of a priority queue parameter takes effect for all frontend nodes at the same time.
For information about frontend nodes, see High availability of the access layer.

Queue	Parameter	Default value	Description (per resource group)
LOWEST (ETL) queue	XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE	20	The maximum number of queries that can be executed for the LOWEST queue of a frontend node is 20.
LOWEST (ETL) queue	XIHE_ENV_QUERY_ETL_MAX_QUEUED_SIZE	200	The maximum number of queries that can be queued for the LOWEST queue of a frontend node is 200.
LOW queue	XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE	20	The maximum number of queries that can be executed for the LOW queue of a frontend node is 20.
LOW queue	XIHE_ENV_QUERY_LOW_PRIORITY_MAX_QUEUED_SIZE	200	The maximum number of queries that can be queued for the LOW queue of a frontend node is 200.
NORMAL queue	XIHE_ENV_QUERY_NORMAL_MAX_CONCURRENT_SIZE	20	The maximum number of queries that can be executed for the NORMAL queue of a frontend node is 20.
NORMAL queue	XIHE_ENV_QUERY_NORMAL_MAX_QUEUED_SIZE	200	The maximum number of queries that can be queued for the NORMAL queue of a frontend node is 200.
HIGH queue	XIHE_ENV_QUERY_HIGH_MAX_CONCURRENT_SIZE	40	The maximum number of queries that can be executed for the HIGH queue of a frontend node is 40.
HIGH queue	XIHE_ENV_QUERY_HIGH_MAX_QUEUED_SIZE	400	The maximum number of queries that can be queued for the HIGH queue of a frontend node is 400.

Examples

Change the maximum number of queries that can be executed for LOW queues in a cluster to 5.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE = 5;
```
Change the maximum number of queries that can be executed for the LOW queue in the rg1 resource group to 5.
```
SET ADB_CONFIG rg1.XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE = 5;
```

Query the queue concurrency

Execute the following statement to query the maximum numbers of queries that can be executed and queued in a cluster: SHOW ADB_CONFIG KEY=<concurrency parameter>;.
Execute the following statement to query the maximum numbers of queries that can be executed and queued in a resource group: SHOW ADB_CONFIG KEY=<resource group name>.<concurrency parameter>;.