Running DuckDB as a MySQL 9.7 storage engine

ducksdb-mysql-engine is an experimental build of MySQL 9.7 where a table you mark ENGINE=DuckDB answers analytical queries from DuckDB instead of InnoDB. Same server, same connection, no second copy of the data. On TPC-H at scale factor 10, InnoDB times out on 6 of the 22 queries and burns 1317 seconds on the 16 it finishes. The DuckDB tables run all 22 in about 15 seconds.

It’s an experiment, not production software. It patches mysqld and has rough edges, which we list at the end. Source is on GitHub under GPLv2:     https://github.com/EvgeniyPatlan/ducksdb-mysql-engine.

Why we made it

MySQL is great for transactions and slow at analytics. A wide GROUP BY over a few hundred million rows, or a six-way join, takes minutes on InnoDB. The usual fix is to copy the data into a column store and keep it in sync, so now you’re running two systems and the pipeline between them.

We wanted the table itself to be the column store, with the heavy queries offloaded for you. Mark it ENGINE=DuckDB, query it the way you always have, and DuckDB does the analytical work.

What it actually is

DuckDB is an in-process columnar query engine, basically SQLite for OLAP. It stores data by column and it’s built for scans and aggregations, which is exactly what a row store is bad at.

We’re not the first to put it behind a relational table. Alibaba’s AliSQL has had a built-in DuckDB engine for a while. MariaDB shipped MariaDB DuckDB about a week ago, while we were already building ours. AliSQL got there first; MariaDB and we landed on the same idea independently, around the same time, them on MariaDB and us on stock MySQL 9.7. Their engine is the closest comparison to ours, so it’s in the benchmarks below.

How it hooks into MySQL

MySQL doesn’t have a select_handler, the API MariaDB uses to grab a whole SELECT and run it inside an engine. We added our own: a handlerton::pushdown_select hook.

The pushdown path. Either the whole query renders to DuckDB SQL and runs columnar, or it declines and the normal row path handles it.

The engine is compiled into mysqld, and each schema is one DuckDB file under the datadir. Three patches do the integration, and all three are generic, so they’ll fire for any engine that exposes the hook:

• The hook runs at the end of JOIN::optimize(). If every base table in the block is one engine that has the hook, that engine looks at the optimized JOIN, and if it can translate the whole query it sets JOIN::override_executor_func (which the executor already checks in sql_union.cc). The query gets regenerated as DuckDB SQL, prepared once, run, and the aggregated result is staged into a temp table. EXPLAIN is left alone.
• A server-side LOAD DATA INFILE goes into a DuckDB COPY instead of crawling through write_row row by row. At 600M rows that’s a 20-minute load instead of 80.
• For single-engine statements we clear OPTIMIZER_SWITCH_SEMIJOIN in prepare, so IN, EXISTS, NOT IN and NOT EXISTS stay as subqueries the builder can render instead of getting rewritten into semijoin nests it can’t recognize.

The builder only renders a node when the output is provably identical to what MySQL would return. If it can’t, it declines and MySQL runs the query unchanged. Literals are bound as parameters. Collation, NULL ordering and decimal scale are matched on purpose, and an unmapped collation or a REAL literal is enough to make it back off. With that in place, all 22 TPC-H queries push down and match InnoDB row for row.

Getting started

The fastest way in is the image:

Make a table, put a few rows in, run an aggregate:

Nothing about that query is special, and that is the point. To check it actually went to DuckDB rather than down the row path, watch the Ducksdb_pushdown_count status variable:

The single-row lookup stays on the row path deliberately. For one row an index seek beats spinning up a DuckDB result, so there is no reason to offload it. OLTP keeps its path, analytics get the column store, and you do not pick by hand.

If you would rather build it, you need the MySQL 9.7 tree under vendor/mysql-server/ and a DuckDB prefix, then:

Does it actually go fast?

All 22 TPC-H queries, were executed in a Docker on one laptop (20 cores, 62 GiB RAM), the same data loaded into four engines: InnoDB, our MySQL+DuckDB, MariaDB+DuckDB, and standalone DuckDB as the reference. Warm wall-clock, minimum over a few runs, in seconds.

SF10, around 60 million lineitem rows

A handful of rows here; the whole table is in docs/tpch_engine_comparison.md:

SF10, all 22 queries, log scale (lower is better). Hatched InnoDB bars did not finish inside 180 s. The three DuckDB engines sit in a tight band near the floor.

InnoDB is somewhere between 100 and 340 times slower per query, and on 6 of the 22 it never finished inside the 180-second cap (the correlated subqueries and the heaviest scans). It burned 1317 seconds on just the 16 it did finish. The three DuckDB engines get through all 22 in about 15 seconds, and ours lands right between MariaDB and plain DuckDB. The gap is so big there is not much else to say about it.

SF100, around 600 million lineitem rows

At this size InnoDB is out of the running (a copy of the data alone is about 100 GB and queries run for hours), so it is the three DuckDB engines only, run one at a time:

SF100, three DuckDB engines, log scale (lower is better). Q15 for our engine is shown at its matched-memory time (~4 s); the capped run measured 1309 s, explained below. MariaDB errored on Q10 and Q13.

At 600 million rows ours is still correct on all 22 and stays close to plain DuckDB. MariaDB’s engine drops two queries (Q10, and Q13 on its column-list syntax) and is a lot slower on the big joins – Q9 took 115 seconds against our 21 and native’s 20.

One honest word on Q15 at SF100, because in the full table it shows an ugly number for our engine. It is not a real loss. We capped DuckDB’s memory so it spills to disk instead of getting OOM-killed inside mysqld, and under that cap Q15’s CTE spills a lot. Give it the memory MariaDB had and it runs in about 4 seconds, like native. The answer was always right; only the clock was bad.

And one number we did not expect: loading those 600 million rows took about 20 minutes with our engine (the COPY shortcut) versus about 80 minutes with MariaDB, which loads row by row on a single core. Roughly four times faster to get the data in.

Try it, then tell us

If any of this sounds useful, pull the image and throw your own queries at it:

The source is on GitHub (GPLv2), patches and benchmark harness included: https://github.com/EvgeniyPatlan/ducksdb-mysql-engine. The full per-query benchmark and how we measured it live in docs/tpch_engine_comparison.md.