AnalyticDB for MySQL allows you to set the maximum number of queries that can be executed and the maximum number of queued queries for the following types of queries: internal system queries, general queries, and extract-transform-load (ETL) queries.

Query queues

To isolate the traffic of internal system queries, general queries, and ETL queries such as the INSERT INTO SELECT query, AnalyticDB for MySQL isolates the queues used for these queries at the access layer.

AnalyticDB for MySQL allows you to set the maximum number of queries that can be executed and the maximum number of queued queries for each query queue.
  • If the number of queries being executed in a query queue is greater than or equal to the maximum number of queries that can be executed, new queries are added to the queue.
  • If the number of queued queries in a query queue is greater than or equal to the maximum number of queued queries, new queries are rejected.
  • AnalyticDB for MySQL uses the first-in-first-out (FIFO) method to determine the order in which queued queries are executed.
Note Query queues are not applicable to the INSERT INTO ...VALUE(...) statement.

Configuration parameters

Note
  • The value of a query queue parameter indicates the queue size of a single frontend node, not the total queue size of the entire cluster. The total queue size of the entire cluster is the size of a single queue multiplied by the number of frontend nodes. You can use the following formula to calculate the total queue size of a cluster: Total queue size of the cluster = Size of a single queue × Number of frontend nodes.
  • The configurations of the query queue parameter takes effect for all frontend nodes at the same time.
Query type Query queue Configuration parameter Configuration item
System query ROOT The values of the parameter are fixed and cannot be configured.
  • The maximum number of queries that can be executed for a single frontend node. Set the value to 400.
  • The maximum number of queued queries for a single frontend node. Set the value to 500.
General query NORMAL XIHE_ENV_QUERY_MAX_CONCURRENT_QUERIES The maximum number of queries that can be executed for a single frontend node. Valid values: 1 to 20.

For example, if this parameter is set to 20 and the cluster has 3 frontend nodes, the maximum number of queries that can be executed for a single frontend node is 20 and that for the entire cluster is 60.

XIHE_ENV_QUERY_MAX_QUEUED_QUERIES The maximum number of queued queries for a single frontend node. Valid values: 1 to 200.

For example, if this parameter is set to 200 and the cluster has 3 frontend nodes, the maximum number of queued queries for a single frontend node is 200 and that for the entire cluster is 600.

ETL query LOW XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE The maximum number of queries that can be executed for a single frontend node. Valid values: 1 to 20.

For example, if this parameter is set to 10 and the cluster has 3 frontend nodes, the maximum number of queries that can be executed for a single frontend node is 10 and that for the entire cluster is 30.

XIHE_ENV_QUERY_ETL_MAX_QUEUED_SIZE The maximum number of queued queries for a single frontend node. Valid values: 1 to 100.

For example, if this parameter is set to 100 and the cluster has 3 frontend nodes, the maximum number of queued queries for a single frontend node is 100 and that for the entire cluster is 300.

Modify query queues

You can execute the following statements to configure the query queue size of a single frontend node.
Note
  • The value of a query queue parameter indicates the queue size of a single frontend node, not the total queue size of the entire cluster. The total queue size of the entire cluster is the size of a single queue multiplied by the number of frontend nodes. You can use the following formula to calculate the total queue size of a cluster: Total queue size of the cluster = Size of a single queue × Number of frontend nodes.
  • The configurations of the query queue parameter takes effect for all frontend nodes at the same time.
-- General query queue
set adb_config XIHE_ENV_QUERY_MAX_CONCURRENT_QUERIES=20
set adb_config XIHE_ENV_QUERY_MAX_QUEUED_QUERIES=200

-- ETL query queue
set adb_config XIHE_ENV_QUERY_ETL_MAX_CONCURRENT_SIZE=20
set adb_config XIHE_ENV_QUERY_ETL_MAX_QUEUED_SIZE=100

In the preceding examples, in terms of general queries, the maximum number of queries that can be executed for a single frontend node is set to 20 and the maximum number of queued queries for a single frontend node is set to 200. In terms of ETL queries, the maximum number of queries that can be executed for a single frontend node is set to 20 and the maximum number of queued queries for a single frontend node is set to 100.

Specify query queues

-- Use a hint to specify that the LOW queue is used for queries.
/*+coordinator_query_queue=low_priority*/ select * from tbl limit 100;

-- Specify that the NORMAL queue is used for general queries.
select * from tbl limit 100;

-- Specify that the LOW queue is used for ETL queries.
insert into dst select * from tbl;

Execution priorities

AnalyticDB for MySQL isolates query execution threads in the computing module to isolate general queries and ETL queries. Query execution threads are divided into two groups: NORMAL and LOW.

  • Internal system queries and general queries are executed by the NORMAL thread group.
  • ETL queries are executed by the LOW thread group.

When the two groups compete for CPU resources, the NORMAL thread group is prioritized before the LOW thread group.

Query type Execution thread group Priority
System query and general query NORMAL High
ETL query LOW Low

Specify execution priorities

-- Use a hint to specify that the LOW thread group is used for queries.
/*+direct_low_priority_cpu_queue=true*/ select * from tbl limit 100;

-- Specify that the NORMAL thread group is used for general queries.
select * from tbl limit 100;

-- Specify that the LOW thread group is used for ETL queries.
insert into dst select * from tbl;