Set priority queues and queue concurrency for interactive search - AnalyticDB

Interactive resource groups in AnalyticDB for MySQL provide priority queues for finer-grained control over query concurrency. Each resource group has a set of priority queues named LOWEST, LOW, NORMAL, and HIGH. You can configure query priorities to direct queries into different priority queues. You can also configure the concurrency for each queue. This topic describes how to configure query priorities and queue concurrency.

Prerequisites

Your cluster version is 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.
The resource group must be an Interactive resource group that uses the XIHE engine.

Function overview

Query priority

Each Interactive resource group has a set of four priority queues: LOWEST, LOW, NORMAL, and HIGH. By default, regular SELECT queries enter the NORMAL queue. Other extract, transform, and load (ETL) queries, such as INSERT INTO SELECT, DELETE SELECT, and INSERT OVERWRITE SELECT, enter the LOWEST queue. You can set a priority for each query to direct the query to a different queue. Within the same priority queue, queries with a higher priority value are executed first. For example, a query with query_priority=8 runs before a query with query_priority=6.

The priority ranges for each queue are as follows.

Queue concurrency

You can set the maximum number of concurrent queries and the maximum number of queued queries for each priority queue.

When the number of running queries in a priority queue reaches the maximum number of concurrent queries, new queries are queued.
When the number of queued queries reaches the maximum, new queries are rejected.
When a running query is complete, the queued query with the highest priority is executed next. If multiple queued queries have the same priority, they are executed in first-in, first-out (FIFO) order.

Enable query priority

For cluster versions 3.1.8.2 and later, query priority is enabled by default.
For clusters with a version from 3.1.6.3 to a version earlier than 3.1.8.2, you must manually enable query priority by executing the following statement:
```
SET ADB_CONFIG XIHE_ENV_QUERY_PRIORITY_QUEUE_ENABLE=true;
```

Set a query priority

The default priority for ETL queries is LOWEST (value: 5). The default priority for regular SELECT queries is NORMAL (value: 25). To raise or lower a query's priority, you can use one of the following methods.

Notes

INSERT INTO ... VALUES(...) statements do not support setting a query priority.

Method

We recommend using Workload Manager to automatically add HINTs. For more information, see Query priority.
Add the following HINT before the query statement to set the query priority.
```
/*+ query_priority=<Priority value>*/ select_statement
```
Priority value: You can specify an integer from 0 to 39, where a higher number indicates a higher priority. You can also use one of the following string values: LOWEST, LOW, NORMAL, or HIGH. These strings correspond to the integer values 5, 15, 25, and 35, respectively.

Examples

Set the priority to HIGH to send the query to the HIGH queue.
```
/*+ query_priority=HIGH*/ SELECT * FROM test_table;
```
Set the priority to 35 to send the query to the HIGH queue.
```
/*+ query_priority=35*/ SELECT * FROM test_table;
```

Set queue concurrency

You can set the maximum number of concurrent queries and the maximum queue size for priority queues in an Interactive resource group. You can configure these settings globally or for a single resource group.

Method

To set the global concurrency for priority queues, execute the following statement: SET ADB_CONFIG <concurrency-parameter> = <value>.
To set the priority queue concurrency for a specific resource group, execute the following statement: SET ADB_CONFIG <resource_group_name>.<concurrency_parameter> = <value>.

Important

Setting priority queue concurrency for a single resource group is not supported for Data Warehouse clusters in reserved mode.

List of concurrency parameters

Note

The parameter values for a priority queue represent the queue size for a single frontend node, not the total queue size for the cluster. The total queue size for the cluster is the queue size for a single node multiplied by the number of frontend nodes. The formula is: Total cluster queue size = (Queue size for a single node) × (Number of frontend nodes).
The parameter configurations for priority queues take effect on all frontend nodes at the same time.
A frontend node is a FrontNode resource. For more information, see Access layer high availability.

Queue name	Configuration parameter	Default value	Description (for a single resource group)
LOWEST (ETL) queue	XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE	20	The maximum number of concurrent queries for the LOWEST queue on a single frontend node is 20.
LOWEST (ETL) queue	XIHE_ENV_QUERY_ETL_MAX_QUEUED_SIZE	200	The maximum number of queued queries for the LOWEST queue on a single frontend node is 200.
LOW queue	XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE	20	The maximum number of concurrent queries for the LOW queue on a single frontend node is 20.
LOW queue	XIHE_ENV_QUERY_LOW_PRIORITY_MAX_QUEUED_SIZE	200	The maximum number of queued queries for the LOW queue on a single frontend node is 200.
NORMAL queue	XIHE_ENV_QUERY_NORMAL_MAX_CONCURRENT_SIZE	20	The maximum number of concurrent queries for the NORMAL queue on a single frontend node is 20.
NORMAL queue	XIHE_ENV_QUERY_NORMAL_MAX_QUEUED_SIZE	200	The maximum number of queued queries for the NORMAL queue on a single frontend node is 200.
HIGH queue	XIHE_ENV_QUERY_HIGH_MAX_CONCURRENT_SIZE	40	The maximum number of concurrent queries for the HIGH queue on a single frontend node is 40.
HIGH queue	XIHE_ENV_QUERY_HIGH_MAX_QUEUED_SIZE	400	The maximum number of queued queries for the HIGH queue on a single frontend node is 400.

Examples

Set the maximum number of concurrent queries for the LOW queue to 5 for all resource groups in the cluster.
```
SET ADB_CONFIG XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE = 5;
```
Set the maximum number of concurrent queries for the LOW queue to 5 for the `rg1` resource group.
```
SET ADB_CONFIG rg1.XIHE_ENV_QUERY_LOW_PRIORITY_MAX_CONCURRENT_SIZE = 5;
```

View queue concurrency

To view the global settings for the maximum number of running queries and the maximum number of queued queries, execute the following statement: SHOW ADB_CONFIG KEY=<concurrency_parameter>;.
To view the maximum number of running queries and the maximum number of queued queries for a specific resource group, execute the following statement: SHOW ADB_CONFIG KEY='<resource group name>.<concurrency parameter>';.