DuckDB inside OLTP databases

AliSQL

Versions

Run asImageServerDuckDBDigest
AliSQLdbench/alisql:8.0.44-3AliSQL 8.0.44-3 (MySQL 8.0.44)1.5.2-dev736sha256:750530032aec307932da7cc5cc9757b49764cbf803a007069fb8b34a40b4d904

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.

AliSQL

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/alisql/my.cnf
duckdb_modeONThe engine is off unless the server is started with duckdb_mode=ON (static, default NONE).systems/alisql/my.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. The engine default 0 means about 80% of physical RAM of the host, not of the container cgroup.systems/alisql/my.cnf
duckdb_threads16One DuckDB thread per core pinned to the database container (default 0 = auto).systems/alisql/my.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. duckdb_max_temp_directory_size stays at its default 0, which the variable documentation defines as 90% of the free disk, so spilling is not capped.systems/alisql/my.cnf
duckdb_use_double_for_decimalOFFExact TPC-H DECIMAL results (set explicitly, disclosed). The default ON maps DECIMAL with precision > 38 to DOUBLE; the benchmark schema has none, but MariaDB gets the same setting.systems/alisql/my.cnf
duckdb_sql_normalizationOFFQueries reach DuckDB exactly as written, with no MySQL-to-DuckDB rewriting (the default, set explicitly and disclosed): a query the engine rejects is recorded as an error.systems/alisql/my.cnf
duckdb_require_primary_keyONEvery DuckDB table must have a primary key (the default, set explicitly); the schema declares them.systems/alisql/my.cnf
innodb_buffer_pool_size4GThe InnoDB side only holds the catalog and row-store staging (the load_convert path); the data lives in DuckDB, so a large pool would only take memory from DuckDB.systems/alisql/my.cnf
innodb_parallel_read_threads16Same as the InnoDB baseline. Only COUNT(*) without WHERE and CHECK TABLE use parallel read threads (ordinary scans stay single-threaded); set to every core so those two are not held back.systems/alisql/my.cnf
innodb_redo_log_capacity8GA redo log of mem_gb/8 keeps checkpoints from throttling the row-store staging load; same capacity as the InnoDB baseline.systems/alisql/my.cnf
join_buffer_size256MPer-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline.systems/alisql/my.cnf
sort_buffer_size256MPer-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline.systems/alisql/my.cnf
tmp_table_size4GInternal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline.systems/alisql/my.cnf
max_heap_table_size4GSame cap for MEMORY-engine temporary tables, which use the smaller of the two sizes.systems/alisql/my.cnf
temptable_max_ram4GRAM the TempTable engine may use before spilling, equal to the caps above.systems/alisql/my.cnf
secure_file_priv/dataServer-side LOAD DATA INFILE may read only the read-only dataset mount.systems/alisql/my.cnf
local_infileOFFBulk load is server-side from /data; client-side LOCAL loads stay disabled.systems/alisql/my.cnf
max_connections200Headroom for the 32-stream throughput run plus probes and cancel connections.systems/alisql/my.cnf

README

AliSQL 8.0.44-3 + DuckDB engine (ENGINE=DuckDB)

Track A. AliSQL is Alibaba's MySQL 8.0.44 fork with a built-in DuckDB storage engine. There is no official Docker image, so systems/alisql/Dockerfile builds dbench/alisql:8.0.44-3 from ubuntu:24.04 (pinned by digest) and the official release asset alisql-8.0.44-3-linux-glibc2.17-x86_64.tar.xz (653924236 bytes) from https://github.com/alibaba/AliSQL/releases/download/AliSQL-8.0.44-3/, verified by SHA-256 9b3ca762483d14e4ee0206b543c1541df91efd02d7f1ba16fcd5a3c1d359fbf7 (computed by us; pinned in the Dockerfile). Build it with docker build -t dbench/alisql:8.0.44-3 systems/alisql. The resolved image digest is recorded in every result as versions.image_digest. The dataset is mounted read-only at /data.

The tarball's mysqld is built on CentOS 7 and needs OpenSSL 1.0.2 (libssl.so.10, libcrypto.so.10), which Ubuntu 24.04 does not ship. The Dockerfile therefore also installs the CentOS 7.9 openssl-libs-1.0.2k-19.el7 RPM (SHA-256 pinned) into /opt/alisql/lib/private, where mysqld already looks (RPATH), and links Ubuntu's libaio.so.1t64 as libaio.so.1. Nothing else differs from the release tarball.

Image and entrypoint

entrypoint.sh is minimal: if the datadir is empty it runs mysqld --initialize-insecure with an init file that creates root@'%' (empty password, mysql_native_password) and the tpch database, creates the DuckDB spill directory, then execs mysqld --user=root --defaults-file=.... No port is published; the server is reachable only over the benchmark's docker network.

How settings are applied

my.cnf is a jinja template rendered from the container limits (dbench/systems/alisql.py, formulas from baseline_conf.py, shared with the Percona and MariaDB engines) and bind-mounted read-only at /etc/alisql/dbench.cnf. Each setting carries a reason comment in the file. At 64 GB and 16 cores (mem_gb = container memory):

SettingValueReason
duckdb_modeONThe engine is off unless set (static, default NONE).
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 default 0 means about 80% of the host's RAM, not the container's. The variable is in bytes; the G suffix is accepted and the live value is verified in the docker test.
duckdb_threadscores in the DB cpuset = 16One DuckDB thread per pinned core (default 0 = auto).
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_use_double_for_decimalOFFSet explicitly and disclosed. The default ON maps DECIMAL with precision > 38 to DOUBLE; the benchmark schema has no such column, but MariaDB gets the same setting.
duckdb_sql_normalizationOFFSet explicitly and disclosed (the default). Queries reach DuckDB as written, with no MySQL-to-DuckDB rewriting; anything it rejects is an error.
duckdb_require_primary_keyONSet explicitly (the default). The schema declares primary keys.
innodb_buffer_pool_sizemem_gb/16 = 4GInnoDB only holds the catalog and load_convert staging; the data lives in DuckDB.
innodb_parallel_read_threadscores = 16Same as the InnoDB baseline. Only COUNT(*) without WHERE and CHECK TABLE use parallel read threads; ordinary scans stay single-threaded.
innodb_redo_log_capacitymem_gb/8 = 8GSame as the InnoDB baseline.
join_buffer_size, sort_buffer_size256MSame as the InnoDB baseline.
tmp_table_size, max_heap_table_size, temptable_max_rammem_gb/16 = 4GSame as the InnoDB baseline.
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.

Left at the default on purpose, all verified on the running server with SHOW VARIABLES: duckdb_max_temp_directory_size=0 (per the variable documentation 0 means 90% of the free disk space, so spilling to the datadir volume is not capped and no value is set), duckdb_dml_in_batch=ON, duckdb_checkpoint_threshold (256 MiB), duckdb_max_threads_per_query (1000000, i.e. no per-query limit), force_innodb_to_duckdb=OFF, duckdb_prefer_high_precision=OFF, and nothing else except skip-log-bin: binary logging is off on every MySQL-family server (on by default for MySQL, Percona and AliSQL, off for MariaDB) because replication is not under test and the default differs between families.

Verified facts (observed on the running server)

Pushdown detection

AliSQL has no fallback. When every table of a SELECT is DuckDB, the original query text goes to DuckDB; a query that mixes engines fails with error 7501 Does not support mixed queries of duckdb engine and other engines, and a query DuckDB rejects fails with a DuckDB error. Both are recorded as error (never as fallback); the mixed-engine message adds extra.kind = "mixed_engine".

Detection reads the session status counter Com_duckdb_select (SELECTs handed to DuckDB) before and after the query, outside the timed region (_query_begin / _query_end): it rises for a pushed query (duckdb), an unchanged counter would be a silent row-path execution (fallback), an unreadable counter is unknown. Observed: a SELECT on a DuckDB table raises it by 1; a SELECT on an InnoDB table does not. MySQL 8.0 has no Rows_read status variable, so MariaDB's approach does not apply.

Timeouts

The per-query timeout is the base MySQL hook, SET SESSION max_execution_time in milliseconds. Tested with a heavy pushed query (three-way cross join of a 1M-row DuckDB table, 5 s limit; docker test test_heavy_pushed_query_times_out_and_server_recovers): the server-side limit fires at 5.006 s. It reaches the client as a DuckDB error (7501 [DuckDB] INTERRUPT Error: Interrupted!, or 7513 ... send result error. Interrupted! while streaming results), not as MySQL's error 3024, so the adapter classifies an interrupt that arrives at the deadline as timeout (an interrupt long before the deadline stays an error). The query did not linger (lingering_queries() was 0 right after cancel() and 3 s later) and the next query ran at once.

Loading

Measurement notes

storage_bytes() is du -sb of the datadir excluding binlog files (binlog.*); it counts duckdb.db and its WAL plus the InnoDB catalog, tablespaces and the redo log (innodb_redo_log_capacity). Replication logs are excluded, the redo log is counted. The server runs with skip-log-bin, so no binlog files are written; the exclusion keeps the rule identical across the family.