Verbesserung der EINSATZ-pro-Sekunde-Leistung von SQLite?

Optimierung von SQLite ist schwierig. Bulk-insert performance einer C-Anwendung kann sich variieren von 85 Einsätzen pro Sekunde auf über 96.000 inserts pro Sekunde!

Hintergrund: Wir sind mit SQLite als Teil einer desktop-Anwendung. Wir haben große Mengen von Konfigurationsdaten in XML-Dateien gespeichert, analysiert und geladen, in eine SQLite-Datenbank für die weitere Verarbeitung, wenn die Anwendung initialisiert wird. SQLite ist ideal für diese situation, denn es ist schnell, es erfordert keine spezielle Konfiguration und die Datenbank gespeichert ist auf der Festplatte als eine einzige Datei.

Begründung: Zunächst war ich enttäuscht, mit der Leistung, die ich sah. Es stellt sich heraus, dass die performance von SQLite erheblich variieren kann (sowohl für bulk-inserts und selects), je nachdem, wie die Datenbank konfiguriert ist und wie du mit der API. War es nicht eine triviale Angelegenheit, um herauszufinden, was all die Optionen und Techniken, so dass ich dachte, es klug, zu schaffen, das community-wiki-Eintrag an teilen der Ergebnisse mit Stapel -  Overflow Leser, um andere zu retten die Mühe, die gleichen Untersuchungen.

Das Experiment:, Anstatt einfach zu reden über die performance-Tipps im Allgemeinen Sinne (d.h. "mit einer Transaktion!"), Ich dachte, es am besten schreiben C-code und eigentlich Messen auf die Auswirkungen der verschiedenen Optionen. Beginnen wir mit einigen einfachen Daten:

• Eine 28 MB Datei durch Tabulatoren getrennter text (etwa 865,000 records) der komplette transit-Zeitplan für die Stadt Toronto
• Meine test-Maschine ist eine 3.60 GHz P4 läuft Windows XP.
• Der code kompiliert wird mit Visual C++ 2005 als "Release" mit "Full Optimization" (/Ox) und Zugunsten Schnellen Code (/Ot).
• Ich bin mit dem SQLite "Verschmelzung", kompiliert direkt in meiner test Anwendung. Die SQLite-version ich gerade habe, ist ein bisschen älter (3.6.7), aber ich vermute, dass diese Ergebnisse vergleichbar mit der aktuellen Version (bitte einen Kommentar hinterlassen, wenn Sie anders denken).

Schreiben wir etwas code!

Code: Einem einfachen C-Programm, das liest die Textdatei Zeile für Zeile, teilt den string in die Werte und dann fügt die Daten in eine SQLite-Datenbank. In dieser "baseline" - version des Codes, der Datenbank erstellt wird, aber wir werden nicht wirklich 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 "Control" -

Läuft der code ist nicht wirklich ausführen, Datenbank-Operationen, aber es gibt uns eine Vorstellung davon, wie schnell die raw-C Datei-I/O-und string-Verarbeitung sind.

Importiert 864913 Datensätze in 0.94
Sekunden

Toll! Wir können tun, 920,000 inserts pro Sekunde, zur Verfügung gestellt, die wir überhaupt nicht tun, keine Beilagen 🙂

---

Das "Worst-Case-Szenario"

Gehen wir zum generieren der SQL-string mit den Werten aus der Datei Lesen und den Aufruf der SQL-operation mit sqlite3_exec:

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);

Dieser wird langsam sein, weil der SQL erstellt werden, in VDBE code für jede insert und jede insert-geschehen wird in einer eigenen Transaktion. Wie langsam?

Importiert 864913 Datensätze in 9933.61
Sekunden

Huch! 2 Stunden und 45 Minuten! Das ist nur 85 Einsätzen pro Sekunde.

Über eine Transaktion

Standardmäßig SQLite wertet jede INSERT - /UPDATE-Anweisung in einer einzigen Transaktion. Wenn Sie eine große Anzahl von Einsätzen, ist es ratsam, wickeln Sie Ihre operation in einer Transaktion:

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);

Importiert 864913 Datensätze in 38.03
Sekunden

Besser. Einfach wickeln alle unsere Einsätze in einer einzigen Transaktion verbessert unsere Leistung zu als 23.000 Einsätzen pro Sekunde.

Mit einer Vorbereiteten Anweisung

Mithilfe einer Transaktion war eine riesige Verbesserung, aber zum erneuten kompilieren der SQL-Anweisung für jede insert-macht keinen Sinn, wenn wir mit dem gleichen SQL-über-und-über. Wir verwenden sqlite3_prepare_v2 zum kompilieren der SQL-Anweisung einmal und dann binden unsere Parameter an diese Anweisung mit 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;

Importiert 864913 Datensätze in 16.27
Sekunden

Schön! Es ist ein wenig mehr code (vergessen Sie nicht, rufen sqlite3_clear_bindings und sqlite3_reset), aber wir haben uns mehr als verdoppelt, unsere Leistung zu 53,000 inserts pro Sekunde.

PRAGMA synchronous = OFF

Standardmäßig SQLite pause machen wird nach der Ausgabe einer OS-level write-Befehl. Dies garantiert, dass die Daten auf die Festplatte geschrieben. Durch die Einstellung synchronous = OFF wir sind angewiesen SQLite einfach zu hand-off-Daten, um das Betriebssystem zu schreiben und dann weiter. Es gibt eine chance, dass die Datenbank-Datei möglicherweise beschädigt, wenn der computer erleidet einen katastrophalen Absturz (oder Stromausfall), bevor die Daten geschrieben, um die Platte:

/* 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);

Importiert 864913 Datensätze in 12.41
Sekunden

Verbesserungen sind jetzt kleiner, aber wir sind bis zu 69,600 inserts pro Sekunde.

PRAGMA journal_mode = SPEICHER

Betrachten speichern Sie die rollback-journal im Speicher durch die Auswertung PRAGMA journal_mode = MEMORY. Ihre Transaktion wird schneller sein, aber wenn Sie macht verlieren oder Ihr Programm abstürzt, während eine Transaktion, die Sie könnte die Datenbank werden Links in einem korrupten Staat mit einer teilweise abgeschlossenen Transaktion:

/* 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);

Importiert 864913 Datensätze in 13.50
Sekunden

Etwas langsamer als die Vorherige Optimierung bei 64,000 inserts pro Sekunde.

PRAGMA synchronous = OFF und PRAGMA journal_mode = SPEICHER

Let ' s kombinieren Sie die beiden vorherigen Optimierungen. Es ist ein wenig mehr riskant (bei einem crash), aber wir sind nur das importieren von Daten (nicht mit einer 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);

Importiert 864913 Datensätze in 12.00
Sekunden

Fantastisch! Wir sind in der Lage, das zu tun 72,000 inserts pro Sekunde.

Mit einem In-Memory-Datenbank

Nur zum Spaß, wir bauen auf den vorherigen Optimierungen und definieren Sie die Datenbank mit dem Namen, also wir arbeiten vollständig im RAM:

#define DATABASE ":memory:"

Importiert 864913 Datensätze in 10.94
Sekunden

Es ist nicht super-praktisch zum verstauen unsere Datenbank im RAM, aber es ist beeindruckend, dass wir durchführen können 79,000 inserts pro Sekunde.

Refactoring Von C-Code

Obwohl nicht ausdrücklich eine SQLite-Verbesserungen, ich weiß nicht, wie die extra char* Zuordnung der Operationen in den while Schleife. Lassen Sie uns schnell umgestalten, dass code zu übergeben, die Ausgabe von strtok() direkt in sqlite3_bind_text(), und lassen Sie die compiler versuchen, die Dinge zu beschleunigen für uns:

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 sind zurück in der Verwendung einer echten Datenbank-Datei. In-memory-Datenbanken sind schnell, aber nicht unbedingt praktisch

Importiert 864913 Datensätze in 8.94
Sekunden

Einer leichten Umgestaltung, um die string-Verarbeitung-code in unserem parameter-Bindung hat es uns erlaubt, zu führen 96,700 inserts pro Sekunde. Ich denke, es ist sicher zu sagen, dass dies reichlich schnell. Als wir beginnen, zu zwicken, andere Variablen (D. H. die Größe der Seite, index-Erstellung, etc.) das wird unser Maßstab.

---

Zusammenfassung (bisher)

Ich hoffe, du bist immer noch mit mir! Der Grund, warum wir Schritte auf diesem Weg ist, dass bulk-insert-Leistung variiert so Wild mit SQLite, und es ist nicht immer klar, welche änderungen vorgenommen werden müssen, zu beschleunigen-unsere Bedienung. Mit dem gleichen compiler (und auch compiler-Optionen), die gleiche version von SQLite und die gleichen Daten, die wir optimiert haben unseren code und unsere Verwendung von SQLite zu gehen von einem worst-case-Szenario von 85 Einsätzen pro Sekunde auf über 96.000 inserts pro Sekunde!

---

CREATE INDEX dann EINFÜGEN vs. EINFÜGEN dann CREATE INDEX

Bevor wir mit der Messung zu beginnen SELECT Leistung, wissen wir, dass wir die Erstellung von Indizes. Es ist vorgeschlagen worden, in einem der nachfolgenden Antworten Sie, dass bei Massen-inserts, es ist schneller, um den index zu erstellen, nachdem die Daten eingefügt wurden (im Gegensatz zu der Erstellung der index zuerst, dann das einfügen der Daten). Lassen Sie uns versuchen:

Create Index-dann Einfügen von Daten

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

Importiert 864913 Datensätze in 18.13
Sekunden

Einfügen von Daten Erstellen Sie dann den Index

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

Importiert 864913 Datensätze in 13.66
Sekunden

Als erwartet, bulk-inserts werden langsamer, wenn einen Spalte indiziert ist, aber es macht schon einen Unterschied, wenn der index erstellt wird, nachdem die Daten eingefügt werden. Unsere no-index baseline 96,000 inserts pro Sekunde. Erstellen der index zuerst, dann einfügen von Daten gibt uns 47,700 inserts pro Sekunde, während das einfügen der Daten erst dann erstellen der index gibt uns 63,300 inserts pro Sekunde.

---

Ich würde gerne mal Vorschläge für andere Szenarien, um zu versuchen... Und kompilieren Sie ähnliche Daten für SELECT-Abfragen schnell.

InformationsquelleAutor |