RDS for MySQL DuckDB-based analytical instances use columnar storage and vectorized execution to accelerate complex analytical queries by 100x while significantly reducing storage costs.
Features
Two types are available:
-
DuckDB-based analytical primary instance
-
A standalone database instance similar to a regular RDS for MySQL primary instance. Available in Cluster Edition with multiple readable standby nodes.
-
Core features:
-
Retains all features of a regular RDS for MySQL instance and is compatible with existing tools and ecosystems. Overview of a DuckDB-based analytical primary instance.
-
Integrates the DuckDB analytical engine into the MySQL kernel, combining columnar storage and vectorized execution to deliver both strong transaction support and high-performance analytical processing.
-
-
-
DuckDB-based analytical read-only instance
-
A read-only instance attached to a regular RDS for MySQL High-availability Edition instance.
-
Core features:
-
Seamless data flow: Uses native binlog replication to automatically synchronize data and transform table schemas, eliminating the need for a separate synchronization link.
-
Integrated HTAP: Supports analytical queries through direct connections to the DuckDB read-only instance or automatic routing via a database proxy. This provides hybrid transactional and analytical processing (HTAP), ensuring TP performance of the primary instance while improving analytics efficiency.
-
-
|
Comparison item |
DuckDB-based analytical primary instance |
DuckDB-based analytical read-only instance |
RDS for MySQL read-only instance |
OLAP database |
|
|
Scenario |
Complex analytic queries |
Complex analytic queries |
Transaction processing |
Complex analytic queries |
|
|
Analytic query performance |
Strong |
Strong |
Weak |
Strong |
|
|
Data synchronization method |
DTS data synchronization link |
Native replication based on binary logging |
Native replication based on binary logging |
DTS data synchronization link |
|
|
MySQL compatibility |
Data type |
Fully compatible |
Fully compatible |
Fully compatible |
Incompatible (requires field mapping) |
|
SQL syntax |
Highly compatible (> 99.9%) |
Highly compatible (>99.9%) |
Fully compatible |
Incompatible (requires SQL rewrite) |
|
|
DDL |
Highly compatible |
Highly compatible (supports automatic rebuilding) |
Fully compatible |
Partially compatible |
|
|
O&M costs |
Low |
Low |
Low |
High |
|
|
Deployment model |
Standalone deployment |
Attached to a regular RDS for MySQL instance |
Attached to a regular RDS for MySQL instance |
Standalone deployment |
|
|
Supported edition |
Cluster Edition |
High-availability Edition |
Basic Edition, High-availability Edition |
- |
|
Scenarios
-
Aggregate analysis: Efficient aggregate queries for real-time analysis, such as log data analytics.
-
Multi-table join queries: Significantly improves MySQL analytical performance for workloads involving multi-table
JOINoperations.
Technical principles
What is DuckDB?
DuckDB is a standalone, embedded OLAP database that balances high-performance analytics with transaction processing:
-
High-performance analytics: Columnar storage accelerates aggregate queries, and the vectorized execution engine processes data in batches.
-
Strong transaction support: Full ACID compliance with MVCC for efficient concurrent reads and writes.
Technical optimizations
ApsaraDB RDS integrates the DuckDB engine into the MySQL ecosystem, combining high-performance analytics with enterprise-grade data reliability and consistency.
Shared optimizations
-
Storage engine optimization
-
Encapsulates DuckDB columnar storage as a MySQL transactional storage engine, using the standard data dictionary for metadata management. Fully compatible with primary instance transaction semantics.
-
Enhances binlog-based synchronization and uses DuckDB write-ahead logging (WAL) for transaction persistence, ensuring data consistency between the RDS instance and the DuckDB analytical instance.
-
-
Compute engine enhancement
-
Integrates the DuckDB optimizer, vectorized execution engine, and JIT compiler to improve complex query performance by 100x compared with InnoDB.
-
Supports 99.9% of MySQL syntax and functions, so existing queries run without modification.
-
The Result Translator converts DuckDB results into MySQL protocol format for seamless client integration without application changes.
-
-
Query acceleration
-
Columnar storage: Data stored in DuckDB's native columnar format improves aggregate query performance by over 100x. For example, a SUM query runs 100x faster than on InnoDB.
-
Automatic hot data caching: Uses DuckDB's buffer pool to cache frequently queried data, providing stable performance in high-concurrency scenarios.
-
Read-only instance optimizations
-
Data synchronization performance improvement
-
Batches high-frequency small transactions into larger ones to reduce write latency.
-
Idempotent replay: Performs idempotence checks on binlog events to ensure strong data consistency.
-
-
DDL synchronization optimization
-
Automatically routes DuckDB-native DDL operations (such as creating tables and adding or removing columns) directly to the DuckDB engine without conversion.
-
For unsupported DDL operations, automatically triggers a table rebuild to prevent replication interruptions.
-
-
Resource isolation: The DuckDB read-only instance runs in isolation from the primary instance, so analytical queries do not affect online transaction processing.
Query workflow
-
SQL parsing: A user's SQL query is parsed by the standard MySQL parser.
-
SQL execution: The parsed query is routed to the DuckDB compute engine for execution.
-
Data retrieval: Columnar data is retrieved from the DuckDB storage engine.
-
Format conversion: The computation result is converted by the
Result Translatorcomponent into the MySQL protocol format and then sent to the client.