Wie man SQLite-Datenbanken schneller zwischen Computern kopiert

 (alexwlchan.net)
1 Punkte von GN⁺ 2025-05-02 | 1 Kommentare | Auf WhatsApp teilen
  • Wenn eine große SQLite-Datenbank von einem Remote-Server lokal übertragen wird, lässt sich das Übertragungsvolumen deutlich reduzieren, wenn man statt der ursprünglichen .db-Datei einen gzip-komprimierten SQL-Dump kopiert
  • Der Hauptgrund für die Dateigröße sind Indizes: Sie speichern Tabellendaten doppelt, um Abfragen zu beschleunigen, und erhöhen dadurch den Speicherbedarf
  • Der .dump-Befehl von SQLite wandelt die Datenbank in SQL-Text um, und Indizes werden nicht als echte Daten, sondern nur als einzelne CREATE INDEX-Zeile dargestellt, wodurch doppelte Übertragung vermieden wird
  • In der Beispieldatenbank war das Original 3,4 GB, der Text-Dump 1,3 GB und die gzip-komprimierte Version 240 MB; der komprimierte Dump war damit 14-mal kleiner als das Original
  • Wenn man die Dump-Datei vor dem Kopieren festschreibt, sinkt das Risiko, dass sich die Quelldaten während der Übertragung ändern und der Fehler database disk image is malformed auftritt

SQLite-Dumps komprimiert kopieren

  • Kleine SQLite-Datenbanken lassen sich problemlos direkt mit rsync kopieren
    • Beispiel: rsync --progress username@server:my_remote_database.db my_local_database.db
  • Wenn die Datenbank größer wird, kann das direkte Kopieren langsam und unzuverlässig werden
    • Das Herunterladen einer 250-MB-Datenbank von einem Webserver über eine Heim-Internetverbindung dauert etwa 1 Minute
    • Bei Datenbanken mit mehreren GB steigt die Wartezeit entsprechend weiter
  • Der Flaschenhals beim Größenwachstum sind Indizes
    • Indizes erhöhen die Abfragegeschwindigkeit stark, vergrößern aber auch die Datenbankdatei
    • In einer Datenbank belegte ein bestimmter Index allein die Hälfte des Speicherplatzes auf der Festplatte
    • Indizes enthalten meist keine eigenen Daten, sondern duplizieren Daten aus anderen Tabellen, um schnellere Abfragen zu ermöglichen
  • SQLite kann eine komplette Datenbank als Textdatei dumpen
    • Befehl: sqlite3 my_database.db .dump > my_database.db.txt
    • Die Ergebnisdatei besteht aus SQL-Anweisungen wie CREATE TABLE, INSERT INTO und CREATE INDEX
    • Indizes werden nicht als eigentliche Indexdaten gespeichert, sondern als Erstellungsanweisung wie CREATE INDEX [idx_photo_locations] ON [photos] ([longitude], [latitude]);
  • Eine Dump-Datei kann wieder in eine SQLite-Datenbank zurückgespielt werden
    • Befehl: cat my_database.db.txt | sqlite3 my_reconstructed_database.db
  • SQL-Dumps enthalten viele Wiederholungen und lassen sich daher mit gzip sehr effizient komprimieren
    • Beispiel: sqlite3 explorer.db .dump | gzip -c > explorer.db.txt.gz
    • Größenvergleich:
      • Ursprüngliche SQLite-Datenbank: 3,4 GB
      • Text-Dump: 1,3 GB
      • gzip-komprimierter Text: 240 MB
    • Die gzip-komprimierte Version war 14-mal kleiner als die ursprüngliche SQLite-Datenbank

Praktisches Vorgehen und Stabilitätsvorteile

  • Der neue Ansatz erstellt auf dem Remote-Server einen komprimierten Dump, lädt nur diese Datei herunter und rekonstruiert die Datenbank anschließend lokal
  • Der Kopiervorgang sieht so aus
    • gzip-komprimierte Textdatei auf dem Server erzeugen: ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c > my_remote_database.db.txt.gz"
    • Lokal kopieren: rsync --progress username@server:my_remote_database.db.txt.gz my_local_database.db.txt.gz
    • Temporäre komprimierte Datei auf dem Server löschen: ssh username@server "rm my_remote_database.db.txt.gz"
    • Lokal entpacken: gunzip my_local_database.db.txt.gz
    • Lokale Datenbank rekonstruieren: cat my_local_database.db.txt | sqlite3 my_local_database.db
    • Lokale Textdatei löschen: rm my_local_database.db.txt
  • Der komprimierte Dump dient als konsistente Kopierquelle
    • Wenn sich die Quelldaten während einer langen Übertragung einer großen Datenbank ändern, kann der Anfang der Ergebnisdatei noch den alten und das Ende bereits den neuen Zustand enthalten
    • Eine solche Datei kann beim lokalen Öffnen den Fehler database disk image is malformed auslösen
  • Wenn vor dem Kopieren ein Text-Dump erstellt wird, ändert sich die von rsync übertragene Datei nicht während der Übertragung
    • Dadurch steigt die Wahrscheinlichkeit, eine vollständige und konsistente Textdatei zu kopieren
  • Wer große SQLite-Datenbanken häufig verschiebt, kann mit dieser Methode die Download-Zeit verkürzen und die Zuverlässigkeit des Kopiervorgangs erhöhen

Verwandte Beiträge

1 Kommentare

 
GN⁺ 2025-05-02
Meinungen auf Hacker News

  • Wenn man eine Datenbankdatei, die gerade läuft und Updates erhält, einfach so kopiert, kann sie natürlich nur beschädigt werden.
    Um eine SQLite-Datenbank sicher zu replizieren, gibt es Litestream: https://github.com/benbjohnson/litestream

    • Litestream funktioniert sogar über normales SFTP, sodass man die Datenbank-Replikation zu praktisch jedem per SSH erreichbaren UNIX-Endpunkt streamen kann.
      Es gibt bevorzugte Dienste, aber jeder SFTP-Server geht.
      [1] https://github.com/benbjohnson/litestream/issues/140
      [2] https://www.rsync.net/resources/notes/2021-q3-rsync.net_technotes.html
    • Bei „zwangsläufig“ beschädigt gibt es Ausnahmen. Wenn man auf Btrfs oder ZFS eine Datei oder ein Subvolume atomar kopiert, passiert bei einer ACID-Datenbank oder einem LSM-Tree im schlimmsten Fall nur ein Rollback.
      Bei mehreren Dateien müssen sie natürlich in einem Subvolume gebündelt sein, damit alle innerhalb derselben Transaktion kopiert werden; einfach nur cp --reflink=always reicht nicht.
      Einen Prozess mit SIGSTOP anzuhalten, kann ein ähnliches Ergebnis liefern, aber darauf würde ich mich nicht verlassen wollen.
    • Auch der eingebaute .backup-Befehl ist das offizielle Werkzeug, um eine Snapshot-Version einer laufenden Datenbank zu erzeugen und kopierbar zu machen.
    • Auf meinem System nutze ich Litestream mit guten Erfahrungen, aber diese Dokumentation, die recht ausführlich zeigt, wie man etwas Ähnliches manuell nur mit eingebauten Werkzeugen macht, ist ebenfalls gut: https://litestream.io/alternatives/cron/
    • Litestream ist wirklich großartig. Ich plane, es auf Container-Ebene für SQLite-Backups und -Wiederherstellung zu verwenden, so wie es ein ehemaliger Googler gemacht hat, der ein kleines KVM-Startup gegründet hatte und dessen Lager im Urlaub überflutet wurde.
      Wenn ich mich richtig erinnere, hat er dazu einen perfekten Guide geschrieben, aber die Chance, ihn zu finden, liegt nahe null. Wenn jemand diese Referenz kennt, wäre ein Link schön.

  • Das wirkt, als ginge es um das Kopieren von Datenbanken zwischen Computern, aber man zeichnet nur den Kreis und lässt den Rest der Eule weg.
    Wie andere schon gesagt haben, wäre inkrementelles rsync viel schneller. Was mich aber mehr stört: Es wird behauptet, SQL-Statements zu übertragen sei schneller als die Datenbank zu übertragen, und dabei wird völlig ausgelassen, dass man diese SQL-Statements auch ausführen muss. Danach muss man außerdem /optimize/ und /vacuum/ laufen lassen.
    Ich habe gerade den Fall, dass ich eine Datenbank aus CSV-Dateien „inkrementell neu aufbauen“ muss; in meinem Fall ist es optimaler, sie von Grund auf neu zu erstellen. Selbst nach vielen Optimierungen dauert allein das Batch-Einfügen in eine leere In-Memory-Datenbank und das Erstellen der Indizes 30 Minuten.

    • Ich hoffe, du hast https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite gesehen.
      Das ist eine sehr gute Zusammenfassung, wie man in SQLite3 schnell einfügt.
    • Bei jeder Optimierung ist hier entscheidend, wo der Flaschenhals liegt. Im Beispiel des Originals scheint die Bandbreite der Flaschenhals zu sein, während CPU und Disk-I/O ausreichend vorhanden sind.
      Dort hieß es, das Herunterladen einer 250-MB-Datenbank dauere 1 Minute; ich habe dank 1-Gbit/s-Glasfaser eine 2-GB-SQLite-Testdatenbank vom Firmenserver in 15 Sekunden geholt.
    • 30 Minuten wirken lang. Sind es sehr viele Daten? Ich arbeite daran, eine SQLite-Datenbank mit vielen JSON-Daten zu bootstrappen, und habe einen ziemlich guten Performance-Sweet-Spot gefunden: Werte in Listen sammeln und jeweils 10.000 auf einmal einfügen, dann lassen sich auch Millionen Zeilen in wenigen Minuten einfügen.
      Ich musste Tricks wie Bloom-Filter und LRU-Cache einsetzen, aber inzwischen kann ich eine 6-GB-Datenbank in etwa 20 Minuten erzeugen.

  • Für diesen Zweck gibt es ein offizielles Tool in SQLite: https://www.sqlite.org/rsync.html
    Es arbeitet auf Seitenebene.
    „Das Protokoll funktioniert so, dass die Replik die kryptografischen Hashes jeder Seite an die Quelle sendet, und die Quelle den vollständigen Inhalt der Seiten zurücksendet, deren Hash nicht übereinstimmt.“

    • Stimmt, aber leider nimmt das SQLite-Team dieses Tool nicht in das autotools-Tarball auf, das die meisten Distributionen und brew für das Packaging von SQLite verwenden.
      Wenn man dieses Tool nutzen will, bleibt nur, es selbst zu kompilieren.

  • Das kürzlich veröffentlichte Utility sqlite_rsync verwendet eine Version des rsync-Algorithmus, die auf die interne Struktur von SQLite-Datenbanken optimiert ist. Es vergleicht interne Datenseiten effizient und synchronisiert anschließend nur geänderte oder fehlende Seiten.
    Die Tricks im Artikel sind auch gut, aber inzwischen ist es einfacher, das eingebaute Utility zu verwenden :)
    Ich habe in einem Blogpost ausführlich beschrieben, wie es funktioniert: https://nochlin.com/blog/how-the-new-sqlite3_rsync-utility-works

    • sqlite3_rsync ist jetzt in die rsync.net-Plattform eingebaut.
      ssh user@rsync.net sqlite3_rsync … blah blah …
      Es wurde erst letzte Woche hinzugefügt und ist noch nicht in allen Regionen ausgerollt, aber bei den ersten Nutzern soll es genau wie erwartet funktioniert haben.
    • sqlite_rsync kann nur im WAL-Modus verwendet werden. Eine zusätzliche Einschränkung des WAL-Modus ist, dass die Datenbankdatei auf einer lokalen Platte liegen muss.
      Meistens ist das natürlich der Fall, aber wenn das nicht möglich ist, kann man dieses Utility nicht verwenden.
    • Wenn man häufig synchronisiert, sollte man wegen der Bandbreite die Seitengröße erhöhen.

  • Es war überraschend, dass die von rsync angebotene Komprimierung während der Übertragung nicht ausprobiert wurde
    -z, --compress komprimiert Dateidaten während der Übertragung, und --compress-level=NUM legt die Kompressionsstufe explizit fest
    Vielleicht ist es schneller, erst mit gzip zu komprimieren und dann zu übertragen, aber es ist gut, dass sich die Übertragung mit einem einzigen Flag verbessern lässt

    • Wenn schon das Problem möglicher Beschädigungen erwähnt wird, wäre es besser, zusammen mit -z sqlite3_rsync (https://sqlite.org/rsync.html) zu verwenden
      Im Grunde ist das rsync mit Verständnis für SQLite-Transaktionen und WAL, ergänzt um Komprimierung während der Übertragung
    • Entscheidend ist, die Indizes zu überspringen, und das muss vor der Komprimierung passieren
      Bei solchen Aufgaben streame ich den Dump direkt nach gzip. Meist findet man auch einen Weg, direkt ans Ziel zu streamen, ohne Zwischendatei
      Außerdem wird er so am Ziel komprimiert gespeichert. Besonders dann, wenn es nicht um Replikation für Arme geht, sondern um Backups
    • Komprimierung lohnt sich meiner Meinung nach nur bei langsamen Netzwerken
    • Das sollte man unbedingt tun. Wenn man rsync auf eine komprimierte Datei anwendet, umgeht man vollständig den Kernnutzen von rsync: Delta-Übertragung auf Basis von Rolling Checksums
    • Mit --remove-source-files hätte man sich ersparen können, sich erneut per SSH einzuloggen und rm auszuführen

  • Das Speichern als Textdatei ist ineffizient. Ich speichere SQLite-Datenbanken mit VACUUM INTO
    sqlite3 -readonly /path/db.sqlite "VACUUM INTO '/path/backup.sqlite';"
    Laut https://sqlite.org/lang_vacuum.html ist ein VACUUM-Befehl mit INTO-Klausel eine Alternative zur Backup-API, um eine Sicherungskopie der laufenden Datenbank zu erstellen. Der Vorteil ist, dass die resultierende Backup-Datenbank minimal groß ist, wodurch sich Dateisystem-I/O reduzieren kann

    • Schick, löst aber nicht das im Original erwähnte Index-Problem. Die Kernidee war, keine Indexdaten über eine langsame Verbindung zu schicken; VACUUM INTO behält die Indizes bei
      Textdateien können im Rohzustand ineffizient sein, lassen sich aber selbst mit primitiven Tools wie gzip gut komprimieren. Ob das binäre SQLite-Format genauso gut komprimiert wird, ist nicht sicher, aber möglich
    • Bewahrt diese Methode die Indizes? Im Original hieß es, der Grund für die große SQLite-Datei seien die Indizes

  • In DuckDB kann man dasselbe tun, aber nach Parquet exportieren; dadurch wird die Datenmenge gegenüber textbasierten SQL-Anweisungen um einen einstelligen Faktor kleiner. Die Übertragung ist schneller und das Laden ebenfalls
    https://duckdb.org/docs/stable/sql/statements/export.html

    • Auf der Kommandozeile geht das so
      duckdb -c "attach 'sqlite-database.db' as db; copy db.table_name to 'table_name.parquet' (format parquet, compression zstd)"
      In meiner Testdatenbank war es etwa 20 % kleiner als mit gzip komprimierte Text-SQL-Anweisungen

  • SQLite hat eine Session-Erweiterung, die Änderungen an einer Menge von Tabellen verfolgt und changesets/patchsets erzeugen kann, die sich auf eine ältere Version einer SQLite-Datenbank anwenden lassen
    https://www.sqlite.org/sessionintro.html

    • Ich habe noch kein einziges SQLite-Binding gesehen, das das unterstützt. Wenn man die Anwendung also nicht in C schreiben oder ein Sprach-Binding patchen will, ist das ziemlich wenig nützlich
      In einem meiner Projekte habe ich eine Session für Arme selbst umgesetzt: Alle SQL-Anweisungen und Parameter werden in einer separaten Datenbank protokolliert, anschließend synchronisiert und wieder abgespielt. Für eine Datenbank von etwa 30 GB, bei der sich täglich nur rund 0,1 % ändern, funktioniert das gut genug
    • Hast du das schon einmal benutzt? Ich habe die Dokumentation gelesen, aber noch nie von jemandem gehört, der diese Erweiterung tatsächlich verwendet
    • Es wäre wirklich schön, wenn SQLite eine Standardmethode für Change Data Capture über Sessions oder einen ähnlichen Ansatz hätte

  • Wenn man regelmäßig von einer alten auf eine neue Version synchronisiert, lässt sich das mit der gzip-Option --rsyncable weiter optimieren
    Die Kompressionsrate sinkt um etwa 1 %, aber Unterschiede von einer Version zur nächsten kaskadieren nicht durch die gesamte komprimierte Ausgabe, sondern bleiben lokal begrenzt
    Eine andere Möglichkeit ist, die Dump-Ausgabe nicht zu komprimieren, sondern rsync die Differenz zwischen dem vorherigen unkomprimierten Dump und dem aktuellen Dump berechnen zu lassen und nur das über das Netzwerk gesendete Changeset zu komprimieren. Also einfach rsync -z verwenden

  • Das ließe sich wohl als eine einzige Pipeline abwickeln
    ssh username@server "sqlite3 my_remote_database.db .dump | gzip -c" | gunzip -c | sqlite3 my_local_database.db

    • Wenn man beim SSH-Aufruf mit -oCompression=on oder -C die SSH-Komprimierung aktiviert, könnten gzip/gunzip redundant sein
    • Das war auch mein erster Gedanke. Und es scheint ziemlich offensichtlich, dass dort auch pv hineingehört