Resource queues in ApsaraDB for ClickHouse -

This topic describes how to use the resource queues of ApsaraDB for ClickHouse.

Background information

Open source ClickHouse does not support resource queues. For restrictions on query complexity in an ApsaraDB for ClickHouse cluster of V20.8 or later, see Restrictions on Query Complexity.

Resource queues are the enhanced feature of ApsaraDB for ClickHouse, and are supported only by ApsaraDB for ClickHouse clusters of V20.3. Open source ClickHouse provides a user-level memory isolation mechanism. By default, the resource queue feature is not enabled when you purchase an ApsaraDB for ClickHouse cluster.

Syntax of resource queues

The following sample syntax shows how to manage resource queues:

-- Create a resource queue.
CREATE RESOURCE QUEUE [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster]
  * {[SET] MEMORY = {number}
  * [, CONCURRENCY = {number}]
  * [, PRIORITY = { LOWEST | LOW | NORMAL | HIGH | HIGHEST }]
  * [, ISOLATE = {number}]
    * }
  * [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]

-- Modify a specified resource queue.
ALTER RESOURCE QUEUE [IF NOT EXISTS | OR REPLACE] name [ON CLUSTER cluster]
  * {[SET] MEMORY = {number}
  * [, CONCURRENCY = {number}]
  * [, PRIORITY = { LOWEST | LOW | NORMAL | HIGH | HIGHEST }]
  * [, ISOLATE = {number}]
    * }
  * [TO {role [,...] | ALL | ALL EXCEPT role [,...]}]  

-- Query a specified resource queue.
SHOW CREATE resource queue name

-- Query the resource queue that you are using.
SHOW CREATE resource queue current

-- Delete a specified resource queue.
DROP resource queue if exists name

Definition parameters:

MEMORY: the size of the memory pool that you want to allocate to the resource queue to be created. If all the memory of the node has been allocated to existing resource queues, the resource queue fails to be created.
CONCURRENCY: the maximum number of concurrent queries that the resource queue to be created supports. Default value: 20. If the number of concurrent queries in the resource queue exceeds this limit, additional queries are blocked. The subqueries that are initiated by the system are counted as concurrent queries. These subqueries are not blocked. If the CONCURRENCY parameter of a resource queue is set to 20 and the number of concurrent system-initiated subqueries in the resource queue is 25, all user-initiated queries are blocked. After the number of concurrent system-initiated subqueries becomes less than 20, the user-initiated queries are processed.
PRIORITY: the priorities of the resource queue to be created. This parameter specifies the CPU scheduling and memory preemption priorities.
ISOLATE: the memory isolation level of the resource queue to be created.
- The default value is 0, which specifies that no memory isolation is implemented for the resource queue to be created. If the memory usage of a resource queue is low, a resource queue with a higher priority can temporarily preempt the available memory of the resource queue when needed.
- If you set this parameter to 1, soft isolation is implemented for the resource queue to be created. In this case, a resource queue with a higher priority cannot preempt the available memory of the resource queue to be created.
- If you set this parameter to 2, absolute isolation is implemented for the resource queue to be created. In this case, a resource queue with a higher priority cannot preempt the available memory of the resource queue to be created. The resource queue to be created also cannot preempt the available memory of a resource queue with a lower priority.
role: the users to whom the resource queue that you create is bound. Specify this parameter in the To role [,...] format. The queries sent from these users are routed to the resource queue that you create. If a user is bound to multiple resource queues, the system routes the queries sent by this user to the resource queue with the highest priority.

Configuration parameters:

target_resource_queue: the resource queue to which the queries sent by a user are routed. You can also configure forced routing for queries in the profile file of the user.
resource_queue_max_wait_ms: the timeout period of a query that is blocked in the resource queue based on the concurrency limit. The default value is 10 seconds.

The following sample code provides examples on how to manage resource queues:

CREATE RESOURCE QUEUE IF NOT EXISTS test_queue ON CLUSTER cluster SET 
  MEMORY = 1073741824, CONCURRENCY = 20, ISOLATE = 0, PRIORITY = NORMAL 
    TO default;

CREATE RESOURCE QUEUE IF NOT EXISTS anonymous_queue ON CLUSTER cluster SET 
  MEMORY = 1073741824, CONCURRENCY = 20, ISOLATE = 1, PRIORITY = LOW;

SHOW CREATE resource queue test_queue;

SHOW CREATE resource queue current;

SELECT count (distinct intDiv(number, 10)) FROM numbers(100000) settings target_resource_queue='anonymous_queue';

DROP resource queue if exists test_queue;

DROP resource queue if exists anonymous_queue;

Query the information about resource queues

To query all your resource queues, execute the following statement:

show resource queues;

The following table describes the parameters in the query results.


Parameter	Data type	Description
name	String	The name of the resource queue.
concurrency	UInt32	The maximum number of concurrent queries that the resource queue supports.
memory	UInt64	The size of the memory pool that is allocated to the resource queue. Unit: bytes.
isolate	UInt8	The memory isolation level of the resource queue.
priority	ENUM8	The priority of the resource queue.
roles	Array<String>	The users to whom the resource queue is bound.

To query the usage information about queries in the specified resource queue, execute the following statement:

show resource queue stat [CURRENT | ALL];

The following table describes the parameters in the query results.


Parameter	Data type	Description
name	String	The name of the resource queue.
running_query	UInt32	The number of queries that are running in the resource queue.
waiting_query	UInt32	The number of queries that are waiting to run in the resource queue.
grabbing_query	UInt32	The number of queries that are temporarily preempting the available memory of the resource queue. These queries do not belong to the resource queue.
allocated_memory	UInt64	The size of memory that is used by the queries that belong to the resource queue. Unit: bytes.
grabbed_memory	UInt64	The size of memory that is temporarily preempted by the queries that do not belong to the resource queue. Unit: bytes.
free_memory	UInt64	The size of the available memory of the resource queue. Unit: bytes.

Errors that may occur when queries run in resource queues

A query timed out because the number of concurrent queries exceeds the concurrency limit: Error code: 13005; Solution: Reduce the number of concurrent queries on your client.

The size of memory that is used by bad queries exceeds the size of the memory pool: Error code: 241; Solution: Optimize the query plan or increase the size of the memory pool that is allocated to the resource queue.

The specified resource queue to which the queries are forcibly routed does not exist: Error code: 13006; Solution: Check whether the specified resource queue is created.

A query that temporarily preempted the memory of a resource queue with a lower priority is terminated: Error code: 394; Solution: Reduce the number of concurrent queries in the resource queue that you use, and change the priority of the resource queue.