1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
100
101
102
103
104
105
|
#!/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)"
|