[Discussion]: Support DuckDB as a columnar storage engine being added to MySQL #79
Replies: 5 comments 1 reply
|
Thank you @baotiao and team for upstreaming this! I thought of a few minor questions based on first read of the proposal:
I assume this is also compressed InnoDB? To make it explicit, if you have recorded this information, could explicitly mention this and more specifically also which InnoDB compression configuration was used.
Are there any types in DuckDB that you would rather see added in future MySQL versions (or perhaps via plugins, existing MySQL versions) rather than conversion? |
|
In this test, InnoDB compression was not enabled. This is because, in the vast majority of production scenarios, InnoDB compression is typically left off. Regarding the second point: there is a mapping relationship between DuckDB and MySQL data types. DuckDB can essentially cover all of the standard MySQL field types. |
|
We have a related ask in VillageSQL to support DuckDB in the server as an extension, to mimic the functionality in pg-duckdb We are tracking in this issue, and it references the AliCloud perf improvements - villagesql/villagesql-server#322 |
|
Hi everyone, I’m from the Alibaba Cloud RDS MySQL team (AliSQL). I’ll share some implementation details of our approach to integrating DuckDB as a MySQL storage engine. Happy to discuss and exchange ideas with the community! |
Integrate DuckDB as a MySQL Analytical Storage Engine
1. Description1.1 BackgroundThe default MySQL storage and execution paths are primarily designed for transactional processing. For analytical workloads involving large scans, aggregations, and complex joins, row-oriented storage and row-at-a-time execution cannot fully exploit modern CPUs and columnar data layouts. This WorkLog introduces embedded DuckDB as a columnar, vectorized MySQL storage engine for analytical workloads. Users continue to access data through the MySQL protocol, account system, and SQL interface, while selecting 1.2 PositioningIn this design, DuckDB is a MySQL storage engine, not a MySQL secondary engine:
A MySQL instance may contain both InnoDB and DuckDB tables, but one relational expression cannot be jointly evaluated by DuckDB and another storage engine. Controlled one-way ingestion is the exception: MySQL may read a non-DuckDB source and write the resulting rows to DuckDB through the handler interface. A transaction may also modify different engines in separate statements and let the MySQL transaction coordinator commit them together. 1.3 Document ScopeThis integration depends on an Alibaba-maintained downstream DuckDB fork rather than an unmodified upstream DuckDB. The fork contains internal changes required by the MySQL integration. This document treats the DuckDB fork as a versioned dependency and defines only the integration boundary between MySQL and DuckDB. The design of DuckDB-internal changes, the patch inventory, and differences from upstream are intentionally excluded and should be documented separately. 1.4 Primary Use Cases
1.5 Goals
1.6 Non-Goals
1.7 Terminology
2. User Documentation and Requirements2.1 Table Definition and MigrationUsers select DuckDB with standard MySQL syntax: CREATE TABLE fact_sales (
id BIGINT NOT NULL,
sale_time DATETIME,
amount DECIMAL(18, 2),
PRIMARY KEY (id)
) ENGINE=DUCKDB;
ALTER TABLE fact_sales ENGINE=INNODB;
ALTER TABLE fact_sales ENGINE=DUCKDB;DuckDB tables remain visible through 2.2 SQL Execution Behavior
For supported whole-statement shapes, 2.3 Configuration and StatusThe implementation exposes the following categories of configuration and status. The complete variable list belongs in separate user documentation:
2.4 Functional Requirements
2.5 Non-Functional Requirements
3. High-Level Architecture3.1 Overall Architecture3.2 Responsibility BoundariesMySQL is responsible for:
DuckDB is responsible for:
The adaptation layer is responsible for:
3.3 Instance and Session ModelThe current implementation creates one embedded DuckDB instance per mysqld process. All local DuckDB tables belong to that instance and are persisted in a single database file under the data directory. DuckDB manages the WAL and temporary spill files. Each MySQL THD that accesses DuckDB owns a separate DuckDB connection. The connection follows the THD lifecycle, isolating transaction state and session configuration while sharing the process-wide DuckDB catalog, buffer manager, and task scheduler. 3.4 Startup and ShutdownAt startup, mysqld constructs the DuckDB instance from global resource settings, opens or recovers the persistent database, and initializes time-zone and MySQL-compatibility functions. When binary logging is enabled, it also initializes the binary-log coordination table. Initialization failure aborts server startup. At shutdown, conversion and other background work stops first. MySQL handler and transaction resources and DuckDB connections are released before the process-wide DuckDB instance is closed, allowing DuckDB to perform required persistence cleanup. 3.5 Whole-Statement SELECT Execution
This path preserves the MySQL entry-point security model while preventing the MySQL optimizer and row iterator executor from re-executing the analytical plan. 3.6 Write PathsWrites use two paths:
The handler row path can select direct DML or a bulk appender:
All pending appenders shall be flushed before transaction prepare/commit, whole-statement UPDATE/DELETE, or related DDL, preserving operation order within one connection. 3.7 DDL and the Data DictionaryThe MySQL data dictionary is authoritative for user-visible metadata. Before DDL reaches the handler, the SQL layer:
The handler converts the resulting definition into DuckDB DDL. Simple column changes prefer native transactional DuckDB DDL. Engine conversion, cross-schema rename, and changes that cannot be represented natively use COPY ALTER. COPY ALTER creates an intermediate table, copies data, and atomically replaces the original table. Failure paths clean up intermediate objects that may have been committed early for parallel copy. 3.8 Transactions and Binary-Log Crash ConsistencyWithout binary logging, MySQL explicitly starts a DuckDB transaction on the first DuckDB write and commits or rolls it back at the corresponding MySQL COMMIT or ROLLBACK boundary. With binary logging enabled, an internal DuckDB table records the recoverable binary-log position as part of commit coordination: The recovery rules are:
When DuckDB mode is enabled, this coordination position is updated even for a transaction that does not modify a DuckDB user table, preserving a continuous binary-log confirmation boundary. 3.9 Replication and Analytical ReplicasA typical topology uses InnoDB tables on the source and DuckDB tables on the analytical replica. The source continues to produce standard binary logs, while the replica reuses the MySQL applier:
For higher apply throughput, multiple consecutive source transactions may be committed in one DuckDB transaction. A batch is bounded by timeout, accumulated bytes, binary-log rotation, reader idle state, DDL, and non-row events. This mode requires a single-threaded applier and is incompatible with parallel replication workers. Crash recovery may read recent relay-log events again. Idempotent mode converts INSERT into removal of an older logical-key version followed by insertion of the new version, with protective checks for key shapes that could otherwise delete multiple rows. 3.10 Table ConversionConversion has three entry points:
Startup conversion first creates DuckDB schemas and handles unsupported foreign keys, then converts tables in parallel. Objects that fail because of MDL conflicts may be retried serially. Ordinary user access can be restricted during conversion, and status variables report the current stage. System schemas, explicitly excluded tables, incompatible tables, and tables above the configured average-row-length threshold are not converted. 4. Low-Level Design OverviewThis section describes the module boundaries, core objects, and principal state flows in the MySQL integration. It is not a function-by-function specification, does not cover the implementation inside the DuckDB fork, and does not turn temporary coupling in the current integration into a long-term stable interface. 4.1 Code Boundaries
MySQL SQL-layer changes currently span the existing SELECT, INSERT, UPDATE, DELETE, DDL, handler, transaction, binary-log, and replication modules. 4.2 Core Objects and Lifecycles
A core lifecycle invariant is that all DuckDB operations in one MySQL transaction use the same 4.3 Storage-Engine Registration and CapabilitiesDuckDB registers as a The handler covers these entry-point groups:
Handler index access is not implemented. A logical primary key in the MySQL data dictionary does not allow the optimizer to select an index scan through the DuckDB handler. Analytical queries are expected to use whole-statement execution. 4.4 Query Routing and ExecutionQuery routing has prepare and execute phases:
DuckDB SQL construction has three levels:
Before execution, these invariants shall hold:
4.5 DDL ImplementationDDL is divided between SQL-layer policy and handler-side physical execution. The SQL-layer policy:
The handler-side DDL converters receive normalized MySQL Native ALTER executes in the current DuckDB transaction. COPY ALTER reuses the MySQL intermediate-table state machine: create the target DuckDB table, copy rows through the handler, rename it over the original, and clean up the intermediate table on failure. Parallel COPY optimizes data movement without changing this state machine. 4.6 Row DML and Bulk AppendersHandler row writes have three batch states:
Within a mixed batch, UPDATE is represented as deletion of the old row followed by insertion of the new row. In addition to business columns, a delta row carries operation type, row sequence, and transaction sequence. Flush selects the last version for each logical key, deletes old versions from the target, and bulk-inserts final versions. This path relies on the following conditions:
4.7 Type, Session, and Result ConversionType adaptation has three directions:
Whole-statement result types do not directly use DuckDB's inferred result metadata. Instead, the Item type, unsigned flag, decimals, and collation resolved during MySQL prepare form the protocol template. This keeps client-visible metadata aligned with MySQL semantics. Each session context caches the schema, time zone, collation, statement time, 4.8 Transaction AdaptationThe first DuckDB write performs these actions:
The prepare callback flushes all appenders. The commit callback commits the DuckDB transaction on the connection. The rollback callback clears batch and GTID state and rolls back the DuckDB transaction. With binary logging enabled, the prepare callback also makes DuckDB visible to the MySQL transaction coordinator as a prepare participant, although DuckDB does not implement generic XA prepared transactions. During commit, DuckDB is moved to the first engine position. A DuckDB commit failure stops iteration before other engines; success allows the remaining MySQL commit sequence to continue. Binary-log position coordination and startup recovery follow the state order in Section 3.8. The current savepoint callbacks do not create DuckDB savepoints. They satisfy the MySQL callback contract but cannot provide partial rollback semantics. 4.9 Replication Batch StateReplication batch state is also stored in
Rows events continue to call handler row interfaces. An Xid event may defer the physical commit. When time, length, rotation, reader state, or statement type establishes a boundary, the implementation reuses the last complete Xid commit path and advances the batch GTID set and relay/source positions together. A batch that may be replayed after a crash uses logical-key idempotency. 4.10 Locking and ConcurrencyThe DuckDB handler reports zero MySQL table locks and does not use THR_LOCK as its data concurrency mechanism. MySQL MDL continues to protect table definitions and DDL lifecycles, while the DuckDB transaction manager handles data conflicts and visibility. Whole statements, handler DML, and appenders in one session share a connection, so they observe that session's uncommitted changes in order. Different sessions use different connections coordinated by DuckDB MVCC. Query cancellation calls connection interrupt directly, and result-sending loops periodically check interruption state. 4.11 Error Boundaries and ObservabilityDuckDB The handler maintains row-write and transaction counters. The SQL layer maintains counters for whole-statement SELECT/UPDATE/DELETE/INSERT SELECT/EXPLAIN. DuckDB internal logs are written to the MySQL error log through a custom log storage, and optional diagnostics can record forwarded SQL and replication batch reasons. 4.12 Build and Fork BoundaryThe build system treats This WorkLog constrains only the DuckDB APIs, settings, and external behavior consumed by the MySQL side. It does not specify how those capabilities are implemented inside the fork. DuckDB-internal changes require a separate design document and change inventory. Fork upgrades shall run both DuckDB's own tests and the MySQL DuckDB MTR suites; successful compilation alone is insufficient. 4.13 Current Constraints and Convergence DirectionThe following items describe the current implementation and are not intended as ideal long-term module boundaries:
The complete field mapping, rewrite rules, system variables, error codes, lock ordering, and per-crash-point state machine remain subjects for a future detailed LLD. 5. Compatibility, Operations and Test Plan5.1 Compatibility ModelCompatibility is implemented in four layers:
The compatibility target is behavior consistent with MySQL within the supported surface, not a claim that every MySQL statement is fully equivalent to InnoDB. Unsupported syntax, types, functions, or boundary values should fail explicitly rather than silently produce untrusted results. 5.2 Locking and Concurrency
5.3 Storage and Metadata
5.4 Observability and Error Handling
5.5 Compatibility and Limitations
5.6 Upgrade, Downgrade and Rollback
5.7 Security Considerations
5.8 Functional Tests
5.9 Transaction and Recovery Tests
5.10 Replication Tests
5.11 Non-Functional Tests
6. Alternatives, Dependencies, Open Issues and References6.1 MySQL Secondary EngineNot selected. A secondary engine requires load, refresh, and consistency state for a primary store and an analytical copy. This design instead lets DuckDB own the table data directly and permits an analytical replica to retain only DuckDB physical data. 6.2 External DuckDB ServiceNot selected. An external service introduces a network protocol, an independent catalog, distributed transactions, and another operational surface, and cannot directly reuse the MySQL handler, transaction, and replication infrastructure. 6.3 Handler Row Interface OnlyNot selected. A row-only handler would be smaller, but the MySQL row executor would still run complex analytical queries, preventing use of DuckDB's optimizer and vectorized execution. 6.4 Dependencies
6.5 Open Issues
6.6 References |
Uh oh!
There was an error while loading. Please reload this page.
Uh oh!
There was an error while loading. Please reload this page.
Original Issue
Issue: #76
Author: @baotiao
Category: Extensibility/Ecosystem
MySQL's pluggable storage engine architecture was designed so that engines targeting different workloads could be integrated behind a common handler interface. InnoDB made MySQL the most widely used open-source OLTP database, but MySQL still lacks a native analytical engine. InnoDB's row-oriented design performs poorly on analytical queries, so users today either accept slow reporting queries on their OLTP data, or ETL the data into an external OLAP system (ClickHouse, Doris, data warehouses). The second option adds operational complexity, data freshness lag, and a second query dialect for applications to learn.
This proposal adds DuckDB as a columnar storage engine for MySQL, giving MySQL native OLAP capability while keeping full compatibility with the MySQL protocol, SQL syntax, replication, and operational tooling. DuckDB is a good fit for this role:
All reactions