#!/bin/sh # SQLite on every backend: the README's table. # # zig build -Doptimize=ReleaseFast --prefix DIR # sh bench/sqlite.sh DIR SRC # # DIR is where ztrace was installed (bin/ztrace, lib/ the agent, the pass # and its runtime); SRC holds the amalgamation, sqlite3.c and shell.c # (https://sqlite.org/2025/sqlite-amalgamation-3500400.zip, unpacked). # The shell is built plain and by `ztrace cc` in a directory of its own # under $TMPDIR, and runs one workload on a file database: two tables and # an index, 5100 rows in a transaction, a join, an aggregate. set -eu prefix=$(realpath "$1") src=$(realpath "$2") ztrace=$prefix/bin/ztrace agent=$prefix/lib/libztrace-agent.so tmp=$(mktemp -d) trap 'rm -rf "$tmp"' EXIT cd "$tmp" cat > workload.sql <<'EOF' CREATE TABLE author(id INTEGER PRIMARY KEY, name TEXT); CREATE TABLE book(id INTEGER PRIMARY KEY, author INTEGER, title TEXT, year INTEGER); CREATE INDEX book_author ON book(author); BEGIN; WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 100) INSERT INTO author SELECT i, 'author ' || i FROM n; WITH RECURSIVE n(i) AS (SELECT 1 UNION ALL SELECT i + 1 FROM n WHERE i < 5000) INSERT INTO book SELECT i, i % 100 + 1, 'title ' || i, 1900 + i % 120 FROM n; COMMIT; SELECT count(*), min(year), max(year) FROM book; SELECT a.name, count(*), avg(b.year) FROM book b JOIN author a ON a.id = b.author GROUP BY a.id ORDER BY 2 DESC, 1 LIMIT 3; SELECT title, year FROM book WHERE author = 42 ORDER BY year LIMIT 3; EOF # Frame pointers kept, for the backtraces; the same flags for every build. cflags="-O2 -g -fno-omit-frame-pointer -I$src" clang $cflags "$src/shell.c" "$src/sqlite3.c" -lm -o plain 2>/dev/null "$ztrace" cc -- clang $cflags "$src/shell.c" "$src/sqlite3.c" -lm -o all 2>/dev/null "$ztrace" cc --only sqlite3VdbeExec -- clang $cflags "$src/shell.c" "$src/sqlite3.c" -lm -o vdbe 2>/dev/null "$ztrace" cc --instr calls -- clang $cflags "$src/shell.c" "$src/sqlite3.c" -lm -o calls 2>/dev/null run() { "$@" db.sqlite ".read workload.sql"; } # Milliseconds a command takes, the best of $1 runs, each on a fresh # database: the wall clock, or a tracer's own measure of the program's run # when it gives one (without its setup and its close: perf --syscalls opens # and closes 736 tracepoints). What it prints is kept in out.txt. ms() { n=$1 shift best= for _ in $(seq "$n"); do rm -f db.sqlite* start=$(date +%s%N) "$@" > out.txt 2>&1 t=$((($(date +%s%N) - start) / 1000)) said=$(sed -n 's/^r\.ztrace: .*, \([0-9.]*\) ms, .*/\1/p' out.txt) [ -n "$said" ] && t=$(echo "$said" | awk '{ printf "%d", $1 * 1000 }') [ -z "$best" ] || [ "$t" -lt "$best" ] && best=$t done echo "$best" | awk '{ printf "%.1f", $1 / 1000 }' } plain=$(ms 5 run ./plain) grep -q '^5000|1900|2019$' out.txt printf 'the shell alone: %s ms; ' "$plain" rm -f db.sqlite* "$ztrace" perf -o r.ztrace -- ./plain db.sqlite ".read workload.sql" 2>&1 | awk '/instructions/ { printf "%d million instructions\n", $1 / 1e6 }' for b in all vdbe calls; do printf ' built by ztrace cc (%s), no tracer: %s ms\n' "$b" "$(ms 3 run "./$b")"; done printf '\n%-56s %9s %9s %8s %7s %9s %7s %10s %10s %7s\n' backend moments events MB B/moment ms 'x shell' moments/s events/s 'seek ms' # One row: its name, how many runs to take the best of, the command; the # recording is r.ztrace. Seek: `ztrace show` of the middle moment (open, # replay from the keyframe before it, print). row() { name=$1 n=$2 shift 2 t=$(ms "$n" "$@") # What the command said of its recording: "r.ztrace: N moments, N events, ...". set -- $(sed -n 's/^r\.ztrace: \([0-9]*\) moments, \([0-9]*\) events.*/\1 \2/p' out.txt) [ $# -eq 2 ] || { cat out.txt; exit 1; } moments=$1 events=$2 size=$(stat -c %s r.ztrace) seek=$(ms 3 "$ztrace" show r.ztrace $((moments / 2))) printf '%-56s %9d %9d %8.1f %7d %9s %6.1fx' "$name" "$moments" "$events" "$(echo "$size" | awk '{ print $1 / 1e6 }')" $((size / moments)) "$t" "$(echo "$t $plain" | awk '{ print $1 / $2 }')" echo "$moments $events $t" | awk '{ printf " %10d %10d", $1 * 1000 / $3, $2 * 1000 / $3 }' printf ' %7s\n' "$seek" } row 'record --by syscall' 3 run "$ztrace" record --by syscall -o r.ztrace -- ./plain row 'record --by line --steps 1000000' 1 run "$ztrace" record --by line --steps 1000000 -o r.ztrace -- ./plain row 'record --by break -b sqlite3BtreeInsert' 3 run "$ztrace" record --by break -b sqlite3BtreeInsert -o r.ztrace -- ./plain row 'run --preload ... --alloc' 3 run "$ztrace" run --preload "$agent" --alloc -o r.ztrace -- ./plain row 'run --preload ... --syscalls' 3 run "$ztrace" run --preload "$agent" --syscalls -o r.ztrace -- ./plain row 'run --preload ... --hook sqlite3BtreeInsert' 3 run "$ztrace" run --preload "$agent" --hook sqlite3BtreeInsert -o r.ztrace -- ./plain row 'perf --faults --syscalls' 3 run "$ztrace" perf --faults --syscalls -o r.ztrace -- ./plain row 'perf --break sqlite3BtreeInsert' 3 run "$ztrace" perf --break sqlite3BtreeInsert -o r.ztrace -- ./plain row 'cc (everything), run' 1 run "$ztrace" run -o r.ztrace -- ./all row 'cc --only sqlite3VdbeExec, run' 1 run "$ztrace" run -o r.ztrace -- ./vdbe row 'cc --instr calls, run' 3 run "$ztrace" run -o r.ztrace -- ./calls row 'cc --instr calls, record --by break ... --alloc --preload' 3 run "$ztrace" record --by break -b sqlite3BtreeInsert --alloc --preload "$agent" -o r.ztrace -- ./calls # The debugger: a breakpoint where the interpreter of SQL begins, and the call chain there. rm -f db.sqlite* printf 'b sqlite3VdbeExec\nr\nbt\nquit\n' | "$ztrace" dbg -o r.ztrace -- ./plain db.sqlite ".read workload.sql" > out.txt 2>&1 printf '\ndbg, b sqlite3VdbeExec, bt: %d frames, %s\n' "$(grep -c '^#' out.txt)" "$(grep '^#' out.txt | tail -1 | cut -d' ' -f3)"