#!/usr/bin/env python
"""
HTTP benchmark harness for the MariaDB-vs-SQLite gameplan comparison.
Connects to a single `bench serve` on 127.0.0.1:PORT and selects the target site
via an explicit Host header (so no /etc/hosts changes are needed). Auth uses the
sid cookie captured from a real login.
Measures, per site:
- read latency : get_discussions feed + open one discussion, at concurrency C
- write throughput/contention : N concurrent GP Comment inserts (full hook cascade)
Usage:
env/bin/python bench_http.py --site gp-maria.test --mode read --conc 8 --requests 400
env/bin/python bench_http.py --site gp-sqlite.test --mode write --conc 10 --requests 200
"""
import argparse
import json
import statistics
import time
from concurrent.futures import ThreadPoolExecutor
import requests
BASE = "http://127.0.0.1:8000" # overridden by --port
def login(site, user="Administrator", pwd="admin"):
h = {"Host": site}
r = requests.post(f"{BASE}/api/method/login", data={"usr": user, "pwd": pwd}, headers=h, timeout=30)
r.raise_for_status()
sid = r.cookies.get("sid")
if not sid:
raise SystemExit(f"login failed on {site}: {r.text[:200]}")
return sid
def hdr(site, sid):
return {"Host": site, "Cookie": f"sid={sid}"}
def get_discussion_names(site, sid, limit=200):
h = hdr(site, sid)
r = requests.get(
f"{BASE}/api/v2/method/gameplan.gameplan.doctype.gp_discussion.api.get_discussions",
params={"limit": limit, "order_by": "last_post_at desc"},
headers=h,
timeout=60,
)
r.raise_for_status()
data = r.json()
rows = data.get("data") or data.get("message") or data
return [d["name"] for d in rows]
def timed(fn):
t0 = time.perf_counter()
ok = True
err = None
try:
fn()
except Exception as e: # noqa: BLE001
ok = False
err = str(e)[:120]
return (time.perf_counter() - t0) * 1000.0, ok, err
def run(reqs, conc):
"""reqs: list of zero-arg callables. Returns latencies(ms), oks, errors."""
lat, oks, errs = [], 0, []
t0 = time.perf_counter()
with ThreadPoolExecutor(max_workers=conc) as ex:
for ms, ok, err in ex.map(lambda f: timed(f), reqs):
lat.append(ms)
if ok:
oks += 1
elif err:
errs.append(err)
wall = time.perf_counter() - t0
return lat, oks, errs, wall
def pct(xs, p):
if not xs:
return 0.0
xs = sorted(xs)
i = min(len(xs) - 1, int(round((p / 100.0) * (len(xs) - 1))))
return xs[i]
def summarize(label, lat, oks, errs, wall, total):
from collections import Counter
out = {
"label": label,
"total": total,
"ok": oks,
"failed": total - oks,
"wall_s": round(wall, 3),
"req_per_s": round(total / wall, 1) if wall else 0,
"p50_ms": round(pct(lat, 50), 1),
"p95_ms": round(pct(lat, 95), 1),
"p99_ms": round(pct(lat, 99), 1),
"max_ms": round(max(lat), 1) if lat else 0,
"err_sample": dict(Counter(errs).most_common(3)),
}
print(json.dumps(out))
return out
def main():
ap = argparse.ArgumentParser()
ap.add_argument("--site", required=True)
ap.add_argument("--mode", choices=["read", "write"], required=True)
ap.add_argument("--conc", type=int, default=8)
ap.add_argument("--requests", type=int, default=400)
ap.add_argument("--warm", type=int, default=30)
ap.add_argument("--port", type=int, default=8000)
args = ap.parse_args()
global BASE
BASE = f"http://127.0.0.1:{args.port}"
sid = login(args.site)
names = get_discussion_names(args.site, sid, limit=200)
if not names:
raise SystemExit("no discussions found; seed first")
h = hdr(args.site, sid)
if args.mode == "read":
# Mix: half feed-list calls, half open-one-discussion calls (realistic home + open).
def feed():
r = requests.get(
f"{BASE}/api/v2/method/gameplan.gameplan.doctype.gp_discussion.api.get_discussions",
params={"limit": 50, "order_by": "last_post_at desc"},
headers=h,
timeout=60,
)
r.raise_for_status()
def open_one(i):
n = names[i % len(names)]
r = requests.get(f"{BASE}/api/v2/document/GP Discussion/{n}", headers=h, timeout=60)
r.raise_for_status()
reqs = []
for i in range(args.requests):
reqs.append(feed if i % 2 == 0 else (lambda i=i: open_one(i)))
# Warmup (not measured)
run(reqs[: args.warm], args.conc)
lat, oks, errs, wall = run(reqs, args.conc)
summarize(f"{args.site} read conc={args.conc}", lat, oks, errs, wall, len(reqs))
else: # write
def post_comment(i):
n = names[i % len(names)]
body = {
"reference_doctype": "GP Discussion",
"reference_name": n,
"content": f"<p>bench concurrent comment {i}</p>",
}
r = requests.post(
f"{BASE}/api/v2/document/GP Comment",
json=body,
headers={**h, "Content-Type": "application/json"},
timeout=120,
)
if r.status_code >= 400:
raise RuntimeError(f"{r.status_code}:{r.text[:80]}")
reqs = [(lambda i=i: post_comment(i)) for i in range(args.requests)]
lat, oks, errs, wall = run(reqs, args.conc)
summarize(f"{args.site} write conc={args.conc}", lat, oks, errs, wall, len(reqs))
if __name__ == "__main__":
main()
Summary
Benchmark comparing a MariaDB-backed vs SQLite-backed Gameplan site on identical data, to inform when each backend is appropriate. Headline: SQLite is lighter (disk/memory) and faster on reads, and even posts higher write throughput on this host — but that write lead is a durability asymmetry, not scaling, and SQLite cannot scale writes at all (single-writer).
Method
Two fresh sites, Gameplan installed:
gp-maria.test(MariaDB 10.6),gp-sqlite.test(SQLite/WAL).Identical seed on both (verified equal counts):
Each post fans out to ~5
GP Unread Recordrows (one per private-space member), so the unread table — not comments — is the largest object and the main write-path stressor.Server:
gunicorn frappe.app:application, restarted fresh per site (so each loads only one engine). HTTP load driven via a Python harness (login → sid; target site selected byHostheader).1. Disk (identical data; SQLite
VACUUM+checkpoint first)SQLite ~13% smaller. MariaDB carries 38.6 MB of InnoDB index/clustering overhead. No FTS search DB existed on either site (search never exercised; it's SQLite FTS5 on both engines regardless, so it cancels out). Largest table on both:
GP Unread Record(~44 MB / 166k rows on MariaDB).2. Memory (gunicorn: 1 master + 4 workers, warm)
mariadbd)Worker RSS is effectively identical — at this size SQLite's in-process page cache is negligible. The real difference is the separate
mariadbddaemon (~76 MB RSS;innodb_buffer_pool_size=128 MB), which is shared across every site on the machine — pure overhead for one site, near-free amortized across many. SQLite has no daemon.3. Read latency (conc=8, 600 reqs; 50% feed-list + 50% open-one-discussion)
SQLite ~1.5× faster on reads: the DB is compiled into the worker, so every query skips the socket round-trip + connection handshake MariaDB pays per call. At this size the whole DB fits in OS page cache.
4. Write throughput + latency — headline (comment insert = full hook cascade)
Each comment insert re-saves the parent discussion (+ a
Versionrow) and inserts ~5 unread records — a fat, multi-statement transaction.SQLite high-concurrency stress (300 reqs): conc 24/32/48/64 → still 0 failures, throughput flat ~21 req/s, p50 914 ms → 2,800 ms, max 8,058 ms.
Neither engine scales write throughput with concurrency — both are single-machine, write-bound; added concurrency becomes queue latency, not throughput. The difference is the ceiling and failure mode:
database is lockedover HTTP, becausebusy_timeout=5000ms+ frappe's deadlock-retry absorb contention and the worker pool bounds real parallelism. The cost surfaces as unbounded tail latency (8 s at conc 64), not errors.Why SQLite "wins" writes — durability asymmetry (read before concluding)
synchronous=NORMAL, WAL — syncs only at checkpoint)innodb_flush_log_at_trx_commit=1+doublewrite)MariaDB does a durable fsync (+ doublewrite) on every commit; SQLite does not. That is essentially the entire write-speed gap, amplified by slow macOS
fsync. This is a fair out-of-the-box defaults comparison, but it pits a less-durable config against a fully-durable one. On production Linux the MariaDB gap narrows substantially.Genuine SQLite write risk
database is lockedis real — it appeared during the initial parallel data seed, where SQLite's internal second connection (sequence-number emulation) self-contended under I/O starvation and exceededbusy_timeout. Over HTTP it stayed hidden only because the worker pool bounds concurrency and short transactions stay under 5 s. Longer transactions, more writer processes than busy-timeout can absorb, or heavy concurrent I/O will surface it. SQLite serializes every writer — no horizontal write headroom.Bottom line
database is lockedunder enough writer contention. MariaDB sustains durable, multi-writer writes and would close most of the gap on production Linux.Fit: SQLite suits single-user / small-team / read-heavy / low-footprint instances. MariaDB is the safer choice for write-concurrent, multi-user teams and durability-sensitive data — its costs (disk, a shared daemon, per-commit fsync) buy write capacity and crash safety SQLite structurally cannot provide.
Suggested follow-up
Re-run with matched durability (SQLite
synchronous=FULLor MariaDBinnodb_flush_log_at_trx_commit=2) on Linux to isolate engine cost from fsync policy and confirm whether SQLite's write lead survives equal durability.Caveats: macOS fsync penalizes MariaDB writes more than Linux.
mariadbdRSS is shared across all machine sites (not isolatable). Effective write concurrency bounded by the 16-worker gunicorn pool. Durability settings differ (above) — the dominant write factor. Benchmark harness + parameterized seed loader available on request.Reproduce — benchmark code
Run from
frappe-bench/. Create two sites (--db-type mariadb/--db-type sqlite), install gameplan, run the seed loader on both, start gunicorn, then the harness scripts. Paths in the shell scripts are absolute to the test bench and should be adjusted.gameplan/benchmark_seed.py — parameterized data loader (bench execute target)bench_http.py — HTTP load harness (read + write modes)measure_disk.sh — disk footprint (SQLite VACUUM vs information_schema)mem_lat.sh — per-site memory + read latency (fresh gunicorn per site)write_sweep.sh — write-concurrency sweep