Files
silo-server/internal/playback/subtitle_fonts.go
CoffeeKnyteandGitHub a2ef26bece perf: root-cause fixes for endpoints still slow after #292 (NextUp, series badges, resume tail, subtitle fonts) (#350)
* 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.
2026-07-09 09:02:45 -04:00

328 lines
9.7 KiB
Go
Raw Permalink Blame History

This file contains ambiguous Unicode characters
This file contains Unicode characters that might be confused with other characters. If you think that this is intentional, you can safely ignore this warning. Use the Escape button to reveal them.
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 — 1760 s for anime releases carrying 1547 fonts. Dumping
// all attachments in one pass opens the file once, cutting p95 from ~30 s to
// ~12 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"
}