To control the number of concurrent queries in a more accurate manner, AnalyticDB for MySQL Data Lakehouse Edition (V3.0) and Data Warehouse Edition (V3.0) provide the priority queue feature for resource groups. 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.
For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.
Overview
Each resource group has a set of priority queues, which are LOWEST, LOW, NORMAL, and HIGH queues. By default, common SELECT queries enter NORMAL queues, and extract-transform-load (ETL) queries such as INSERT INTO SELECT
, DELETE SELECT
, and INSERT OVERWRITE INTO SELECT
enter LOWEST queues. You can configure query priorities to allow queries to enter different priority queues. In each queue, queries that have higher priorities are preferentially executed. The following figure shows the priority range of each queue.
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 maximum number of queries that can be queued and 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) mode.
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;
For information about how to query the minor version of a cluster, see How do I query the version of an AnalyticDB for MySQL cluster? To update the minor version of a cluster, contact technical support.
Configure query priorities
By default, the priority 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 WLM.
Execute the following statement to add a hint to a query:
/*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, such as LOWEST (5), LOW (15), NORMAL (25), and HIGH (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 for queues in a cluster or a resource group.
Configuration methods
Configure the number of concurrent queries for queues in a cluster:
SET ADB_CONFIG <concurrency parameter> = <value>
.Configure the number of concurrent queries for queues in a resource group:
SET ADB_CONFIG <resource group name>.<concurrency parameter> = <value>
.ImportantYou can configure the number of concurrent queries for queues in a resource group only for Data Warehouse Edition (V3.0) clusters in elastic mode for Cluster Edition that have 32 cores or more and Data Lakehouse Edition (V3.0) clusters.
Concurrency parameters
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 more information about frontend nodes, see High availability of the access layer.
Queue | Parameter | Default value | Description for a 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. |
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. |
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. |
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. |
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
Query the maximum numbers of queries that can be executed and queued in a cluster:
SHOW ADB_CONFIG KEY=<concurrency parameter>;
.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>;
.