Domanda Migliora le prestazioni INSERT al secondo di SQLite?


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!

Sfondo: 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.

Fondamento logico:  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 Stack Overflow al fine di salvare gli altri il problema delle stesse indagini.

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

  • Un file di testo delimitato da TAB da 28 MB (circa 865.000 record) di programma completo di transito per la città di Toronto
  • La mia macchina di prova è un P4 a 3,60 GHz con Windows XP.
  • Il codice è stato compilato con Visual C ++ 2005 come "Release" con "Full Optimization" (/ Ox) e Favor Fast Code (/ 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?

Record 864913 importati in 9933.61   secondi

Yikes! 2 ore e 45 minuti! Questo è 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 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 compilare la nostra istruzione SQL una volta e quindi associare i nostri parametri a quella 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 'di codice in più (non dimenticare di chiamare sqlite3_clear_bindings e sqlite3_reset), ma abbiamo più che raddoppiato le nostre prestazioni 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 lo stiamo facendo 69.600 inserti al secondo.

PRAGMA journal_mode = MEMORY

Valuta la possibilità di memorizzare il giornale di rollback in memoria 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);

Record 864913 importati in 13.50   secondi

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

PRAGMA sincrono = 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 farlo 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 in RAM, ma è impressionante che possiamo eseguire 79.000 inserti al secondo.

Codice di refactoring C

Anche se non specificamente un miglioramento SQLite, non mi piace l'extra char*operazioni di assegnazione nel while ciclo continuo. Cerchiamo rapidamente il codice per passare l'output di strtok() direttamente in sqlite3_bind_text()e lascia che il compilatore provi a velocizzare 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 al 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 esempio la dimensione della pagina, la 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 andare da uno 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 SELECT performance, 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.


2566


origine


risposte:


Diversi suggerimenti:

  1. Inserisci inserti / aggiornamenti in una transazione.
  2. Per le versioni precedenti di SQLite: prendere in considerazione una modalità journal meno paranoica (pragma journal_mode). C'è NORMALe poi c'è OFF, che può aumentare significativamente la velocità di inserimento se non si è troppo preoccupati che il database possa essere danneggiato se il sistema operativo si arresta in modo anomalo. Se l'applicazione si blocca, i dati dovrebbero andare bene. Si noti che nelle versioni più recenti, il OFF/MEMORY le impostazioni non sono sicure per arresti anomali a livello di applicazione.
  3. Anche giocare con le dimensioni delle pagine fa la differenza (PRAGMA page_size). Avere dimensioni di pagina più grandi può rendere le letture e le scritture un po 'più veloci mentre le pagine più grandi sono conservate in memoria. Si noti che verrà utilizzata più memoria per il database.
  4. Se hai indici, considera la possibilità di chiamare CREATE INDEXdopo aver fatto tutti i tuoi inserti. Questo è significativamente più veloce della creazione dell'indice e quindi dei tuoi inserimenti.
  5. Bisogna stare molto attenti se si ha accesso simultaneo a SQLite, poiché l'intero database è bloccato al termine delle scritture e, sebbene siano possibili più lettori, le scritture verranno bloccate. Questo è stato leggermente migliorato con l'aggiunta di un WAL nelle nuove versioni di SQLite.
  6. Approfitta del risparmio di spazio ... i database più piccoli diventano più veloci. Ad esempio, se hai coppie di valori chiave, prova a fare la chiave a INTEGER PRIMARY KEY se possibile, che sostituirà la colonna del numero di riga univoco implicito nella tabella.
  7. Se si utilizzano più thread, è possibile provare a utilizzare il cache della pagina condivisa, che consente di condividere pagine caricate tra thread, che possono evitare costose chiamate I / O.
  8. Non usare !feof(file)!

Ho anche fatto domande simili Qui e Qui.


671



Prova a usare SQLITE_STATIC invece di SQLITE_TRANSIENT per quegli inserti.

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

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.


102



Evita sqlite3_clear_bindings (stmt);

Il codice nel test imposta le associazioni ogni volta che dovrebbe essere sufficiente.

L'introduzione dell'API C dai documenti SQLite dice

Prima di chiamare sqlite3_step () per la prima volta o immediatamente   dopo sqlite3_reset (), l'applicazione può richiamare uno dei   sqlite3_bind () interfacce per allegare valori ai parametri. Ogni   la chiamata a sqlite3_bind () sovrascrive i bind precedenti sullo stesso parametro

(vedere: sqlite.org/cintro.html). Non c'è nulla nei documenti per quella funzione dicendo che devi chiamarlo oltre a impostare semplicemente i binding.

Più dettaglio: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


80



Sugli inserti sfusi

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

https://github.com/rdpoor/CreateOrUpdate

quale massa carica un array di ActiveRecords in MySQL, SQLite o PostgreSQL banche dati. 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.


47



Le importazioni di massa sembrano essere le migliori se riesci a ridurre il tuo INSERT / UPDATE dichiarazioni. Un valore di 10.000 circa ha funzionato bene per me su un tavolo con solo poche righe, YMMV ...


40



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)


32



Non ho ottenuto alcun guadagno dalle transazioni finché non ho aumentato cache_size a un valore più alto, ad es. PRAGMA cache_size=10000;


20