Was sind die Leistungsmerkmale von Sqlite bei sehr großen Datenbankdateien?

Question

Aktualisierung 2020 etwa 11 Jahre, nachdem die Frage gestellt und später geschlossen wurde, was neuere Antworten verhindert.

Fast alles, was hier steht, ist veraltet. Es gab einmal eine Zeit, da war Sqlite auf die Speicherkapazität oder auf 2 GB Speicherplatz (32 Bit) oder andere populäre Zahlen beschränkt... nun, das ist lange her.

Offizielle Einschränkungen sind hier aufgeführt . Praktisch wird sqlite wahrscheinlich funktionieren, solange Speicherplatz vorhanden ist . Es funktioniert gut mit Datensätzen, die größer als der Speicher sind. Es wurde ursprünglich erstellt, als der Speicher noch knapp war, und war von Anfang an ein sehr wichtiger Punkt.

Es ist absolut kein Problem, 100 GB an Daten zu speichern. Wahrscheinlich könnte man auch ein TB speichern, aber irgendwann ist der Punkt erreicht, an dem man sich fragen muss, ob SQLite das beste Werkzeug für diese Aufgabe ist, und man möchte wahrscheinlich die Funktionen einer vollwertigen Datenbank (Remote-Clients, gleichzeitige Schreibvorgänge, schreibgeschützte Replikate, Sharding usw.).

---

Original:

Ich weiß, dass Sqlite mit extrem großen Datenbankdateien nicht gut zurechtkommt, selbst wenn sie unterstützt werden (auf der Sqlite-Website gab es einmal einen Kommentar, der besagte, dass man bei Dateigrößen über 1 GB die Verwendung eines Unternehmens-RDBs in Betracht ziehen sollte. Ich kann ihn nicht mehr finden, vielleicht bezieht er sich auf eine ältere Version von sqlite).

Für meine Zwecke möchte ich jedoch erst eine Vorstellung davon bekommen, wie schlimm es wirklich ist, bevor ich andere Lösungen in Betracht ziehe.

Ich spreche von Sqlite-Datendateien im Multi-Gigabyte-Bereich, ab 2 GB aufwärts. Hat jemand Erfahrung damit? Irgendwelche Tipps/Ideen?

Answer 1

Ich habe also einige Tests mit Sqlite für sehr große Dateien durchgeführt und bin zu einigen Schlussfolgerungen gekommen (zumindest für meine spezielle Anwendung).

Die Tests umfassen eine einzige Sqlite-Datei mit einer oder mehreren Tabellen. Jede Tabelle hatte etwa 8 Spalten, fast alle Ganzzahlen, und 4 Indizes.

Die Idee war, so viele Daten einzufügen, bis die Sqlite-Dateien etwa 50 GB groß waren.

Einzelner Tisch

Ich habe versucht, mehrere Zeilen in eine Sqlite-Datei mit nur einer Tabelle einzufügen. Als die Datei etwa 7 GB groß war (sorry, ich kann die Zeilenzahl nicht genau angeben), dauerte das Einfügen viel zu lange. Ich hatte geschätzt, dass mein Test zum Einfügen aller Daten etwa 24 Stunden dauern würde, aber er war auch nach 48 Stunden noch nicht abgeschlossen.

Dies führt mich zu dem Schluss, dass eine einzelne, sehr große Sqlite-Tabelle Probleme mit Einfügungen und wahrscheinlich auch andere Operationen haben wird.

Ich denke, das ist keine Überraschung, denn je größer die Tabelle wird, desto länger dauert das Einfügen und Aktualisieren aller Indizes.

Mehrere Tische

Ich habe dann versucht, die Daten nach Zeit auf mehrere Tabellen aufzuteilen, eine Tabelle pro Tag. Die Daten für die ursprüngliche 1 Tabelle wurden auf ~700 Tabellen aufgeteilt.

Bei diesem Aufbau gab es keine Probleme mit dem Einfügen, es dauerte auch nicht länger, da für jeden Tag eine neue Tabelle erstellt wurde.

Vakuum-Probleme

Wie von i_like_caffeine angemerkt, ist der VACUUM-Befehl ein Problem, je größer die Sqlite-Datei ist. Je mehr Einfügungen/Löschungen vorgenommen werden, desto stärker wird die Fragmentierung der Datei auf der Festplatte, so dass das Ziel darin besteht, regelmäßig VACUUM zu verwenden, um die Datei zu optimieren und Speicherplatz zurückzugewinnen.

Allerdings, wie von Dokumentation wird eine vollständige Kopie der Datenbank erstellt, um ein Vakuum zu erzeugen, was sehr viel Zeit in Anspruch nimmt. Je kleiner also die Datenbank ist, desto schneller wird dieser Vorgang abgeschlossen sein.

Schlussfolgerungen

Für meine spezielle Anwendung werde ich die Daten wahrscheinlich auf mehrere DB-Dateien aufteilen, eine pro Tag, um das Beste aus der Vakuumleistung und der Einfüge-/Löschgeschwindigkeit herauszuholen.

Das erschwert Abfragen, aber für mich ist es ein lohnender Kompromiss, so viele Daten indizieren zu können. Ein weiterer Vorteil ist, dass ich einfach eine ganze DB-Datei löschen kann, um die Daten eines Tages zu löschen (ein häufiger Vorgang in meiner Anwendung).

Ich müsste wahrscheinlich auch die Tabellengröße pro Datei überwachen, um festzustellen, wann die Geschwindigkeit zum Problem wird.

Es ist schade, dass es keine andere inkrementelle Vakuum-Methode zu geben scheint als Autovakuum . Ich kann es nicht verwenden, weil mein Ziel für Vakuum ist es, die Datei zu defragmentieren (Dateispeicherplatz ist keine große Sache), die Auto-Vakuum nicht tun. In der Dokumentation steht sogar, dass es die Fragmentierung verschlimmern kann, so dass ich die Datei in regelmäßigen Abständen vollständig leeren muss.

Answer 2

Wir verwenden DBS von 50 GB+ auf unserer Plattform. Es gibt keine Beschwerden, es funktioniert hervorragend. Stellen Sie sicher, dass Sie alles richtig machen! Verwenden Sie vordefinierte Anweisungen? *SQLITE 3.7.3

1. Transaktionen
2. Vorab abgegebene Erklärungen

3. Wenden Sie diese Einstellungen an (gleich nach der Erstellung der DB)

   PRAGMA main.page_size = 4096;
   PRAGMA main.cache_size=10000;
   PRAGMA main.locking_mode=EXCLUSIVE;
   PRAGMA main.synchronous=NORMAL;
   PRAGMA main.journal_mode=WAL;
   PRAGMA main.cache_size=5000;

Ich hoffe, dass dies anderen helfen wird, hier funktioniert es hervorragend

Answer 3

Ich habe SQLite-Datenbanken mit einer Größe von bis zu 3,5 GB ohne nennenswerte Leistungsprobleme erstellt. Wenn ich mich richtig erinnere, gab es bei SQLite2 vielleicht eine niedrigere Grenze, aber ich glaube nicht, dass es bei SQLite3 solche Probleme gibt.

Nach Angaben der SQLite-Grenzen Seite beträgt die maximale Größe jeder Datenbankseite 32K. Und die maximale Anzahl von Seiten in einer Datenbank beträgt 1024^3. Nach meinen Berechnungen ergibt sich also eine maximale Größe von 32 Terabyte. Ich denke, Sie werden an die Grenzen Ihres Dateisystems stoßen, bevor Sie an die von SQLite stoßen!

Answer 4

Der Grund dafür, dass Ihre Einfügungen mehr als 48 Stunden in Anspruch nahmen, liegt zum großen Teil an Ihren Indizes. Es ist unglaublich viel schneller,:

1 - Alle Indizes löschen 2 - Alle Einfügungen vornehmen 3 - Indizes neu erstellen

Answer 5

Neben der üblichen Empfehlung:

1. Drop-Index für Masseneinlage.
2. Stapelweise Einfügungen/Aktualisierungen in großen Transaktionen.
3. Stimmen Sie Ihren Puffercache ab / deaktivieren Sie Journal /w PRAGMAs.
4. Verwenden Sie einen 64-Bit-Rechner (um viel Cache™ nutzen zu können).
5. [hinzugefügt Juli 2014] Verwendung Gemeinsamer Tabellenausdruck (CTE) anstatt mehrere SQL-Abfragen auszuführen! Benötigt SQLite Version 3.8.3.

Aus meiner Erfahrung mit SQLite3 habe ich Folgendes gelernt:

1. Für eine maximale Einfügegeschwindigkeit sollten Sie kein Schema mit einer Spaltenbeschränkung verwenden. ( Tabelle später nach Bedarf ändern Sie können mit ALTER TABLE keine Einschränkungen hinzufügen).
2. Optimieren Sie Ihr Schema, um das zu speichern, was Sie brauchen. Manchmal bedeutet dies, dass Sie Tabellen aufteilen und/oder sogar Ihre Daten vor dem Einfügen in die Datenbank komprimieren/transformieren müssen. Ein gutes Beispiel ist die Speicherung von IP-Adressen als (lange) Ganzzahlen.
3. Eine Tabelle pro DB-Datei - zur Minimierung von Sperrkonflikten. (Verwenden DATENBANK ANHÄNGEN wenn Sie ein einziges Verbindungsobjekt haben wollen.
4. SQLite kann verschiedene Datentypen in derselben Spalte speichern (dynamische Typisierung), nutzen Sie dies zu Ihrem Vorteil.

Fragen/Kommentare willkommen ;-)