Poprawić wydajność INSERT-per-second SQLite?

Optymalizacja SQLite jest trudna. Wydajność wkładek zbiorczych w aplikacji C może się wahać od 85 wkładek na sekundę do ponad 96 000 wkładek na sekundę!

Background: używamy SQLite jako części aplikacji desktopowej. Mamy duże ilości danych konfiguracyjnych przechowywanych w plikach XML, które są przetwarzane i ładowane do bazy danych SQLite w celu dalszego przetwarzania po zainicjowaniu aplikacji. SQLite jest idealny do tej sytuacji, ponieważ jest szybki, nie wymaga specjalistycznej konfiguracji, a baza danych jest przechowywana na dysku jako pojedynczy plik.

Uzasadnienie: początkowo byłem rozczarowany występem, który oglądałem. okazuje się, że wydajność SQLite może się znacznie różnić (zarówno dla wkładek zbiorczych, jak i selekcji) w zależności od konfiguracji bazy danych i sposobu korzystania z API. Nie było trywialną sprawą dowiedzieć się, jakie były wszystkie opcje i techniki, więc pomyślałem, że rozsądne jest stworzenie tego wpis na wiki społeczności, aby podzielić się wynikami z czytelnikami Stack Overflow, aby zaoszczędzić innym kłopotów związanych z tymi samymi badaniami.

Eksperyment: zamiast po prostu mówić o poradach dotyczących wydajności w ogólnym sensie (tj. " użyj transakcji!"), pomyślałem, że najlepiej napisać jakiś kod C i właściwie zmierzyć wpływ różnych opcji. Zaczniemy od prostych danych:

• plik tekstowy rozdzielany 28 MB w latach 1975-1998 miejscowość administracyjnie należała do województwa toruńskiego.]}
[43]}moja maszyna testowa to P4 3,60 GHz z systemem Windows XP.
• kod jest kompilowany z Visual C++ 2005 jako "Release" z "Full Optimization" (/Ox) i Favor Fast Code (/Ot).
• używam SQLite "Amalgamation", skompilowanego bezpośrednio do mojej aplikacji testowej. Wersja SQLite, którą akurat mam jest nieco starsza (3.6.7), ale podejrzewam, że te wyniki będą porównywalne z najnowszym wydaniem (proszę zostawić komentarz, jeśli uważasz inaczej).

napiszmy jakiś kod!

Kod: prosty program w języku C, który odczytuje plik tekstowy linia po linii, dzieli łańcuch znaków na wartości, a następnie wstawia dane do bazy danych SQLite. W tej "podstawowej" wersji kodu, baza danych jest tworzona, ale tak naprawdę nie będziemy wstawiać danych: {]}

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

"Kontrola"

Uruchamianie kodu jako-jest w rzeczywistości nie wykonuje żadnych operacji bazodanowych, ale da nam wyobrażenie o tym, jak szybkie są operacje wejścia/wyjścia plików C i przetwarzania łańcuchów.

Zaimportowano 864913 rekordów w 0.94 seconds

Najgorszy Scenariusz

Wygenerujemy łańcuch SQL używając wartości odczytanych z pliku i wywołamy tę operację SQL używając 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);

To będzie powolne, ponieważ SQL zostanie skompilowany do kodu VDBE dla każdej wstawki, a każda wstawka stanie się w swojej własnej transakcji. Jak wolno?

Zaimportowano 864913 rekordów w 9933.61 seconds

Yikes! 2 godziny i 45 minut! To tylko 85 wstawek na sekundę.

Korzystanie z transakcji

Domyślnie SQLite oceni każdą instrukcję INSERT / UPDATE w unikalnym transakcja. W przypadku wykonywania dużej liczby wstawek zaleca się zawinąć operację w transakcję:

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

Zaimportowano 864913 rekordów w 38.03 seconds

Używając przygotowanego Oświadczenia

Korzystanie z transakcji było ogromną poprawą, ale przekompilowanie instrukcji SQL dla każdej wstawki nie ma sens, jeśli używamy tego samego SQL w kółko. Użyjmy sqlite3_prepare_v2 Aby skompilować nasze polecenie SQL raz, a następnie powiązać nasze parametry z tą instrukcją za pomocą 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;

Zaimportowano 864913 rekordów w 16.27 seconds

PRAGMA synchroniczna = OFF

Domyślnie SQLite będzie pauza po wydaniu polecenia zapisu na poziomie systemu operacyjnego. Gwarantuje to, że dane zostaną zapisane na dysku. Ustawiając synchronous = OFF, instruujemy SQLite, aby po prostu przekazał dane do systemu operacyjnego w celu zapisu, a następnie kontynuował. Jest szansa, że plik bazy danych może zostać uszkodzony, jeśli komputer ulegnie katastrofalnej awarii (lub awarii zasilania), zanim dane zostaną zapisane na talerzu: {]}

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

Zaimportowano 864913 rekordów w 12.41 seconds

Ulepszenia są teraz mniejsze, ale do 69 600 wstawek na sekundę.

PRAGMA journal_mode = MEMORY

Rozważ przechowywanie rollback journal w pamięci, oceniając PRAGMA journal_mode = MEMORY. Transakcja będzie szybsza, ale jeśli stracisz zasilanie lub program ulegnie awarii podczas transakcji, baza danych może zostać pozostawiona w stanie uszkodzonym z częściowo zakończoną transakcją: {]}

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

Zaimportowano 864913 rekordów w 13.50 seconds

Trochę wolniej niż poprzednia Optymalizacja przy 64 000 wstawek na sekundę.

PRAGMA synchronous = OFF and PRAGMA journal_mode = MEMORY

Połączmy dwie poprzednie optymalizacje. Jest to trochę bardziej ryzykowne (w przypadku awarii), ale po prostu importujemy dane (nie obsługujemy banku): {]}

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

Zaimportowano 864913 rekordów w 12.00 seconds

Korzystanie z pamięci Baza danych

Dla Zabawy, zbudujmy na wszystkich poprzednich optymalizacjach i zdefiniujmy na nowo nazwę pliku bazy danych, aby pracować całkowicie w pamięci RAM:

#define DATABASE ":memory:"

Zaimportowano 864913 rekordów w 10.94 seconds

Przechowywanie naszej bazy danych w pamięci RAM nie jest zbyt praktyczne, ale imponujące jest to, że możemy wykonać 79 000 wstawek na sekundę.

Refaktoryzacja Kodu C

Chociaż nie konkretnie poprawa SQLite, nie podoba mi się dodatkowe char* operacje przypisania w pętli while. Niech kompilator spróbuje przyspieszyć działanie tego kodu, aby przekazać wyjście strtok() bezpośrednio do sqlite3_bind_text(), i niech kompilator spróbuje przyspieszyć to dla nas:

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

Uwaga: wracamy do używania prawdziwego pliku bazy danych. Bazy danych w pamięci są szybkie, ale niekoniecznie praktyczne

Zaimportowano 864913 rekordów w 8.94 seconds

Mała refaktoryzacja do kodu przetwarzania łańcuchów użytego w naszym wiązaniu parametrów pozwoliło nam wykonać 96,700 wstawek na sekundę. myślę, że można bezpiecznie powiedzieć, że to jest bardzo szybko . W miarę jak zaczynamy podkręcać inne zmienne(np. rozmiar strony, tworzenie indeksu, itp.) to będzie nasz punkt odniesienia.

Podsumowanie (do tej pory)

mam nadzieję, że nadal jesteś ze mną! powodem, dla którego zaczęliśmy tę drogę, jest to, że wydajność wkładek luzem różni się tak bardzo w przypadku SQLite i nie zawsze jest oczywiste, jakie zmiany należy wprowadzić, aby przyspieszyć nasze operacja. Używając tego samego kompilatora( i opcji kompilatora), tej samej wersji SQLite i tych samych danych zoptymalizowaliśmy nasz kod i nasze użycie SQLite, aby przejść z najgorszego scenariusza 85 wstawek na sekundę do ponad 96 000 wstawek na sekundę!

Utwórz indeks następnie wstaw vs. Wstaw następnie Utwórz indeks

Zanim zaczniemy mierzyć SELECT wydajność, wiemy, że będziemy tworzyć indeksy. Zostało zasugerowane w jednej z poniższych odpowiedzi, że podczas wykonywania wkładki zbiorcze, jest szybsze wytworzyć indeks po włożeniu danych (w przeciwieństwie do pierwszego wytworzenia indeksu, a następnie włożenia danych). Spróbujmy:

Utwórz indeks, a następnie wstaw dane

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

Zaimportowano 864913 rekordów w 18.13 seconds

Wstaw dane, a następnie Utwórz indeks

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

Zaimportowano 864913 rekordów w 13.66 seconds

Zgodnie z oczekiwaniami, wkładki zbiorcze są wolniejsze, jeśli jedna kolumna jest zindeksowane, ale ma znaczenie, czy indeks zostanie utworzony po włożeniu danych. Nasza wartość bazowa bez indeksu wynosi 96 000 wstawek na sekundę. najpierw utworzenie indeksu, a następnie wstawienie danych daje nam 47 700 wstawek na sekundę, podczas gdy wstawienie danych najpierw utworzenie indeksu daje nam 63 300 wstawek na sekundę.

Chętnie przyjmę propozycje innych scenariuszy, aby spróbować... I wkrótce będą kompilować podobne dane dla wybranych zapytań.