mirror of
https://github.com/navidrome/navidrome.git
synced 2026-08-01 07:21:17 +00:00
cc315dcc8c
* 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
98 lines
4.5 KiB
Go
98 lines
4.5 KiB
Go
package insights
|
|
|
|
type Data struct {
|
|
InsightsID string `json:"id"`
|
|
Version string `json:"version"`
|
|
Uptime int64 `json:"uptime"`
|
|
Build struct {
|
|
// build settings used by the Go compiler
|
|
Settings map[string]string `json:"settings"`
|
|
GoVersion string `json:"goVersion"`
|
|
} `json:"build"`
|
|
OS struct {
|
|
Type string `json:"type"`
|
|
Distro string `json:"distro,omitempty"`
|
|
Version string `json:"version,omitempty"`
|
|
Containerized bool `json:"containerized"`
|
|
Arch string `json:"arch"`
|
|
NumCPU int `json:"numCPU"`
|
|
Package string `json:"package,omitempty"`
|
|
} `json:"os"`
|
|
Mem struct {
|
|
Alloc uint64 `json:"alloc"`
|
|
TotalAlloc uint64 `json:"totalAlloc"`
|
|
Sys uint64 `json:"sys"`
|
|
NumGC uint32 `json:"numGC"`
|
|
} `json:"mem"`
|
|
FS struct {
|
|
Music *FSInfo `json:"music,omitempty"`
|
|
Data *FSInfo `json:"data,omitempty"`
|
|
Cache *FSInfo `json:"cache,omitempty"`
|
|
Backup *FSInfo `json:"backup,omitempty"`
|
|
} `json:"fs"`
|
|
Library struct {
|
|
Tracks int64 `json:"tracks"`
|
|
Albums int64 `json:"albums"`
|
|
Artists int64 `json:"artists"`
|
|
Playlists int64 `json:"playlists"`
|
|
Shares int64 `json:"shares"`
|
|
Radios int64 `json:"radios"`
|
|
Libraries int64 `json:"libraries"`
|
|
ActiveUsers int64 `json:"activeUsers"`
|
|
ActivePlayers map[string]int64 `json:"activePlayers,omitempty"`
|
|
FileSuffixes map[string]int64 `json:"fileSuffixes,omitempty"`
|
|
} `json:"library"`
|
|
Config struct {
|
|
LogLevel string `json:"logLevel,omitempty"`
|
|
LogFileConfigured bool `json:"logFileConfigured,omitempty"`
|
|
TLSConfigured bool `json:"tlsConfigured,omitempty"`
|
|
ScannerEnabled bool `json:"scannerEnabled,omitempty"`
|
|
ScannerExtractor string `json:"scannerExtractor,omitempty"`
|
|
ScanSchedule string `json:"scanSchedule,omitempty"`
|
|
ScanWatcherWait uint64 `json:"scanWatcherWait,omitempty"`
|
|
ScanOnStartup bool `json:"scanOnStartup,omitempty"`
|
|
EnableScheduledDBAnalyze bool `json:"enableScheduledDBAnalyze,omitempty"`
|
|
TranscodingCacheSize string `json:"transcodingCacheSize,omitempty"`
|
|
ImageCacheSize string `json:"imageCacheSize,omitempty"`
|
|
EnableArtworkPrecache bool `json:"enableArtworkPrecache,omitempty"`
|
|
EnableDownloads bool `json:"enableDownloads,omitempty"`
|
|
EnableSharing bool `json:"enableSharing,omitempty"`
|
|
EnableStarRating bool `json:"enableStarRating,omitempty"`
|
|
EnableLastFM bool `json:"enableLastFM,omitempty"`
|
|
EnableListenBrainz bool `json:"enableListenBrainz,omitempty"`
|
|
EnableDeezer bool `json:"enableDeezer,omitempty"`
|
|
EnableMediaFileCoverArt bool `json:"enableMediaFileCoverArt,omitempty"`
|
|
EnableJukebox bool `json:"enableJukebox,omitempty"`
|
|
EnablePrometheus bool `json:"enablePrometheus,omitempty"`
|
|
EnableArtworkUpload bool `json:"enableArtworkUpload,omitempty"`
|
|
CoverArtQuality int `json:"coverArtQuality,omitempty"`
|
|
EnableWebPEncoding bool `json:"enableWebPEncoding,omitempty"`
|
|
UICoverArtSize int `json:"uiCoverArtSize,omitempty"`
|
|
EnableCoverAnimation bool `json:"enableCoverAnimation,omitempty"`
|
|
EnableNowPlaying bool `json:"enableNowPlaying,omitempty"`
|
|
SessionTimeout uint64 `json:"sessionTimeout,omitempty"`
|
|
SearchFullString bool `json:"searchFullString,omitempty"`
|
|
SearchBackend string `json:"searchBackend,omitempty"`
|
|
RecentlyAddedByModTime bool `json:"recentlyAddedByModTime,omitempty"`
|
|
PreferSortTags bool `json:"preferSortTags,omitempty"`
|
|
BackupSchedule string `json:"backupSchedule,omitempty"`
|
|
BackupCount int `json:"backupCount,omitempty"`
|
|
DevActivityPanel bool `json:"devActivityPanel,omitempty"`
|
|
DefaultBackgroundURLSet bool `json:"defaultBackgroundURL,omitempty"`
|
|
HasSmartPlaylists bool `json:"hasSmartPlaylists,omitempty"`
|
|
ReverseProxyConfigured bool `json:"reverseProxyConfigured,omitempty"`
|
|
HasCustomPID bool `json:"hasCustomPID,omitempty"`
|
|
HasCustomTags bool `json:"hasCustomTags,omitempty"`
|
|
} `json:"config"`
|
|
Plugins map[string]PluginInfo `json:"plugins,omitempty"`
|
|
}
|
|
|
|
type PluginInfo struct {
|
|
Name string `json:"name"`
|
|
Version string `json:"version"`
|
|
}
|
|
|
|
type FSInfo struct {
|
|
Type string `json:"type,omitempty"`
|
|
}
|