Migliora le prestazioni INSERT al secondo di SQLite?


Answers

Prova a utilizzare SQLITE_STATIC anziché SQLITE_TRANSIENT per questi inserti.

SQLITE_TRANSIENT farà sì che SQLite copi i dati di stringa prima di ritornare.

SQLITE_STATIC dice che l'indirizzo di memoria che hai dato sarà valido fino a quando la query è stata eseguita (che in questo ciclo è sempre il caso). Ciò consentirà di risparmiare diverse allocare, copiare e deallocare le operazioni per ciclo. Forse un grande miglioramento.

Question

L'ottimizzazione di SQLite è complicata. Le prestazioni di inserimento di massa di un'applicazione C possono variare da 85 inserti al secondo a oltre 96.000 inserti al secondo!

Background: stiamo usando SQLite come parte di un'applicazione desktop. Disponiamo di grandi quantità di dati di configurazione memorizzati in file XML che vengono analizzati e caricati in un database SQLite per ulteriori elaborazioni quando l'applicazione viene inizializzata. SQLite è l'ideale per questa situazione perché è veloce, non richiede alcuna configurazione specializzata e il database è archiviato su disco come un singolo file.

Motivazione: Inizialmente ero deluso dalla prestazione che stavo vedendo. Risulta che le prestazioni di SQLite possono variare in modo significativo (sia per gli inserimenti di massa che per le selezioni) a seconda della configurazione del database e del modo in cui si utilizza l'API. Non è stato semplice capire quali fossero tutte le opzioni e le tecniche, quindi ho pensato che fosse prudente creare questa voce wiki della comunità per condividere i risultati con i lettori al fine di salvare gli altri il problema delle stesse indagini.

L'esperimento: Piuttosto che parlare semplicemente di consigli per le prestazioni in senso generale (ad esempio "Usa una transazione!" ), Ho pensato che fosse meglio scrivere un codice C e misurare effettivamente l'impatto delle varie opzioni. Iniziamo con alcuni semplici dati:

  • Un file di testo delimitato da TAB da 28 MB (circa 865.000 record) del programma di transito completo per la città di Toronto
  • La mia macchina di prova è un P4 a 3,60 GHz con Windows XP.
  • Il codice è compilato con Visual C ++ 2005 come "Release" con "Full Optimization" (/ Ox) e Codice rapido Favor (/ Ot).
  • Sto usando SQLite "Amalgamation", compilato direttamente nella mia applicazione di test. La versione SQLite che mi capita di avere è un po 'più vecchia (3.6.7), ma ho il sospetto che questi risultati saranno paragonabili all'ultima versione (per favore lasciate un commento se pensate diversamente).

Scriviamo un po 'di codice!

Il codice: un semplice programma C che legge il file di testo riga per riga, divide la stringa in valori e quindi inserisce i dati in un database SQLite. In questa versione "di base" del codice, viene creato il database, ma in realtà non inseriremo i dati:

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

Il controllo"

L'esecuzione del codice as-is in realtà non esegue alcuna operazione di database, ma ci darà un'idea di quanto siano veloci le operazioni di I / O e di elaborazione stringa del file C.

Importati record 864913 in 0,94 secondi

Grande! Possiamo fare 920.000 inserti al secondo, a condizione che non inseriamo effettivamente alcun inserto :-)

Lo "Scenario peggiore"

Genereremo la stringa SQL utilizzando i valori letti dal file e invocheremo l'operazione SQL utilizzando 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);

Questo sarà lento perché l'SQL verrà compilato nel codice VDBE per ogni inserto e ogni inserimento avverrà nella propria transazione. Quanto è lento?

Importati record 864913 in 9933,61 secondi

Yikes! 2 ore e 45 minuti! Sono solo 85 inserti al secondo.

Utilizzando una transazione

Per impostazione predefinita, SQLite valuterà ogni istruzione INSERT / UPDATE all'interno di una transazione univoca. Se si esegue un numero elevato di inserti, è consigliabile avvolgere l'operazione in una transazione:

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

Importati record 864913 in 38.03 secondi

Così va meglio. Il semplice completamento di tutti i nostri inserti in un'unica transazione ha migliorato le nostre prestazioni a 23.000 inserti al secondo.

Utilizzando una dichiarazione preparata

L'utilizzo di una transazione è stato un enorme miglioramento, ma la ricompilazione dell'istruzione SQL per ogni inserto non ha senso se si utilizza lo stesso over-and-over SQL. Usiamo sqlite3_prepare_v2 per compilare la nostra istruzione SQL una volta e quindi leghiamo i nostri parametri a tale istruzione usando 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;

Importati record 864913 in 16.27 secondi

Bello! C'è un po 'più di codice (non dimenticare di chiamare sqlite3_clear_bindings e sqlite3_reset ), ma abbiamo più che raddoppiato le nostre prestazioni a 53.000 inserti al secondo.

PRAGMA sincrono = OFF

Per impostazione predefinita, SQLite si interrompe dopo l'emissione di un comando di scrittura a livello di sistema operativo. Ciò garantisce che i dati vengano scritti sul disco. Impostando synchronous = OFF , stiamo ordinando a SQLite di passare semplicemente i dati al sistema operativo per la scrittura e poi continuare. C'è una possibilità che il file di database possa essere danneggiato se il computer subisce un crash catastrofico (o interruzione di corrente) prima che i dati vengano scritti sul piatto:

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

Importati record 864913 in 12,41 secondi

I miglioramenti ora sono più piccoli, ma siamo fino a 69.600 inserti al secondo.

PRAGMA journal_mode = MEMORY

Considera di memorizzare il journal di rollback in memoria valutando PRAGMA journal_mode = MEMORY . La tua transazione sarà più veloce, ma se perdi energia o il tuo programma si blocca durante una transazione, il database potrebbe essere lasciato in uno stato corrotto con una transazione parzialmente completata:

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

Importati record 864913 in 1350 secondi

Un po 'più lento della precedente ottimizzazione a 64.000 inserti al secondo.

PRAGMA synchronous = OFF e PRAGMA journal_mode = MEMORY

Combiniamo le due ottimizzazioni precedenti. È un po 'più rischioso (in caso di crash), ma stiamo solo importando dati (non eseguendo una banca):

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

Importati record 864913 in 12,00 secondi

Fantastico! Siamo in grado di fare 72.000 inserti al secondo.

Utilizzo di un database in memoria

Solo per i calci, costruiamo su tutte le ottimizzazioni precedenti e ridefiniamo il nome del file del database in modo che stiamo lavorando interamente nella RAM:

#define DATABASE ":memory:"

Importati record 864913 in 10,94 secondi

Non è super-pratico archiviare il nostro database nella RAM, ma è impressionante che possiamo eseguire 79.000 inserti al secondo.

Codice di refactoring C

Sebbene non sia specificamente un miglioramento di SQLite, non mi piacciono le operazioni di assegnazione di char* extra char* nel ciclo while . sqlite3_bind_text() rapidamente quel codice per passare l'output di strtok() direttamente in sqlite3_bind_text() , e lasciamo che il compilatore provi ad accelerare le cose per noi:

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

Nota: torniamo a utilizzare un vero file di database. I database in memoria sono veloci, ma non necessariamente pratici

Record 864913 importati in 8,94 secondi

Un leggero refactoring del codice di elaborazione delle stringhe utilizzato nel nostro legame ai parametri ci ha permesso di eseguire 96.700 inserti al secondo. Penso che sia sicuro dire che questo è molto veloce . Mentre iniziamo a modificare altre variabili (ad es. Dimensioni della pagina, creazione dell'indice, ecc.) Questo sarà il nostro punto di riferimento.

Riassunto (finora)

Spero tu sia ancora con me! Il motivo per cui siamo partiti da questa strada è che le prestazioni degli inserimenti di massa variano così profondamente con SQLite, e non è sempre ovvio quali modifiche debbano essere apportate per accelerare le nostre operazioni. Utilizzando lo stesso compilatore (e le opzioni del compilatore), la stessa versione di SQLite e gli stessi dati abbiamo ottimizzato il nostro codice e il nostro utilizzo di SQLite per passare dallo scenario peggiore di 85 inserti al secondo a oltre 96.000 inserti al secondo!

CREATE INDEX quindi INSERT vs. INSERT quindi CREATE INDEX

Prima di iniziare a misurare le prestazioni SELECT , sappiamo che creeremo degli indici. È stato suggerito in una delle risposte seguenti che quando si eseguono gli inserimenti di massa, è più veloce creare l'indice dopo che i dati sono stati inseriti (anziché creare prima l'indice e poi inserire i dati). Proviamo:

Crea indice quindi Inserisci dati

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

Importati record 864913 in 18.13 secondi

Inserisci dati quindi Crea indice

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

Importati record 864913 in 13,66 secondi

Come previsto, gli inserimenti di massa sono più lenti se una colonna è indicizzata, ma fa la differenza se l'indice viene creato dopo l'inserimento dei dati. La nostra baseline senza indice è di 96.000 inserti al secondo. La creazione dell'indice prima dell'inserimento dei dati ci fornisce 47.700 inserti al secondo, mentre l'inserimento dei dati prima della creazione dell'indice ci dà 63.300 inserti al secondo.

Prenderei volentieri suggerimenti per altri scenari da provare ... e presto compileremo dati simili per le query SELECT.




Dopo aver letto questo tutorial, ho provato a implementarlo nel mio programma.

Ho 4-5 file che contengono indirizzi. Ogni file ha circa 30 milioni di record. Sto usando la stessa configurazione che stai suggerendo ma il mio numero di INSERT al secondo è molto basso (~ 10.000 record al secondo).

Qui è dove il tuo suggerimento fallisce. Si utilizza una singola transazione per tutti i record e un singolo inserimento senza errori / non riesce. Diciamo che stai dividendo ogni record in più inserti su tavoli diversi. Cosa succede se il record è rotto?

Il comando ON CONFLICT non si applica, perché se hai 10 elementi in un record e hai bisogno che ogni elemento sia inserito in una tabella diversa, se l'elemento 5 ottiene un errore CONSTRAINT, allora tutti i 4 inserti precedenti devono andare anche tu.

Quindi qui è dove arriva il rollback. L'unico problema con il rollback è che si perdono tutti gli inserti e si inizia dall'alto. Come puoi risolvere questo?

La mia soluzione era usare più transazioni. Inizio e concludo una transazione ogni 10.000 record (non chiedere perché quel numero, è stato il più veloce che ho testato). Ho creato un array di dimensioni 10.000 e inserire lì i record di successo. Quando si verifica l'errore, eseguo un rollback, inizio una transazione, inserisco i record dal mio array, eseguo il commit e quindi inizio una nuova transazione dopo il record interrotto.

Questa soluzione mi ha aiutato a superare i problemi che ho quando si tratta di file contenenti record non validi / duplicati (avevo quasi il 4% di record non validi).

L'algoritmo che ho creato mi ha aiutato a ridurre il mio processo di 2 ore. Processo di caricamento finale del file 1 ora e 30 minuti, che è ancora lento ma non paragonato alle 4 ore che inizialmente ha richiesto. Sono riuscito a velocizzare gli inserti da 10.000 / sa ~ 14.000 / s

Se qualcuno ha altre idee su come accelerarlo, sono aperto a suggerimenti.

AGGIORNAMENTO :

In aggiunta alla mia risposta sopra, dovresti tenere presente che inserti al secondo dipendono anche dal disco fisso che stai utilizzando. L'ho provato su 3 PC diversi con dischi rigidi diversi e ho avuto enormi differenze nei tempi. PC1 (1 ora e 30 minuti), PC2 (6 ore) PC3 (14 ore), quindi ho iniziato a chiedermi perché sarebbe stato così.

Dopo due settimane di ricerche e controllo di più risorse: Hard Drive, Ram, Cache, ho scoperto che alcune impostazioni sul disco rigido possono influire sulla frequenza I / O. Facendo clic sulle proprietà sull'unità di output desiderata è possibile visualizzare due opzioni nella scheda generale. Opt1: comprimere questa unità, Opt2: consentire ai file di questa unità di avere il contenuto indicizzato.

Disabilitando queste due opzioni, tutti e 3 i PC ora impiegano all'incirca lo stesso tempo (1 ora e 20-40 minuti). Se incontri inserimenti lenti, verifica se il tuo disco fisso è configurato con queste opzioni. Ti farà risparmiare un sacco di tempo e mal di testa cercando di trovare la soluzione







Sugli inserti sfusi

Ispirato da questo post e dalla domanda che mi ha portato qui - È possibile inserire più righe alla volta in un database SQLite? - Ho pubblicato il mio primo repository Git :

https://github.com/rdpoor/CreateOrUpdate

quale bulk carica un array di ActiveRecords in MySQL , SQLite o PostgreSQL . Include un'opzione per ignorare i record esistenti, sovrascriverli o generare un errore. I miei benchmark rudimentali mostrano un miglioramento della velocità di 10 volte rispetto alle scritture sequenziali: YMMV.

Lo sto usando nel codice di produzione in cui ho spesso bisogno di importare set di dati di grandi dimensioni, e sono abbastanza soddisfatto.




Se ti interessa solo leggere, una versione leggermente più veloce (ma potrebbe leggere dati obsoleti) è leggere da più connessioni da più thread (connessione per-thread).

Prima trova gli oggetti, nella tabella:

 SELECT COUNT(*) FROM table

quindi leggi nelle pagine (LIMIT / OFFSET)

  SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>

dove e sono calcolati per-thread, come questo:

int limit = (count + n_threads - 1)/n_threads;

per ogni filo:

int offset = thread_index * limit

Per il nostro db piccolo (200mb) questo ha reso più veloce il 50-75% (3.8.0.2 64-bit su Windows 7). Le nostre tabelle sono fortemente non normalizzate (1000-1500 colonne, circa 100.000 o più righe).

Troppi o troppo piccoli thread non lo faranno, è necessario fare un benchmark e un profilo.

Anche per noi, SHAREDCACHE ha rallentato le prestazioni, quindi ho inserito manualmente PRIVATECACHE (perché è stato abilitato globalmente per noi)