Ursprüngliche FrageHintergrund

Es ist bekannt, dass SQLitemuss fein abgestimmt werden um Beilagegeschwindigkeiten in der Größenordnung von 50.000 Beilagen / s zu erreichen. Hier gibt es viele Fragen zu langsamen Einfügegeschwindigkeiten und einer Fülle von Ratschlägen und Benchmarks.

Es gibt auchbehauptet, dass SQLite große Datenmengen verarbeiten kann, mit Berichten über 50 GB, die keine Probleme mit den richtigen Einstellungen verursachen.

Ich habe hier und anderswo den Rat befolgt, um diese Geschwindigkeiten zu erreichen, und ich bin mit 35k-45k Einsätzen / s zufrieden. Das Problem, das ich habe, ist, dass alle Benchmarks nur schnelle Einfügungsgeschwindigkeiten mit <1 m Datensätzen aufweisen. Was ich sehe, ist dasDie Einfügungsgeschwindigkeit scheint umgekehrt proportional zur Tabellengröße zu sein.

Problem

Mein Anwendungsfall erfordert die Speicherung von 500m bis 1b Tupeln ([x_id, y_id, z_id]) über ein paar Jahre (1m Zeilen / Tag) in einer Verknüpfungstabelle. Die Werte sind alle Integer-IDs zwischen 1 und 2.000.000. Es gibt einen einzelnen Index fürz_id.

Die Leistung ist für die ersten 10-Millionen-Zeilen (~ 35.000 Einfügungen / s) hervorragend, aber bis die Tabelle ~ 20-Millionen-Zeilen aufweist, leidet die Leistung. Ich sehe jetzt ungefähr 100 Einsätze / s.

Die Größe des Tisches ist nicht besonders groß. Mit 20 m Zeilen beträgt die Größe der Festplatte ca. 500 MB.

Das Projekt ist in Perl geschrieben.

Frage

Ist das die Realität großer Tabellen in SQLite oder gibt es irgendwelche Geheimnisse dafür?Aufrechterhaltung hohe Einfügeraten für Tabellen mit> 10m Zeilen?

Bekannte Workarounds, die ich nach Möglichkeit vermeiden möchteLöschen Sie den Index, fügen Sie die Datensätze hinzu, und indizieren Sie neu: Dies ist eine gute Umgehung, funktioniert jedoch nicht, wenn die Datenbank während der Aktualisierung noch verwendet werden muss. Es wird nicht funktionieren, die Datenbank für vollständig unzugänglich zu machenx Minuten / TagTeilen Sie die Tabelle in kleinere Untertabellen / Dateien auf: Das wird kurzfristig funktionieren und ich habe bereits damit experimentiert. Das Problem ist, dass ich in der Lage sein muss, Daten aus dem gesamten Verlauf abzurufen, wenn ich eine Abfrage vornehme. Das Anhängen, Sammeln von Ergebnissen in einer temporären Tabelle und das hundertfache Trennen pro Anforderung ist anscheinend viel Arbeit und Aufwand, aber ich werde es versuchen, wenn es keine anderen Alternativen gibt.einstellen SQLITE_FCNTL_CHUNK_SIZE: Ich kenne C (?!) Nicht, deshalb würde ich es lieber nicht lernen, um das zu erledigen. Es ist jedoch nicht möglich, diesen Parameter mit Perl festzulegen.AKTUALISIEREN

Im AnschlussTims Vorschlag Da ein Index trotz der Behauptung von SQLite, dass er große Datenmengen verarbeiten kann, immer langsamere Einfügezeiten verursachte, führte ich einen Benchmark-Vergleich mit den folgenden Einstellungen durch:

eingefügte Zeilen:14 MillionenChargengröße festlegen:50.000 Datensätzecache_size Pragma:10.000page_size Pragma:4,096temp_store Pragma:Erinnerungjournal_mode Pragma:löschensynchronous Pragma:aus

In meinem Projekt wird wie in den folgenden Benchmark-Ergebnissen eine dateibasierte temporäre Tabelle erstellt und die in SQLite integrierte Unterstützung für den Import von CSV-Daten verwendet. Die temporäre Tabelle wird dann an die empfangende Datenbank angehängt und Sätze von 50.000 Zeilen werden mit einem eingefügtinsert-select Aussage. Daher spiegeln die Einfügezeiten nicht widerDatei in Datenbank mal einfügen, aber eherTisch an Tisch Geschwindigkeit eingeben. Die Berücksichtigung der CSV-Importzeit würde die Geschwindigkeit um 25-50% reduzieren (eine sehr grobe Schätzung, der Import der CSV-Daten dauert nicht lange).

Offensichtlich führt ein Index zu einer Verlangsamung der Einfügungsgeschwindigkeit, wenn die Tabellengröße zunimmt.

Aus den obigen Daten geht klar hervor, dass die richtige Antwort zugewiesen werden kannTims Antwort eher als die Behauptungen, dass SQLite einfach nicht damit umgehen kann. Klar eskönnen große Datensätze verarbeitenob Die Indizierung dieses Datensatzes ist nicht Teil Ihres Anwendungsfalls. Ich benutze SQLite für genau das, als Backend für ein Protokollierungssystem, und das schon eine Weilenicht müssen indiziert werden, so war ich ziemlich überrascht über die Verlangsamung, die ich erlebte.

Fazit

Wenn jemand Lust hat, eine große Datenmenge mit SQLite zu speichernund habe es indiziert,mit Scherben kann die Antwort sein. Ich entschied mich schließlich dafür, die ersten drei Zeichen eines MD5-Hashs zu verwenden, in dem eine eindeutige Spalte enthalten istz Zuweisung zu einer von 4.096 Datenbanken zu bestimmen. Da mein Anwendungsfall in erster Linie archivarischer Natur ist, wird sich das Schema nicht ändern, und Abfragen erfordern niemals Shard-Walking. Die Datenbankgröße ist begrenzt, da extrem alte Daten reduziert und schließlich verworfen werden. Diese Kombination aus Sharding, Pragma-Einstellungen und sogar einigendeDurch die Normalisierung erhalte ich eine gute Balance, die basierend auf dem obigen Benchmarking eine Einfügungsgeschwindigkeit von mindestens 10.000 Einfügungen / Sekunde beibehält.