DuckDB inside OLTP databases

VillageSQL

Versions

Run asImageServerDuckDBDigest
VillageSQLdbench/villagesql:0.0.7-duckdb-0.0.3VillageSQL 0.0.7 (MySQL 8.4) + vsql_duckdb 0.0.31.5.5sha256:19d420e5d207afaca0b6bd355a9aa5dcd5327d3feef209ec696b0eaced99e68c

Observed in this run

Generated the same way for every system from its result files: record counts per measure and status, and the reasons given for anything that is not ok.

VillageSQL

Settings that differ from the defaults

SettingValueReasonFile
skip-log-binBinary logging is off on every MySQL-family server (MySQL, Percona and AliSQL enable it by default, MariaDB does not): replication is not under test and the default differs between families.systems/villagesql/my.cnf
innodb_buffer_pool_size4GThe InnoDB side only holds the small table the JSON_TABLE join example uses; the analytical data is read by DuckDB from Parquet, so a large pool would only take memory from DuckDB.systems/villagesql/my.cnf
innodb_parallel_read_threads16Same as the other MySQL-family servers: every core for the two operations that use parallel reads.systems/villagesql/my.cnf
innodb_redo_log_capacity8GSame redo log capacity as the InnoDB baseline.systems/villagesql/my.cnf
join_buffer_size256MPer-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline.systems/villagesql/my.cnf
sort_buffer_size256MPer-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline.systems/villagesql/my.cnf
tmp_table_size4GInternal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline.systems/villagesql/my.cnf
max_heap_table_size4GSame cap for MEMORY-engine temporary tables, which use the smaller of the two sizes.systems/villagesql/my.cnf
temptable_max_ram4GRAM the TempTable engine may use before spilling, equal to the caps above.systems/villagesql/my.cnf
secure_file_priv/dataServer-side LOAD DATA INFILE (the nation table of the join example) may read only the dataset mount.systems/villagesql/my.cnf
local_infileOFFLoads are server-side from /data; client-side LOCAL loads stay disabled.systems/villagesql/my.cnf
max_connections200Headroom for probes and cancel connections.systems/villagesql/my.cnf
character-set-serverutf8mb4vsql_duckdb returns utf8mb4 text; the server default is already utf8mb4, set so it cannot change.systems/villagesql/my.cnf
vsql_duckdb.threads16One DuckDB worker thread per core pinned to the database container (the extension default is 2).systems/villagesql/settings.sql
vsql_duckdb.memory_limit_mb49152Caps DuckDB at 75% of the container, the share the other MySQL-family engines give DuckDB (the extension default is 1024 MB).systems/villagesql/settings.sql
vsql_duckdb.allow_local_filesONRequired to read the Parquet files on the dataset mount (the default OFF refuses local files); the same switch also lets DuckDB spill to disk.systems/villagesql/settings.sql
vsql_duckdb.temp_directory'/var/lib/mysql/duckdb_tmp'Explicit spill location on the data volume (only used while allow_local_files is ON).systems/villagesql/settings.sql
vsql_duckdb.timeout_ms900000The benchmark's per-query timeout (900 s) in ms; the extension default is 30000 and its maximum is 3600000. The adapter lowers it per query when a run uses a shorter --timeout.systems/villagesql/settings.sql
vsql_duckdb.max_result_bytes16777216The largest result the extension accepts (default 1048576). The result buffer the extension declares is 1 MiB on the stable extension ABI, so results above 1 MiB are still refused; see systems/villagesql/README.md.systems/villagesql/settings.sql

README

VillageSQL Server 0.0.7 + vsql_duckdb 0.0.3 (Track B)

Why VillageSQL is a separate track

The other MySQL-family systems (Percona, MariaDB, AliSQL) are measured in Track A as storage engines: the TPC-H tables live in a DuckDB engine inside the server, the same SQL text is sent to every system, and the server decides whether DuckDB runs it. VillageSQL's vsql_duckdb extension is built differently. It adds three SQL functions, duckdb_query(sql), duckdb_scalar(sql) and duckdb_status(), that hand DuckDB SQL text to one embedded DuckDB and return the answer as text. There is no DuckDB table type, no ENGINE= clause and no automatic routing of ordinary SQL. The data stays in external files (Parquet, CSV, JSON; local or remote), and the query is DuckDB SQL over read_parquet(...), not MySQL SQL over tables.

So the question Track A asks ("how fast is this server on TPC-H tables it stores?") has no VillageSQL answer, and a different question applies: how fast is each system's raw DuckDB entry point on the same Parquet files? Track B asks that of every system that has one:

System (Track B name)Entry pointQuery text
duckdb-1.5.5 (the reference)the DuckDB query server, read_parquet()canonical DuckDB text
villagesqlSELECT duckdb_query('<sql>'), JSON parsed into rowsthe same
pg_duckdb-trackbSELECT * FROM duckdb.query($dbq$<sql>$dbq$)the same
mariadb-trackbSELECT run_in_duckdb('<sql>'), text parsed into rowsthe same

On the site, VillageSQL is listed with the other systems and Track B is the "Parquet entry points" section of the overview and charts pages. Track B results never appear on a Track A chart, and VillageSQL is not compared with the storage-engine systems in Track A.

The Track B query text

dbench/queries/trackb.py has the one substitution function (substitute_parquet) all four systems share. It takes the canonical DuckDB text of each TPC-H query (dbench/queries/tpch/) and replaces every table reference (the word after FROM, JOIN or a comma in a FROM list) by read_parquet('/data/parquet/<table>/<table>.*.parquet'), appending AS <table> when the query gives no alias and keeping an explicit alias (nation n1, lineitem AS l1) as written. Nothing else changes. Tests run it on all 22 queries and check that every alias survives and that undoing the substitution restores the canonical text. trackb_diff(n) prints the difference for one query.

How a result is read back

duckdb_query() returns one string: a JSON array with one object per row, keys in column order.

Settings (and why)

The image is dbench/villagesql:0.0.7-duckdb-0.0.3, built by the Dockerfile here:

1. Stage 1 (Ubuntu 24.04, the server image's base, pinned by digest) builds vsql-duckdb at tag 0.0.3 (commit a3cefdb) against the 0.0.7 extension SDK (release asset villagesql-extension-sdk-0.0.7.tar.gz, SHA-256 checked) with DUCKDB_TAG=v1.5.5, the extension's default, plus the two development-ABI source edits described below. The extension builds DuckDB 1.5.5 from source and links it in statically. 2. Stage 2 is villagesql/server:mysql-8.4_0.0.7, pinned by digest, with the resulting vsql_duckdb.veb copied into /usr/lib/veb (the server's veb_dir). 3. install-extension.sql runs once on an empty datadir: SET PERSIST vsql_allow_preview_extensions = ON; INSTALL EXTENSION vsql_duckdb;.

Settings are rendered from the container limits (dbench/systems/villagesql.py). my.cnf is mounted at /etc/my.cnf; settings.sql is mounted into /docker-entrypoint-initdb.d/ and SET PERSISTs the extension's variables, so a restarted container keeps them. Every line has a reason comment in the file. At 64 GB and 16 cores:

SettingValueReason
vsql_duckdb.threadscores = 16One DuckDB worker thread per pinned core (extension default 2).
vsql_duckdb.memory_limit_mb0.75 x mem_gb x 1024 = 49152Same 75% share the other MySQL-family engines give DuckDB (default 1024).
vsql_duckdb.allow_local_filesONWithout it the extension refuses to read the Parquet files on the dataset mount; the same switch also lets DuckDB spill to disk.
vsql_duckdb.temp_directory/var/lib/mysql/duckdb_tmpExplicit spill directory on the data volume.
vsql_duckdb.timeout_ms900000 (the 900 s query timeout)The extension's own deadline; its default is 30000 and its maximum 3600000.
vsql_duckdb.max_result_bytes16777216 (its maximum)Default is 1048576; effective only on the development-ABI build, see below.
innodb_buffer_pool_sizemem_gb/16 = 4GThe InnoDB side only holds the four dimension tables of the hybrid joins (customer is the largest: 15M rows at SF100).
innodb_parallel_read_threads, innodb_redo_log_capacity, join_buffer_size, sort_buffer_size, tmp_table_size, max_heap_table_size, temptable_max_ramas the other MySQL-family enginesShared formulas from baseline_conf.duckdb_engine_settings.
skip-log-bin, secure_file_priv=/data, local_infile=OFF, max_connections=200, character-set-server=utf8mb4as the other MySQL-family serversSame on every MySQL-family system.

timeout_ms is a global variable (there is no session form). When a run uses a shorter --timeout, the adapter sets SET GLOBAL vsql_duckdb.timeout_ms before each query; the extension does not rebuild its DuckDB instance for that variable. Changing any other vsql_duckdb.* variable does rebuild it, which is why they are set before the first query.

Non-default build: development ABI and the result cap

The benchmark uses a non-default build of the same 0.0.3 release. The extension's own README ("Returning more than one megabyte") says the released build is made against the stable extension ABI, whose duckdb_query() result buffer is a fixed 1 MiB: max_result_bytes is clamped to it whatever it is set to, and a larger answer is refused. The README documents the way to lift this: build against the development ABI (the SDK's include-dev headers) and raise kQueryBufferSize / declare .max_result_length(). This image does exactly that, at the benchmark owner's decision, so that the documented 16 MiB max_result_bytes applies:

A query whose answer is larger than 16 MiB is still refused with an error rather than returned cut (a cut JSON array would not parse), and is recorded as error with extra.kind = "result_cap". It is a result, not a harness failure, and the query text is not changed. Tests check on the live server that about 2 MiB and 4.5 MiB come back whole and that about 22 MiB is a result_cap. At SF1 TPC-H Q16 (18314 rows) now succeeds; with the stable build it was a result_cap.

KILL does not reach duckdb_query()

KILL QUERY does not interrupt the embedded DuckDB, so cancel() returns False. The deadline is the extension's timeout_ms, which interrupts the DuckDB connection; the query then fails with query passed vsql_duckdb.timeout_ms (N ms) and was stopped, recorded as timeout. After any timeout the power run restarts the container (a cancel() that cannot cancel forces it), and the record carries extra.restarted_after_timeout = true and extra.cancel_unavailable = true.

The hybrid joins (join_innodb*)

The extension documents joining a DuckDB result to MySQL tables with JSON_TABLE, since DuckDB cannot see MySQL tables. Track B measures that pattern with four items (dbench/queries/hybrid.py). In each, duckdb_query() runs DuckDB SQL over Parquet, JSON_TABLE turns its JSON into rows, and MySQL joins them to InnoDB tables. The InnoDB side holds region, nation, supplier and customer, loaded with the row-store schema (primary keys and foreign-key indexes) before the queries run. lineitem and orders stay Parquet-only.

ItemDuckDB part (over Parquet)InnoDB part
join_innodbcustomers and balance per nation (25 rows)joined to nation, ordered by name
join_innodb_top_customersthe 20 customers with the most revenue from returned items in Q4 1993 (TPC-H Q10 shape)looked up in customer and nation
join_innodb_supplier_regionthe 100 suppliers with the most 1994 revenue (Q5 shape)joined to supplier, nation, region; ASIA only, summed per nation
join_innodb_segment_filterunshipped revenue per customer, one row per customer with an open order (Q3 shape)joined to customer and nation; BUILDING segment only, summed per nation

Because DuckDB cannot see the InnoDB tables, a filter on an InnoDB column applies only after the DuckDB result has crossed. join_innodb_segment_filter measures exactly that: its DuckDB part returns one row per customer with an open order (millions at SF100). A result larger than max_result_bytes (16 MiB) is recorded as error with extra.kind = "result_cap". The other items return at most 100 rows from DuckDB.

The reference is plain DuckDB 1.5.5 computing the same result with every table read from Parquet. pg_duckdb runs the same items in its own documented form, one PostgreSQL statement joining read_parquet() to heap tables (see systems/pgduckdb/README.md). MariaDB records n/a: run_in_duckdb() returns text that SQL cannot join. join_innodb is unchanged from earlier runs.

Other disclosures