DuckDB inside OLTP databases

MySQL InnoDB

Versions

Run asImageServerDuckDBDigest
MySQL InnoDBmysql:9.7MySQL 9.7 (InnoDB)n/asha256:8fb11c38b3551e22d50adbb688f027f8ff4ea2d2445aa277c1cc97b4af85ec54

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.

MySQL InnoDB

Settings that differ from the defaults

SettingValueReasonFile
skip-log-binBinary logging is off on every MySQL-family server (MySQL and Percona enable it by default, MariaDB does not): replication is not under test and the default differs between families.systems/innodb/my.cnf
innodb_buffer_pool_size48G75% of container memory for the buffer pool; the rest covers connections, sort and join buffers.systems/innodb/my.cnf
innodb_parallel_read_threads16Only COUNT(*) without WHERE and CHECK TABLE use parallel read threads; ordinary scans stay single-threaded in MySQL. Set to every core given to the database so those two are not held back.systems/innodb/my.cnf
secure_file_priv/dataServer-side LOAD DATA INFILE may read only the read-only dataset mount.systems/innodb/my.cnf
local_infileOFFBulk load is server-side from /data; client-side LOCAL loads stay disabled.systems/innodb/my.cnf
max_connections200Headroom for the 32-stream throughput run plus probes and cancel connections.systems/innodb/my.cnf
join_buffer_size256MPer-join memory comparable to Postgres work_mem=256MB, so hash and block-nested joins do not spill.systems/innodb/my.cnf
sort_buffer_size256MPer-sort memory comparable to Postgres work_mem=256MB, so ORDER BY and GROUP BY sorts stay in RAM.systems/innodb/my.cnf
tmp_table_size4GInternal temporary tables (GROUP BY, DISTINCT, derived tables) stay in memory up to mem_gb/16.systems/innodb/my.cnf
max_heap_table_size4GSame cap for MEMORY-engine temporary tables, which use the smaller of the two sizes.systems/innodb/my.cnf
temptable_max_ram4GRAM the TempTable engine may use before spilling to disk, equal to the caps above.systems/innodb/my.cnf
innodb_redo_log_capacity8GA redo log of mem_gb/8 keeps checkpoints from throttling bulk load at the 100MB default.systems/innodb/my.cnf

README

MySQL 9.7 (InnoDB) baseline

Image mysql:9.7 (digest recorded in every result as versions.image_digest), started as the mysql user with the config rendered from my.cnf and mounted read-only at /etc/mysql/conf.d/dbench.cnf. The dataset is mounted read-only at /data.

Non-default settings

Rendered from the container limits (dbench/systems/baseline_conf.py); at 64 GB and 16 cores:

SettingValueReason
innodb_buffer_pool_size0.75 x container GB = 48GMost of the memory for the buffer pool; the rest covers connections and per-query buffers.
innodb_parallel_read_threadscores in the DB cpuset = 16Only COUNT(*) without WHERE and CHECK TABLE use parallel read threads; MySQL's ordinary scans stay single-threaded. Set to every core so those two are not held back.
secure_file_priv/dataServer-side LOAD DATA INFILE may read only the read-only dataset mount.
local_infileOFFLoading is server-side; client-side LOCAL loads stay off.
join_buffer_size256MPer-join memory comparable to Postgres work_mem=256MB.
sort_buffer_size256MPer-sort memory comparable to Postgres work_mem=256MB.
tmp_table_size, max_heap_table_sizemem_gb/16 = 4GInternal temporary tables (GROUP BY, DISTINCT, derived tables) stay in memory.
temptable_max_rammem_gb/16 = 4GRAM the TempTable engine may use before spilling; equal to the caps above.
innodb_redo_log_capacitymem_gb/8 = 8GKeeps checkpoints from throttling bulk load at the 100MB default.
max_connections200Headroom over the 32-stream throughput run plus probe and cancel connections.

skip-log-bin: binary logging is off on every MySQL-family server (it is on by default for MySQL, Percona and AliSQL, off for MariaDB); replication is not under test and the default differs between families. Everything else stays at the MySQL 9.7 default.

Loading and measurement notes

Schema

TPC-H tables with primary keys, plus a plain index on every foreign-key column (row-store baseline rule); no other indexes and no declared foreign-key constraints. Strings are VARCHAR(n) with the spec lengths. Default charset and collation (utf8mb4_0900_ai_ci) are kept. There are no schema deviations.

Dialect

Queries are the DuckDB text plus the mechanical rules in dbench/queries/dialect.py; only Q13 is rewritten (derived-table column list).

M11 and M12 are n/a: there is no DuckDB copy to convert or push down to.