All Products
Search
Document Center

AnalyticDB for MySQL:Priority queues and concurrency control of interactive resource groups

Last Updated:Dec 15, 2023

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.

Note

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;
Note

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>.

    Important

    You 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

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 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>;.