Enable and use the job resubmission feature - AnalyticDB

AnalyticDB for MySQL provides the job resubmission feature that allows you to resubmit query jobs of interactive resource groups that meet specific conditions to a specific resource group. This prevents the query jobs from affecting the execution of other query jobs. This topic describes how to enable and use the job resubmission feature.

In AnalyticDB for MySQL, some queries consume substantial resources and require extended execution durations. This may affect the performance of other queries, lead to resource contention, and pose a risk to system stability. You can use the job resubmission feature to resubmit such query jobs to a specific resource group for execution to minimize the impact on other queries. Common scenarios:

Queries that use a large amount of memory and are prone to Out of Memory (OOM) errors.
Ad-hoc queries that involve occasional large-volume scans.
Extract, transform, load (ETL) queries that are insensitive to execution durations and executed at regular intervals.

Overview

The job resubmission feature of AnalyticDB for MySQL is categorized into automatic job resubmission and manual job resubmission. The following table describes the differences between automatic job resubmission and manual job resubmission.

Item	Automatic job resubmission (recommended)	Manual job resubmission
Supported product editions	Enterprise Edition, Basic Edition, and Data Lakehouse Edition.	Enterprise Edition, Basic Edition, Data Lakehouse Edition, and Data Warehouse Edition in elastic mode.
Minor version	V3.2.2.7 and later.	V3.1.8.4 and later.
Enabling method	By default, the automatic job resubmission feature is disabled. You can enable the feature by executing the SET statement. After the feature is enabled, it takes effect for all Interactive resource groups in the cluster.	Manually configure job resubmission rules for a single interactive resource group.
Job resubmission rules	You do not need to manually configure job resubmission rules. The following built-in rules apply: Queries that fail due to insufficient memory are automatically resubmitted. Long queries that exceed a P50 execution duration of 5 minutes are automatically resubmitted.	You can manually configure job resubmission rules in the AnalyticDB for MySQL console or by using the WLM syntax. For example, set the Execution Duration Threshold parameter to 30 s, or set Peak Memory (MB) threshold to 24 MB.
Source resource groups that support job resubmission rules	Interactive resource groups that use the XIHE engine(including the default resource group use_default).	Interactive resource groups that use the XIHE engine (excluding the default resource group use_default).
Destination resource groups that support the job resubmission feature	Query jobs can be resubmitted to only the job resource groups.	Query jobs can be resubmitted to the following resource groups: Interactive resource groups that utilize the XIHE engine which contain the default resource group named use_default. Job resource groups.
Billing rules	You are not charged when you enable the automatic job resubmission feature. You are charged for the resources consumed by the query jobs that are automatically resubmitted. AnalyticDB for MySQL provides a monthly free quota. If the amount of used resources exceeds the free quota, you are charged for AnalyticDB compute unit (ACU) elastic resources based on the pay-as-you-go billing method. For more information, see Pricing. Free quota of resources: 1,000 ACUs per month if the amount of reserved resources is less than or equal to 100 ACUs. 100 2000 ACUs per month if the amount of reserved resources is less than 500 ACUs and greater than 100 ACUs. 3000 ACUs per month if the number of reserved resources are greater than or equal to 500 ACUs. Note For Data Lakehouse Edition clusters: Reserved resources = Reserved computing resources + Reserved storage resources.	You are not charged when you enable the manual job resubmission feature. You are charged for the resources consumed by the query jobs that are manually resubmitted. AnalyticDB for MySQL does not provide a free quota. You are charged for the resources used to execute queries based on the pay-as-you-go billing method. For more information, see Pricing.

Automatic job resubmission (recommended)

Prerequisites

An AnalyticDB for MySQL cluster of V3.2.2.7 or later is created.

Note

To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.

Enable the automatic job resubmission feature

By default, the automatic job resubmission feature is disabled. You can enable the feature by executing the SET statement. After the feature is enabled, it takes effect for all Interactive resource groups in the AnalyticDB for MySQL cluster. The query jobs of all interactive resource groups that meet the conditions in the job resubmission rules are resubmitted to the destination resource groups.

Enable or disable the automatic job resubmission feature.
```
SET ADB_CONFIG SERVERLESS_ROUTER_LEVEL=0|1|2;
```
Valid values:
- 0 (default): disables the automatic job resubmission feature.
- 1: enables the automatic job resubmission feature only if the monthly free quota is available.
- 2. enables the automatic job resubmission feature even if the monthly free quota is exhausted.
Query whether the automatic job resubmission feature is enabled.
```
SHOW ADB_CONFIG key=SERVERLESS_ROUTER_LEVEL;
```

Configure the destination resource group

By default, after the automatic job resubmission feature is enabled, all query jobs that meet specific conditions in the job resubmission rules within the AnalyticDB for MySQL cluster are resubmitted to the job resource group named serverless. You can also specify a custom destination resource group.

Syntax

SET ADB_CONFIG SERVERLESS_ROUTER_TARGET = <resource_name>;

Example

Set the destination resource group to testjob.

SET ADB_CONFIG SERVERLESS_ROUTER_TARGET = testjob;

Configure the maximum number of ACUs for a single query job

By default, a single automatically resubmitted query job can use up to 128 ACUs. You can specify the maximum number of ACUs that can be used by a single query job based on your business requirements.

Syntax

SET ADB_CONFIG SERVERLESS_ROUTER_MAX_ACU = <n>;

Example

Set the maximum number of ACUs for a single query job to 24 ACUs.

SET ADB_CONFIG SERVERLESS_ROUTER_MAX_ACU = 24;

Query whether a job is automatically resubmitted

Execute the following statement for AnalyticDB for MySQL clusters of V3.2.2.12 or later:

SELECT process_id, reason, status FROM information_schema.ELASTIC_JOB_LIST WHERE process_id = <process_id>

Execute the following statement for AnalyticDB for MySQL clusters earlier than V3.2.2.12:

SELECT process_id, reason, status FROM information_schema.kepler_meta_elastic_job_list WHERE process_id = <process_id>

The following table describes the response parameters.

Note

If all the parameters in the following table are empty, the query job is not automatically resubmitted.

Parameter	Description
process_id	The job ID.
reason	The reason why the job is automatically resubmitted. Valid values: out of memory: insufficient memory. long running: extended execution duration.
status	The status of the query job after the job is resubmitted to the destination resource group.

Query the ACUs used by a job

You can log on to the AnalyticDB for MySQL console and choose Cluster Management > Resource Groups. On the page that appears, click the Job Usage Statistics tab to view the ACUs used by the automatically resubmitted queries.

Manual job resubmission

Prerequisites

An AnalyticDB for MySQL cluster of V3.1.8.4 or later is created.
Note
- To query the minor version of an AnalyticDB for MySQL Data Lakehouse Edition cluster, execute the SELECT adb_version(); statement. To update the minor version of a cluster, contact technical support.
- For information about how to view and update the minor version of an AnalyticDB for MySQL Data Warehouse Edition cluster, see Update the minor version of a cluster.
If the AnalyticDB for MySQL cluster is of Data Warehouse Edition in elastic mode, the cluster must meet the following requirements:
- At least two resource groups are created for the cluster. For more information, see Create a resource group.
- The cluster provides computing resources of 32 cores and 128 GB memory or more.

Usage notes

Query jobs are re-executed in the destination resource group with an extended response time and consume the computing resources of the resource group. This affects the query stability of the resource group. We recommend that you do not resubmit query jobs to resource groups that handle your core workloads.

Procedure

You can use one of the following methods to configure job resubmission rules:

AnalyticDB for MySQL console: This method allows you to configure job resubmission rules in a simple and quick manner.
WLM syntax: This method is suitable for custom and more advanced requirements. For more information, see WLM.

The following section describes how to configure job resubmission rules in the AnalyticDB for MySQL console:

Enterprise Edition, Basic Edition, and Data Lakehouse Edition

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Data Lakehouse Edition tab, find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, choose Cluster Management > Resource Management. On the Resource Management page, click the Resource Groups tab. In the upper-right corner of the Resource Groups tab, click Create Resource Group.

Configure job resubmission rules.

In the upper-right corner of the Resource Groups tab, click Create Resource Group. When you create a resource group, configure job resubmission rules.
On the Resource Groups tab, find the resource group whose job resubmission rules you want to modify and click Modify in the Actions column.

The following table describes the parameters in the Job Resubmission Rules section of the Create Resource Group or Modify Resource Group panel.

Parameter	Description
Destination Resource Group	The name of the resource group to which you want to resubmit query jobs.
Execution Duration Threshold	The amount of time that is consumed to execute query jobs. Unit: seconds. This parameter is left empty by default.

Note

For more information about the parameters required to create a resource group, see Create and manage a resource group.

Click OK.
(Optional) Perform the following operations to manage job resubmission rules:
- Modify job resubmission rules: In the Modify Resource Group panel, click Job Resubmission Rules and modify the configured parameters.
- Delete job resubmission rules: In the Modify Resource Group panel, click Job Resubmission Rules and click the icon to the right of the specified resource group in the Destination Resource Group field. In this case, the job resubmission rules become ineffective.

Data Warehouse Edition

Log on to the AnalyticDB for MySQL console. In the upper-left corner of the console, select a region. In the left-side navigation pane, click Clusters. On the Data Warehouse Edition tab, find the cluster that you want to manage and click the cluster ID.
In the left-side navigation pane, click Workload Management.
Click the Job Resubmission tab to view job resubmission rules.
In the upper-right corner of the tab, select a resource group from the Resource Group drop-down list.

In the upper-right corner of the tab, click Configure Job Resubmission Rules. In the Configure Job Resubmission Rules panel, configure the parameters that are described in the following table.

Parameter	Description	Example
Threshold for Peak Memory	The amount of computing resources that are consumed by a query job. Unit: MB. Default value: null.	24
Threshold for Execution Duration	The amount of time that is required to execute a query job. Unit: milliseconds. Default value: null.	24
Result for OOM Error	Specifies whether a query job fails due to insufficient memory. Default value: No.	No
Destination Resource Group	The name of the resource group to which you want to resubmit the query job.	test

Important

If a query job meets one of the conditions in the job resubmission rules, the query job is resubmitted to the specified resource group for execution.

Click OK.
(Optional) Perform the following operations to manage job resubmission rules:
- Modify job resubmission rules: In the Configure Job Resubmission Rules panel, modify the configured parameters.
- Delete job resubmission rules: In the Configure Job Resubmission Rules panel, delete the values of Threshold for Peak Memory and Threshold for Execution Duration. Then, set the value of Result for OOM Error to No. In this case, the job resubmission rules become ineffective.
In the Resubmitted Jobs section, view the number of query jobs that are resubmitted to the specified resource group.