Verbesserung der INSERT-per-Sekunde-Leistung von SQLite

Question

Die Optimierung von SQLite ist knifflig. Die Bulk-Insert-Leistung einer C-Anwendung kann von 85 Inserts pro Sekunde bis zu über 96.000 Inserts pro Sekunde reichen!

Hintergrund: Wir verwenden SQLite als Teil einer Desktop-Anwendung. Wir haben große Mengen an Konfigurationsdaten in XML-Dateien gespeichert, die geparst und in eine SQLite-Datenbank zur weiteren Verarbeitung geladen werden, wenn die Anwendung initialisiert wird. SQLite ist ideal für diese Situation, da es schnell ist, keine spezielle Konfiguration erfordert und die Datenbank als einzelne Datei auf der Festplatte gespeichert wird.

Begründung: Anfänglich war ich von der Leistung, die ich sah, enttäuscht. Es hat sich herausgestellt, dass die Leistung von SQLite erheblich variieren kann (sowohl bei Bulk-Inserts als auch bei Selects), je nachdem, wie die Datenbank konfiguriert ist und wie Sie die API verwenden. Es war nicht trivial herauszufinden, welche Optionen und Techniken es gibt. Daher hielt ich es für klug, diesen Wiki-Eintrag zu erstellen, um die Ergebnisse mit den Stack Overflow-Lesern zu teilen und anderen die gleichen Untersuchungen zu ersparen.

Das Experiment: Anstatt einfach über Leistungstipps im allgemeinen Sinne zu sprechen (d.h. "Verwenden Sie eine Transaktion!" ), hielt ich es für das Beste, etwas C-Code zu schreiben und tatsächlich messen die Auswirkungen der verschiedenen Optionen. Wir werden mit einigen einfachen Daten beginnen:

• Eine 28 MB große TAB-getrennte Textdatei (etwa 865.000 Datensätze) der vollständiger Fahrplan für die Stadt Toronto
• Mein Testrechner ist ein 3,60 GHz P4 mit Windows XP.
• Der Code wird kompiliert mit Visual C++ 2005 als "Release" mit "Full Optimization" (/Ox) und Favor Fast Code (/Ot).
• Ich verwende SQLite "Amalgamation", das direkt in meine Testanwendung kompiliert wurde. Die SQLite-Version, die ich zufällig habe, ist etwas älter (3.6.7), aber ich vermute, dass diese Ergebnisse mit der neuesten Version vergleichbar sind (bitte hinterlassen Sie einen Kommentar, wenn Sie anderer Meinung sind).

Schreiben wir etwas Code!

Der Kodex: Ein einfaches C-Programm, das die Textdatei Zeile für Zeile liest, die Zeichenkette in Werte zerlegt und die Daten dann in eine SQLite-Datenbank einfügt. In dieser "Basisversion" des Codes wird die Datenbank erstellt, aber wir werden keine Daten einfügen:

/*************************************************************
    Baseline code to experiment with SQLite performance.

    Input data is a 28 MB TAB-delimited text file of the
    complete Toronto Transit System schedule/route info
    from http://www.toronto.ca/open/datasets/ttc-routes/

**************************************************************/
#include <stdio.h>
#include <stdlib.h>
#include <time.h>
#include <string.h>
#include "sqlite3.h"

#define INPUTDATA "C:\\TTC_schedule_scheduleitem_10-27-2009.txt"
#define DATABASE "c:\\TTC_schedule_scheduleitem_10-27-2009.sqlite"
#define TABLE "CREATE TABLE IF NOT EXISTS TTC (id INTEGER PRIMARY KEY, Route_ID TEXT, Branch_Code TEXT, Version INTEGER, Stop INTEGER, Vehicle_Index INTEGER, Day Integer, Time TEXT)"
#define BUFFER_SIZE 256

int main(int argc, char **argv) {

    sqlite3 * db;
    sqlite3_stmt * stmt;
    char * sErrMsg = 0;
    char * tail = 0;
    int nRetCode;
    int n = 0;

    clock_t cStartClock;

    FILE * pFile;
    char sInputBuf [BUFFER_SIZE] = "\0";

    char * sRT = 0;  /* Route */
    char * sBR = 0;  /* Branch */
    char * sVR = 0;  /* Version */
    char * sST = 0;  /* Stop Number */
    char * sVI = 0;  /* Vehicle */
    char * sDT = 0;  /* Date */
    char * sTM = 0;  /* Time */

    char sSQL [BUFFER_SIZE] = "\0";

    /*********************************************/
    /* Open the Database and create the Schema */
    sqlite3_open(DATABASE, &db);
    sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);

    /*********************************************/
    /* Open input file and import into Database*/
    cStartClock = clock();

    pFile = fopen (INPUTDATA,"r");
    while (!feof(pFile)) {

        fgets (sInputBuf, BUFFER_SIZE, pFile);

        sRT = strtok (sInputBuf, "\t");     /* Get Route */
        sBR = strtok (NULL, "\t");            /* Get Branch */
        sVR = strtok (NULL, "\t");            /* Get Version */
        sST = strtok (NULL, "\t");            /* Get Stop Number */
        sVI = strtok (NULL, "\t");            /* Get Vehicle */
        sDT = strtok (NULL, "\t");            /* Get Date */
        sTM = strtok (NULL, "\t");            /* Get Time */

        /* ACTUAL INSERT WILL GO HERE */

        n++;
    }
    fclose (pFile);

    printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

    sqlite3_close(db);
    return 0;
}

---

Die "Kontrolle"

Wenn Sie den Code so ausführen, wie er ist, werden zwar keine Datenbankoperationen durchgeführt, aber Sie erhalten einen Eindruck davon, wie schnell die rohen C-Dateieingabe- und Zeichenkettenverarbeitungsoperationen sind.

Importierte 864913 Datensätze in 0,94 Sekunden

Großartig! Wir können 920.000 Einfügungen pro Sekunde machen, vorausgesetzt, wir machen keine Einfügungen :-)

---

Das "Worst-Case-Szenario"

Wir werden den SQL-String mit den aus der Datei gelesenen Werten generieren und diese SQL-Operation mit sqlite3_exec aufrufen:

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, '%s', '%s', '%s', '%s', '%s', '%s', '%s')", sRT, sBR, sVR, sST, sVI, sDT, sTM);
sqlite3_exec(db, sSQL, NULL, NULL, &sErrMsg);

Dies wird langsam sein, da das SQL für jede Einfügung in VDBE-Code kompiliert wird und jede Einfügung in einer eigenen Transaktion erfolgt. Wie langsam?

Importierte 864913 Datensätze in 9933,61 Sekunden

Igitt! 2 Stunden und 45 Minuten! Das ist nur 85 Einsätze pro Sekunde.

Verwendung einer Transaktion

SQLite wertet standardmäßig jede INSERT / UPDATE-Anweisung innerhalb einer einzigen Transaktion aus. Wenn Sie eine große Anzahl von Einfügungen vornehmen, ist es ratsam, Ihre Operation in eine Transaktion zu verpacken:

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    ...

}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

Importierte 864913 Datensätze in 38,03 Sekunden

Das ist besser. Wenn wir alle unsere Einfügungen in eine einzige Transaktion packen, verbessert sich unsere Leistung auf 23.000 Einsätze pro Sekunde.

Verwendung einer vorbereiteten Erklärung

Die Verwendung einer Transaktion war eine enorme Verbesserung, aber es macht keinen Sinn, die SQL-Anweisung für jede Einfügung neu zu kompilieren, wenn wir immer wieder die gleiche SQL verwenden. Verwenden wir sqlite3_prepare_v2 um unsere SQL-Anweisung einmal zu kompilieren und dann unsere Parameter an diese Anweisung zu binden, indem wir sqlite3_bind_text :

/* Open input file and import into the database */
cStartClock = clock();

sprintf(sSQL, "INSERT INTO TTC VALUES (NULL, @RT, @BR, @VR, @ST, @VI, @DT, @TM)");
sqlite3_prepare_v2(db,  sSQL, BUFFER_SIZE, &stmt, &tail);

sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sRT = strtok (sInputBuf, "\t");   /* Get Route */
    sBR = strtok (NULL, "\t");        /* Get Branch */
    sVR = strtok (NULL, "\t");        /* Get Version */
    sST = strtok (NULL, "\t");        /* Get Stop Number */
    sVI = strtok (NULL, "\t");        /* Get Vehicle */
    sDT = strtok (NULL, "\t");        /* Get Date */
    sTM = strtok (NULL, "\t");        /* Get Time */

    sqlite3_bind_text(stmt, 1, sRT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 2, sBR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 3, sVR, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 4, sST, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 5, sVI, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 6, sDT, -1, SQLITE_TRANSIENT);
    sqlite3_bind_text(stmt, 7, sTM, -1, SQLITE_TRANSIENT);

    sqlite3_step(stmt);

    sqlite3_clear_bindings(stmt);
    sqlite3_reset(stmt);

    n++;
}
fclose (pFile);

sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);

printf("Imported %d records in %4.2f seconds\n", n, (clock() - cStartClock) / (double)CLOCKS_PER_SEC);

sqlite3_finalize(stmt);
sqlite3_close(db);

return 0;

Importierte 864913 Datensätze in 16,27 Sekunden

Sehr schön! Es gibt noch ein bisschen mehr Code (vergessen Sie nicht, die sqlite3_clear_bindings y sqlite3_reset ), aber wir haben unsere Leistung mehr als verdoppelt auf 53.000 Einsätze pro Sekunde.

PRAGMA synchron = AUS

Standardmäßig pausiert SQLite nach der Ausgabe eines Schreibbefehls auf Betriebssystemebene. Dadurch wird gewährleistet, dass die Daten auf die Festplatte geschrieben werden. Durch die Einstellung synchronous = OFF wird SQLite angewiesen, die Daten einfach zum Schreiben an das Betriebssystem zu übergeben und dann fortzufahren. Es besteht die Möglichkeit, dass die Datenbankdatei beschädigt wird, wenn der Computer einen katastrophalen Absturz (oder Stromausfall) erleidet, bevor die Daten auf die Festplatte geschrieben werden:

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);

Importierte 864913 Datensätze in 12,41 Sekunden

Die Verbesserungen sind jetzt kleiner, aber wir sind auf dem Weg 69.600 Einsätze pro Sekunde.

PRAGMA journal_mode = MEMORY

Erwägen Sie die Speicherung des Rollback-Journals im Speicher durch Auswertung von PRAGMA journal_mode = MEMORY . Ihre Transaktion ist zwar schneller, aber wenn Sie während einer Transaktion den Strom verlieren oder Ihr Programm abstürzt, könnte Ihre Datenbank in einem beschädigten Zustand mit einer teilweise abgeschlossenen Transaktion zurückbleiben:

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

Importierte 864913 Datensätze in 13,50 Sekunden

Ein wenig langsamer als die vorherige Optimierung bei 64.000 Einsätze pro Sekunde.

PRAGMA synchron = AUS und PRAGMA journal_mode = MEMORY

Kombinieren wir die beiden vorherigen Optimierungen. Das ist etwas riskanter (im Falle eines Absturzes), aber wir importieren nur Daten (und führen keine Bank):

/* Open the database and create the schema */
sqlite3_open(DATABASE, &db);
sqlite3_exec(db, TABLE, NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA synchronous = OFF", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);

Importierte 864913 Datensätze in 12,00 Sekunden

Fantastisch! Wir sind in der Lage zu tun 72.000 Einsätze pro Sekunde.

Verwendung einer In-Memory-Datenbank

Nur so zum Spaß bauen wir auf allen vorherigen Optimierungen auf und definieren den Dateinamen der Datenbank neu, so dass wir komplett im RAM arbeiten:

#define DATABASE ":memory:"

Importierte 864913 Datensätze in 10,94 Sekunden

Es ist nicht besonders praktisch, unsere Datenbank im RAM zu speichern, aber es ist beeindruckend, dass wir die 79.000 Einsätze pro Sekunde.

Refactoring von C-Code

Obwohl es sich nicht speziell um eine SQLite-Verbesserung handelt, mag ich die zusätzliche char* Zuordnungsvorgänge in der while Schleife. Lassen Sie uns diesen Code schnell umstrukturieren, um die Ausgabe von strtok() direkt in sqlite3_bind_text() und lassen Sie den Compiler versuchen, die Dinge für uns zu beschleunigen:

pFile = fopen (INPUTDATA,"r");
while (!feof(pFile)) {

    fgets (sInputBuf, BUFFER_SIZE, pFile);

    sqlite3_bind_text(stmt, 1, strtok (sInputBuf, "\t"), -1, SQLITE_TRANSIENT); /* Get Route */
    sqlite3_bind_text(stmt, 2, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Branch */
    sqlite3_bind_text(stmt, 3, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Version */
    sqlite3_bind_text(stmt, 4, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Stop Number */
    sqlite3_bind_text(stmt, 5, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Vehicle */
    sqlite3_bind_text(stmt, 6, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Date */
    sqlite3_bind_text(stmt, 7, strtok (NULL, "\t"), -1, SQLITE_TRANSIENT);    /* Get Time */

    sqlite3_step(stmt);        /* Execute the SQL Statement */
    sqlite3_clear_bindings(stmt);    /* Clear bindings */
    sqlite3_reset(stmt);        /* Reset VDBE */

    n++;
}
fclose (pFile);

Hinweis: Wir verwenden jetzt wieder eine echte Datenbankdatei. In-Memory-Datenbanken sind schnell, aber nicht unbedingt praktisch

Importierte 864913 Datensätze in 8,94 Sekunden

Durch eine geringfügige Umstrukturierung des Codes für die Verarbeitung von Zeichenketten, der in unserer Parameterbindung verwendet wird, können wir nun 96.700 Einsätze pro Sekunde. Ich denke, man kann mit Sicherheit sagen, dass dies eine sehr schnell . Wenn wir beginnen, andere Variablen zu optimieren (z. B. Seitengröße, Indexerstellung usw.), wird dies unser Benchmark sein.

---

Zusammenfassung (bis jetzt)

Ich hoffe, du bist noch bei mir! Der Grund, warum wir diesen Weg eingeschlagen haben, ist, dass die Leistung von Bulk-Insert bei SQLite so stark schwankt und es nicht immer offensichtlich ist, welche Änderungen vorgenommen werden müssen, um unseren Vorgang zu beschleunigen. Unter Verwendung desselben Compilers (und derselben Compiler-Optionen), derselben Version von SQLite und derselben Daten haben wir unseren Code und unsere Verwendung von SQLite so optimiert, dass wir von einem Worst-Case-Szenario von 85 Einsätzen pro Sekunde auf über 96.000 Einsätze pro Sekunde!

---

CREATE INDEX dann INSERT vs. INSERT dann CREATE INDEX

Bevor wir mit der Messung beginnen SELECT Leistung, wissen wir, dass wir Indizes erstellen werden. In einer der unten stehenden Antworten wurde vorgeschlagen, dass es bei Masseneinfügungen schneller ist, den Index zu erstellen, nachdem die Daten eingefügt wurden (im Gegensatz zur Erstellung des Indexes und dem anschließenden Einfügen der Daten). Versuchen wir es:

Index erstellen und dann Daten einfügen

sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &sErrMsg);
...

Importierte 864913 Datensätze in 18,13 Sekunden

Daten einfügen und dann Index erstellen

...
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &sErrMsg);
sqlite3_exec(db, "CREATE  INDEX 'TTC_Stop_Index' ON 'TTC' ('Stop')", NULL, NULL, &sErrMsg);

Importierte 864913 Datensätze in 13,66 Sekunden

Wie erwartet, sind Masseneinfügungen langsamer, wenn eine Spalte indiziert ist, aber es macht einen Unterschied, wenn der Index nach dem Einfügen der Daten erstellt wird. Unser Basiswert ohne Index liegt bei 96.000 Einfügungen pro Sekunde. Wenn wir zuerst den Index erstellen und dann die Daten einfügen, erhalten wir 47.700 Einfügungen pro Sekunde, während wir 63.300 Einfügungen pro Sekunde erhalten, wenn wir zuerst die Daten einfügen und dann den Index erstellen.

---

Ich nehme gerne Vorschläge für andere Szenarien entgegen, die ich ausprobieren möchte... Und ich werde demnächst ähnliche Daten für SELECT-Abfragen zusammenstellen.

Answer 1

Einige Tipps:

1. Fügen Sie Einfügungen/Aktualisierungen in eine Transaktion ein.
2. Für ältere Versionen von SQLite - Erwägen Sie einen weniger paranoiden Journalmodus ( pragma journal_mode ). Es gibt NORMAL und dann gibt es noch OFF Dies kann die Einfügegeschwindigkeit erheblich erhöhen, wenn Sie sich keine Sorgen machen müssen, dass die Datenbank bei einem Absturz des Betriebssystems beschädigt werden könnte. Wenn Ihre Anwendung abstürzt, sollten die Daten in Ordnung sein. Beachten Sie, dass in neueren Versionen die OFF/MEMORY Einstellungen sind bei Abstürzen auf Anwendungsebene nicht sicher.
3. Auch das Spiel mit der Seitengröße macht einen Unterschied ( PRAGMA page_size ). Mit größeren Seitengrößen können Lese- und Schreibvorgänge etwas schneller ablaufen, da größere Seiten im Speicher gehalten werden. Beachten Sie, dass für Ihre Datenbank mehr Speicherplatz benötigt wird.
4. Wenn Sie Indizes haben, sollten Sie Folgendes anrufen CREATE INDEX nachdem Sie alle Ihre Einsätze gemacht haben. Dies ist wesentlich schneller als die Erstellung des Indexes und die anschließende Durchführung der Einfügungen.
5. Sie müssen sehr vorsichtig sein, wenn Sie gleichzeitig auf SQLite zugreifen, da die gesamte Datenbank gesperrt ist, wenn Schreibvorgänge durchgeführt werden, und obwohl mehrere Leser möglich sind, werden Schreibvorgänge ausgesperrt. Dies wurde durch die Hinzufügung eines WAL in neueren SQLite-Versionen etwas verbessert.
6. Nutzen Sie die Vorteile der Platzersparnis... kleinere Datenbanken sind schneller. Wenn Sie zum Beispiel Schlüssel-Wert-Paare haben, versuchen Sie, den Schlüssel zu einem INTEGER PRIMARY KEY wenn möglich, was die implizite eindeutige Zeilennummernspalte in der Tabelle ersetzt.
7. Wenn Sie mehrere Threads verwenden, können Sie versuchen, mit der gemeinsamer Seiten-Cache Dadurch können geladene Seiten von mehreren Threads gemeinsam genutzt werden, was teure E/A-Aufrufe vermeiden kann.
8. Verwenden Sie nicht !feof(file) !

Ich habe auch ähnliche Fragen gestellt aquí y aquí .

Answer 2

Versuchen Sie es mit SQLITE_STATIC anstelle von SQLITE_TRANSIENT für diese Einsätze.

SQLITE_TRANSIENT veranlasst SQLite, die Zeichenkettendaten vor der Rückkehr zu kopieren.

SQLITE_STATIC sagt ihm, dass die Speicheradresse, die Sie ihm gegeben haben, gültig ist, bis die Abfrage durchgeführt wurde (was in dieser Schleife immer der Fall ist). Dies erspart Ihnen mehrere Allokations-, Kopier- und Deallokationsoperationen pro Schleife. Möglicherweise eine große Verbesserung.

Answer 3

Vermeiden Sie sqlite3_clear_bindings(stmt) .

Der Code im Test setzt die Bindungen jedes Mal, was ausreichend sein sollte.

El C API-Einführung aus den SQLite-Dokumenten sagt:

Vor dem Anruf sqlite3_step() zum ersten Mal oder sofort nach sqlite3_reset() aufrufen, kann die Anwendung die sqlite3_bind() Schnittstellen, um den Parametern Werte zuzuweisen. Jeder Aufruf von sqlite3_bind() setzt frühere Bindungen für denselben Parameter außer Kraft

In den Dokumenten findet sich nichts über sqlite3_clear_bindings die besagt, dass Sie sie zusätzlich zum einfachen Setzen der Bindungen aufrufen müssen.

Mehr Details: Avoid_sqlite3_clear_bindings()

Answer 4

Auf Großpackungen

Inspiriert von diesem Beitrag und der Stack Overflow-Frage, die mich hierher geführt hat. Ist es möglich, mehrere Zeilen auf einmal in eine SQLite-Datenbank einzufügen? -- Ich habe meine erste Git Repository:

https://github.com/rdpoor/CreateOrUpdate

die ein Array von ActiveRecords in die MySQL , SQLite oder PostgreSQL Datenbanken. Es bietet die Möglichkeit, vorhandene Datensätze zu ignorieren, zu überschreiben oder einen Fehler zu melden. Meine rudimentären Benchmarks zeigen eine 10-fache Geschwindigkeitsverbesserung im Vergleich zu sequenziellen Schreibvorgängen - YMMV.

Ich verwende es im Produktionscode, wo ich häufig große Datensätze importieren muss, und ich bin sehr zufrieden damit.

Answer 5

Massenimporte scheinen am besten zu funktionieren, wenn Sie Ihre Daten in Stücke schneiden können. EINFÜGEN/UPDATE Aussagen. Ein Wert von 10.000 oder so hat gut funktioniert für mich auf einer Tabelle mit nur ein paar Zeilen, YMMV...