How to enable and use the job resubmission feature - AnalyticDB

The job resubmission feature in AnalyticDB for MySQL lets you resubmit query jobs from interactive resource groups that meet specific conditions to a designated resource group. This prevents those jobs from affecting other queries in the source resource group. This topic describes how to enable and use the job resubmission feature.

In AnalyticDB for MySQL, some queries consume substantial resources and run for extended periods. These queries can degrade the performance of other queries, cause resource contention, and compromise system stability. Use the job resubmission feature to route such queries to a dedicated resource group for execution. This minimizes their impact on other queries. Typical scenarios include the following:

Queries that use large amounts of memory and are prone to out-of-memory (OOM) errors.
Ad hoc queries that involve occasional large-volume data scans.
Extract, transform, and load (ETL) queries that are not time-sensitive and run on a fixed schedule.

Feature overview

The job resubmission feature in AnalyticDB for MySQL supports both automatic and manual resubmission. The following table compares these two methods.

Item	Automated delivery (recommended)	Manual Delivery
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.
Cluster kernel version	3.2.2.7 or later.	3.1.8.4 or later.
Enabling method	This feature is disabled by default. Enable it using the SET command. After you enable it, the feature applies to all interactive resource groups in the cluster.	Manually configure resubmission rules for a single interactive resource group.
Resubmission rules	No manual configuration is required. Queries that fail due to insufficient memory are automatically resubmitted.	Manually configure rules in the console or using WLM commands. For example, the Execution Duration Threshold exceeds 30 s, or the Peak Memory (MB) threshold exceeds 24 MB.
Source resource groups that support 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 resubmission	Queries can be resubmitted only to Job resource groups.	Queries can be resubmitted to the following resource groups: Interactive resource groups that use the XIHE engine, including the default resource group use_default. Job resource groups.
Fees	Enabling automatic job resubmission is free. You are charged for the resources consumed by the automatically resubmitted queries. The cluster provides a monthly free quota. Resources that exceed the free quota are charged on a pay-as-you-go basis for AnalyticDB Compute Unit (ACU) Elastic Resources. For more information, see Pricing. The free quota rules are as follows: 1,000 ACU-hours/month if reserved resources are ≤ 100 ACUs. 2,000 ACU-hours/month if reserved resources are > 100 ACUs and < 500 ACUs. 3,000 ACU-hours/month if reserved resources are ≥ 500 ACUs. Note For Data Lakehouse Edition clusters, Reserved resources = Reserved compute resources + Reserved storage resources.	Enabling manual job resubmission is free. You are charged for the resources consumed by the manually resubmitted queries. No free quota is provided. You are charged for the resources used to run queries on a pay-as-you-go basis. For more information, see Pricing.

System Automatic Delivery (Recommended)

Prerequisites

The cluster kernel version must be 3.2.2.7 or later.

Note

To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.

Enable automatic resubmission

The automatic job resubmission feature is disabled by default. Enable it using the SET statement. After you enable the feature, it applies to the entire AnalyticDB for MySQL cluster. This means that queries from all interactive resource groups that meet the resubmission rules are sent to the destination resource group.

Enable or disable automatic resubmission:
```
SET ADB_CONFIG SERVERLESS_ROUTER_LEVEL=0|1|2;
```
The parameter values are as follows:
- 0 (default): Disables automatic resubmission.
- 1: Enables automatic resubmission only when the monthly free quota is not used up.
- 2: Enables automatic resubmission even if the monthly free quota is used up.
Check whether automatic resubmission is enabled:
```
SHOW ADB_CONFIG key=SERVERLESS_ROUTER_LEVEL;
```

Configure the destination resource group

After you enable automatic job resubmission, AnalyticDB for MySQL resubmits all queries that meet the resubmission rules to a Job resource group named Serverless by default. 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 ACUs for a single query

By default, a single automatically resubmitted query can use up to 128 ACUs. You can customize this limit.

Syntax

SET ADB_CONFIG SERVERLESS_ROUTER_MAX_ACU = <n>;

Example

Specify that a single query can use up to 24 ACUs.

SET ADB_CONFIG SERVERLESS_ROUTER_MAX_ACU = 24;

Check whether a job was automatically resubmitted

For clusters with kernel version 3.2.2.12 or later:

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

For clusters with kernel version earlier than 3.2.2.12:

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

Return value description:

Note

If all fields in the following table are empty, the query was not automatically resubmitted.

Parameter	Description
process_id	The query ID.
reason	The reason why the query was automatically resubmitted. Valid values: out of memory: Resubmitted due to insufficient memory. long running: Resubmitted due to long running time.
status	The execution status of the query after it 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 Delivery

Prerequisites

The cluster kernel version must be 3.1.8.4 or later.
Note
To view and update the minor version, go to the Configuration Information section on the Cluster Information page in the AnalyticDB for MySQL console.
If your cluster is a Data Warehouse Edition in elastic mode cluster, it must also meet the following conditions:
- At least two resource groups are created. For more information, see Create a resource group.
- The compute resources are 32 cores and 128 GB or higher.

Usage notes

Queries are re-executed in the destination resource group. This consumes the compute resources of that group and increases the query response time. This can affect the query stability of the destination resource group. Do not resubmit queries to resource groups that run core business workloads.

Procedure

You can configure job resubmission rules in two ways:

Configuration in the AnalyticDB for MySQL console: The console provides a simple and fast way to configure resubmission rules.
Configuration using Workload Manager commands: Workload Manager (WLM) commands offer more flexibility for custom and advanced needs. For more information, see WLM.

The following sections describe how to configure 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. 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 the job delivery rule.
- In the upper-right corner of the Resource Groups tab, click Create Resource Group. When you create a resource group, configure job resubmission rules.
- Click Modify in the Actions column of the target resource group to configure job resubmission rules.
Job delivery rule configuration parameters:
Parameter name
Description
Destination Resource Group
The name of the destination resource group.
Note
For more information about other parameters for creating a resource group, see Create and manage resource groups.
Click OK.
(Optional) Manage the job resubmission rules:
- Modify job resubmission rules: In the Modify Resource Group panel, modify the configured parameters.
- Delete job resubmission rules: In the Modify Resource Group panel, click the button 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 navigation pane on the left, click Workload Management.
Click Job Resubmission to view the job delivery rules.
In the upper-right corner, select a resource group from the Resource Group drop-down list.

In the upper-right corner, click Configure Job Resubmission Rules. In the Configure Job Resubmission Rules panel that appears, configure the following parameters:

Parameter	Metric description	Example value
Threshold for peak memory usage	The compute resources consumed by a query. The unit is MB. The default value is null.	24
Threshold for execution time	The time taken to run a query. The unit is ms. The default value is null.	24
Result if query fails due to insufficient memory	Specifies whether the query fails due to insufficient memory. The default value is No.	No
Destination Resource Group	The name of the destination resource group.	test

Important

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

Click OK.
(Optional) Manage the 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, you can view the number of query jobs that are successfully resubmitted to another resource group.

Parameter name	Description
Destination Resource Group	The name of the destination resource group.