AnalyticDB for MySQL:Query execution modes

Background information

AnalyticDB for MySQL is a real-time online analytical processing (OLAP) service that is developed by Alibaba Cloud to analyze large amounts of data at high concurrency. AnalyticDB for MySQL can instantly analyze large amounts of data across multiple dimensions and provide data-driven insights into your business. AnalyticDB for MySQL utilizes an execution engine that integrates massively parallel processing (MPP) with directed acyclic graphs (DAGs). This allows AnalyticDB for MySQL to compute data at high speeds by using memory. Analysis queries involve a variety of loads, including low-latency real-time analysis queries and high-throughput extract-transform-load (ETL) queries. You can perform real-time analysis queries with low latency based only on memory. ETL queries have low requirements on query latency but require lower processing costs and higher throughput.

To address the preceding issues, AnalyticDB for MySQL Data Warehouse Edition provides the batch mode based on the original interactive mode in which only memory is used for computing. Unlike the interactive mode, the batch mode can be used to execute query tasks in batches. If memory is insufficient, each subtask can transfer memory data to disks. This reduces computing costs and increases data processing capacities.

Interactive mode

After AnalyticDB for MySQL receives queries from users, it parses the queries and uses the optimizer to generate execution plans. The execution engine receives the execution plans and then generates corresponding distributed execution tasks. These distributed execution tasks are computed based on the logic described in the execution plans. The execution engine schedules and manages these distributed execution tasks.

In interactive mode, all the distributed execution tasks of a query are scheduled and executed at the same time. Each task is computed based only on memory. Data is transferred from upstream tasks to downstream tasks in a pipelined manner.

The interactive mode is suitable for real-time analysis queries that require low latency.

Batch mode

In batch mode, the execution engine schedules distributed execution tasks in batches based on their dependencies. If memory is insufficient while tasks are being computed, data is automatically transferred to disks to increase data processing capacities. Intermediate data generated during computing is also written to disks. When downstream tasks are scheduled to be executed, intermediate data is read from disks for computing.

The batch mode is suitable for large-volume queries that require a long period of time to execute, such as ETL queries.

Default query execution mode

Change the query execution mode

• You can change the query execution mode of an AnalyticDB for MySQL cluster.

  • Execute the following statement to change the query execution mode of an AnalyticDB for MySQL cluster to interactive:

    set adb_config query_type=interactive

  • Execute the following statement to change the query execution mode of an AnalyticDB for MySQL cluster to batch:

    set adb_config query_type=batch

    Note

    In most cases, we recommend that you change the query execution mode to batch for a query or a resource group instead of for a cluster.

• For information about how to change the query execution mode of a resource group, see the "Modify a resource group" section of the Create a resource group topic.

Hybrid loads

AnalyticDB for MySQL resource groups support multiple tenants in a cluster. You can configure different query execution modes for the resource groups in a cluster. This way, a cluster can process hybrid loads at the same time. For more information, see Resource group overview.