Home A - Z
Page Index

An Otter Wiki

Commit 7b9a93

2026-08-17 09:45:48 Mark Tarek Hepperle: Why performance is so bad deleted.
Why performance is so bad.md .. /dev/null
@@ 1,95 0,0 @@
- # Why performance is so bad
-
- I reproduced this with a synthetic 2000-page wiki and profiled it. Here's what's going on.
-
- How titles are fetched
- Titles aren't stored in the database as titles. Each entry's title comes from two places:
-
- The filename — via get_pagename / get_pagename_for_title (helper.py:184).
- The page's first Markdown heading, used to fix the capitalization (this happens whenever RETAIN_PAGE_NAME_CASE is off, which is the default). This is the ftoc = get_ftoc(f) call at pageindex.py:133.
- get_ftoc (helper.py:373) is the database part, and it runs once per page: stat the file for its mtime, compute sha256("ftoc://<filename>"), then issue one ORM SELECT against the cache table (models.py:79) filtered on key == hash AND datetime >= mtime. On a miss it reads the file, runs the full Markdown renderer, and writes the row back with its own db.session.commit() (helper.py:350).
-
- So for 2000 pages the index does 2000 stats + 2000 separate SELECTs — and on a cold cache also 2000 file reads, 2000 Markdown parses, and 2000 commits. Separately, the sidebar re-reads the first 512 bytes of all 2000 files to get the same headers again (sidebar.py:160); it doesn't share the ftoc cache.
-
- Measured breakdown (2000 pages, 5 headings each)
- Warm request ≈ 1.0s, cold ≈ 4.0s. Of the 0.85s spent in PageIndex():
-
- time note
- get_ftoc() × 2001 0.399s same rows via one bulk IN query: 0.024s
- has_children scan (pageindex.py:172-175) 0.256s O(n²): 4,010,273 startswith calls. Same answer from a set of directory prefixes: 0.001s
- url_for / get_pagename / split_path / storage.exists ~0.2s 12k+ url_for calls
- The DB itself is not the bottleneck — SQLite's own execute time for those 2000 queries is 0.065s. Running the identical 2000 SELECTs through raw DBAPI takes 0.031s versus 0.399s through the ORM. It's ~200µs of SQLAlchemy per-query overhead, multiplied by page count.
-
- Cold path: Markdown parsing all pages is 1.296s, and update_ftoc_cache's per-page commit is 0.918s — the same writes in a single commit take 0.072s.
-
- Why yours is 6s and not 1s
- The index emits a link for every heading of every page, each with its own url_for:
-
- headings/page cold warm HTML
- 0 2.5s 0.84s 1.0 MB
- 5 3.8s 0.98s 3.6 MB
- 15 6.2s 1.15s 8.8 MB
- 30 9.6s 1.52s 16.5 MB
- All of that heading markup goes into a <div class="pagetoc"> that is display:none on load (snippets/pageindex.html) — it's hidden behind the "Toggle page headings" switch. So the bulk of the payload and the parse cost is for content nobody sees until they flip that switch.
-
- Two things also keep the cache from being warm:
-
- Cache validity is Cache.datetime >= file mtime. I verified that after a fresh git clone of the repo, 2001/2001 entries are stale — checkout rewrites mtimes. Every redeploy or container rebuild pays the full cold cost.
- The default SQLALCHEMY_DATABASE_URI is sqlite:///:memory: (server.py:53). The Docker entrypoints override it with a file, but a plain install without that setting keeps the cache in memory, so every restart — and every gunicorn worker independently — starts cold.
- Where the wins are
- Roughly in order of payoff, if you want me to implement any of these:
-
- Batch the ftoc lookups — one WHERE key IN (...) query before the loop instead of 2000. ~0.38s, and it's the single biggest warm cost.
- Replace the has_children scan with a precomputed set of directory prefixes. ~0.25s for a few lines.
- Don't emit per-heading TOC entries into the initial page (or make them opt-in / lazy). This is what turns 1s into 6s on heading-heavy wikis, and it's hidden by default anyway.
- Single commit in the cold rebuild instead of one per page. ~0.85s off first load.
- Make cache validity content-based (git blob SHA) rather than mtime, so redeploys don't invalidate everything.
- pageindex.py
- Wait, what do you mean by commit? Are there 2000 git commits made for every page load?
- No — SQLite transaction commits, not git commits. Sorry for the ambiguous word. Let me make it concrete:
-
- Read helper.py (lines 351-372)
- Bash Verify no git commits occur on index load
- IN
- cd /tmp/claude-1000/-home-tadeo-code2-otterwiki/dfaab8e6-d048-4e29-b117-5886b952377e/scratchpad && cat > gitcheck.py <<'EOF'
- import os
- from timeit import default_timer as timer
- BASE = "/tmp/claude-1000/-home-tadeo-code2-otterwiki/dfaab8e6-d048-4e29-b117-5886b952377e/scratchpad/wiki"
- os.environ["OTTERWIKI_SETTINGS"] = os.path.join(BASE, "settings.cfg")
- from otterwiki.server import app, storage, db
- from otterwiki.models import Cache
- client = app.test_client()
- def ncommits():
- return int(storage.repo.git.rev_list("--count", "HEAD"))
- with app.app_context():
- Cache.query.delete(); db.session.commit() # force the cold path
- before = ncommits()
- t = timer(); client.get("/-/index"); print("cold index load: %.3fs" % (timer()-t))
- after = ncommits()
- with app.app_context():
- print("cache rows written:", Cache.query.count())
- print("git commits before:", before, " after:", after, " -> created:", after - before)
- print("git dirty:", storage.repo.is_dirty(), " untracked:", len(storage.repo.untracked_files))
- EOF
- ./venv/bin/python gitcheck.py 2>&1 | tail -6
-
- OUT
- *** Starting An Otter Wiki 2.23.0
- cold index load: 3.836s
- cache rows written: 2001
- git commits before: 1 after: 1 -> created: 0
- git dirty: False untracked: 0
- Shell cwd was reset to /home/tadeo/code2/otterwiki
- Confirmed: zero git commits. The index load created 0 commits and left the repo clean, while writing 2001 cache rows.
-
- What I meant is db.session.commit() at helper.py:370 — a SQLAlchemy session commit, i.e. one SQLite transaction commit. update_ftoc_cache is called once per page, and each call ends its own transaction, so a full cold rebuild ends 2000 separate transactions instead of one.
-
- Two clarifications on my earlier phrasing:
-
- It's per cache miss, not per page load. get_ftoc only calls update_ftoc_cache when the cache lookup fails (helper.py:398). On a warm cache the index does 2000 reads and zero writes. The 0.918s figure applies to a cold rebuild — first load after a restart, or after a redeploy resets file mtimes.
-
- Why 2000 transactions costs ~0.9s: each commit is durability-bound (SQLite syncs at transaction end), so you pay per-transaction overhead 2000 times rather than once. That's the 0.918s → 0.072s difference I measured. On a server with slower or network-backed storage this gap gets considerably wider than it is on my local disk.
-
- Worth noting while you're looking at this function: line 362 does another SELECT to check whether the key already exists — on top of the one get_ftoc just performed at helper.py:378. So a cold page costs 2 SELECTs plus a commit, and all three collapse into batched operations if the loop is restructured.
-
- The git side of a page load is only reads — os.walk for the file list, plus open() on the working copy. No git command runs at all on the index path.
0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9 0 1 2 3 4 5 6 7 8 9