Question Améliorer les performances INSERT-par-seconde de SQLite?


L'optimisation de SQLite est difficile. Les performances d'insertion en masse d'une application C peuvent varier de 85 insertions par seconde à plus de 96 000 insertions par seconde!

Contexte: Nous utilisons SQLite dans le cadre d'une application de bureau. Nous avons de grandes quantités de données de configuration stockées dans des fichiers XML qui sont analysés et chargés dans une base de données SQLite pour un traitement ultérieur lorsque l'application est initialisée. SQLite est idéal pour cette situation car il est rapide, ne nécessite aucune configuration spécialisée et la base de données est stockée sur le disque en tant que fichier unique.

Raisonnement:  Au départ, j'étais déçu de la performance que je voyais. Il s'avère que les performances de SQLite peuvent varier de manière significative (à la fois pour les encarts en masse et les sélections) en fonction de la configuration de la base de données et de la manière dont vous utilisez l'API. Ce n'était pas une question triviale de comprendre quelles étaient toutes les options et techniques, donc j'ai pensé qu'il était prudent de créer cette entrée wiki communautaire pour partager les résultats avec les lecteurs Stack Overflow afin de sauver les autres des mêmes problèmes.

L'expérience: Plutôt que de simplement parler de conseils de performance au sens général (c.-à-d. "Utilisez une transaction!"), Je pensais qu'il valait mieux écrire du code C et mesure réellement l'impact de diverses options. Nous allons commencer avec quelques données simples:

  • Un fichier texte délimité par des tabulations de 28 Mo (environ 865 000 enregistrements) calendrier complet des transports en commun pour la ville de Toronto
  • Ma machine d'essai est un P4 fonctionnant sous Windows XP à 3.60 GHz.
  • Le code est compilé avec Visual C ++ 2005 en tant que "Release" avec "Full Optimization" (/ Ox) et Favor Fast Code (/ Ot).
  • J'utilise SQLite "Amalgamation", compilé directement dans mon application de test. La version de SQLite que j'ai est un peu plus ancienne (3.6.7), mais je soupçonne que ces résultats seront comparables à la dernière version (s'il vous plaît laissez un commentaire si vous pensez autrement).

Écrivons du code!

Le code: Un simple programme C qui lit le fichier texte ligne par ligne, divise la chaîne en valeurs, puis insère les données dans une base de données SQLite. Dans cette version «de base» du code, la base de données est créée, mais nous n'insérerons pas de données:

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

Le contrôle"

L'exécution du code tel qu'il est n'effectue aucune opération de base de données, mais elle nous donne une idée de la rapidité avec laquelle les opérations d'E / S et de traitement de chaîne de fichiers C sont exécutées.

Importés 864913 enregistrements dans 0,94   secondes

Génial! Nous pouvons faire 920 000 insertions par seconde, à condition de ne pas faire d'insertions :-)


Le "pire scénario"

Nous allons générer la chaîne SQL en utilisant les valeurs lues dans le fichier et appeler cette opération SQL en utilisant 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);

Cela va être lent parce que le SQL sera compilé dans le code VDBE pour chaque insertion et chaque insertion se produira dans sa propre transaction. Combien lent?

Imported 864913 enregistrements dans 9933.61   secondes

Yikes! 2 heures et 45 minutes! C'est seulement 85 inserts par seconde.

Utiliser une transaction

Par défaut, SQLite évalue chaque instruction INSERT / UPDATE dans une transaction unique. Si vous effectuez un grand nombre d'insertions, il est conseillé d'envelopper votre opération dans une transaction:

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

Importé 864913 enregistrements dans 38.03   secondes

C'est mieux. Envelopper simplement tous nos inserts dans une seule transaction a amélioré notre performance 23 000 inserts par seconde.

Utilisation d'une instruction préparée

L'utilisation d'une transaction était une amélioration considérable, mais la recompilation de l'instruction SQL pour chaque insertion n'a pas de sens si nous utilisons le même code SQL de manière répétée. Utilisons sqlite3_prepare_v2 pour compiler une fois notre instruction SQL, puis lier nos paramètres à cette instruction en utilisant 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;

Importé 864913 notices dans 16.27   secondes

Agréable! Il y a un peu plus de code (n'oubliez pas d'appeler sqlite3_clear_bindings et sqlite3_reset), mais nous avons plus que doublé notre performance 53 000 inserts par seconde.

PRAGMA synchrone = OFF

Par défaut, SQLite se met en pause après l'émission d'une commande d'écriture au niveau du système d'exploitation. Cela garantit que les données sont écrites sur le disque. En réglant synchronous = OFF, nous demandons à SQLite de simplement transférer les données au système d'exploitation pour les écrire, puis de continuer. Il est possible que le fichier de base de données soit corrompu si l'ordinateur subit un plantage catastrophique (ou une coupure de courant) avant que les données ne soient écrites sur le plateau:

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

Importés 864913 enregistrements dans 12.41   secondes

Les améliorations sont maintenant plus petites, mais nous sommes à la hauteur 69 600 inserts par seconde.

PRAGMA journal_mode = MEMOIRE

Envisagez de stocker le journal de restauration en mémoire en évaluant PRAGMA journal_mode = MEMORY. Votre transaction sera plus rapide, mais si vous perdez de la puissance ou si votre programme tombe en panne pendant une transaction, votre base de données pourrait être laissée dans un état corrompu avec une transaction partiellement terminée:

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

Importé 864913 notices dans la catégorie 13.50   secondes

Un peu plus lent que l'optimisation précédente à 64 000 inserts par seconde.

PRAGMA synchrone = OFF et PRAGMA journal_mode = MEMOIRE

Combinons les deux optimisations précédentes. C'est un peu plus risqué (en cas de crash), mais nous importons juste des données (pas une banque):

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

Importé 864913 enregistrements en 12.00   secondes

Fantastique! Nous sommes capables de faire 72 000 inserts par seconde.

Utilisation d'une base de données en mémoire

Juste pour les coups de pied, construisons sur toutes les optimisations précédentes et redéfinissons le nom de fichier de base de données afin que nous travaillions entièrement dans la RAM:

#define DATABASE ":memory:"

Importé 864913 notices dans 10.94   secondes

Ce n'est pas super pratique de stocker notre base de données en RAM, mais c'est impressionnant que nous puissions effectuer 79 000 inserts par seconde.

Refactoring C Code

Bien que pas spécifiquement une amélioration SQLite, je n'aime pas le supplément char*opérations d'affectation dans le while boucle. Refaisons rapidement ce code pour passer la sortie de strtok() directement dans sqlite3_bind_text(), et laissez le compilateur essayer d'accélérer les choses pour nous:

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

Note: Nous sommes de retour à l'aide d'un vrai fichier de base de données. Les bases de données en mémoire sont rapides, mais pas nécessairement pratiques

Importé 864913 notices dans 8.94   secondes

Un léger refactoring au code de traitement de chaîne utilisé dans notre liaison de paramètre nous a permis d'effectuer 96 700 insertions par seconde. Je pense que c'est sûr de dire que c'est beaucoup vite. Comme nous commençons à modifier d'autres variables (c'est-à-dire la taille de la page, la création de l'index, etc.), ce sera notre référence.


Résumé (jusqu'à présent)

J'espère que tu es toujours avec moi! La raison pour laquelle nous avons commencé cette route est que les performances d'encapsulation en masse varient énormément avec SQLite, et il n'est pas toujours évident quels changements doivent être faits pour accélérer notre opération. En utilisant le même compilateur (et les options du compilateur), la même version de SQLite et les mêmes données, nous avons optimisé notre code et notre utilisation de SQLite pour aller d'un scénario le plus défavorable de 85 insertions par seconde à plus de 96 000 insertions par seconde!


CREATE INDEX puis INSERT contre INSERT puis CREATE INDEX

Avant de commencer à mesurer SELECT performances, nous savons que nous allons créer des index. Il a été suggéré dans l'une des réponses ci-dessous que lorsque vous faites des insertions en masse, il est plus rapide de créer l'index après l'insertion des données (par opposition à la création de l'index puis à l'insertion des données). Essayons:

Créer un index puis insérer des données

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

Importé 864913 notices du 18.13   secondes

Insérer des données puis créer un index

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

Importés 864913 enregistrements dans 13.66   secondes

Comme prévu, les insertions groupées sont plus lentes si une colonne est indexée, mais cela fait une différence si l'index est créé après l'insertion des données. Notre base de référence sans index est de 96 000 insertions par seconde. La création de l'index en premier, puis l'insertion de données nous donne 47 700 insertions par seconde, alors que l'insertion des données en premier, puis la création de l'index nous donne 63 300 insertions par seconde.


Je prendrais volontiers des suggestions pour d'autres scénarios à essayer ... Et compilerai bientôt des données similaires pour les requêtes SELECT.


2568


origine


Réponses:


Plusieurs conseils:

  1. Mettre des insertions / mises à jour dans une transaction.
  2. Pour les anciennes versions de SQLite - Considérons un mode de journal moins paranoïaque (pragma journal_mode). Il y a NORMAL, et puis il y a OFF, ce qui peut augmenter considérablement la vitesse d'insertion si vous n'êtes pas trop inquiet de la corruption de la base de données si le système d'exploitation tombe en panne. Si votre application plante les données devraient être bien. Notez que dans les versions plus récentes, le OFF/MEMORY les paramètres ne sont pas sécurisés pour les plantages au niveau de l'application.
  3. Jouer avec les tailles de page fait aussi une différence (PRAGMA page_size). Avoir des tailles de page plus grandes peut rendre les lectures et les écritures aller un peu plus vite que les pages plus grandes sont gardées en mémoire. Notez que plus de mémoire sera utilisée pour votre base de données.
  4. Si vous avez des indices, pensez à appeler CREATE INDEXaprès avoir fait tous vos inserts. C'est nettement plus rapide que de créer l'index et de faire vos insertions.
  5. Vous devez être très prudent si vous avez un accès simultané à SQLite, car toute la base de données est verrouillée lorsque les écritures sont terminées et bien que plusieurs lecteurs soient possibles, les écritures seront verrouillées. Cela a été amélioré avec l'ajout d'un WAL dans les nouvelles versions de SQLite.
  6. Profitez de l'économie d'espace ... les bases de données plus petites vont plus vite. Par exemple, si vous avez des paires de valeurs clés, essayez de faire une clé INTEGER PRIMARY KEY si possible, qui remplacera la colonne de numéro de ligne unique implicite dans la table.
  7. Si vous utilisez plusieurs threads, vous pouvez essayer d'utiliser le cache de pages partagées, qui permettra aux pages chargées d'être partagées entre les threads, ce qui peut éviter les appels d'E / S coûteux.
  8. Ne pas utiliser !feof(file)!

J'ai aussi posé des questions similaires ici et ici.


672



Essayez d'utiliser SQLITE_STATIC au lieu de SQLITE_TRANSIENT pour ces inserts.

SQLITE_TRANSIENT entraînera SQLite à copier les données de chaîne avant de retourner.

SQLITE_STATIC lui indique que l'adresse mémoire que vous lui avez donnée sera valide jusqu'à ce que la requête ait été effectuée (ce qui dans cette boucle est toujours le cas). Cela vous permettra d'économiser plusieurs opérations d'allocation, de copie et de désaffectation par boucle. Peut-être une grande amélioration.


102



Évitez sqlite3_clear_bindings (stmt);

Le code du test définit les liaisons à chaque fois, ce qui devrait être suffisant.

L'intro de l'API C des documents SQLite indique

Avant d'appeler sqlite3_step () pour la première fois ou immédiatement   après sqlite3_reset (), l'application peut invoquer l'un des   sqlite3_bind () interfaces pour attacher des valeurs aux paramètres. Chaque   appel à sqlite3_bind () remplace les liaisons antérieures sur le même paramètre

(voir: sqlite.org/cintro.html). Il n'y a rien dans les docs pour cette fonction disant que vous devez l'appeler en plus de simplement définir les liaisons.

Plus de détails: http://www.hoogli.com/blogs/micro/index.html#Avoid_sqlite3_clear_bindings ()


80



Sur les encarts en vrac

Inspiré par ce post et par la question Stack Overflow qui m'a conduit ici - Est-il possible d'insérer plusieurs lignes à la fois dans une base de données SQLite? - J'ai posté mon premier Git dépôt:

https://github.com/rdpoor/CreateOrUpdate

qui charge en masse un tableau de ActiveRecords dans MySQL, SQLite ou PostgreSQL bases de données. Il comprend une option permettant d'ignorer les enregistrements existants, de les remplacer ou de déclencher une erreur. Mes benchmarks rudimentaires montrent une amélioration de la vitesse de 10x par rapport aux écritures séquentielles - YMMV.

Je l'utilise dans un code de production où j'ai souvent besoin d'importer de grands ensembles de données, et j'en suis plutôt content.


47



Importations en vrac semble fonctionner mieux si vous pouvez chunk votre INSERT / MISE À JOUR déclarations. Une valeur de 10 000 ou plus a bien fonctionné pour moi sur une table avec seulement quelques lignes, YMMV ...


40



Si vous vous souciez seulement de la lecture, la version un peu plus rapide (mais peut lire des données périmées) est de lire à partir de plusieurs connexions à partir de plusieurs threads (connexion par thread).

Trouvez d'abord les éléments, dans le tableau:

 SELECT COUNT(*) FROM table

puis lire dans les pages (LIMIT / OFFSET)

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

où et sont calculés par thread, comme ceci:

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

pour chaque fil:

int offset = thread_index * limit

Pour notre petit format (200 Mo), cela fait 50 à 75% d'accélération (3.8.0.2 64 bits sur Windows 7). Nos tables sont fortement non normalisées (1000-1500 colonnes, environ 100.000 lignes ou plus).

Trop ou trop peu de threads ne le feront pas, vous devez vous benchmarker et vous profiler.

Aussi pour nous, SHAREDCACHE a rendu la performance plus lente, donc j'ai manuellement mis PRIVATECACHE (car il était activé globalement pour nous)


32



Je n'ai aucun gain sur les transactions avant d'avoir augmenté cache_size à une valeur plus élevée, c'est-à-dire PRAGMA cache_size=10000;


20