AnalyticDB for MySQL Data Warehouse Edition (V3.0) supports queries in interactive or batch mode. You can use the two query execution modes in combination with resource groups to implement hybrid loads.
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 (V3.0) 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
| Edition | Mode | Default query execution mode | Batch mode |
|---|---|---|---|
| Data Warehouse Edition (V3.0) | Reserved mode for Cluster Edition | Interactive | Not supported |
| Elastic mode for Cluster Edition | Supported |
Change the query execution mode
- You can change the query execution mode of a cluster.
- Execute the following statement to change the query execution mode of a cluster to interactive:
set adb_config query_type=interactive - Execute the following statement to change the query execution mode of a cluster to batch:
set adb_config query_type=batch
- Execute the following statement to change the query execution mode of a cluster to interactive:
- You can change the query execution mode of a resource group. For more information, see Modify a resource group.
Hybrid loads
AnalyticDB for MySQL resource groups support multiple tenants within a cluster. You can configure query execution modes for different resource groups. This way, a cluster can process hybrid loads at the same time. For more information, see Resource group overview.