Web UI gets progressively slower over hours of uptime #132

New issue

Closed

opened 2026-08-11 20:47:55 +00:00 by agent-pi · 1 comment

agent-pi commented

2026-08-11 20:47:55 +00:00

Collaborator

Cang's web interface gets progressively slower over hours to days of uptime. A daily container restart restores normal performance, pointing to a resource that accumulates and is reset on restart.

The primary cause is SQLite WAL file unbounded growth. WAL mode is enabled for concurrency, and the periodic camera scan writes new snapshot rows every few minutes. The default PASSIVE auto-checkpoint never succeeds because the web server always has at least one concurrent reader (e.g. the HTMX live-snapshot poll). The WAL accumulates write changes from every scan cycle indefinitely, and each read must scan the ever-growing WAL to find current page versions. Container restart triggers a RESTART checkpoint on close, truncating the WAL and restoring performance.

A secondary issue is that the calendar view (/days) runs a full table scan via SELECT DATE(recorded_at) AS day, COUNT(*), SUM(motion) FROM snapshots GROUP BY day ORDER BY day DESC — there is no index with recorded_at as the leftmost column.

With ~5000 snapshots/day from 2 cameras (soon doubling to 4 cameras), both the WAL accumulation and table scan degrade significantly over time.

Cang's web interface gets progressively slower over hours to days of uptime. A daily container restart restores normal performance, pointing to a resource that accumulates and is reset on restart. The primary cause is SQLite WAL file unbounded growth. WAL mode is enabled for concurrency, and the periodic camera scan writes new snapshot rows every few minutes. The default PASSIVE auto-checkpoint never succeeds because the web server always has at least one concurrent reader (e.g. the HTMX live-snapshot poll). The WAL accumulates write changes from every scan cycle indefinitely, and each read must scan the ever-growing WAL to find current page versions. Container restart triggers a RESTART checkpoint on close, truncating the WAL and restoring performance. A secondary issue is that the calendar view (/days) runs a full table scan via SELECT DATE(recorded_at) AS day, COUNT(*), SUM(motion) FROM snapshots GROUP BY day ORDER BY day DESC — there is no index with recorded_at as the leftmost column. With ~5000 snapshots/day from 2 cameras (soon doubling to 4 cameras), both the WAL accumulation and table scan degrade significantly over time.

agent-pi commented

2026-08-11 20:48:05 +00:00

Author

Collaborator

Plan

1. Add WAL checkpoint after periodic scan cycle

In src/cang/web/lifespan.py — after await _run_cleanup(cfg, db) returns in _periodic_scan, run:

await db.execute("PRAGMA wal_checkpoint(TRUNCATE)")

TRUNCATE mode blocks until all readers finish, then zeroes the WAL. This is safe because _periodic_scan is the only writer path — no race with concurrent writes. The checkpoint runs once per scan cycle (every 5 min by default), so overhead is minimal.

Also add PRAGMA busy_timeout = 5000 in open_db alongside the existing journal_mode/synchronous pragmas so readers don't wait indefinitely if a checkpoint briefly holds the lock.

2. Add index on (recorded_at, motion)

In src/cang/db.py — add a new migration V5 with:

CREATE INDEX IF NOT EXISTS idx_snapshots_recorded_motion
    ON snapshots (recorded_at, motion);

This turns the calendar GROUP BY into a single-pass index-only scan. It also speeds up list_snapshots_for_day without a camera filter.

Branch

fix/issue-132-wal-checkpoint-and-index

Commits

⚡ Add WAL checkpoint after periodic scan and busy_timeout
⚡ Add index on snapshots(recorded_at, motion) for calendar query
✅ Update definition-of-done checklist if needed

PR

fj pr create "Fix progressive web UI slowdown from WAL bloat and missing index" \
  -H https://forge.marvin8.zone \
  --base main \
  --head fix/issue-132-wal-checkpoint-and-index \
  -r marvin8/cang \
  --body "Closes #132"

## Plan ### 1. Add WAL checkpoint after periodic scan cycle In `src/cang/web/lifespan.py` — after `await _run_cleanup(cfg, db)` returns in `_periodic_scan`, run: ```python await db.execute("PRAGMA wal_checkpoint(TRUNCATE)") ``` TRUNCATE mode blocks until all readers finish, then zeroes the WAL. This is safe because `_periodic_scan` is the only writer path — no race with concurrent writes. The checkpoint runs once per scan cycle (every 5 min by default), so overhead is minimal. Also add `PRAGMA busy_timeout = 5000` in `open_db` alongside the existing journal_mode/synchronous pragmas so readers don't wait indefinitely if a checkpoint briefly holds the lock. ### 2. Add index on (recorded_at, motion) In `src/cang/db.py` — add a new migration V5 with: ```sql CREATE INDEX IF NOT EXISTS idx_snapshots_recorded_motion ON snapshots (recorded_at, motion); ``` This turns the calendar GROUP BY into a single-pass index-only scan. It also speeds up `list_snapshots_for_day` without a camera filter. ### Branch `fix/issue-132-wal-checkpoint-and-index` ### Commits 1. :zap: Add WAL checkpoint after periodic scan and busy_timeout 2. :zap: Add index on snapshots(recorded_at, motion) for calendar query 3. :white_check_mark: Update definition-of-done checklist if needed ### PR ``` fj pr create "Fix progressive web UI slowdown from WAL bloat and missing index" \ -H https://forge.marvin8.zone \ --base main \ --head fix/issue-132-wal-checkpoint-and-index \ -r marvin8/cang \ --body "Closes #132" ```

agent-pi referenced this issue from a pull request that will close it,

2026-08-11 20:51:09 +00:00

Fix progressive web UI slowdown from WAL bloat and missing index #133