lobste.rs wechselt von MariaDB zu SQLite

 (lobste.rs)
1 Punkte von GN⁺ 3 시간 전 | 1 Kommentare | Auf WhatsApp teilen
  • lobste.rs hat nach dem Wechsel von MariaDB zu SQLite auch den montäglichen Traffic-Anstieg stabil bewältigt und dabei geringere CPU- und Speicherauslastung sowie eine spürbar schnellere Reaktionsfähigkeit festgestellt
  • Wird der separate MariaDB-VPS abgeschaltet, halbieren sich die VPS-Kosten, und das seit 2019 laufende Migrationsprojekt ist abgeschlossen
  • Beim ersten Deployment erreichten allein schreibgeschützte Zugriffe sofort 100 % CPU-Auslastung, doch nach der Behebung von zwei Full Table Scans in großen Tabellen und einem N+1-Problem gelang das erneute Deployment
  • Mithilfe benutzerdefinierter SQLite-Funktionen wurden regexp, if und stddev implementiert; zugleich mussten Kompatibilitätseinschränkungen wie fehlendes unsigned bigint und das nur ASCII verarbeitende NOCASE gelöst werden
  • Um die zugrunde liegende Datenbank ohne Zugriff auf die Produktionsdatenbank auszutauschen, braucht es Testdaten in realistischer Größe, eine Test-Suite, Deployment-/Rollback-Checklisten und enge Zusammenarbeit

Von der Diskussion 2019 bis zur Umstellung auf SQLite

  • Die Diskussion über die Migration begann 2019 mit Issue #539
    • Damals wurde MySQL als Alternative vorgeschlagen, um die Kompatibilität mit MariaDB zu berücksichtigen, während bereits eine Umstellung auf PostgreSQL diskutiert wurde
    • 2025 erwähnte Rahul die Übernahme von MariaDB durch K1, woraufhin die Details einer PostgreSQL-Migration weiter besprochen wurden
    • Im Februar fragte Rahul, ob lobsters auf SQLite laufen könnte, und ab Juni 2025 nahm die Mitarbeit am Projekt konkret Fahrt auf
  • Der erste PR vom August 2025 wurde nach einem Arbeitsstopp von GitHub als veralteter PR geschlossen und konnte nicht erneut geöffnet werden
    • Der zweite PR umfasste Performance-Tests, ein eigenes Migrationsskript zwischen Datenbanken und Debugging zur Datenintegrität
    • Da keines der bestehenden Konvertierungsskripte für MariaDB/MySQL→SQLite die Anforderungen erfüllte, wurde ein separates Skript geschrieben

Fehlgeschlagenes erstes Deployment und Behebung der Performance-Engpässe

  • Das erste Deployment am 21. Februar wurde mit einer vorbereiteten Deployment-Checkliste durchgeführt, doch direkt danach brachte schon der schreibgeschützte Traffic alle CPUs auf 100 %
    • Da die Ursache nicht gefunden wurde, erfolgte ein Rollback
    • Ohne Zugriff auf die Produktionsdatenbank war es schwierig, echte Performance-Probleme im Vorfeld nachzustellen
  • Zwei Tage nach dem Fehlschlag wurde der dritte und letzte PR eröffnet, Suchprobleme wurden behoben und ein Skript zur Erzeugung großer Datenmengen hinzugefügt, das lokal einen Datensatz in halber Größe der echten lobsters-Daten erzeugt
    • Die Datenerzeugung dauerte eine Woche
    • Der Engpass beim ersten Deployment bestand aus Full Table Scans durch zwei Queries auf großen Tabellen sowie einem separaten N+1-Problem
    • Dafür wurden Änderung 1, Änderung 2 und Änderung 3 umgesetzt
    • Um weitere Performance-Probleme verfolgen zu können, wurde am Morgen des Deployment-Tages zusätzlich ein Slow Query Log ergänzt

Ergebnis des erneuten Deployments und niedrigere Betriebskosten

  • Beim zweiten Deployment am 11. Juli wurden erneut Deployment- und Rollback-Checklisten vorbereitet; auch nach der Umstellung lief die Website normal weiter, und CPU- sowie Speichernutzung blieben stabil
    • Während Site-Metriken und IRC überwacht wurden, konnten gemeldete Probleme mit Fix 1 und Fix 2 sofort behoben werden
    • Auch beim Traffic-Anstieg am Montag gab es keine Probleme; bestätigt wurden geringere CPU- und Speicherauslastung sowie eine spürbar schnellere Reaktionsfähigkeit
    • Mit der Abschaltung des MariaDB-VPS halbieren sich die VPS-Kosten, und das Migrationsticket kann geschlossen werden

Einschränkungen von SQLite im Vergleich zu MariaDB

  • Über die Unterstützung für benutzerdefinierte Funktionen (UDF) im SQLite-Gem wurden die in SQLite fehlenden Funktionen regexp, if, stddev implementiert
    • Dadurch waren keine übermäßigen Workarounds für SQL-Migrationen nötig
  • SQLite unterstützt das für einige IDs in MariaDB verwendete unsigned bigint nicht, daher wurde auf bigint umgestellt
  • Auch die Sortierregeln sind eingeschränkter als bei MariaDB
    • In MariaDB wurde utf8mb4_general_ci verwendet, in SQLite dagegen NOCASE
    • NOCASE unterstützt kein vollständiges UTF-Case-Folding und verarbeitet nur ASCII-Zeichen
  • Für die SQLite-Full-Text-Search-Tabellen eignet sich statt der Voreinstellung die empfohlene Variante Contentless-Delete Tables
  • Die standardmäßigen PRAGMA-Einstellungen von Rails funktionieren bei lobsters korrekt
    • Da sich Datenbankmigrationen je nach DB unterscheiden, wurden bestehende Migrationen in das Verzeichnis old_migrations verschoben, sodass db:migrate weiter funktioniert

Erkenntnisse aus Tests und Zusammenarbeit

  • Im lobsters-Codebase fanden sich der Search Parser und heinous_inline_partials, ein Hack zur Beschleunigung des Renderings
    • Dank der Test-Suite ließ sich die Korrektheit der SQLite-Umstellung ohne umfangreiche manuelle Tests verifizieren
  • Reibungslose Kommunikation zwischen den Beteiligten war entscheidend für den Erfolg der Migration
    • Ohne Zugriff auf die Produktionsdatenbank ist es sehr schwierig, die zugrunde liegende Datenbank auszutauschen
    • Würde man die gleiche Aufgabe noch einmal angehen, sollte man vorab realistische Datenmengen vorbereiten
  • Hätte man Tests so konfigurieren können, dass bei Full Table Scans ein Fehler ausgelöst wird, wären die Performance-Probleme des ersten Deployments früher aufgefallen
  • Es werden Werkzeuge benötigt, mit denen sich produktionsnahe Datensätze leicht erzeugen lassen, ohne sie manuell zu schreiben oder jeweils eine Woche darauf zu warten

Verwandte Beiträge

1 Kommentare

 
GN⁺ 3 시간 전
Meinungen auf Lobste.rs

  • @thomas0 hat die große Migration sorgfältig übernommen, @355E3B hat bei Planung und Betrieb geholfen, und dank der Mitwirkenden rund um #539 sowie #lobsters ließ sich auch die Nur-Lese-Zeit gut überbrücken.
    Ursprünglich war in #539 PostgreSQL geplant, aber die Person, die die eigentliche Arbeit unterstützte, nutzte SQLite. Man wollte eine Lösung vermeiden, die größer und komplexer ist als der erwartete Bedarf, und entschied sich daher für SQLite. PostgreSQL ist normalerweise die Standardwahl, bringt aber den Aufwand mit sich, einen separaten Dienst zu betreiben, abzustimmen und zu warten.
    Überraschenderweise sind nach der Migration sowohl CPU- als auch RAM-Nutzung gesunken; detaillierte Diagramme finden sich am Ende von #539.

  • Jedes Mal, wenn ich die Standardeinstellungen von SQLite sehe, bin ich überrascht. Es wäre gut, einen Modus für neue Projekte zu haben, der WAL, Fremdschlüssel, synchronous=NORMAL usw. standardmäßig bereitstellt; vermutlich gäbe es darüber hinaus noch weitere Default-Werte, die verbessert werden könnten.

    • Ich meine mich vage zu erinnern, dass es auch Stellen gab, an denen die Planung oder das Format von Schreibvorgängen auf der Festplatte für HDDs vorteilhaft, für NVMe aber ziemlich ungeeignet war.

  • Ich frage mich, welche Backup-Strategie für die SQLite-Datenbank genutzt wird. Mich würde interessieren, ob ein Tool wie litestream verwendet wird oder ein anderer Ansatz.

  • Ich frage mich, wie lobste.rs Update-Vorgänge handhabt.

    • Es wird das update-Statement von SQLite verwendet. Falls eine andere Art von Update gemeint war, bitte genauer erklären.

  • SQLite hat bereits die iif-Funktion, aber es ist auch interessant, in diesem Bereich überhaupt benutzerdefinierte Funktionen zu betreiben.

  • Ich dachte, SQLite unterstütze Nebenläufigkeit nicht auf dem Niveau von PostgreSQL oder MySQL. Wenn ich die Übersichtsdokumentation lese: Kann man es so verstehen, dass es inzwischen von mehreren Prozessen genutzt werden kann, lobste.rs aber auf einem einzelnen Server laufen muss?
    Mich würde auch interessieren, ob sich die Prozesse innerhalb von SQLite über Shared Memory oder etwas wie mmap synchronisieren, sodass immer nur ein Schreibvorgang gleichzeitig möglich ist, aber mehrere Lesevorgänge erlaubt sind, und ob wartende Schreibvorgänge in einer Queue der Reihe nach warten.

    • Die SQLite-FAQ kann einen Teil dieser Fragen beantworten. Über mehrere Prozesse hinweg sind mehrere Lesevorgänge möglich, aber Schreibvorgänge nur einer nach dem anderen; SQLite stellt das über Dateisystem-Locks sicher.
      Für wartende Schreibvorgänge scheint keine explizite Shared-Memory-Queue verwendet zu werden, sondern ein kurzes Warten mit anschließendem erneuten Versuch. Ob SQLite über mehrere Maschinen hinweg völlig unmöglich ist, ist nicht sicher, aber der einfache Ansatz, die Datei per NFS zu teilen, wird ausdrücklich nicht empfohlen. Daher ist ein einzelner Server eine vernünftige Annäherung, und heutzutage kann auch eine einzelne Maschine eine beträchtliche Last bewältigen.
    • Schreibvorgänge werden zwar bis zu einem gewissen Grad warten gelassen, aber die Standardeinstellungen sind nicht gut und müssen angepasst werden. Die derzeit verwendeten Einstellungen sehen so aus; außerdem muss der Immediate-Transaction-Modus aktiviert werden, damit busy_timeout funktioniert. 
      "PRAGMA foreign_keys=ON;"  
"PRAGMA journal_mode = WAL;"  
"PRAGMA synchronous = NORMAL;"  
"PRAGMA busy_timeout = 5000;"  
"PRAGMA temp_store = MEMORY;"  
"PRAGMA mmap_size = 134217728;"  
"PRAGMA journal_size_limit = 67108864;"  
"PRAGMA cache_size = 2000;"
      Als Referenzen dienen https://fractaledmind.github.io/2024/04/… sowie https://kerkour.com/sqlite-for-servers und https://www.sqlite.org/lang_transaction.html#immediate; nähere Details finden sich im zweiten Link im Abschnitt zu SQLITE_BUSY und im dritten Link.

  • Das ist ein beeindruckendes Upgrade, bei dem PR, Issue und Gist die Details sehr gut dokumentieren. Ich frage mich, ob diese Änderung funktionale Unterschiede etwa bei der Seitensuche mit sich gebracht hat und wie groß die aktuelle SQLite-Datei auf der Festplatte ist.

    • Für die Suchergebnisse wird die Ranking-Funktion von SQLite verwendet, daher kann sich die Ergebnisreihenfolge von der bei MariaDB unterscheiden. Es gibt zwar Suchtests, aber nur wenige Prüfungen des Rankings. Funktionale Änderungen sind also möglich, es gibt dafür aber bislang keine Belege.
      Ich meine, irgendwo gelesen zu haben, dass die SQLite-Datei 3,8 GB groß ist.