Soft isolation of computing resources in ApsaraDB for SelectDB - ApsaraDB for SelectDB

ApsaraDB for SelectDB allows you to use the workload group feature to isolate computing resources in a cluster on a software basis. This topic describes the workload group feature of ApsaraDB for SelectDB and how to use this feature to manage computing resources.

Overview

A workload group limits the resource usage of tasks within a compute cluster by limiting the use of computing resources and memory resources by tasks in the workload group on a single compute node.

In business scenarios where you can control when high-load query and import tasks are run and want to maximize the resources of a compute cluster, you can associate the tasks with separate workload groups. For example, you can associate scheduled offline computing tasks of SelectDB with a separate workload group. In addition, if your business has regular peak hours, you can also use workload groups to isolate resources for business tasks.

You can use workload groups to isolate resources from the following dimensions based on your business scenarios:

Isolate resources for large and small queries.
Isolate resources for long and short queries.
Isolate resources for queries on cold data and hot data.
Isolate resources for offline and real-time import tasks.

Use workload groups

This section describes how to create, delete, modify and view workload groups.

Note

We recommend that you use the admin account with full permissions to perform operations on workload groups.

Create a workload group

You can execute the following statement to create a workload group. Workload groups can be used to isolate CPU and memory resources in a compute cluster.

Syntax

CREATE WORKLOAD GROUP [IF NOT EXISTS] 'rg_name'
PROPERTIES (
    property_list
);

Parameters

Parameter	Required	Description
rg_name	Yes	The name of the workload group.

The following table describes the parameters in PROPERTIES.

Parameter	Required	Description
cpu_share	Yes	The CPU time that is available to the workload group. This parameter is used to implement soft isolation of CPU resources. The value of the cpu_share parameter is a relative value that indicates the weight of CPU resources that the workload group can obtain. For example, the following workload groups are created: `rg-a`, `rg-b`, and `rg-c`. The `cpu_share` parameter is set to 10, 30, and 40 for the workload groups. At a specific point in time, tasks are run in `rg-a` and `rg-b`, but no task is run in `rg-c`. In this case, `rg-a` can obtain `25% (10/(10 + 30))` of the CPU resources, whereas `rg-b` can obtain 75% of the CPU resources. If the system has only one running workload group, the workload group can obtain all the CPU resources, regardless of the value of the `cpu_share` parameter.
memory_limit	Yes	The percentage of the compute cluster memory that the workload group can use. The absolute value of the memory limit for the workload group is calculated by using the following formula: `Physical memory × mem_limit × memory_limit`. In the formula, the `mem_limit` parameter is a configuration item of the compute cluster. For more information about how to modify this configuration item, see Manage parameters. In most cases, tasks in the workload group can use the memory specified by the memory_limit parameter. If the memory usage of the workload group exceeds the memory limit, tasks with the highest memory usage in this group may be canceled to release the excess memory. Note The sum of the memory_limit values for all workload groups in the system cannot exceed 100%. Otherwise, an error is reported.
enable_memory_overcommit	No	Specifies whether to enable soft memory isolation for the workload group. Default value: false. If you set this parameter to false, hard memory isolation is enabled for the workload group. If the system detects that the memory usage of the workload group exceeds the limit, the system immediately cancels the tasks with the highest memory usage in the group to release the excess memory. If you set this parameter to true, soft memory isolation is enabled for the workload group. If the system has idle memory resources, the workload group can obtain more system memory even if the memory usage exceeds the limit specified by the memory_limit parameter. If the total system memory is insufficient, the system cancels the tasks with the highest memory usage to release some of the excess memory. This reduces the total system memory usage. If you set this parameter to true for several workload groups, we recommend that you configure the memory_limit parameter for all workload groups in a way that the sum of the memory_limit values is less than 100%. This way, spare memory is reserved for the workload groups that can overcommit the memory.
max_concurrency	No	The variable that controls the maximum number of concurrent queries allowed for the workload group. By default, the maximum number of concurrent queries is not limited. In ApsaraDB for SelectDB, the maximum number of concurrent queries is calculated by using the following formula: `max_concurrency × 2`. If the maximum number of concurrent queries is exceeded, new queries are queued.
max_queue_size	No	The variable that controls the maximum length of the query queue. By default, the maximum length of the query queue is not limited. In ApsaraDB for SelectDB, the maximum length of the query queue is calculated by using the following formula: `max_queue_size × 2`. If the queue is full, new queries are rejected.
queue_timeout	No	The variable that controls the maximum duration that a query waits in the queue, in milliseconds. By default, the maximum duration that a query waits in the queue is not limited. In ApsaraDB for SelectDB, the maximum duration is calculated by using the following formula: `queue_timeout × 2`. If the waiting time of a query exceeds this value, the query is rejected.

Example

Create a workload group named g1.

 CREATE WORKLOAD GROUP IF NOT EXISTS g1
 PROPERTIES (
     "cpu_share"="10",
     "memory_limit"="30%",
     "enable_memory_overcommit"="true"
 );

Delete a workload group

You can execute the following statement to delete a workload group.

Syntax

DROP WORKLOAD GROUP [IF EXISTS] 'rg_name'

Example

Delete the workload group named g1.

DROP WORKLOAD GROUP IF EXISTS g1;

Modify a workload group

You can execute the following statement to modify a workload group.

Syntax

ALTER WORKLOAD GROUP  "rg_name"
PROPERTIES (
    property_list
);

Parameters

For more information about the parameters in PROPERTIES, see the Parameters section of this topic.

Example

Modify the workload group named g1.

ALTER WORKLOAD GROUP g1
PROPERTIES (
    "cpu_share"="30",
    "memory_limit"="30%"
);

Note

When you modify the memory_limit parameter, make sure that the sum of the memory_limit values for all workload groups does not exceed 100%.
You can modify only some of the parameters. For example, you can modify only the cpu_share parameter. In this case, you need to only specify the cpu_share parameter in PROPERTIES.

View workload groups

You can execute the following statement to view the workload groups on which the current user has the USAGE-PRIV permission.

Syntax

SHOW WORKLOAD GROUPS;

Example

View all workload groups.

mysql> SHOW WORKLOAD GROUPS;
+----------+--------+--------------------------+---------+
| Id       | Name   | Item                     | Value   |
+----------+--------+--------------------------+---------+
| 10343386 | normal | cpu_share                | 10      |
| 10343386 | normal | memory_limit             | 30%     |
| 10343386 | normal | enable_memory_overcommit | true    |
| 10352416 | g1     | memory_limit             | 20%     |
| 10352416 | g1     | cpu_share                | 10      |
+----------+--------+--------------------------+---------+

Complete example

The following example shows how to enable the workload group feature in an SelectDB cluster and use workload groups.

Set the FE configuration item enable_workload_group to true.

enable_workload_group=true

Note

This configuration item cannot be directly specified. If you want to specify this configuration item, submit a ticket.
This configuration item is a dynamic configuration item. If you modify this configuration item and restart your instance, the system automatically creates a default workload group named normal. If you modify this configuration item but do not restart your instance, no default workload group is created. For more information about how to modify this configuration item, submit a ticket.

Create a workload group.

CREATE WORKLOAD GROUP IF NOT EXISTS g1
properties (
    "cpu_share"="10",
    "memory_limit"="30%",
    "enable_memory_overcommit"="true"
);

Set the session variable experimental_enable_pipeline_engine to true to enable the pipeline execution engine. The CPU isolation for a workload group is implemented based on the pipeline execution engine.

set experimental_enable_pipeline_engine = true;

Associate queries with the workload group so that the resource limit specified for the workload group can take effect. You can use one of the following methods to associate queries with the workload group:

Configure the user property default_workload_group to specify the default workload group. If the default workload group is automatically created by the system, the default value of this property is normal.
```
set property 'default_workload_group' = 'g1';
```
The preceding statement specifies g1 as the default workload group to be used by the queries of the current user.
Configure the session variable workload_group to specify the workload group that you want to use. This variable is empty by default.
```
set workload_group = 'g1';
```
The priority of the session variable workload_group is higher than that of the user property default_workload_group. If the workload_group variable is empty, queries are associated with the workload group that is specified by the default_workload_group property. If the workload_group variable is not empty, queries are associated with the workload group that is specified by the workload_group variable.
If you are not an administrator, you must first execute the SHOW WORKLOAD GROUPS statement to check whether you can view the workload group that you want to use. If the workload group is not displayed, the workload group may not exist, or you do not have the permissions to use the workload group. In this case, an error is reported when you execute queries.

Execute a query. The query is associated with the specified workload group. In this case, the resource limit specified for the workload group takes effect.