Ein paar neue Dinge, die ich beim Betrieb von SQLite gelernt habe

 (jvns.ca)
1 Punkte von GN⁺ 9 시간 전 | 1 Kommentare | Auf WhatsApp teilen
  • SQLite lässt sich auch in Produktionsumgebungen kleiner Websites einsetzen, aber mit den zunehmenden Aufgaben des Django-ORM wurde die betriebliche Komplexität der Datenbank spürbar
  • Eine FTS5-Suche in einer Tabelle mit 4.000 Zeilen dauerte 5 Sekunden, sank nach Ausführung von ANALYZE jedoch auf etwa 0,05 Sekunden, was zeigt, dass Statistiken des Query Planners die Performance stark beeinflussen können
  • Wenn ein umfangreiches DELETE länger als 5 Sekunden dauert, laufen auch andere Schreibvorgänge in ein Timeout und sogar Worker und VM können beendet werden, daher werden Bereinigungen in kleine Batches aufgeteilt
  • Für Backups wurden VACUUM INTO und restic verwendet, dabei kam es jedoch zu OOM-Abbrüchen; für effizientere inkrementelle Backups wird außerdem Litestream getestet
  • Tabellen lassen sich auf mehrere SQLite-Dateien aufteilen, und Mess with DNS läuft seit 2022 vier Jahre lang mit SQLite, was dort als passende Wahl für das Projekt bewertet wird

SQLite in einer Django-Site betreiben

  • Unter Verweis auf mehrere Artikel, die zeigen, dass SQLite auch für kleine Produktionssites geeignet sein kann, wurde es als Datenbank für eine Django-Site gewählt
  • Auch SQLite ist eine komplexe Datenbank, für die Betriebswissen nötig ist, und in diesem Projekt wird ihr über das Django-ORM mehr Arbeit übertragen als zuvor
  • Entsprechend mehreren Empfehlungen wurde von Anfang an der WAL-Modus aktiviert
  • Diese Site ist bereits die vierte Website, die SQLite verwendet

Wie ANALYZE die Such-Performance verändert hat

  • Eine Full-Text-Suchabfrage mit SQLite FTS5 auf einer Tabelle mit 4.000 Zeilen dauerte 5 Sekunden
  • Nach Ausführung von ANALYZE verkürzte sich dieselbe Abfrage auf etwa 0,05 Sekunden, sodass keine weitere Untersuchung mehr nötig schien
  • Welches Problem im Query Plan genau vorlag, konnte nicht festgestellt werden; vermutet wird eine Situation ähnlich versehentlicher quadratischer Laufzeit
  • ANALYZE erzeugt statistische Informationen wie etwa die Anzahl der Tabellenzeilen und hilft dem Query Planner, bessere Entscheidungen zu treffen
  • Wie man Query Plans liest, wurde bislang noch nicht gelernt

Datenbereinigung und die Beschränkung auf einen einzelnen Writer

  • Beim massenhaften Löschen unnötiger Zeilen, etwa abgeschlossener Jobs aus django-tasks-db, traten Kettenprobleme auf
    • Ein Bereinigungsbefehl, der viele Zeilen verarbeitet, lief länger als 5 Sekunden
    • Währenddessen überschritten Datenbankschreibvorgänge anderer Worker das konfigurierte 5-Sekunden-Limit und liefen in ein Timeout
    • Worker, deren Schreibvorgang fehlschlug, stürzten ab, und auch die VM wurde beendet
  • Der genaue Grund, warum DELETE langsam war, konnte nicht festgestellt werden; in Betracht gezogen wird auch, dass innerhalb der Transaktion viel Python-Code ausgeführt wird
  • Derzeit werden Bereinigungen in kleine Batches aufgeteilt, damit keine einzelne Datenbankabfrage länger als 5 Sekunden dauert
  • Diese Erfahrung machte deutlich, warum man für mehrere gleichzeitige Schreibvorgänge eine Datenbank wie Postgres braucht
  • Künftig wird auch erwogen, die Site für solche Arbeiten herunterzufahren und eine geplante Wartung durchzuführen, ein entsprechender Ablauf existiert aber noch nicht

ORM-Abfragen und aktueller Datenumfang

  • Bisher wurde beim Erstellen gewünschter Abfragen mit dem Django-ORM die Performance nicht gesondert überprüft, doch abgesehen vom ANALYZE-Problem funktionierte das meiste normal
  • Die Datenbank ist mit etwa 10.000 Zeilen klein und wird voraussichtlich auch künftig klein bleiben

SQLite-Backup-Methoden

  • Für SQLite-Backups wurden zwei Ansätze ausprobiert: restic und Litestream
  • Backup-Jobs werden normalerweise mit einem Dead Man’s Switch überwacht, ein tatsächlicher Restore-Test scheint aber noch nicht erfolgt zu sein

  • Backup mit restic

    • Mit VACUUM INTO wird eine Datenbankkopie erstellt, mit gzip komprimiert und dann nach S3 hochgeladen
    • Mit restic werden Backups erstellt, Snapshots geprüft sowie alte Backups gelöscht und bereinigt
    • Gelegentlich wurde das Backup wegen OOM beendet, wobei ein Lock zurückblieb, sodass auch restic unlock ausgeführt wurde
    sqlite3 /data/calendar.db "VACUUM INTO '/tmp/calendar.sqlite'"
gzip /tmp/calendar.sqlite
restic -r s3://s3.amazonaws.com/some_bucket/ unlock
restic -r s3://s3.amazonaws.com/some_bucket/ backup /tmp/calendar.sqlite.gz
restic -r s3://s3.amazonaws.com/some_bucket/ snapshots
restic -r s3://s3.amazonaws.com/some_bucket/ forget -l 1 -H 6 -d 2 -w 2 -m 2 -y 2
restic -r s3://s3.amazonaws.com/some_bucket/ prune

  • Inkrementelles Backup mit Litestream

    • Um OOM-Abbrüche bei restic-Backups zu vermeiden und die Effizienz inkrementeller Backups zu testen, wurde Litestream in Betrieb genommen
    • Nach dem Schreiben einer Konfigurationsdatei wird die Replikation mit folgendem Befehl gestartet
    litestream replicate -config litestream.yml
    • Um die Datenbankhistorie für einen gewissen Zeitraum zu behalten, wurde retention: 400h gesetzt, es ist aber noch unklar, ob das wie beabsichtigt funktioniert
    • Es wird auf AWS gesichert, aber da das Erstellen von Zugangsdaten in der Konsole umständlich ist, wird künftig auch ein Wechsel zu einem anderen S3-kompatiblen Dienst erwogen

Mehrere SQLite-Datenbankdateien verwenden

  • Das aktuelle Projekt verwendet nur eine Datenbank, bei Mess with DNS wurden jedoch Tabellen, die nicht zusammen liegen mussten, auf 3 Datenbankdateien aufgeteilt
  • Diese Aufteilung wird als hilfreich für den Betrieb bewertet
  • Mess with DNS läuft seit 2022 vier Jahre lang mit SQLite, und für dieses Projekt war der Wechsel von Postgres zu SQLite eine gute Entscheidung

Grundlegende Funktionen, die erst im Betrieb entdeckt wurden

  • SQLite wurde in Webprojekten erstmals 2022 eingesetzt, doch von der Existenz von ANALYZE wurde erst jetzt erfahren
  • Selbst grundlegende Funktionen der verwendeten Technik lernt man im realen Betrieb über Jahre hinweg weiter kennen

Verwandte Beiträge

1 Kommentare

 
GN⁺ 9 시간 전
Hacker-News-Kommentare

  • Mit dem .expert-Modus von SQLite kann man das Lernen, wie man Query-Pläne liest, noch etwas aufschieben: https://www.sqlite.org/cli.html#index_recommendations_sqlite...
    Wenn man SELECT * FROM x1 WHERE a=? AND b>?; analysiert, wird ein empfohlener Index wie CREATE INDEX x1_idx_000123a7 ON x1(a, b); ausgegeben. Analysiert man nach dem Erstellen erneut, wird angezeigt, dass kein neuer Index nötig ist.
    Auch in „echten“ Datenbanken wie Postgres wird empfohlen, Aufräumarbeiten normalerweise in kleine Batches aufzuteilen. Bei kleineren Systemen fällt nur weniger auf, dass es sich um eine ineffiziente Operation handelt, daher war der Ansatz im Original doch richtiger als gedacht.

    • Wenn man in manchen Datenbanken 10 Millionen Zeilen löscht, wird entsprechend viel Undo-Datenmaterial protokolliert. In Oracle kann dadurch der Plattenplatz für Archiv-Logs ausgehen, wenn diese nicht rechtzeitig gesichert und geleert werden.
      Häufiges Committen hilft, aber bei regelmäßigen Bereinigungen großer Datenbanken war Partitionierung am effektivsten. Das Löschen der ältesten Partition ist fast sofort erledigt.
      Unklar ist allerdings, warum bei „der Worker nicht in die Datenbank schreiben kann, in Konflikt gerät und die VM beendet wird“ die VM beendet wird. Mit VM ist hier offenbar die virtuelle Maschine gemeint, auf der das Betriebssystem läuft.
    • In großen MySQL-Umgebungen mit zeilenbasierter Replikation mussten UPDATE- oder DELETE-Operationen über Millionen von Zeilen in Batches ausgeführt werden. Bei einer einzelnen Query müssten sonst die aktualisierten Millionen Zeilen auf einmal an alle Replikate übertragen werden.
    • Sieht ähnlich aus wie EXPLAIN QUERY PLAN: https://sqlite.org/eqp.html
      Das normale EXPLAIN gibt meist unnötig ausführlichen Bytecode aus, während EXPLAIN QUERY PLAN einen zusammengefassten Plan zeigt.
    • Ich frage mich, ob es hier als Vorteil gesehen wird, dass „weniger sichtbar wird, dass es eine ineffiziente Operation ist“.

  • Aus Sicht von jemandem, der mit Datenbanken arbeitet, war das frustrierend zu lesen, und ich hätte die Ursache lieber selbst gefunden und behoben. Wenn eine Tabelle nur 10.000 Zeilen hat, sollte selbst ein Full Table Scan sehr schnell sein, erst recht bei SQLite, das im Prozess auf demselben physischen Server läuft.
    Die naheliegende Lösung war natürlich: „Erstell einen Index.“ Das langsame Löschen wirkt sehr nach dem klassischen N+1-Problem, das ORM-Nutzer oft erleben, bevor sie die Datenbankinteraktionen im Inneren verstehen. Hoffentlich schreibt Julia noch einen Folgebeitrag.

  • Weil das Erstellen von Zugangsdaten in der AWS-Konsole so umständlich war, habe ich vor ein paar Jahren ein Tool gebaut, das genau dieses Problem löst.
    uvx s3-credentials create my-existing-s3-bucket
    Dieser Befehl gibt Lese-/Schreib-Zugangsdaten aus, die nur auf diesen Bucket begrenzt sind. Mit --read-only oder --write-only kann man die Rechte weiter einschränken, oder mit --prefix foo/bar nur das Lesen und Schreiben von Schlüsseln erlauben, die mit einem bestimmten Präfix beginnen.
    Ich habe es auch mit Restic und Cloudflare R2 verwendet, und es hat sehr gut funktioniert.

    • Ein allgemeinerer Weg für komplexe AWS-Services ist, ein wenig Terraform zu lernen und den Rest dem LLM zu überlassen. Man muss sich nicht merken, welche Ressourcen erstellt wurden, und späteres Aufräumen ist deutlich einfacher.
    • Als ähnliches bestehendes Tool gibt es auch https://litestream.io/.
    • Sehr gutes Tool, das eigentlich ins AWS CLI übernommen werden sollte. Ich frage mich nur, wann man Write-only-Zugangsdaten wirklich braucht.

  • Im Zeitalter der LLMs schätze ich Julias Text noch mehr. Dieser ehrliche Erkundungsprozess ist ein Gegenmittel zu automatisch erzeugten Texten, die selbstsicher so tun, als wüssten sie alles.

  • Ich führe Backups so aus:
    OUT="${i}.sql.zst"
    PART="${OUT}.part"
    sqlite3 -readonly "${i}" .dump | zstd --fast --rsyncable -v -o "${PART}" -
    mv "${PART}" "${OUT}"
    Wenn der Schreib-Worker WAL verwendet, blockiert das ihn nicht, und es entsteht ein Dump, der sich gut komprimieren lässt und zugleich leicht zu synchronisieren ist. Die Home-Assistant-Datenbank ist 1,8 GB groß, aber der komprimierte Dump ist 286 MB, und schätzungsweise sind etwa 90 % des täglich erzeugten Inhalts identisch.

    • Ich frage mich, was bei Home Assistant gesichert wird. Das Standard-Backup war mir zu groß, deshalb habe ich nur die Konfiguration behalten und Videos, Cache sowie von HACS heruntergeladene Repositories ausgeschlossen. Vielleicht übersehe ich aber etwas Wichtiges.
      Ich frage mich auch, ob die Datenbank wegen langfristig aufbewahrter Zeitreihenaufzeichnungen so groß geworden ist.
    • VACUUM INTO, .backup, sqlite3_rsync und Litestream blockieren den Schreib-Worker ebenfalls nicht. .backup verwendet die Backup-API.
    • Bei einer Live-Datenbank hatte ich wegen .dump einmal einen Lock und bin danach auf .backup umgestiegen. Trotzdem ist das Verfahren, erst nach .part zu schreiben und dann mv zu verwenden, elegant.

  • Ich halte den Beitrag für inhaltlich wenig gehaltvoll, weil er fast nur Formulierungen wie „ich habe nicht weiter recherchiert“, „das ist meine beste Vermutung“, „vielleicht auch andere Dinge?“ oder „möglicherweise wird innerhalb einer Transaktion viel Python-Code ausgeführt“ enthält. Es wird nicht gründlich nachgeprüft oder recherchiert, teils werden sogar falsche Vermutungen angestellt.
    Als Debian-Nutzer öffne ich Ubuntu-Foren in Linux-Suchergebnissen oft gar nicht erst, weil dort zu viele falsche Vermutungen auftauchen. Umgekehrt ist Arch zwar deutlich anders als Debian, aber das Arch Wiki, geschrieben von kompetenten Leuten, schaue ich meist trotzdem nach.

    • Julia Evans verfügt über sehr viel technisches Wissen und ist besonders gut darin, den geheimnisvollen Nimbus von Technik zu entfernen und verständlich zu machen, wie echte Problemlösung abläuft.
      Dieser Beitrag gibt sich nicht als Erklärung der weltbesten SQLite-Expertin aus, sondern setzt schon im Titel klare Erwartungen: „Ein paar Dinge, die ich beim Betreiben von SQLite gelernt habe“.
      Durch Julias Texte zieht sich die Botschaft, dass man nicht alles wissen oder so tun muss, als wüsste man alles, sondern sich auch mit einfachen Methoden ein Bild vom Problem machen und dabei Wissen aufbauen kann. Das, was man bisher herausgefunden hat, möglichst klar zu teilen, ist eine gute Haltung.
    • Ich finde den Beitrag hervorragend, weil er die Lage eines klugen normalen Nutzers dieser Technik gut zeigt. Der Fokus der Autorin liegt auf dem Betreiben einer Website, und genau an solchen Problemen bleiben alltägliche Nutzer oft hängen, die nicht den ganzen Tag nur mit einem bestimmten Tool arbeiten.
      Ich selbst habe gestern beim Umsetzen eines Features zwei Programmiersprachen, zwei Build-Systeme, einen Cloud-Anbieter, ein Secret-Management-Tool, ein komplexes Client-Server-Kommunikations-Framework über zwei Sprachen hinweg, ein Versionsverwaltungssystem, einen Editor und ein CI-Tool benutzt. Dazu könnte man noch tiefer in Betriebssystem und Runtime-Versionen, Datenbank, Reverse Proxy, Cache und Domänenlogik eintauchen.
      Wenn man jedes Thema, dem man begegnet, bis in die Tiefe verfolgt, bekommt man am Ende nichts fertig; deshalb muss man wie die Autorin auswählen, auf welches Problem man sich konzentriert.
    • Das Arch Wiki ist eine der besten Linux-Quellen überhaupt. Als ich Mint benutzt habe, habe ich ständig dort nachgeschlagen, aber nachdem ich tatsächlich auf Arch umgestiegen bin, tue ich das seltener als noch zu Mint-Zeiten.

  • Das DELETE-Problem lässt sich leicht entschärfen durch Löschen in Batches, Pausen zwischen den Batches und dadurch, dass man die rowid vorher per SELECT lädt. SELECT verursacht keine Blockierung.
    Wenn Daten nacheinander in dieselbe Tabelle eingefügt wurden, ist es wahrscheinlich, dass sie auch in ähnlicher Reihenfolge in der Datei gespeichert sind; dann kann das Löschen in dieser Reihenfolge oder umgekehrt schneller sein. Je nach Speichermedium und anderen Bedingungen kann das Ergebnis variieren.

    • Vorabladen von Zeilen-IDs war nicht nur bei SQLite, sondern auch in großen Aurora-MySQL- oder Postgres-Clustern sehr effektiv. Man konnte das SELECT an Replikate schicken, und der durch Zeilenfilterung verursachte Druck auf den Indexspeicher belastete CPU und Buffer-Cache der Datenbank stark.
      Wenn sich unnötige Daten nicht durch so etwas wie Partition Pruning aus dem zentralen Verarbeitungspfad fernhalten lassen, ist das eine sehr starke Strategie.

  • Auch heute ist es eine gute Methode, die eigenen Fähigkeiten zu verbessern, sich etwas tiefer mit Datenbanken zu beschäftigen, als es der aktuelle Kenntnisstand oder die Arbeitsanforderungen verlangen. Viele Webentwickler geraten bei Datenbank-Tools ins Stocken; bei manchen Betriebstechniken wie K8s geht es mir ähnlich.
    Wenn man versteht, wie SQL in konkrete Lese- und Schreibvorgänge auf dem Datenträger übersetzt wird, hilft das enorm dabei, intuitiv einzuschätzen, welcher Ansatz sinnvoll sein könnte. Man sollte auch das Locking-Modell der Datenbank verstehen.
    Mit diesem Wissen gerät man auch weniger in Panik, wenn in Postgres selbst ein einfaches COUNT nicht schnell fertig wird.

  • In sqlite_stat1 und sqlite_stat4 werden verschiedene statistische Informationen über die Verteilung von Indexwerten gespeichert; der Query Planner schätzt auf dieser Grundlage Selektivität und Nutzen eines Indexes.
    sqlite_stat1 liefert nur die Anzahl der Datensätze eines Indexes und die durchschnittliche Zahl von Datensätzen pro Wert, während sqlite_stat4 bei Aktivierung auch Histogrammdaten speichert.

  • Litestream ist sehr interessant, und ich habe es erfolgreich mit S3 als Backend betrieben. Damit kann man viele Apps, die SQLite verwenden, fast vollständig von Dateisystemzuständen entkoppeln.
    Der Zustand von S3 lässt sich viel einfacher verwalten, und Backups sowie Synchronisierung übernimmt der Anbieter.