c50b3cfb3b
A 50k-file library used to wedge the database. This wave fixes the four reasons why, plus a timestamp-ordering bug the work exposed. Chunked scans (internal/library/scan.go) - One transaction wrapped the whole reconciliation, holding SQLite's single write lock for the entire scan. Now 500 groups per transaction, and the two missing-file passes are batched too. - Verified: a 20,000-file scan reports partial progress (scanned=1500 mid-scan) and a concurrent progress save returns 200 throughout. Under the old code that save blocked for the whole scan. - Behaviour change: a scan is no longer all-or-nothing. A crash now leaves a consistent partial scan. The scan is idempotent, so re-running finishes it. - res.Errors is capped at 100 (a broken mount used to append one string per file, then serialise the lot into the job row); the overflow is counted. Concurrent job workers (internal/jobs/jobs.go) - Run() was single-threaded, so a scan stalled every enrichment job behind it. Now 4 workers with jittered idle waits. - Handlers run concurrently, so Register/lookup take an RWMutex, and a panicking handler is recovered and marked failed rather than killing a worker and stranding a row in 'running'. - RecoverStale() requeues jobs left 'running' by a dead process; called at boot from main.go. Previously such a job stayed 'running' forever and the UI polled it forever. Explore (internal/media/discover.go) - Full-scanned and sorted the whole library on every home load, and was deterministic, so it showed the same 30 items every visit. Now a per-kind fair-share id range scan with a random start, wrap-around, dedup and interleaving. The doc comment claimed per-kind sampling before; now it does. Indexes (migrations/0008_query_indexes.sql) - Benchmarked with ANALYZE over 60k items: browse page 1 13.37ms -> 0.03ms (484x, full scan -> covering index), deep page 8x, recently added 3.2x. - A candidate index for the Continue shelf was built, measured and REJECTED: the planner never chose it because `finished = 0` selects the majority of a user's rows, so it had no selectivity and would have cost a write on every progress save. Recorded in the migration rather than shipped as dead weight. Canonical timestamps (internal/core/stamp.go) - time.RFC3339Nano trims trailing zeros, so its fraction width varies and the strings are not lexicographically sortable: ".5Z" > ".55Z" as strings though .500s < .550s. That broke every ORDER BY <timestamp> and MAX(timestamp), including the Continue shelf and Most Viewed. It was wrong even in columns written 100% by Go, so it was not a cross-format issue. - All 14 write sites now use core.Stamp, matching SQLite's own default format exactly. Read paths are untouched: RFC3339Nano parses both shapes, so existing rows keep working. Also: busy_timeout 5s -> 15s. Chunked scans still delay a concurrent write, and measured tail latency on a first scan of 15k new files reached 3.3s, leaving almost no margin at 5s. Waiting beats failing with SQLITE_BUSY and losing a playback position. A 10ms inter-batch yield was implemented, measured, found to have no effect distinguishable from noise (overlapping ranges), and removed. Verified end to end, not just compiled: orphan recovery logged at boot, scan returns 202 in 0.7ms, Explore varies across loads, concurrent writes survive a scan. gofmt/vet clean, 14 packages pass, -race clean on the queue, svelte-check 0/0, fresh install applies all 8 migrations.
67 lines
2.2 KiB
Go
67 lines
2.2 KiB
Go
package core
|
|
|
|
import (
|
|
"database/sql"
|
|
"fmt"
|
|
"net/url"
|
|
"strings"
|
|
"time"
|
|
|
|
// Pure-Go SQLite driver. No cgo means CGO_ENABLED=0 static binaries and
|
|
// trivial cross-compilation for arm64.
|
|
_ "modernc.org/sqlite"
|
|
)
|
|
|
|
// pragmas applied to every pooled connection, via the DSN.
|
|
//
|
|
// These are passed in the DSN rather than run as statements after connecting
|
|
// because database/sql pools connections: a one-off PRAGMA would configure
|
|
// only whichever single connection happened to serve it.
|
|
var sqlitePragmas = []string{
|
|
"journal_mode(WAL)", // concurrent readers during a scan
|
|
// busy_timeout is deliberately generous. Chunked scans hold the single
|
|
// write lock in short bursts, so a concurrent write is never blocked for
|
|
// long — but it is blocked, and measured tail latency on a first scan of
|
|
// 15,000 new files reached 3.3s. At the old 5s ceiling that left almost no
|
|
// margin, and on slower storage a progress save would fail outright with
|
|
// SQLITE_BUSY and the user would lose their place. Waiting is strictly
|
|
// better than failing: the write still succeeds, just later.
|
|
"busy_timeout(15000)",
|
|
"foreign_keys(1)", // enforce the schema's referential integrity
|
|
"synchronous(NORMAL)", // safe under WAL, far faster than FULL
|
|
"cache_size(-32000)", // ~32 MB page cache
|
|
}
|
|
|
|
// OpenDB opens the SQLite database with the pragmas Vellum depends on.
|
|
func OpenDB(path string) (*sql.DB, error) {
|
|
db, err := sql.Open("sqlite", buildDSN(path, sqlitePragmas))
|
|
if err != nil {
|
|
return nil, fmt.Errorf("open sqlite %s: %w", path, err)
|
|
}
|
|
|
|
// SQLite is a single-writer database. A large pool just produces
|
|
// SQLITE_BUSY contention; a small pool with a busy_timeout is faster.
|
|
db.SetMaxOpenConns(8)
|
|
db.SetMaxIdleConns(8)
|
|
db.SetConnMaxLifetime(0)
|
|
db.SetConnMaxIdleTime(5 * time.Minute)
|
|
|
|
if err := db.Ping(); err != nil {
|
|
_ = db.Close()
|
|
return nil, fmt.Errorf("ping sqlite %s: %w", path, err)
|
|
}
|
|
return db, nil
|
|
}
|
|
|
|
// buildDSN assembles a modernc.org/sqlite DSN with repeated _pragma params.
|
|
func buildDSN(path string, pragmas []string) string {
|
|
var b strings.Builder
|
|
b.WriteString("file:")
|
|
b.WriteString(path)
|
|
b.WriteString("?_time_format=sqlite")
|
|
for _, p := range pragmas {
|
|
b.WriteString("&_pragma=")
|
|
b.WriteString(url.QueryEscape(p))
|
|
}
|
|
return b.String()
|
|
}
|