DuckDB inside OLTP databases

MariaDB

Versions

Run asImageServerDuckDBDigest
MariaDBdbench/mariadb-duckdb:12.3.3MariaDB 12.3.3 + MariaDB-duckdb-enginebundled; see versions in recordssha256:74f116a3c80938cdd74f8025cfa996798892727ddd6d27e5bd04bbf13ae13ce8

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.

MariaDB

MariaDB (run_in_duckdb)

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 is already off, set explicitly): replication is not under test and the default differs between families.systems/mariadb/duckdb.cnf
plugin-load-addha_duckdb.soThe DuckDB engine ships as a plugin library, not built in.systems/mariadb/duckdb.cnf
plugin-maturitygammaThe engine is gamma maturity; the server refuses to load it under the default 'stable'.systems/mariadb/duckdb.cnf
duckdb_memory_limit48GCaps DuckDB (the engine that runs the queries) at 75% of the container, the share the InnoDB baseline gives its buffer pool; the rest covers the server, connections and the OS.systems/mariadb/duckdb.cnf
duckdb_max_threads16One DuckDB thread per core pinned to the database container.systems/mariadb/duckdb.cnf
duckdb_temp_directory/var/lib/mysql/duckdb_tmpExplicit spill location on the datadir volume (created by the image entrypoint), the same spill-location rule as every engine.systems/mariadb/duckdb.cnf
duckdb_allow_run_in_duckdbONrun_in_duckdb() runs DuckDB SQL inside the server and is refused by default. It is MariaDB's documented bulk load (INSERT ... SELECT FROM read_parquet(), storage/duckdb/tpch/04_load.sh), the engine's only way to run DuckDB's CHECKPOINT, and the Track B entry point.systems/mariadb/duckdb.cnf
duckdb_use_double_for_decimalOFFExact TPC-H DECIMAL results: the default (ON) maps DECIMAL to DOUBLE. AliSQL gets the same setting.systems/mariadb/duckdb.cnf
innodb_buffer_pool_size4GThe InnoDB side only holds the catalog and the row-store staging of the conversion load (load_convert, which stages the whole dataset in InnoDB before ALTER TABLE ... ENGINE=DuckDB); the data lives in DuckDB, so a larger pool would only take memory from DuckDB. The InnoDB baseline gets 0.75 * mem_gb.systems/mariadb/duckdb.cnf
innodb_log_file_size8GA redo log of mem_gb/8 keeps checkpoints from throttling the row-store staging load; same capacity as the InnoDB baseline (MariaDB calls it innodb_log_file_size).systems/mariadb/duckdb.cnf
join_buffer_size256MPer-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline.systems/mariadb/duckdb.cnf
sort_buffer_size256MPer-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline.systems/mariadb/duckdb.cnf
tmp_table_size4GInternal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline.systems/mariadb/duckdb.cnf
max_heap_table_size4GSame cap for MEMORY-engine temporary tables, which use the smaller of the two sizes.systems/mariadb/duckdb.cnf
query_cache_typeOFFMySQL 9.7 has no query cache, so MariaDB's is switched off for parity (it is off by default in recent releases, set explicitly so the result does not depend on that).systems/mariadb/duckdb.cnf
query_cache_size0Zero-size query cache, same reason.systems/mariadb/duckdb.cnf
secure_file_priv/dataServer-side LOAD DATA INFILE may read only the read-only dataset mount.systems/mariadb/duckdb.cnf
local_infileOFFBulk load is server-side from /data; client-side LOCAL loads stay disabled.systems/mariadb/duckdb.cnf
max_connections200Headroom for the 32-stream throughput run plus probes and cancel connections.systems/mariadb/duckdb.cnf
character-set-serverutf8mb4The DuckDB engine accepts only utf8mb4 tables.systems/mariadb/duckdb.cnf
collation-serverutf8mb4_general_ciutf8mb4 default collation for the bench database and its tables.systems/mariadb/duckdb.cnf

README

MariaDB 12.3.3 (LTS) + DuckDB engine (ENGINE=DuckDB)

Track A. MariaDB's DuckDB storage engine (MariaDB-duckdb-engine, plugin maturity gamma). The official MariaDB Docker image does not include it, so systems/mariadb/Dockerfile builds dbench/mariadb-duckdb:12.3.3 from rockylinux:9 (pinned by digest) and the official repo https://rpm.mariadb.org/12.3/rhel/9/x86_64/, installing exactly MariaDB-server-12.3.3-1.el9, MariaDB-client-12.3.3-1.el9 and MariaDB-duckdb-engine-12.3.3-1.el9. Build it with docker build -t dbench/mariadb-duckdb:12.3.3 systems/mariadb.

Version choice

12.3 is MariaDB's newest long-term support series with the DuckDB engine (11.8 also ships it). The other systems run their newest release; MariaDB runs its newest LTS release because MariaDB's developers asked for it to be tested rather than 13.0.2, the newest release. 13.0.2 was measured in the gate-sf10 test run. Both bundle DuckDB 1.5.5 and the same engine (plugin version 1.0, gamma). The resolved image digest is recorded in every result as versions.image_digest. The dataset is mounted read-only at /data.

Image and entrypoint

entrypoint.sh is minimal: if the datadir is empty it runs mariadb-install-db (the Dockerfile empties the datadir the RPM pre-creates), creates root@'%' with an empty password and the tpch database, creates the DuckDB spill directory, then execs mariadbd --user=root. No port is published; the server is reachable only over the benchmark's docker network.

Resource limits

The container is capped by Docker, the same way as every database container (dbench/dockerctl.py): cpuset_cpus pins it to the campaign's db_cpuset (16 cores, 0-15), and mem_limit = memswap_limit caps it at mem_gb (64 GB) with no swap. Inside that cap the server settings below size DuckDB (duckdb_max_threads, duckdb_memory_limit) and InnoDB. DuckDB spills to duckdb_temp_directory when a query needs more than duckdb_memory_limit; at SF100 and above under 64 GB that can happen, and M9 (SF300) is the bigger-than-memory test.

How settings are applied

duckdb.cnf is a jinja template rendered from the container limits (dbench/systems/mariadb.py, formulas from baseline_conf.py, shared with the Percona and AliSQL engines) and bind-mounted read-only at /etc/my.cnf.d/dbench.cnf. Each setting carries a reason comment in the file.

At 64 GB and 16 cores (mem_gb = container memory):

SettingValueReason
plugin-load-addha_duckdb.soThe engine is a plugin library.
plugin-maturitygammaThe server refuses a gamma plugin under the default maturity.
duckdb_memory_limit0.75 x mem_gb = 48GCaps DuckDB (the query engine) at 75% of the container, the share the InnoDB baseline gives its buffer pool. The engine default is 1 GiB.
duckdb_max_threadscores in the DB cpuset = 16One DuckDB thread per pinned core.
duckdb_temp_directory/var/lib/mysql/duckdb_tmpExplicit spill location on the datadir volume (created by the entrypoint); same spill rule for all engines.
duckdb_allow_run_in_duckdbONrun_in_duckdb() is MariaDB's documented bulk load, the engine's only way to run DuckDB's CHECKPOINT, and the Track B entry point. Refused by default.
duckdb_use_double_for_decimalOFFExact TPC-H DECIMAL results. The default (ON) maps DECIMAL to DOUBLE. AliSQL gets the same setting.
innodb_buffer_pool_sizemem_gb/16 = 4GInnoDB only holds the catalog and the load_convert staging (the whole dataset in InnoDB before ALTER TABLE ... ENGINE=DuckDB); the data lives in DuckDB. The InnoDB baseline gets 0.75 x mem_gb = 48G.
innodb_log_file_sizemem_gb/8 = 8GMariaDB's name for the redo log size; equals the InnoDB baseline's innodb_redo_log_capacity.
join_buffer_size, sort_buffer_size256MSame as the InnoDB baseline.
tmp_table_size, max_heap_table_sizemem_gb/16 = 4GSame as the InnoDB baseline.
query_cache_type, query_cache_sizeOFF, 0MySQL 9.7 has no query cache; switched off explicitly for parity.
secure_file_priv/dataServer-side LOAD DATA INFILE may read only the dataset mount.
local_infileOFFLoading is server-side.
max_connections200Headroom for the 32-stream throughput run plus probes.
character-set-server, collation-serverutf8mb4, utf8mb4_general_ciThe engine accepts only utf8mb4.

Left at the default on purpose: duckdb_require_primary_key=ON (the schema declares primary keys), duckdb_checkpoint_threshold (256 MiB), duckdb_max_temp_directory_size=0 (per the engine source, ha_duckdb.cc, 0 means "90% of the available disk", i.e. DuckDB's own default, so spilling to the datadir volume is allowed and no cap is set), and (set explicitly, skip-log-bin) the binary log: off on every MySQL-family server because replication is not under test and the default differs between families (MariaDB's is already off). There is no innodb_parallel_read_threads equivalent in MariaDB, so none is set. temptable_max_ram does not exist in MariaDB either.

Verified facts (MariaDB 12.3.3-1.el9 RPMs, observed on the running server)

Pushdown detection (differs from the task's assumption)

The engine plugs into MariaDB's select_handler: a whole SELECT is handed to DuckDB. The documented marker is select_type = PUSHED SELECT in EXPLAIN, but it never appears for this engine: the engine builds its handler only when the statement text itself starts with SELECT (or WITH), so EXPLAIN SELECT ... is not pushed and shows an ordinary plan (select_type SIMPLE, type ALL) for queries that really are pushed when run. Detection through EXPLAIN would therefore report every query as a fallback. Instead the adapter reads the session status Rows_read before and after the query, outside the timed region (_query_begin / _query_end): a pushed query does not use the handler row API, so the counter does not move (duckdb); the row path raises it (fallback); an unreadable counter is unknown. Observed: an InnoDB scan of 3 rows moves Rows_read by 3; pushed single-engine and cross-engine (InnoDB + DuckDB) joins leave it at 0. Limitation: a row-path query that reads zero rows cannot be told from a pushed one. Queries that cannot be pushed (user variables, LAST_INSERT_ID, SLEEP, and so on) are not handled by the row path either in this build: they fail with ERROR 1031: Storage engine DUCKDB ... doesn't have this option, which the harness records as an error, never a rewrite.

Timeouts on pushed queries (engine property, not worked around)

Tested with a heavy pushed query (3-way cross join of a 3M-row DuckDB table, 5 s limit, docker test test_heavy_pushed_query_times_out_and_server_recovers). The server-side max_statement_time does not stop a pushed query on this engine. The harness records a timeout only when the client read timeout (timeout + 30 s margin) fires; KILL QUERY is accepted but the DuckDB query keeps running in the server (observed still Executing 20 s after the kill), so later queries can overlap leftover work until it finishes. The server stays responsive (SELECT 1 succeeds). The power run handles this for every adapter: after a timeout it calls cancel(), polls lingering_queries() (information_schema.PROCESSLIST rows of the bench user with COMMAND Query or Killed, excluding the probe; a KILLed pushed query stays listed as Killed while it keeps running) for up to 10 s and, if the query is still running, restarts the server. That query's record carries extra.restarted_after_timeout = true and extra.lingering = n. (Percona's engine honours its server-side limit: error 3024 after about 5 s.)

Loading

Measurement notes

The per-query timeout is SET SESSION max_statement_time (MariaDB's name for max_execution_time, in seconds; MariaDB applies it to SELECT); cancel() is KILL QUERY. The M3 checkpoint is SELECT run_in_duckdb('CHECKPOINT') (MariaDB has no CHECKPOINT statement). storage_bytes() is du -sb of the datadir excluding binlog files (none are written: skip-log-bin; replication logs are excluded and the redo log is counted); it counts duckdb.db and its WAL plus the InnoDB catalog and redo log (innodb_log_file_size).

Track B variant (mariadb-trackb)

Track B runs the canonical DuckDB text over Parquet (dbench/queries/trackb.py) through SELECT run_in_duckdb('<sql>'). The function does return the query's rows, as text: a line of column names, a line of DuckDB type names, [ Rows: N], then the tab-separated rows (NULL is the word NULL); the adapter parses it and takes numeric columns from the type line. It is refused unless the server runs with duckdb_allow_run_in_duckdb = ON, which the shared config sets for both tracks. Image and settings are the Track A ones. See systems/villagesql/README.md for why Track B exists.