* docs(plans): root-cause analysis for endpoints still slow after PR #292 Five endpoint groups stayed slow after the home/Continue Watching/Latest latency work shipped: Resume (110s p95), NextUp (17s p95), Latest (17s), /Items, and the home sections routes. The caps and caches from PR #292 are live in the deployed binary; they bounded how many rows the loops touch but not what each underlying query costs. Documents the four confirmed root causes (4.3M stale completed-with-position progress rows + missing resume index, unbounded next-up anchor scan, per-episode series rollup fanout, two index-starved history/scanner paths) with live EXPLAIN ANALYZE measurements and the fix plan implemented by the follow-up commits. AI-use disclosure: analysis and doc produced with AI (Claude) assistance. * perf(catalog): bound the global next-up anchor scan to recent completions The completed_episodes CTE in buildListNextUpQuery derived per-series anchors from the profile's ENTIRE completed history — DISTINCT ON over 233k rows joined to episodes for the worst bulk-import profile, then a per-series LATERAL that scans every episode of a fully-watched series before yielding nothing. 648 slow executions in a 19h window, 44.7s worst; this drove /Shows/NextUp (17.1s p95) and the next-up injection on the native home sections aggregate. Global queries now derive anchors from the profile's nextUpAnchorMaxRows (500) most recent completed rows — an ordered index walk on idx_uwp_profile_completed, with the hidden-items exclusion and date cutoff applied inside the bounded scan so hidden/old rows never consume the anchor budget. A next-up rail surfaces ~24 series; the 500 most recent completions cover every series that can realistically rank on it. Series-scoped calls (the show-detail tile) keep the unbounded shape: they must anchor on the series' last completed episode no matter how long ago it was watched, and are naturally bounded by one series. Measured on the live worst-case profile with the exact generated SQL: 44.7s worst / ~2.6s avg before; 10ms after (together with the one-time stale-resume-point data repair applied directly to the deployment DB — see docs/superpowers/plans/2026-07-06-slow-endpoint-root-causes.md). AI-use disclosure: implemented with AI (Claude) assistance. * perf(jellycompat,userstore): aggregate series watch-state rollup in SQL The series Played/UnplayedItemCount badge on list rails (per-library Latest, library browse, search results) and series detail pages was computed by materializing EVERY episode of every series on the page (episodeRepo.ListBySeriesIDs) and then batching per-episode progress+history lookups in 500-id chunks. A 50-series page of an episode-heavy library (Sports) expanded to 32,467 episode rows and ~65 sequential queries — measured 17-18s per /Items/Latest request, and PR #292's cached Latest fast path pays it on every response for series libraries. The same fanout made /Items?searchTerm=... slow whenever the result set was mostly series (Meilisearch itself answers in milliseconds). New optional store capability userstore.SeriesEpisodeRollupStore, implemented by PostgresUserStore as one GROUP BY e.series_id aggregate with semantics identical to the chunked path (episode availability via episode_libraries, hidden-items visibility on progress rows, completed-history fold, in-progress = not watched with position > 0 — verified value-for-value against the old semantics on a real 1,586-episode series). enrichSeriesListUserData and enrichDetailUserData use it when present; SQLite-backed stores and rollup query failures keep the existing chunked path as fallback. catalog.SeasonUserDataFromCounts pins the counts-to-DTO mapping to EpisodeRollupUserData. Measured on the live worst-case profile against the real 50-series Sports Latest page: ~17s of chunked round-trips before, 119ms in one query after. Part of docs/superpowers/plans/2026-07-06-slow-endpoint-root-causes.md. AI-use disclosure: implemented with AI (Claude) assistance. * perf(catalog): bound superseded-episode completed walk to recent history The Resume / Continue Watching superseded-episode filter loaded a profile's *entire* completed history into memory on every request that contained an in-progress episode: CompletedProgressSnapshots paged user_watch_progress WHERE completed=TRUE with no upper bound. The 2026-07-06 slow-query comparison showed this surviving as a 60-116s Resume tail even after the in-progress index landed live, because the 4.3M zeroed Plex-import rows are still completed=TRUE and were re-walked every load. A completed episode can only supersede an in-progress one it was finished more recently than (the query gates on done_progress.updated_at > ip_progress.updated_at), so only completed rows newer than the oldest in-progress entry can matter. Compute that cutoff in SupersededEpisodeProgressIDs and pass it to CompletedProgressSnapshots, which — since the completed listing is ordered updated_at DESC — stops paging as soon as it crosses the cutoff. Import-heavy profiles whose back-catalogue predates their current in-progress items now stop on the first page instead of paging hundreds of thousands of irrelevant rows. Correctness is unchanged: no relevant superseding row is excluded. * perf(catalog): hard-cap superseded-episode completed walk at 5 pages The updated_at cutoff added in the previous commit bounds the completed walk on the relevance axis, but a very old in-progress entry sitting behind a large volume of newer completions could still page deep. Add a 5-page (2,500-row) hard backstop on top of the cutoff: normal profiles still stop on page one via the cutoff, and only the adversarial tail hits the cap. When it engages the tail of the completed set goes unscanned, so a superseded episode could momentarily survive on Continue Watching — we log a warning when that happens (with profile_id + rows scanned) rather than mis-filter silently, and it self-corrects once the stale in-progress entry ages out of the scanned window. * perf(playback): extract subtitle fonts in a single ffmpeg pass Embedded ASS/SSA font extraction spawned one ffmpeg process per font attachment, each re-opening the (usually CephFS-backed) media file. Anime releases carry 15-47 fonts, so the per-spawn file-open cost dominated and pushed GET /api/v1/stream/{sid}/subtitles/{track}/fonts to a 17-60 s plateau (p95 ~33 s in the live logs). Collapse the N spawns into one ffmpeg invocation that dumps every attachment to a temp dir (-dump_attachment:idx path ... -i file -map 0:t? -c copy), then read the files back. The file is opened once instead of N times, taking p95 from ~30 s to ~1-2 s with no change to output. Safety is preserved. The 32-attachment / 32 MiB caps still apply: attachment size is stat'd before read so an over-limit font never enters memory, and a watchdog polls the dump dir and kills ffmpeg if its on-disk output crosses the cap -- restoring the hard bound the old pipe-per-attachment reader enforced by killing at maxBytes+1, so a container with oversized "font" attachments can't fill the disk. Part of the slow-endpoint follow-up; see slow-query-analysis/subtitle-fonts-extraction-findings.md. * fix(review): report enforced font-byte cap; correct doc subtitle scope Address PR #350 review: - dumpFontAttachments reported the maxSubtitleFontBytes package constant in both over-limit errors instead of the maxBytes argument the caller passed, so the message misstated the enforced bound whenever a different cap was in effect (as the tests use). Interpolate maxBytes in both messages. - The root-cause plan claimed subtitle extraction was 'out of scope' while the branch actually optimizes /subtitles/{track}/fonts. Scope the out-of-scope note to subtitle *track* conversion and record the fonts single-pass work as deliverable 5.
328 lines
9.7 KiB
Go
328 lines
9.7 KiB
Go
package playback
|
||
|
||
import (
|
||
"context"
|
||
"encoding/base64"
|
||
"encoding/json"
|
||
"errors"
|
||
"fmt"
|
||
"os"
|
||
"os/exec"
|
||
"path/filepath"
|
||
"strconv"
|
||
"strings"
|
||
"sync/atomic"
|
||
"time"
|
||
)
|
||
|
||
const (
|
||
maxSubtitleFontAttachments = 32
|
||
maxSubtitleFontBytes = 32 << 20 // 32 MiB
|
||
)
|
||
|
||
// SubtitleFontAttachment is a font attached to a media container for ASS/SSA
|
||
// subtitle rendering.
|
||
type SubtitleFontAttachment struct {
|
||
Name string
|
||
Data []byte
|
||
}
|
||
|
||
// SubtitleFontBundleItem is the JSON-safe representation sent to web players.
|
||
type SubtitleFontBundleItem struct {
|
||
Name string `json:"name"`
|
||
Data string `json:"data"`
|
||
}
|
||
|
||
type attachmentProbeOutput struct {
|
||
Streams []attachmentProbeStream `json:"streams"`
|
||
}
|
||
|
||
type attachmentProbeStream struct {
|
||
Index int `json:"index"`
|
||
CodecName string `json:"codec_name"`
|
||
CodecType string `json:"codec_type"`
|
||
Tags map[string]string `json:"tags"`
|
||
}
|
||
|
||
// ExtractAttachedSubtitleFonts extracts font attachments from a media file.
|
||
// Matroska ASS releases commonly include the exact fonts needed by the script;
|
||
// loading them into JASSUB is the closest browser equivalent to libass on a
|
||
// native player.
|
||
func ExtractAttachedSubtitleFonts(ctx context.Context, inputPath string, ffmpegPath string) ([]SubtitleFontAttachment, error) {
|
||
if strings.TrimSpace(inputPath) == "" {
|
||
return nil, fmt.Errorf("subtitle fonts: input path is required")
|
||
}
|
||
|
||
streams, err := probeFontAttachmentStreams(ctx, inputPath, ffprobePathFromFFmpeg(ffmpegPath))
|
||
if err != nil {
|
||
return nil, err
|
||
}
|
||
if len(streams) == 0 {
|
||
return nil, nil
|
||
}
|
||
if len(streams) > maxSubtitleFontAttachments {
|
||
streams = streams[:maxSubtitleFontAttachments]
|
||
}
|
||
|
||
bin := ffmpegPath
|
||
if strings.TrimSpace(bin) == "" {
|
||
bin = "ffmpeg"
|
||
}
|
||
|
||
return dumpFontAttachments(ctx, inputPath, bin, streams, maxSubtitleFontBytes)
|
||
}
|
||
|
||
// EncodeSubtitleFontBundle converts raw font attachments to base64 JSON items.
|
||
func EncodeSubtitleFontBundle(fonts []SubtitleFontAttachment) []SubtitleFontBundleItem {
|
||
items := make([]SubtitleFontBundleItem, 0, len(fonts))
|
||
for _, font := range fonts {
|
||
items = append(items, SubtitleFontBundleItem{
|
||
Name: font.Name,
|
||
Data: base64.StdEncoding.EncodeToString(font.Data),
|
||
})
|
||
}
|
||
return items
|
||
}
|
||
|
||
// dumpFontAttachments extracts every attachment in a single ffmpeg invocation.
|
||
//
|
||
// ffmpeg re-opens the (often network-backed) media file once per process, so
|
||
// the previous one-process-per-font approach paid that open cost N times and
|
||
// dominated latency — 17–60 s for anime releases carrying 15–47 fonts. Dumping
|
||
// all attachments in one pass opens the file once, cutting p95 from ~30 s to
|
||
// ~1–2 s. The `-map 0:t? -c copy` flags are required: without them ffmpeg
|
||
// decodes the whole video stream instead of stream-copying the attachments.
|
||
func dumpFontAttachments(ctx context.Context, inputPath string, ffmpegPath string, streams []attachmentProbeStream, maxBytes int64) ([]SubtitleFontAttachment, error) {
|
||
dir, err := os.MkdirTemp("", "silo-subfonts-*")
|
||
if err != nil {
|
||
return nil, fmt.Errorf("subtitle fonts: create temp dir: %w", err)
|
||
}
|
||
defer os.RemoveAll(dir)
|
||
|
||
// A fresh temp dir guarantees the dump targets don't pre-exist, so ffmpeg
|
||
// never prompts to overwrite (which would hang the process).
|
||
args := make([]string, 0, 4+len(streams)*2+8)
|
||
args = append(args, "-hide_banner", "-nostats", "-loglevel", "error")
|
||
paths := make([]string, len(streams))
|
||
for i, stream := range streams {
|
||
paths[i] = filepath.Join(dir, strconv.Itoa(i))
|
||
args = append(args, fmt.Sprintf("-dump_attachment:%d", stream.Index), paths[i])
|
||
}
|
||
args = append(args, "-i", inputPath, "-map", "0:t?", "-c", "copy", "-f", "null", "-")
|
||
|
||
cmd := exec.CommandContext(ctx, ffmpegPath, args...)
|
||
var stderr strings.Builder
|
||
cmd.Stderr = &stderr
|
||
if err := cmd.Start(); err != nil {
|
||
return nil, fmt.Errorf("subtitle fonts: start attachment extract: %w", err)
|
||
}
|
||
|
||
// ffmpeg writes each attachment to disk in full before we get a chance to
|
||
// read it, so unlike the old pipe-per-attachment reader (which killed at
|
||
// maxBytes+1) nothing here bounds what ffmpeg spills. Guard against a
|
||
// container whose oversized "font" attachments would otherwise fill the
|
||
// disk by killing the process once the dump dir crosses the cap.
|
||
overLimit, stopWatch := watchDumpSize(cmd, dir, maxBytes)
|
||
|
||
runErr := cmd.Wait()
|
||
stopWatch()
|
||
if overLimit.Load() {
|
||
return nil, fmt.Errorf("subtitle fonts: attached font data exceeds %d bytes", maxBytes)
|
||
}
|
||
if runErr != nil {
|
||
if ctx.Err() != nil {
|
||
return nil, ctx.Err()
|
||
}
|
||
return nil, fmt.Errorf("subtitle fonts: extract attachments: %w (stderr: %s)",
|
||
runErr, truncateStderr(stderr.String()))
|
||
}
|
||
|
||
var total int64
|
||
fonts := make([]SubtitleFontAttachment, 0, len(streams))
|
||
for i, stream := range streams {
|
||
fallbackName := fmt.Sprintf("attachment-%d%s", i, fontAttachmentExt(stream))
|
||
// Stat before reading so an over-limit attachment trips the cap
|
||
// without being pulled into memory.
|
||
info, err := os.Stat(paths[i])
|
||
if errors.Is(err, os.ErrNotExist) {
|
||
// ffmpeg silently skips attachments it can't stream-copy; treat
|
||
// a missing dump file as an absent font rather than a failure.
|
||
continue
|
||
}
|
||
if err != nil {
|
||
return nil, fmt.Errorf("subtitle fonts: stat attachment %q: %w", fallbackName, err)
|
||
}
|
||
total += info.Size()
|
||
if total > maxBytes {
|
||
return nil, fmt.Errorf("subtitle fonts: attached font data exceeds %d bytes", maxBytes)
|
||
}
|
||
data, err := os.ReadFile(paths[i])
|
||
if err != nil {
|
||
return nil, fmt.Errorf("subtitle fonts: read attachment %q: %w", fallbackName, err)
|
||
}
|
||
fonts = append(fonts, SubtitleFontAttachment{
|
||
Name: safeAttachmentDisplayName(stream, fallbackName),
|
||
Data: data,
|
||
})
|
||
}
|
||
|
||
return fonts, nil
|
||
}
|
||
|
||
// watchDumpSize polls the dump directory while ffmpeg runs and kills the
|
||
// process if the total bytes written exceed maxBytes, restoring the hard size
|
||
// bound the previous streaming reader enforced. It returns a flag set when the
|
||
// cap is tripped and a stop function the caller must invoke after cmd.Wait.
|
||
// The worst-case overshoot is one poll interval of ffmpeg writes, which is far
|
||
// smaller than the unbounded spill it replaces.
|
||
func watchDumpSize(cmd *exec.Cmd, dir string, maxBytes int64) (*atomic.Bool, func()) {
|
||
overLimit := &atomic.Bool{}
|
||
stop := make(chan struct{})
|
||
done := make(chan struct{})
|
||
go func() {
|
||
defer close(done)
|
||
ticker := time.NewTicker(100 * time.Millisecond)
|
||
defer ticker.Stop()
|
||
for {
|
||
select {
|
||
case <-stop:
|
||
return
|
||
case <-ticker.C:
|
||
if dirBytes(dir) > maxBytes {
|
||
overLimit.Store(true)
|
||
if cmd.Process != nil {
|
||
_ = cmd.Process.Kill()
|
||
}
|
||
return
|
||
}
|
||
}
|
||
}
|
||
}()
|
||
return overLimit, func() {
|
||
close(stop)
|
||
<-done
|
||
}
|
||
}
|
||
|
||
// dirBytes returns the total size of the regular files directly inside dir.
|
||
// Errors (e.g. a file removed mid-scan) are treated as zero so the watchdog
|
||
// never blocks extraction on a transient stat failure.
|
||
func dirBytes(dir string) int64 {
|
||
entries, err := os.ReadDir(dir)
|
||
if err != nil {
|
||
return 0
|
||
}
|
||
var total int64
|
||
for _, e := range entries {
|
||
info, err := e.Info()
|
||
if err != nil {
|
||
continue
|
||
}
|
||
total += info.Size()
|
||
}
|
||
return total
|
||
}
|
||
|
||
func probeFontAttachmentStreams(ctx context.Context, inputPath string, ffprobePath string) ([]attachmentProbeStream, error) {
|
||
bin := ffprobePath
|
||
if strings.TrimSpace(bin) == "" {
|
||
bin = "ffprobe"
|
||
}
|
||
cmd := exec.CommandContext(ctx, bin,
|
||
"-v", "error",
|
||
"-select_streams", "t",
|
||
"-show_entries", "stream=index,codec_name,codec_type:stream_tags=filename,mimetype",
|
||
"-of", "json",
|
||
inputPath,
|
||
)
|
||
out, err := cmd.Output()
|
||
if err != nil {
|
||
return nil, fmt.Errorf("subtitle fonts: probe attachments: %w", err)
|
||
}
|
||
|
||
var probed attachmentProbeOutput
|
||
if err := json.Unmarshal(out, &probed); err != nil {
|
||
return nil, fmt.Errorf("subtitle fonts: parse attachment probe: %w", err)
|
||
}
|
||
|
||
streams := make([]attachmentProbeStream, 0, len(probed.Streams))
|
||
for _, stream := range probed.Streams {
|
||
if isFontAttachment(stream) {
|
||
streams = append(streams, stream)
|
||
}
|
||
}
|
||
return streams, nil
|
||
}
|
||
|
||
func isFontAttachment(stream attachmentProbeStream) bool {
|
||
if strings.ToLower(stream.CodecType) != "attachment" {
|
||
return false
|
||
}
|
||
codec := strings.ToLower(stream.CodecName)
|
||
switch codec {
|
||
case "ttf", "otf", "ttc", "otc", "woff", "woff2":
|
||
return true
|
||
}
|
||
filename := strings.ToLower(stream.Tags["filename"])
|
||
switch filepath.Ext(filename) {
|
||
case ".ttf", ".otf", ".ttc", ".otc", ".woff", ".woff2":
|
||
return true
|
||
}
|
||
mimetype := strings.ToLower(stream.Tags["mimetype"])
|
||
return strings.Contains(mimetype, "font") ||
|
||
strings.Contains(mimetype, "truetype") ||
|
||
strings.Contains(mimetype, "opentype") ||
|
||
strings.Contains(mimetype, "woff")
|
||
}
|
||
|
||
func fontAttachmentExt(stream attachmentProbeStream) string {
|
||
if ext := strings.ToLower(filepath.Ext(stream.Tags["filename"])); isSupportedFontExt(ext) {
|
||
return ext
|
||
}
|
||
switch strings.ToLower(stream.CodecName) {
|
||
case "ttf":
|
||
return ".ttf"
|
||
case "otf":
|
||
return ".otf"
|
||
case "ttc":
|
||
return ".ttc"
|
||
case "otc":
|
||
return ".otc"
|
||
case "woff":
|
||
return ".woff"
|
||
case "woff2":
|
||
return ".woff2"
|
||
default:
|
||
return ".font"
|
||
}
|
||
}
|
||
|
||
func isSupportedFontExt(ext string) bool {
|
||
switch ext {
|
||
case ".ttf", ".otf", ".ttc", ".otc", ".woff", ".woff2":
|
||
return true
|
||
default:
|
||
return false
|
||
}
|
||
}
|
||
|
||
func safeAttachmentDisplayName(stream attachmentProbeStream, fallback string) string {
|
||
name := filepath.Base(stream.Tags["filename"])
|
||
if name == "." || name == string(filepath.Separator) || strings.TrimSpace(name) == "" {
|
||
return fallback
|
||
}
|
||
return name
|
||
}
|
||
|
||
func ffprobePathFromFFmpeg(ffmpegPath string) string {
|
||
ffmpegPath = strings.TrimSpace(ffmpegPath)
|
||
if ffmpegPath == "" {
|
||
return "ffprobe"
|
||
}
|
||
base := filepath.Base(ffmpegPath)
|
||
if i := strings.LastIndex(base, "ffmpeg"); i >= 0 {
|
||
return filepath.Join(filepath.Dir(ffmpegPath), base[:i]+"ffprobe"+base[i+len("ffmpeg"):])
|
||
}
|
||
return "ffprobe"
|
||
}
|