navidrome/cmd/scan.go
Deluan Quintão cc315dcc8c
perf(db): keep query planner statistics trustworthy with full ANALYZE (#5740)
* perf(db): keep query planner statistics trustworthy with full ANALYZE

PRAGMA optimize's internal ANALYZE runs with a limited analysis budget
(~2000 rows) that writes wrong sqlite_stat1 entries for low-cardinality
indexes: on a 96K-track library it claimed (missing, library_id) narrows
to ~2000 rows when it matches the whole table. The planner then prefers
that index over the sort index and falls back to a full-table temp
B-tree sort per request, turning paginated song listings into
multi-second queries (reproduced at 5.5s on real hardware; ~90x slower
than with correct stats). Every index-creating migration re-triggered
the poisoning via the post-migration optimize, and the daily optimizer
could re-trigger it on large library changes. Setting analysis_limit on
the connection does not help: optimize ignores it.

Run a plain full ANALYZE instead: after migrations with schema changes,
and in db.Optimize (daily schedule and scan-end). Stats are stored in
the database file, so one connection suffices and the per-connection
pool loop is gone. The Optimize call at shutdown is removed: stats are
maintained at migration/scan/daily points, and an ANALYZE during
shutdown only delays it and races container stop timeouts.

* perf(db): drop startup PRAGMA optimize that re-poisons planner stats

The startup PRAGMA optimize=0x10002 runs SQLite's budget-limited internal
ANALYZE (bit 0x02), which writes truncated sqlite_stat1 rows for
low-cardinality indexes -- the exact statistics-poisoning this PR set out to
eliminate. Because DevOptimizeDB defaults to true, a restart with no pending
migrations would re-poison the planner until the next scan or daily Optimize.

Remove it: statistics are already refreshed with a full ANALYZE after
schema-changing migrations (Init) and via Optimize at scan-end and on the daily
schedule, so nothing on the startup path needs to touch them. Also clarify that
Optimize is a no-op unless DevOptimizeDB is enabled.

* chore(db): remove the DevOptimizeDB flag and skip Optimize on quick scans

The flag only gated the optimize/ANALYZE maintenance calls and there is
no reason to leave planner statistics unmaintained; the guards are gone
along with the flag. The scan-end Optimize now runs only after full
scans — quick scans barely move the statistics, and the daily schedule
covers drift.

* style(scanner): drop redundant comment in runOptimize

* chore(persistence): drop the no-op PRAGMA optimize from ScanEnd

Mask 0x10000 only selects candidate tables by size change; without the
0x02 action bit optimize does nothing (verified: sqlite_stat1 stays
stale after a 100x table growth). The scan-end statistics refresh is
db.Optimize's full ANALYZE, and the expression-collation-index concern
the old comment guarded against no longer applies.

* fix(scanner): run the post-scan ANALYZE in the server process

With the external scanner (the default), the scan pipeline runs in a
subprocess, so its ANALYZE was invisible to the server: SQLite loads
sqlite_stat1 into the process's shared schema cache, and an ANALYZE from
another process does not refresh it — verified with the production DSN
that even brand-new pool connections keep planning with the old
statistics until the server restarts. An in-process ANALYZE, by
contrast, is immediately visible to every pooled connection through the
same shared cache.

Move the full-scan Optimize from the scanner pipeline to the scan
controller, which always runs in the server process.

* fix(scanner): honor promoted full scans in the optimize gate

A quick scan resuming an interrupted full scan is promoted inside the
scanner (possibly in a subprocess); mirror the promotion in the
controller so the post-scan ANALYZE isn't skipped.

* refactor: apply cleanup review findings

- drop forceFullRescan's inline ANALYZE: Init already runs a full
  ANALYZE after any migration batch with schema changes, so upgrades
  including a full-rescan migration analyzed the whole DB twice
- resumingFullScan uses a filtered CountAll instead of fetching and
  scanning all libraries
- document why CallScan (CLI) deliberately skips the post-scan Optimize

* perf(db): make planner analysis maintenance resilient

Check analysis freshness every 30 minutes and refresh statistics when the last successful run is over 24 hours old or a scan marked them pending. Persist successful analysis state, retry skipped or failed maintenance, coordinate checks with scans, and cover standalone CLI full scans.

* perf(db): avoid analyzing routine quick-scan changes

Reserve pending analysis for full scans, unscanned libraries, and retry state. Incremental quick scans now rely on the 24-hour freshness window instead of triggering a full ANALYZE at the next maintenance check.

* fix(scan): analyze resumed full scans in CLI

* fix(db): back off failed analysis retries

* feat(db): allow disabling scheduled analysis

* test(db): remove redundant analysis coverage

* refactor(db): split ANALYZE maintenance into optimize.go and dedupe call sites

- move query-planner statistics code from db.go to its own optimize.go
  (and matching optimize_test.go)
- log ANALYZE elapsed time inside Optimize/OptimizeIfNeeded instead of
  repeating the timing block at every call site
- drop the LastDBAnalyzeAttemptAt write on success: it is only read
  while failures >= 1, and every failure rewrites it first
- extract runPostScanAnalysis (cmd) and anyIncludedLibrary (scanner)
  helpers
2026-07-13 12:04:29 -04:00

168 lines
4.7 KiB
Go

package cmd
import (
"bufio"
"context"
"encoding/gob"
"errors"
"fmt"
"os"
"strings"
"github.com/navidrome/navidrome/core"
"github.com/navidrome/navidrome/core/playlists"
"github.com/navidrome/navidrome/db"
"github.com/navidrome/navidrome/log"
"github.com/navidrome/navidrome/model"
"github.com/navidrome/navidrome/persistence"
"github.com/navidrome/navidrome/scanner"
"github.com/navidrome/navidrome/utils/pl"
"github.com/spf13/cobra"
)
var (
fullScan bool
subprocess bool
targets []string
targetFile string
)
func init() {
scanCmd.Flags().BoolVarP(&fullScan, "full", "f", false, "check all subfolders, ignoring timestamps")
scanCmd.Flags().BoolVarP(&subprocess, "subprocess", "", false, "run as subprocess (internal use)")
scanCmd.Flags().StringArrayVarP(&targets, "target", "t", []string{}, "list of libraryID:folderPath pairs, can be repeated (e.g., \"-t 1:Music/Rock -t 1:Music/Jazz -t 2:Classical\")")
scanCmd.Flags().StringVar(&targetFile, "target-file", "", "path to file containing targets (one libraryID:folderPath per line)")
rootCmd.AddCommand(scanCmd)
}
var scanCmd = &cobra.Command{
Use: "scan",
Short: "Scan music folder",
Long: "Scan music folder for updates",
Run: func(cmd *cobra.Command, args []string) {
runScanner(cmd.Context())
},
}
func trackScanInteractively(ctx context.Context, progress <-chan *scanner.ProgressInfo) (bool, error) {
var changesDetected bool
var scanErrors []error
for status := range pl.ReadOrDone(ctx, progress) {
if status.Warning != "" {
log.Warn(ctx, "Scan warning", "error", status.Warning)
}
if status.Error != "" {
log.Error(ctx, "Scan error", "error", status.Error)
scanErrors = append(scanErrors, errors.New(status.Error))
}
if status.ChangesDetected {
changesDetected = true
}
}
if fullScan {
log.Info("Finished full rescan")
} else {
log.Info("Finished rescan")
}
return changesDetected, errors.Join(scanErrors...)
}
func trackScanAsSubprocess(ctx context.Context, progress <-chan *scanner.ProgressInfo) {
encoder := gob.NewEncoder(os.Stdout)
for status := range pl.ReadOrDone(ctx, progress) {
err := encoder.Encode(status)
if err != nil {
log.Error(ctx, "Failed to encode status", err)
}
}
}
func runScanner(ctx context.Context) {
sqlDB := db.Db()
defer db.Db().Close()
ds := persistence.New(sqlDB)
pls := playlists.NewPlaylists(ds, core.NewImageUploadService())
// Parse targets from command line or file
var scanTargets []model.ScanTarget
var err error
if targetFile != "" {
scanTargets, err = readTargetsFromFile(targetFile)
if err != nil {
log.Fatal(ctx, "Failed to read targets from file", err)
}
log.Info(ctx, "Scanning specific folders from file", "numTargets", len(scanTargets))
} else if len(targets) > 0 {
scanTargets, err = model.ParseTargets(targets)
if err != nil {
log.Fatal(ctx, "Failed to parse targets", err)
}
log.Info(ctx, "Scanning specific folders", "numTargets", len(scanTargets))
}
effectiveFullScan := fullScan
if !subprocess {
effectiveFullScan = scanner.EffectiveFullScan(ctx, ds, fullScan, scanTargets)
if effectiveFullScan {
if err := db.MarkOptimizePending(ctx); err != nil {
log.Error(ctx, "Error marking DB analysis pending", err)
}
}
}
progress, err := scanner.CallScan(ctx, ds, pls, fullScan, scanTargets)
if err != nil {
log.Fatal(ctx, "Failed to scan", err)
}
// Wait for the scanner to finish
if subprocess {
trackScanAsSubprocess(ctx, progress)
} else {
changesDetected, scanErr := trackScanInteractively(ctx, progress)
runPostScanAnalysis(ctx, changesDetected, effectiveFullScan, scanErr)
}
}
func runPostScanAnalysis(ctx context.Context, changesDetected, effectiveFullScan bool, scanErr error) {
if changesDetected {
if err := db.MarkOptimizePending(ctx); err != nil {
log.Error(ctx, "Error marking DB analysis pending", err)
}
}
if effectiveFullScan && scanErr == nil {
if err := db.Optimize(ctx); err != nil {
log.Error(ctx, "Error analyzing DB", err)
}
}
}
// readTargetsFromFile reads scan targets from a file, one per line.
// Each line should be in the format "libraryID:folderPath".
// Empty lines and lines starting with # are ignored.
func readTargetsFromFile(filePath string) ([]model.ScanTarget, error) {
file, err := os.Open(filePath)
if err != nil {
return nil, fmt.Errorf("failed to open target file: %w", err)
}
defer file.Close()
var targetStrings []string
scanner := bufio.NewScanner(file)
for scanner.Scan() {
line := strings.TrimSpace(scanner.Text())
// Skip empty lines and comments
if line == "" {
continue
}
targetStrings = append(targetStrings, line)
}
if err := scanner.Err(); err != nil {
return nil, fmt.Errorf("failed to read target file: %w", err)
}
return model.ParseTargets(targetStrings)
}