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

Background information

Open source ClickHouse does not support resource queues. The resource queue feature is provided by ApsaraDB for ClickHouse for performance enhancement. This feature is supported only for kernel version 20.3. Open source ClickHouse provides a complete user-level memory isolation mechanism. By default, the resource queue feature is not enabled when you purchase an ApsaraDB for ClickHouse cluster. To enable this feature, submit a ticket.

Syntax and parameters

The following sample code shows you 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, the excessive queries are blocked and wait to be run. Subqueries that are initiated by the system are counted as concurrent queries but are not blocked. If the CONCURRENCY parameter of a resource queue is set to 20 and the number of concurrent system subqueries in the resource queue is 25, all the user-initiated queries are blocked. After the number of concurrent system subqueries drops below 20, the user-initiated queries begin to be run.
  • PRIORITY: the priorities of the resource queue to be created. The priorities include the CPU scheduling priority and the memory preemption priority.
  • ISOLATE: the memory isolation level of the resource queue to be created.
    • Default value: 0, which indicates that no memory isolation is implemented for the resource queue to be created. If the memory usage of the resource queue is low, a resource queue with higher priorities 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 higher priorities 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 higher priorities cannot preempt the available memory of the resource queue to be created. In addition, the resource queue to be created cannot preempt the available memory of a resource queue with lower priorities.
  • role: the users to whom the resource queue to be created is bound. Specify this parameter in the following format: To role [,..].By default, the queries from these users are routed to the resource queue to be created. If a user is bound to multiple resource queues, the system preferentially routes the queries from this user to the resource queue with the highest priorities.
Configuration parameters:
  • target_resource_queue: the resource queue to which the queries from a user are routed. You can also configure forcible query routing 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 because of 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.
isolate UInt8 The memory isolation level of the resource queue.
priority ENUM8 The priorities of the resource queue.
roles Array<String> The users to whom the resource queue is bound.
To query the usage information about the resource queue that you are using, 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 being run in the resource queue.
waiting_query UINT32 The number of queries that are waiting to be 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 the memory that is normally occupied by the queries that belong to the resource queue.
grabbed_memory UINT64 The size of the memory that is temporarily preempted by the queries that do not belong to the resource queue.
free_memory UINT64 The size of the available memory of the resource queue.

Errors that may occur when queries are run in resource queues

A query times out because of the concurrency limit
Error code: 13005
Solution: Reduce the number of concurrent queries on your client.
The size of the memory that is occupied 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 from a user are routed does not exist
Error code: 13006
Solution: Check whether the specified resource queue is created.
A query that is temporarily preempting the memory of a resource queue with lower priorities is terminated
Error code: 394
Solution: Reduce the number of concurrent queries in the resource queue that you are using, and adjust the priorities of the resource queue.