How to enable and use the job resubmission feature - AnalyticDB

The job resubmission feature in AnalyticDB for MySQL allows you to resubmit query jobs that meet specific conditions from an interactive resource group to a specified 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 resource-intensive and long-running queries can affect the execution of other queries, leading to resource contention and potential system instability. The job resubmission feature addresses this by resubmitting these queries to a dedicated resource group. Typical scenarios include:

Queries that consume large amounts of memory and are prone to out-of-memory (OOM) errors.
Occasional ad-hoc queries that scan large volumes of data.
ETL queries that are less sensitive to execution time and run on a predictable schedule.

Introduction

AnalyticDB for MySQL provides two types of job resubmission: automatic and manual. The following table describes the differences.

Item	Automatic resubmission	Manual resubmission
Supported editions	Enterprise Edition, Basic Edition, and Data Lakehouse Edition	Enterprise Edition, Basic Edition, Data Lakehouse Edition, and Data Warehouse Edition (elastic mode)
Kernel version	3.2.2.7 or later	3.1.8.4 or later
Enablement	This feature is disabled by default. You must enable it with a SET command. Once enabled, the feature takes effect for all interactive resource groups in the cluster.	Manually configure a job resubmission rule for a single interactive resource group.
Job resubmission rule	You do not need to manually configure rules. A query is automatically resubmitted if it fails due to insufficient memory.	Rules are manually configured in the console or with WLM commands. For example, a query is resubmitted if its execution time exceeds the Execution Duration Threshold of 30 s or its memory usage exceeds the Peak Memory (MB) threshold of 24 MB.
Applicable source resource groups	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`.
Applicable destination resource groups	Queries can be resubmitted only to a job resource group.	Queries can be resubmitted to the following types of resource groups: Interactive resource groups that use the XIHE engine, including the default resource group `use_default`. Job resource groups.
Billing	Enabling automatic job resubmission is free of charge. You are charged for the resources consumed by automatically resubmitted queries. Your cluster has a monthly free quota. If you exceed the free quota, you are charged for the ACU elastic resources used on a pay-as-you-go basis. For more information, see Pricing. The free quota is determined as follows: If reserved resources are less than or equal to 100 ACU, the free quota is 1,000 ACU-hours/month. If reserved resources are greater than 100 ACU and less than 500 ACU, the free quota is 2,000 ACU-hours/month. If reserved resources are greater than or equal to 500 ACU, the free quota is 3,000 ACU-hours/month. Note For a Data Lakehouse Edition cluster: reserved resources = reserved computing resources + reserved storage resources.	Enabling manual job resubmission is free of charge. You are charged for the resources consumed by manually resubmitted queries. No free quota is provided. You are charged on a pay-as-you-go basis for the resources consumed by the queries. For more information, see Pricing.

Automatic job resubmission

Prerequisites

Your cluster must run kernel version 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 job resubmission

Automatic job resubmission is disabled by default. You can enable it by running a SET statement. Once enabled, it takes effect across the entire AnalyticDB for MySQL cluster. All queries from any interactive resource group that meet the resubmission rules are resubmitted to the destination resource group.

To enable or disable automatic job resubmission:
```
SET ADB_CONFIG SERVERLESS_ROUTER_LEVEL=0|1|2;
```
Valid values:
- 0 (default): Disables automatic job resubmission.
- 1: Enables automatic job resubmission only while the monthly free quota is available.
- 2: Enables automatic job resubmission even after the monthly free quota is exhausted.
To check whether automatic job 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

Specify the target resource group as testjob.

SET ADB_CONFIG SERVERLESS_ROUTER_TARGET = testjob;

Configure max ACUs per query

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

Syntax

SET ADB_CONFIG SERVERLESS_ROUTER_MAX_ACU = <n>;

Example

Specify that a single query can use a maximum of 24 ACUs.

SET ADB_CONFIG SERVERLESS_ROUTER_MAX_ACU = 24;

Check resubmission status

For clusters that run 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 that run a 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>

The following table describes the returned parameters.

Note

If all these fields are empty, the query was not automatically resubmitted.

Parameter	Description
process_id	The job ID.
reason	The reason for the automatic resubmission. Valid values: out of memory: The query was resubmitted due to insufficient memory. long running: The query was resubmitted because it was long-running.
status	The query's execution status in the destination resource group.

Check job ACU usage

Log on to the AnalyticDB for MySQL console. In the navigation pane on the left, choose Cluster Management > Resource Groups. Click the Job Usage Statistics tab to view the ACUs consumed by automatically resubmitted queries.

Manual job resubmission

Prerequisites

Your cluster must run kernel version 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.
A Data Warehouse Edition (elastic mode) cluster must also meet the following requirements:
- At least two resource groups are created. For more information, see Create and manage resource groups.
- Computing resources must be at least 32 cores and 128 GB of memory.

Usage notes

A resubmitted query must be re-executed in the destination resource group. This consumes computing resources and increases the query response time, which can affect the stability of the destination resource group. We recommend not resubmitting query jobs to resource groups that run core business workloads.

Procedure

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

AnalyticDB for MySQL console: The console provides a simple and fast way to configure job resubmission rules.
Workload Manager commands: WLM commands provide a flexible way to configure job resubmission rules for custom and advanced requirements. For more information about the syntax, see WLM.

The following sections describe 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. 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 a job resubmission rule.

Click Create Resource Group in the upper-right corner of the resource group list to configure the rule when creating a resource group.
Click Modify in the Actions column of a resource group to configure the rule for an existing resource group.

The following table describes the parameter for configuring job resubmission rules.

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

Note

For more information about the other parameters for creating a resource group, see Create and manage resource groups.

Click OK.
(Optional) Manage the job resubmission rule. You can perform the following operations:
- To modify the rule, change the parameters in the Modify Resource Group panel.
- To delete the rule, open the Modify Resource Group panel and click the icon next to the resource group name in the Destination Resource Group field to clear the selection. This invalidates the rule.

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 the Job Resubmission tab to view the job resubmission 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, configure the following parameters:

Parameter	Description	Example
Peak memory threshold	The peak memory consumed by a query job, in MB. The default value is null.	24
Execution duration threshold	The execution time of a query job, in ms. The default value is null.	24
Result for OOM Error	Specifies whether to resubmit a query job if it fails with an OOM error. The default value is No.	No
Destination Resource Group	The name of the destination resource group.	test

Important

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

Click OK.
(Optional) Manage the job resubmission rule. You can perform the following operations:
- To modify the rule, change the parameters in the Configure Job Resubmission Rules panel.
- To delete the rule, delete the values for Peak memory threshold and Execution duration threshold in the Configure Job Resubmission Rules panel, and set Result for OOM Error to No. This invalidates the rule.
In the Resubmitted Jobs section, you can view the number of jobs that were successfully resubmitted to another resource group.