Skip to content

mariadb-duckdb: switch to Parquet ingestion, remove PRIMARY KEY, add 2026-07-13 results #986

New issue

Have a question about this project? Sign up for a free GitHub account to open an issue and contact its maintainers and the community.

Sign up for GitHub

By clicking “Sign up for GitHub”, you agree to our terms of service and privacy statement. We’ll occasionally send you account related emails.

Already on GitHub? Sign in to your account

Open
mariadb-AlekseiBukhalov wants to merge 1 commit into
base: main
Choose a base branch
from
Open

mariadb-duckdb: switch to Parquet ingestion, remove PRIMARY KEY, add 2026-07-13 results #986

Show file tree
Hide file tree
Changes from all commits
Commits
Show all changes
1 commit
Select commit
File filter

Filter by extension

Filter by extension
Conversations
Failed to load comments.
Loading
Jump to
Jump to file
Failed to load files.
Loading
Diff view
Diff view
4 changes: 3 additions & 1 deletion mariadb-duckdb/benchmark.sh
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,3 +1,5 @@
#!/bin/bash
export BENCH_DOWNLOAD_SCRIPT="download-hits-tsv"
export BENCH_DOWNLOAD_SCRIPT="download-hits-parquet-single"
export BENCH_DURABLE=yes
export BENCH_RESTARTABLE=yes
exec ../lib/benchmark-common.sh
4 changes: 2 additions & 2 deletions mariadb-duckdb/create.sql
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,3 +1,4 @@
set global duckdb_require_primary_key = OFF;
CREATE TABLE hits
(
WatchID BIGINT NOT NULL,
Expand Down Expand Up @@ -104,6 +105,5 @@ CREATE TABLE hits
HasGCLID SMALLINT NOT NULL,
RefererHash BIGINT NOT NULL,
URLHash BIGINT NOT NULL,
CLID INTEGER NOT NULL,
PRIMARY KEY (CounterID, EventDate, UserID, EventTime, WatchID)
CLID INTEGER NOT NULL
) ENGINE=DuckDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin;
9 changes: 3 additions & 6 deletions mariadb-duckdb/install
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,7 +1,7 @@
#!/bin/bash
set -eu

CI_SOURCES_URL="https://ci.mariadb.org/68929/amd64-ubuntu-2404-deb-autobake/mariadb.sources"
CI_SOURCES_URL="https://ci.mariadb.org/70621/amd64-ubuntu-2404-deb-autobake/mariadb.sources"

# Idempotent: if DuckDB plugin is already active, nothing to do.
if sudo mariadb -e "SELECT PLUGIN_STATUS FROM information_schema.PLUGINS WHERE PLUGIN_NAME='DuckDB';" 2>/dev/null | grep -q 'ACTIVE'; then
Expand All @@ -19,19 +19,16 @@ if dpkg -l | grep -qE '^ii\s+(mariadb-server|mysql-server)'; then
sudo rm -rf /var/lib/mysql /etc/mysql
fi

# Add MariaDB CI build as apt source (Trusted: yes — unsigned CI packages, testing only).
sudo wget -q -O /etc/apt/sources.list.d/mariadb-duckdb-ci.sources "$CI_SOURCES_URL"

sudo apt-get update -y
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y \
mariadb-server mariadb-client
sudo DEBIAN_FRONTEND=noninteractive apt-get install -y mariadb-server mariadb-client

# Configure the DuckDB storage engine plugin (from mariadb.org article defaults).
sudo tee /etc/mysql/mariadb.conf.d/duckdb.cnf >/dev/null <<'EOF'
[mysqld]
plugin-maturity=alpha
plugin-load-add=ha_duckdb.so
duckdb-memory-limit=24G
duckdb-allow-run-in-duckdb=ON
EOF

sudo systemctl enable mariadb
Expand Down
26 changes: 15 additions & 11 deletions mariadb-duckdb/load
View file
Open in desktop
Original file line number Diff line number Diff line change
Expand Up @@ -13,22 +13,26 @@ sudo mariadb -e "DROP DATABASE IF EXISTS test"
sudo mariadb -e "CREATE DATABASE test"
sudo mariadb test < create.sql

# Load via DuckDB's native vectorized CSV reader (12x faster than LOAD DATA LOCAL
# Load via DuckDB's native vectorized parquet reader (faster than LOAD DATA LOCAL
# INFILE which routes every row through MariaDB's single-threaded write_row path).
# hits.tsv is tab-delimited with datetime strings in TIMESTAMP columns, matching
# the table schema — no type conversion needed.
#
# DuckDB glob expander requires read permission on all parent directories.
# /home/ubuntu is mode 751 (o+x only) so DuckDB can traverse but not list it,
# causing "No files found" errors. Hardlink to /var/lib/mysql/ (mysql-owned,
# mode 755) resolves this instantly without copying 70 GB.
HITS_LINK=/var/lib/mysql/hits_load.tsv
sudo ln "$(realpath hits.tsv)" "$HITS_LINK"
sudo chmod 644 "$HITS_LINK"
# NULLSTR '\N' matches MySQL/MariaDB convention: only \N is NULL, empty fields → ''.
# Without this, DuckDB treats every empty TSV field as NULL → NOT NULL violations.
duck "COPY test.hits FROM '$HITS_LINK' (FORMAT CSV, DELIMITER '\t', NULLSTR '\N')"
# mode 755) resolves this instantly without copying 14 GB.
HITS_LINK=/var/lib/mysql/hits.parquet
if [ ! -f "$HITS_LINK" ]; then
sudo ln "$(realpath hits.parquet)" "$HITS_LINK"
sudo chmod 644 "$HITS_LINK"
fi
duck "INSERT INTO test.hits
SELECT * REPLACE (
make_date(EventDate) AS EventDate,
epoch_ms(EventTime * 1000) AS EventTime,
epoch_ms(ClientEventTime * 1000) AS ClientEventTime,
epoch_ms(LocalEventTime * 1000) AS LocalEventTime)
FROM read_parquet('$HITS_LINK', binary_as_string=True);"

sudo rm -f "$HITS_LINK"
rm -f hits.tsv
rm -f hits.parquet
sync
59 changes: 59 additions & 0 deletions mariadb-duckdb/results/20260713/c6a.4xlarge.json
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,59 @@
{
"system": "MariaDB (DuckDB)",
"date": "2026-07-13",
"machine": "c6a.4xlarge",
"cluster_size": 1,
"proprietary": "no",
"hardware": "cpu",
"tuned": "no",
"tags": ["C++", "column-oriented", "MySQL compatible", "DuckDB derivative"],
"load_time": 100.378,
"data_size": 20458778624,
"concurrent_qps": 1.530,
"concurrent_error_ratio": 0.000,
"result": [
[0.039, 0.002, 0.001],
[0.145, 0.005, 0.005],
[0.245, 0.021, 0.021],
[0.316, 0.032, 0.032],
[0.503, 0.301, 0.299],
[1.004, 0.258, 0.261],
[0.114, 0.003, 0.003],
[0.163, 0.007, 0.007],
[0.900, 0.378, 0.375],
[1.613, 0.528, 0.525],
[0.541, 0.072, 0.071],
[0.782, 0.082, 0.080],
[0.986, 0.333, 0.332],
[2.308, 0.655, 0.677],
[1.391, 0.382, 0.386],
[0.548, 0.341, 0.340],
[2.219, 0.779, 0.790],
[1.977, 0.524, 0.532],
[4.615, 2.761, 2.781],
[0.221, 0.005, 0.005],
[10.688, 0.484, 0.485],
[12.324, 0.500, 0.498],
[14.490, 1.657, 2.319],
[0.596, 0.068, 0.066],
[0.216, 0.021, 0.024],
[0.922, 0.103, 0.102],
[0.232, 0.021, 0.020],
[10.865, 0.404, 0.405],
[8.675, 6.651, 6.601],
[0.201, 0.027, 0.026],
[2.796, 0.287, 0.285],
[5.822, 0.373, 0.380],
[5.005, 1.663, 1.750],
[11.170, 1.654, 1.649],
[11.192, 1.852, 1.827],
[0.610, 0.428, 0.431],
[0.134, 0.029, 0.026],
[0.122, 0.010, 0.009],
[0.121, 0.015, 0.015],
[0.229, 0.054, 0.064],
[0.136, 0.006, 0.006],
[0.148, 0.006, 0.006],
[0.238, 0.150, 0.154]
]
}
59 changes: 59 additions & 0 deletions mariadb-duckdb/results/20260713/c6a.metal.json
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -0,0 +1,59 @@
{
"system": "MariaDB (DuckDB)",
"date": "2026-07-13",
"machine": "c6a.metal",
"cluster_size": 1,
"proprietary": "no",
"hardware": "cpu",
"tuned": "no",
"tags": ["C++", "column-oriented", "MySQL compatible", "DuckDB derivative"],
"load_time": 125.173,
"data_size": 20562063360,
"concurrent_qps": 13.947,
"concurrent_error_ratio": 0.000,
"result": [
[0.035, 0.002, 0.002],
[0.099, 0.005, 0.004],
[0.140, 0.006, 0.006],
[0.523, 0.008, 0.007],
[0.556, 0.067, 0.064],
[1.663, 0.055, 0.054],
[0.089, 0.002, 0.002],
[0.094, 0.006, 0.006],
[1.406, 0.080, 0.077],
[2.424, 0.117, 0.115],
[1.104, 0.025, 0.024],
[1.449, 0.026, 0.026],
[1.673, 0.063, 0.059],
[3.163, 0.116, 0.108],
[2.226, 0.068, 0.065],
[0.574, 0.073, 0.074],
[3.156, 0.147, 0.139],
[3.177, 0.146, 0.147],
[5.910, 0.446, 0.445],
[0.105, 0.006, 0.005],
[15.024, 0.087, 0.082],
[17.363, 0.082, 0.077],
[17.764, 0.695, 0.666],
[1.129, 0.028, 0.027],
[0.180, 0.014, 0.012],
[1.661, 0.028, 0.041],
[0.170, 0.014, 0.011],
[15.271, 0.074, 0.067],
[11.911, 1.083, 1.059],
[0.103, 0.014, 0.012],
[4.245, 0.059, 0.059],
[7.893, 0.135, 0.164],
[5.652, 0.364, 0.360],
[15.121, 0.311, 0.329],
[15.130, 0.321, 0.327],
[0.205, 0.101, 0.100],
[0.106, 0.026, 0.032],
[0.083, 0.008, 0.009],
[0.125, 0.013, 0.012],
[0.199, 0.053, 0.052],
[0.108, 0.008, 0.006],
[0.111, 0.008, 0.006],
[0.207, 0.130, 0.130]
]
}
21 changes: 18 additions & 3 deletions mariadb-duckdb/start
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,7 +1,22 @@
#!/bin/bash
set -eu

if sudo mariadb -e "SELECT 1" >/dev/null 2>&1; then
exit 0
if ! sudo mariadb -e "SELECT 1" >/dev/null 2>&1; then
/bin/sh /usr/bin/mariadbd-safe --datadir=/var/lib/mysql &>/dev/null &
# Wait up to 30s for the server to accept connections.
for _ in $(seq 1 30); do
sudo mariadb -e "SELECT 1" >/dev/null 2>&1 && break
sleep 1
done
if ! sudo mariadb -e "SELECT 1" >/dev/null 2>&1; then
echo "mariadbd-safe did not accept connections within 30s" >&2
exit 1
fi
fi

# Cap at physical core count on HT machines: above nproc/2 DuckDB gains no
# throughput from hyperthreaded siblings but pays extra scheduling overhead.
CORES=$(nproc)
if [ "$CORES" -gt 32 ]; then
sudo mariadb -e "SET GLOBAL duckdb_max_threads = $((CORES / 2))"
fi
sudo systemctl start mariadb
2 changes: 1 addition & 1 deletion mariadb-duckdb/stop
View file
Open in desktop
Original file line number Diff line number Diff line change
@@ -1,2 +1,2 @@
#!/bin/bash
sudo systemctl stop mariadb || true
/usr/bin/mariadb-admin shutdown