Query execution modes

Prerequisites

Your cluster must be of the Data Warehouse Edition product series.

Background

AnalyticDB for MySQL is a cloud service from Alibaba that provides real-time, high-concurrency online analytical processing (OLAP) for massive datasets. It supports instant multi-dimensional analysis and business exploration. The AnalyticDB for MySQL execution engine uses a hybrid MPP DAG architecture and memory-based computing to achieve high speeds. Analytical query workloads are diverse, ranging from low-latency, real-time analytical queries to high-throughput Extract-Transform-Load (ETL) queries. Fully in-memory computing enables low latency for real-time analytical queries. In contrast, ETL queries are less sensitive to latency and prioritize higher throughput and lower processing costs.

To support these different requirements, AnalyticDB for MySQL Data Warehouse Edition introduces a new Batch execution mode in addition to the original, fully in-memory Interactive execution mode. Compared to the Interactive mode, Batch mode executes query tasks in batches. If memory is insufficient, data is adaptively spilled to disk, reducing computation costs and increasing data processing capacity.

Interactive mode

When AnalyticDB for MySQL receives a query, the optimizer parses it and generates an execution plan. The execution engine receives this plan, generates distributed tasks based on the plan's logic, and then schedules and manages these tasks.

In Interactive mode, all distributed tasks for a query are scheduled and run concurrently. Each task runs entirely in memory, and data streams between upstream and downstream tasks in a pipeline.

Applicable scenarios: Ideal for latency-sensitive, real-time analytical queries.

Batch mode

In Batch mode, the execution engine schedules tasks in batches based on their dependencies. During computation, if memory is insufficient, a task adaptively spills data to disk to increase the data processing capacity. Intermediate results are also written to disk. When downstream tasks are scheduled, they read the intermediate data from disk for computation.

Applicable scenarios: Ideal for long-running queries that process large amounts of data, such as Extract-Transform-Load (ETL) jobs.

Default execution mode

Switching execution modes

• Switch the execution mode for an instance:

  • To switch the instance to Interactive mode:

    set adb_config query_type=interactive

  • To switch the instance to Batch mode:

    set adb_config query_type=batch

    Note

    We recommend that you do not switch the execution mode to Batch at the instance level. Instead, set Batch mode at the query or resource group level.

• Switch the execution mode for a resource group.

• Switch the execution mode for a query:

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

Mixed workloads

The resource group feature in AnalyticDB for MySQL provides multi-tenant capabilities within an instance. By combining query execution modes with resource groups, you can set a specific execution mode for each resource group. This allows a single instance to handle mixed workloads. For more information, see Resource groups.