VillageSQL
Versions
| Run as | Image | Server | DuckDB | Digest |
|---|---|---|---|---|
| VillageSQL | dbench/villagesql:0.0.7-duckdb-0.0.3 | VillageSQL 0.0.7 (MySQL 8.4) + vsql_duckdb 0.0.3 | 1.5.5 | sha256: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
- M1: error 1, ok 25
- M4: crashed 3, ok 2
- M1 join_innodb_segment_filter (error): OperationalError: VDF error in function 'duckdb_query': vsql_duckdb: result reached 16777216 bytes at row 401952, the limit set by vsql_duckdb.max_result_bytes. Narrow the query or add a LIMIT
- M4 streams=16 (crashed): the container was killed by its memory limit during the run (OOMKilled, exit 137) (327 crashed, 25 ok of 352 queries); no throughput is reported; first errors: [['q21', 'crashed', "OperationalError: (2013, 'Lost connection to MySQL server during query')"], ['q02', 'crashed', 'StopIteration: '], ['q06', 'crashed', 'StopIteration: ']]
- M4 streams=32 (crashed): the container was killed by its memory limit during the run (OOMKilled, exit 137) (653 crashed, 32 timeout, 19 ok of 704 queries); no throughput is reported; first errors: [['q21', 'timeout', "OperationalError: (2013, 'Lost connection to MySQL server during query (The read operation timed out)')"], ['q02', 'crashed', "OperationalError: (2013, 'Lost connection to MySQL server during query')"], ['q06', 'crashed', 'StopIteration: ']]
- M4 streams=8 (crashed): the container was killed by its memory limit during the run (OOMKilled, exit 137) (145 crashed, 8 timeout, 23 ok of 176 queries); no throughput is reported; first errors: [['q21', 'timeout', "OperationalError: (2013, 'Lost connection to MySQL server during query (The read operation timed out)')"], ['q02', 'crashed', "OperationalError: (2013, 'Lost connection to MySQL server during query')"], ['q06', 'crashed', 'StopIteration: ']]
Settings that differ from the defaults
| Setting | Value | Reason | File |
|---|---|---|---|
skip-log-bin | Binary 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_size | 4G | The 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_threads | 16 | Same as the other MySQL-family servers: every core for the two operations that use parallel reads. | systems/villagesql/my.cnf |
innodb_redo_log_capacity | 8G | Same redo log capacity as the InnoDB baseline. | systems/villagesql/my.cnf |
join_buffer_size | 256M | Per-join memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/villagesql/my.cnf |
sort_buffer_size | 256M | Per-sort memory comparable to Postgres work_mem=256MB, same as the InnoDB baseline. | systems/villagesql/my.cnf |
tmp_table_size | 4G | Internal temporary tables stay in memory up to mem_gb/16, same as the InnoDB baseline. | systems/villagesql/my.cnf |
max_heap_table_size | 4G | Same cap for MEMORY-engine temporary tables, which use the smaller of the two sizes. | systems/villagesql/my.cnf |
temptable_max_ram | 4G | RAM the TempTable engine may use before spilling, equal to the caps above. | systems/villagesql/my.cnf |
secure_file_priv | /data | Server-side LOAD DATA INFILE (the nation table of the join example) may read only the dataset mount. | systems/villagesql/my.cnf |
local_infile | OFF | Loads are server-side from /data; client-side LOCAL loads stay disabled. | systems/villagesql/my.cnf |
max_connections | 200 | Headroom for probes and cancel connections. | systems/villagesql/my.cnf |
character-set-server | utf8mb4 | vsql_duckdb returns utf8mb4 text; the server default is already utf8mb4, set so it cannot change. | systems/villagesql/my.cnf |
vsql_duckdb.threads | 16 | One DuckDB worker thread per core pinned to the database container (the extension default is 2). | systems/villagesql/settings.sql |
vsql_duckdb.memory_limit_mb | 49152 | Caps 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_files | ON | Required 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_ms | 900000 | The 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_bytes | 16777216 | The 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 point | Query text |
|---|---|---|
duckdb-1.5.5 (the reference) | the DuckDB query server, read_parquet() | canonical DuckDB text |
villagesql | SELECT duckdb_query('<sql>'), JSON parsed into rows | the same |
pg_duckdb-trackb | SELECT * FROM duckdb.query($dbq$<sql>$dbq$) | the same |
mariadb-trackb | SELECT run_in_duckdb('<sql>'), text parsed into rows | the 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.
- Integers, decimals, floats and booleans keep their JSON types. The adapter parses JSON numbers to
intandDecimal(never binaryfloat), so DECIMAL sums keep their digits. - Dates, timestamps and every other type arrive as JSON strings, and the function does not expose column types. The adapter leaves them as text;
correctness.compareequates an ISO date string with the reference'sdate, and numeric columns are the union of the reference's (typed) and the adapter's (JSON number) columns. Limitation: a column that arrives as a string and holds a number is only compared numerically because the reference marks it numeric; the adapter itself cannot tell. - The JSON parse runs after the timed region; the time covers the call and the transfer of the string.
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:
| Setting | Value | Reason |
|---|---|---|
vsql_duckdb.threads | cores = 16 | One DuckDB worker thread per pinned core (extension default 2). |
vsql_duckdb.memory_limit_mb | 0.75 x mem_gb x 1024 = 49152 | Same 75% share the other MySQL-family engines give DuckDB (default 1024). |
vsql_duckdb.allow_local_files | ON | Without 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_tmp | Explicit spill directory on the data volume. |
vsql_duckdb.timeout_ms | 900000 (the 900 s query timeout) | The extension's own deadline; its default is 30000 and its maximum 3600000. |
vsql_duckdb.max_result_bytes | 16777216 (its maximum) | Default is 1048576; effective only on the development-ABI build, see below. |
innodb_buffer_pool_size | mem_gb/16 = 4G | The 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_ram | as the other MySQL-family engines | Shared formulas from baseline_conf.duckdb_engine_settings. |
skip-log-bin, secure_file_priv=/data, local_infile=OFF, max_connections=200, character-set-server=utf8mb4 | as the other MySQL-family servers | Same 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:
systems/villagesql/Dockerfileconfigures with-DVSQL_USE_DEV_ABI=ON, the SDK's own switch for itsinclude-devheaders (the samevillagesql-extension-sdk-0.0.7.tar.gz, SHA-256 checked, so no extra artifact), setskQueryBufferSizeto 16777216 and adds.max_result_length(kQueryBufferSize)forduckdb_query(). These are the only two source edits; the tag, commit, DuckDB version and server image are unchanged. The build argsABI=developmentandRESULT_BUFFER_BYTES=16777216are recorded in the lock's build inputs.- The SDK and the server image are the same 0.0.7 release. That this server loads the development-ABI
.veband honours the 16 MiB buffer is checked live bytest_live_result_cap_is_16_mib_on_the_development_abiandtest_live_result_cap_boundary_at_16_mibintests/test_villagesql.py.versionsrecordsabi: "development", and the lock file lists the build args in the build inputs. - The development ABI is not the stable surface, and an extension built against it needs a matching server; results here are for this build, not for the released
.veb. - Cost of the 16 MiB buffer, measured at 16 GB / cores 0-7 on this build and on the stable-ABI build of the same release (two rounds each, 1000 x
SELECT 1and 50 x TPC-H q06 at SF1, timed region ofexecute()):SELECT 1median 0.40-0.43 ms (dev) vs 0.42-0.47 ms (stable), p95 about 1.6 ms on both; q06 median 21.4 / 19.3 ms (dev) vs 19.8 / 18.2 ms (stable), p95 30.8 / 21.7 ms vs 27.9 / 20.7 ms, so the development build was about 1.2-1.6 ms (6-8%) slower on q06 in both rounds, a difference of the size of the run-to-run spread; container memory after the runs was 734-784 MiB (dev) vs 735-924 MiB (stable), peak 763-811 vs 763-939 MiB, no measurable memory cost.
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.
| Item | DuckDB part (over Parquet) | InnoDB part |
|---|---|---|
join_innodb | customers and balance per nation (25 rows) | joined to nation, ordered by name |
join_innodb_top_customers | the 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_region | the 100 suppliers with the most 1994 revenue (Q5 shape) | joined to supplier, nation, region; ASIA only, summed per nation |
join_innodb_segment_filter | unshipped 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
- One embedded DuckDB serves the whole server. Tables, macros and settings changes made through it are visible to every connection. M1 runs one stream; Track B M4 runs up to 32 connections at once, all calling
duckdb_query()on that one instance (docs/methodology/throughput.md). versionsin every record holds the server version,vsql_duckdb 0.0.3, the DuckDB version reported byduckdb_status()and the image digest.- Track B pg_duckdb bundles DuckDB 1.4.3 (the image
pgduckdb/pgduckdb:18-v1.1.1), not 1.5.5. MariaDB reports 1.5.5; its server runs withduckdb_allow_run_in_duckdb = ONin both tracks (Track A loads and checkpoints throughrun_in_duckdb()). - Track B pg_duckdb and MariaDB use the Track A images and settings otherwise.
- M11 (conversion) and M12 (pushdown) are n/a: VillageSQL has no engine to convert tables into and no routing decision to detect.