Interactive vs Batch Query Modes for OLAP Workloads - AnalyticDB for MySQL

AnalyticDB for MySQL Data Warehouse Edition supports two query execution modes: Interactive and Batch. Use these modes with resource groups to handle mixed workloads on a single cluster.

Prerequisites

The cluster must be Data Warehouse Edition.

How it works

AnalyticDB for MySQL is a real-time online analytical processing (OLAP) service. Its execution engine combines massively parallel processing (MPP) with directed acyclic graphs (DAGs) to run queries at high speed using memory.

When a query arrives, the execution engine parses it, generates an execution plan, and distributes tasks across the cluster. How those tasks are scheduled and whether disk storage is used depends on the execution mode.

Interactive mode

Interactive mode is optimized for low-latency, real-time analytics.

All distributed tasks for a query are scheduled and run simultaneously. Data flows from upstream to downstream tasks in a pipeline, and all computation stays in memory.

Use Interactive mode when:

Queries must return results within seconds
Workloads are real-time dashboards, ad hoc exploration, or concurrent analytical queries

Batch mode

Batch mode is designed for high-throughput workloads that process large data volumes.

The execution engine schedules tasks in batches based on their dependencies. When memory is insufficient, data automatically spills to disk — enabling large jobs to complete without running out of memory. Intermediate results are written to disk and read back when downstream tasks run.

This disk spill capability is core to Batch mode: it lets large-scale extract, transform, load (ETL) jobs finish reliably under memory constraints, at lower compute cost and higher throughput than running them in Interactive mode.

Use Batch mode when:

Queries are expected to run for minutes or longer
Jobs involve large data volumes that may exceed available memory
Workloads are ETL pipelines or batch data processing

Default query execution mode

Edition	Product series	Default mode	Supports Batch mode
Data Warehouse Edition	Reserved mode	Interactive	No
Data Warehouse Edition	Elastic mode		Yes

Change the query execution mode

AnalyticDB for MySQL lets you set the execution mode at three levels of granularity: cluster, resource group, or individual query. Set the mode at the most specific level that fits your use case.

Cluster level

Changing the mode at the cluster level affects all queries that don't have a resource group or query-level override. In most cases, avoid switching the instance to Batch mode — configure Batch mode at the resource group or query level instead.

To switch the cluster to Interactive mode:

set adb_config query_type=interactive

To switch the cluster to Batch mode:

set adb_config query_type=batch

Resource group level

Configure different execution modes for different resource groups to run Interactive and Batch workloads in parallel. See Change the query execution mode of a resource group.

Query level

Override the execution mode for a single query using a SQL hint:

/* query_type=batch|interactive*/
SELECT * FROM test_table;

Hybrid loads

Resource groups support multi-tenant isolation within a single cluster. Assign Interactive mode to resource groups that handle real-time queries and Batch mode to resource groups that run ETL jobs. The cluster processes both workload types simultaneously, with each resource group drawing from its allocated compute and memory resources. See Resource group overview.