perf(state): boot auto-archive issues ~6k autocommit writes on a single-connection state.db #716

New issue

Open

opened 2026-09-25 08:50:04 +02:00 by dries · 0 comments

dries commented

2026-09-25 08:50:04 +02:00

Owner

Follow-up from the perf report (item 6 + second tier).

Symptoms:

`internal/server/server.go:642-707` `autoArchiveInactiveSessions` runs at the start of `runAutoArchiveLoop` (:626). On a 5.9k-session DB `GetSessionsInactiveBefore` returns ~3,100 rows; each goes through `state/archive.go:41-53` `ArchiveSession` = UPSERT (always bumps `archived_at`) + DELETE, i.e. ~6,200 autocommit write txns, none skipped (the `keep` set only excludes recently-unarchived).
`internal/state/db.go:75` `SetMaxOpenConns(1)`: every `/api/sessions` poll (4 state reads in `applySessionStateWithWrites`, `handlers_state.go:171-194`; `PinnedSessions` fetched twice incl. `handlers_sessions.go:38`) queues behind those writes, plus the factory 1 Hz loop, notify poll, inbox.
`internal/server/handlers_sessions.go:222-231` writes 4 txns to state.db on every `/api/session/{id}` fetch (`state/archive.go:58-67,241-250`: DELETE + `recordUnarchive` UPSERT for session and project) even when nothing is archived — and the frontend re-issues that fetch on idle/tool-part/reconnect.

Fixes:

(1) load `ArchivedSessions()` first, skip rows whose `session_time_updated` already matches, wrap the rest in one `BEGIN…COMMIT`.
(2) WAL is already on: allow N readers (`SetMaxOpenConns(4)`) with an app-level write mutex, or a second read-only `*sql.DB`.
(3) only write when the id is present in `ArchivedSessions()`/`ArchivedProjects()` (or check `RowsAffected()` of the DELETE before recording intent).

Tests: count write txns in a fake for (1) and (3); (2) needs a race-free check that concurrent reads don't serialise behind a held writer.

Follow-up from the perf report (item 6 + second tier). **Symptoms:** 1. \`internal/server/server.go:642-707\` \`autoArchiveInactiveSessions\` runs at the start of \`runAutoArchiveLoop\` (:626). On a 5.9k-session DB \`GetSessionsInactiveBefore\` returns ~3,100 rows; each goes through \`state/archive.go:41-53\` \`ArchiveSession\` = UPSERT (always bumps \`archived_at\`) + DELETE, i.e. ~6,200 autocommit write txns, none skipped (the \`keep\` set only excludes recently-unarchived). 2. \`internal/state/db.go:75\` \`SetMaxOpenConns(1)\`: every \`/api/sessions\` poll (4 state reads in \`applySessionStateWithWrites\`, \`handlers_state.go:171-194\`; \`PinnedSessions\` fetched twice incl. \`handlers_sessions.go:38\`) queues behind those writes, plus the factory 1 Hz loop, notify poll, inbox. 3. \`internal/server/handlers_sessions.go:222-231\` writes 4 txns to state.db on every \`/api/session/{id}\` fetch (\`state/archive.go:58-67,241-250\`: DELETE + \`recordUnarchive\` UPSERT for session and project) even when nothing is archived — and the frontend re-issues that fetch on idle/tool-part/reconnect. **Fixes:** - (1) load \`ArchivedSessions()\` first, skip rows whose \`session_time_updated\` already matches, wrap the rest in one \`BEGIN…COMMIT\`. - (2) WAL is already on: allow N readers (\`SetMaxOpenConns(4)\`) with an app-level write mutex, or a second read-only \`*sql.DB\`. - (3) only write when the id is present in \`ArchivedSessions()\`/\`ArchivedProjects()\` (or check \`RowsAffected()\` of the DELETE before recording intent). Tests: count write txns in a fake for (1) and (3); (2) needs a race-free check that concurrent reads don't serialise behind a held writer.