Vue Activité: Sofortige Anzeige bei großen Datenbanken (PR #2647)

Entwicklung
1

Nach dem Release von 4.82 und den Diskussionen mit @pierre-gilles über die Leistung der neuen Aktivitätsansicht auf etwas größeren Installationen (zumindest wie meiner), hier der Bericht zur PR #2647, getestet auf meiner Datenbank mit 448 Millionen Zuständen.

Das Problem

Bei einer großen Datenbank erzwingt das Filtern der Aktivität nach einer sparsamen Kategorie (z. B. „Öffnungen“: wenige, alte Zustände), dass der Server einen großen Teil des Historienscans durchführen muss, bevor er antwortet: Der Server antwortet erst, wenn seine Seite mit 80 Zuständen gefüllt ist, unabhängig von der zu durchsuchenden Tiefe. Ergebnis: Ein eingefrorener Spinner für 20 bis 50 Sekunden, manchmal 3 Minuten.

Wir haben alle „Server-only“-Lösungen systematisch gemessen (progressive Fenster, Anker auf der letzten Aktivität — danke @pierre-gilles für die #2642 —, disjunkte Abschnitte, physische Kompaktierung der Tabelle): Jede verbessert, aber keine beseitigt die Mauer — die Mindestkosten sind die Scanrate von DuckDB multipliziert mit dem zu durchsuchenden Volumen.

Die Lösung: Dem Client die Steuerung der Suche überlassen

  • Server: getDeviceStatesHistory akzeptiert eine Grenze since → eine Abfrage = ein zeitlich begrenztes Fenster, das zurückgibt, was es enthält (auch wenn es weniger als 80 sind) und sich nie erweitert. Ein begrenztes Fenster antwortet in Millisekunden dank der DuckDB-Zonenkarten. Ohne since bleibt das Verhalten streng unverändert (rückwärtskompatibel).
  • Frontend: Die Aktivitätsansicht sondiert 1 → 2 → 4 → 8 → 16 → 32 Monate, dann eine letzte unbegrenzte Abfrage, zeigt jeden Satz sofort an, sobald er eintrifft, mit einem Banner „Aktivitäten suchen — März 2025…“ während ältere Fenster im Hintergrund sondiert werden.

Die Messungen (448 M Zustände)

Fall Vorher Nachher
Filter „Öffnungen“ (wenige, alte Zustände) 20 bis 33 s eingefrorener Spinner Erste Zustände in ~100 ms, die Seite füllt sich im Hintergrund
Filter „Knöpfe“ (Zustände nach 17 Monaten) 22 bis 51 s Sofortiges Banner, das den gescannten Monat anzeigt, Zustände, sobald sie gefunden werden
Ansicht „Alles“ / Live / redselige Sensoren ~100 ms Unverändert (~100 ms)

Die Gesamtarbeit im schlimmsten Fall ändert sich nicht — aber sie wird hinter einer bereits nutzbaren Seite durchgeführt, anstatt die erste Anzeige zu blockieren. Das ist das Prinzip des Home Assistant Logbooks, angepasst an die Gladys-API.

image
image1111×499 25.5 KB

image
image1814×755 83 KB

Die PR ist bereit für die Überprüfung. Sie betrifft weder das Datenmodell noch die Aggregationen — es ist reine Aufteilung der Abfragen.

2

Hallo @Terdious, danke für diese Analyse! :folded_hands:

Ich bin trotzdem überrascht, dass DuckDB diesen Fall noch nicht richtig behandelt. Es ist schon verrückt, 30 Sekunden warten zu müssen, nur für ein SELECT [...] ORDER BY created_at DESC, wo es doch gerade eine der großen Versprechen von DuckDB war.

Meiner Meinung nach nutzen wir es nicht richtig. Entweder fehlt uns ein Index (mit dem Hinweis, dass komplexe Multi-Spalten-Index schnell viel Speicherplatz kosten können), oder unsere Abfrage ist nicht optimal. :slightly_smiling_face:

3

Hallo @pierre-gilles,

Wir hatten genau die gleiche Frage und haben beide Hypothesen (Abfrage und Struktur) getestet, bevor wir den PR geschrieben haben. Du kannst dir denken, dass ich, um dir bestmöglich zu antworten, meine Punkte an die KI gestellt habe, basierend auf den Tests und Überlegungen, die wir während der Entwicklung hatten. Ich hoffe, es wird klarer sein als alles, was ich selbst hätte schreiben können ^^ :

Kurze Antwort: Die Abfrage ist nicht „einfach“ ein ORDER BY, und der Index, der sie retten würde, existiert in DuckDB nicht — aus Designgründen.

1. Die echte Abfrage. Es ist nicht nur SELECT … ORDER BY created_at DESC LIMIT 80 — das macht DuckDB sehr gut (Top-N vektorisiert). Es ist WHERE device_feature_id IN (die Features der Kategorie) ORDER BY created_at DESC LIMIT 80. Bei einer seltenen Kategorie sind die 80 passenden Zeilen weit in der Vergangenheit verstreut — und ohne sekundären Index hat der Motor keine Möglichkeit zu wissen, wo sie sind: Er scannt, bis er sie findet. Schlechtester Fall = die gesamte Tabelle.

2. Warum kein Index? Der einzige Index von DuckDB ist der ART, der für Primärschlüssel und Punkt-Lookups entwickelt wurde: Er wird nicht zum Sortieren verwendet (kein geordneter Durchlauf wie bei B-Trees), die Dokumentation rät davon ab, darüber hinaus welche zu erstellen, und bei 448 M Zeilen würde er zusätzlich zu jedem unserer Dutzend Schreibvorgänge pro Sekunde Gigabyte kosten. Der „echte“ Index von DuckDB sind die Zonemaps (Min/Max pro Block von ~120 k Zeilen) — effektiv nur, wenn der Filter mit der physischen Reihenfolge der Daten korreliert.

3. „Strukturieren wir unsere Daten falsch?“ — auch getestet, das hat etwa 30 Minuten gedauert. Ich habe die gesamte Tabelle in ORDER BY created_at auf meiner Kopie umgeschrieben (das optimale Layout für zeitliche Zonemaps): Die begrenzten Fenster gehen auf einige Millisekunden :white_check_mark:… aber die seltene Kategorie gewinnt nur das Doppelte (33 s → 15 s). Das Problem ist nicht die Anordnung: Man muss trotzdem ein Jahr dichter Daten lesen, um 80 Zustände einer Kategorie zu finden, die fast keine produziert. Und das Clusterisieren nach Features würde stattdessen die Ansicht „Alles“ brechen und sich kontinuierlich auflösen (die Live-Inserts kommen in zeitlicher Reihenfolge an). Dein Anker last_value_changed (#2642) und eine Variante mit disjunkten Scheiben wurden ebenfalls gemessen: gleiche Größenordnungen.

4. Die Stärke von DuckDB liegt woanders — und sie wird gehalten. Spalte + OLAP = ultra-schnelle massive Scans und Aggregationen (unsere Energie-Graphen über Millionen von Zeilen!), bei gleichzeitiger Leichtigkeit der DB, im Austausch für den Verzicht auf sekundäre Indizes. „Die letzten 80 Zustände einer Entität“ ist die OLTP-Abfrage par excellence — genau deshalb bedient Home Assistant sie mit einem zusammengesetzten Index (Entität, Zeit) auf einem Row-Store, indem es den Preis zahlt, den es kostet: mehr Speicherplatz und schwerere Schreibvorgänge. Gleiche Abwägung, andere Seite.

Daher die Wahl der #2647: begrenzte Zeitfenster (wo DuckDB glänzt, einige Millisekunden pro Abfrage), gesteuert vom Client, der schrittweise anzeigt — der schlechteste Fall blockiert nie wieder die erste Darstellung. Wenn wir eines Tages auch den gesamten Kostenaufwand des schlechtesten Falls eliminieren wollen, wäre der „saubere“ Weg eine Mini-Index-Tabelle (1 Zeile pro Feature und pro Monat), die zuerst abgefragt wird — aber das ist eine Ergänzung zum Datenmodell.