Comment améliorer les performances d'INSERT par seconde de SQLite ?

Question

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

Le 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ées et chargées dans une base de données SQLite pour un traitement ultérieur lorsque l'application est initialisée. SQLite est idéal dans cette situation parce qu'il est rapide, qu'il ne nécessite aucune configuration spécialisée et que la base de données est stockée sur le disque dans un seul fichier.

Justification : Au départ, j'étais déçu par la performance que je voyais. Il s'avère que les performances de SQLite peuvent varier de manière significative (tant pour les insertions en masse que pour les sélections) en fonction de la configuration de la base de données et de la manière dont vous utilisez l'API. Il n'a pas été facile de déterminer toutes les options et techniques possibles, aussi ai-je jugé prudent de créer cette entrée wiki communautaire pour partager les résultats avec les lecteurs de l'OS afin d'éviter à d'autres de faire les mêmes recherches.

L'expérience : Plutôt que de parler simplement de conseils de performance au sens général (c'est à dire "Utilisez une transaction !" ), j'ai pensé qu'il valait mieux écrire un peu de code C et mesurer réellement l'impact des différentes options. Nous allons commencer par des données simples :

• Un fichier texte de 28 mégaoctets délimité par des tabulations (environ 865 000 enregistrements) contenant les données suivantes horaire complet des transports en commun de la ville de Toronto
• Ma machine de test est un P4 de 3,60 GHz fonctionnant sous Windows XP.
• Le code est compilé avec MSVC 2005 comme "Release" avec "Full Optimization" (/Ox) et Favor Fast Code (/Ot).
• J'utilise le 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 pense que ces résultats seront comparables à ceux de la dernière version (veuillez laisser un commentaire si vous pensez le contraire).

Écrivons du code !

Le code : Un simple programme C qui lit le fichier texte ligne par ligne, divise la chaîne de caractères en valeurs et insère ensuite 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érons pas réellement 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 quel n'effectue aucune opération sur la base de données, mais elle nous donne une idée de la rapidité des opérations brutes d'entrée/sortie de fichiers en C et de traitement des chaînes de caractères.

Importation de 864913 enregistrements en 0,94 secondes

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

---

Le "pire des scénarios".

Nous allons générer la chaîne SQL en utilisant les valeurs lues dans le fichier et invoquer 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 car le SQL sera compilé en code VDBE pour chaque insertion et chaque insertion se fera dans sa propre transaction. Lent comment ?

Importation de 864913 enregistrements en 9933.61 secondes

Oups ! 1 heure et 45 minutes ! C'est seulement 85 insertions par seconde.

Utilisation d'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);

Importation de 864913 enregistrements en 38,03 secondes

C'est mieux. Le simple fait de regrouper toutes nos insertions dans une seule transaction a amélioré nos performances. 23 000 insertions par seconde.

Utilisation d'une déclaration préparée

L'utilisation d'une transaction était une amélioration considérable, mais la recompilation de la déclaration SQL pour chaque insertion n'a pas de sens si nous utilisons le même SQL à plusieurs reprises. Utilisons sqlite3_prepare_v2 pour compiler notre déclaration SQL une fois et ensuite lier nos paramètres à cette déclaration en utilisant sqlite3_bind_text :

/* Open input file and import into 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;

Importation de 864913 enregistrements en 16,27 secondes

Joli ! 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é nos performances pour atteindre les objectifs suivants 53 000 insertions 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 définissant synchronous = OFF nous demandons à SQLite de simplement transmettre les données au système d'exploitation pour qu'il les écrive, puis de continuer. Il est possible que le fichier de la base de données soit corrompu si l'ordinateur subit un crash catastrophique (ou une panne 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);

Importation de 864913 enregistrements en 12,41 secondes

Les améliorations sont maintenant moins importantes, mais nous en sommes à 69 600 insertions par seconde.

PRAGMA journal_mode = MEMORY

Envisagez de stocker le journal de rollback en mémoire en évaluant PRAGMA journal_mode = MEMORY . Votre transaction sera plus rapide, mais si vous perdez le courant ou si votre programme se bloque pendant une transaction, votre base de données peut se retrouver dans un état corrompu avec une transaction partiellement achevé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);

Importation de 864913 enregistrements en 13,50 secondes

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

PRAGMA synchrone = OFF et PRAGMA journal_mode = MEMORY

Combinons les deux optimisations précédentes. C'est un peu plus risqué (en cas de crash), mais nous ne faisons qu'importer des données (nous ne faisons pas tourner 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);

Importation de 864913 enregistrements en 12,00 secondes

Fantastique ! Nous sommes en mesure de faire 72 000 insertions par seconde.

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

Juste pour le plaisir, basons-nous sur toutes les optimisations précédentes et redéfinissons le nom de fichier de la base de données afin de travailler entièrement en RAM :

#define DATABASE ":memory:"

Importation de 864913 enregistrements en 10,94 secondes

Il n'est pas très pratique de stocker notre base de données dans la RAM, mais il est impressionnant de constater que nous pouvons exécuter 79 000 insertions par seconde.

Refactoring du code C

Bien qu'il ne s'agisse pas d'une amélioration spécifique de SQLite, je n'aime pas l'ajout de l'option char* les opérations d'affectation dans le while boucle. Refactorons rapidement ce code pour passer la sortie de strtok() directement dans sqlite3_bind_text() et laisser 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 revenus à l'utilisation d'un vrai fichier de base de données. Les bases de données en mémoire sont rapides mais pas nécessairement pratiques.

Importation de 864913 enregistrements en 8,94 secondes

Un léger remaniement du code de traitement des chaînes de caractères utilisé dans notre liaison de paramètres nous a permis d'effectuer les opérations suivantes 96 700 insertions par seconde. Je pense qu'il est sûr de dire que c'est très rapide . Lorsque nous commencerons à modifier d'autres variables (taille des pages, création d'index, etc.), ce sera notre point de référence.

---

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

J'espère que vous êtes toujours avec moi ! La raison pour laquelle nous nous sommes engagés dans cette voie est que les performances de l'insertion en masse varient énormément avec SQLite et qu'il n'est pas toujours évident de savoir quels changements doivent être apportés pour accélérer notre opération. En utilisant le même compilateur (et les mêmes options de compilation), la même version de SQLite et les mêmes données, nous avons optimisé notre code et notre utilisation de SQLite pour qu'il devienne d'un scénario catastrophe de 85 insertions par seconde à plus de 96 000 insertions par seconde !

---

CREATE INDEX puis INSERT vs. INSERT puis CREATE INDEX

Avant de commencer à mesurer SELECT nous savons que nous allons créer des index. Il a été suggéré dans l'une des réponses ci-dessous que lors d'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 d'abord, 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);
...

Importation de 864913 enregistrements en 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);

Importation de 864913 enregistrements en 13,66 secondes

Comme prévu, les insertions en masse 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 indexation est de 96 000 insertions par seconde. En créant d'abord l'index puis en insérant les données, on obtient 47 700 insertions par seconde, tandis qu'en insérant d'abord les données puis en créant l'index, on obtient 63 300 insertions par seconde.

---

Je serais heureux de recevoir des suggestions pour d'autres scénarios à essayer... Et je compilerai bientôt des données similaires pour les requêtes SELECT.

Answer 1

Si vous ne vous souciez que de la lecture, une version un peu plus rapide (mais qui risque de lire des données périmées) consiste à lire à partir de plusieurs connexions de plusieurs threads (connexion par thread).

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

SELECT COUNT(*) FROM table

puis lire en 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 petite base de données (200 Mo), l'accélération a été de 50 à 75 % (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 fils ne suffiront pas, vous devez vous évaluer et vous profiler.

De plus, pour nous, SHAREDCACHE a ralenti les performances, donc j'ai mis manuellement PRIVATECACHE (parce qu'il a été activé globalement pour nous).

Answer 2

La sélection des performances est l'autre aspect de la question, celui qui m'intéresse le plus et la raison pour laquelle j'ai décidé d'utiliser le système. amour SQLite . J'ai vu plus de 100 000 sélections par seconde dans mon application C++, avec une jointure à trois voies sur une table de 50 Mo. C'est évidemment après un temps de "chauffe" suffisant pour que les tables soient placées dans le cache des pages de Linux, mais c'est quand même une performance étonnante !

Historiquement SQLite a eu du mal à sélectionner les indices à utiliser pour une jointure, mais la situation s'est améliorée, au point que je ne le remarque plus. Une utilisation prudente des indices est évidemment importante. Parfois, le simple fait de réorganiser les paramètres d'une SÉLECTIONNER La déclaration peut également faire une grande différence.

Answer 3

Je n'ai pas pu obtenir de gain sur les transactions jusqu'à ce que j'augmente la taille du cache à une valeur plus élevée, c'est à dire PRAGMA cache_size=10000;

Answer 4

Après avoir lu ce tutoriel, j'ai essayé de l'appliquer à mon programme.

J'ai 4-5 fichiers qui contiennent des adresses. Chaque fichier contient environ 30 millions d'enregistrements. J'utilise la même configuration que celle que vous suggérez, mais le nombre d'INSERTs par seconde est très faible (~10.000 enregistrements par seconde).

C'est là que votre suggestion échoue. Vous utilisez une seule transaction pour tous les enregistrements et une seule insertion sans erreurs/échecs. Disons que vous divisez chaque enregistrement en plusieurs insertions sur différentes tables. Que se passe-t-il si l'enregistrement est rompu ?

La commande ON CONFLICT ne s'applique pas, car si vous avez 10 éléments dans un enregistrement et que vous avez besoin que chaque élément soit inséré dans une table différente, si l'élément 5 obtient une erreur de CONSTRAINT, alors les 4 insertions précédentes doivent aussi disparaître.

C'est donc ici qu'intervient le retour en arrière. Le seul problème avec le retour en arrière est que vous perdez toutes vos insertions et recommencez depuis le début. Comment pouvez-vous résoudre ce problème ?

Ma solution était d'utiliser multiple transactions. Je commence et termine une transaction tous les 10 000 enregistrements (ne demandez pas pourquoi ce nombre, c'est le plus rapide que j'ai testé). J'ai créé un tableau de taille 10.000 et j'y insère les enregistrements réussis. Lorsque l'erreur se produit, je fais un rollback, je commence une transaction, j'insère les enregistrements de mon tableau, je fais un commit et je commence une nouvelle transaction après l'enregistrement cassé.

Cette solution m'a permis de contourner les problèmes que je rencontre lorsque je traite des fichiers contenant des enregistrements erronés/dupliqués (j'avais presque 4 % d'enregistrements erronés).

L'algorithme que j'ai créé m'a permis de réduire mon processus de 2 heures. Le processus de chargement final du fichier est de 1 h 30, ce qui est encore lent, mais pas comparé aux 4 heures qu'il fallait initialement. J'ai réussi à accélérer les insertions de 10.000/s à ~14.000/s.

Si quelqu'un a d'autres idées pour accélérer le processus, je suis ouvert aux suggestions.

UPDATE :

En plus de ma réponse ci-dessus, vous devez garder à l'esprit que les insertions par seconde dépendent également du disque dur que vous utilisez. Je l'ai testé sur 3 PC différents avec des disques durs différents et j'ai obtenu des différences massives dans les temps. PC1 (1hr 30m), PC2 (6hrs) PC3 (14hrs), alors j'ai commencé à me demander pourquoi.

Après deux semaines de recherche et de vérification de multiples ressources : Disque dur, Ram, Cache, j'ai découvert que certains paramètres de votre disque dur peuvent affecter le taux d'E/S. En cliquant sur propriétés sur votre disque de sortie désiré, vous pouvez voir deux options dans l'onglet général. Opt1 : Compresser ce disque, Opt2 : Permettre aux fichiers de ce disque d'avoir un contenu indexé.

En désactivant ces deux options, les trois PC mettent à peu près le même temps pour terminer (1 heure et 20 à 40 minutes). Si vous rencontrez des insertions lentes, vérifiez si votre disque dur est configuré avec ces options. Cela vous épargnera beaucoup de temps et de maux de tête pour trouver la solution.

Answer 5

La réponse à votre question est que le nouveau SQLite 3 a amélioré les performances, utilisez-le.

Cette réponse Pourquoi les insertions de SQLAlchemy avec sqlite sont-elles 25 fois plus lentes qu'avec sqlite3 directement ? par SqlAlchemy Orm Author a 100k inserts en 0.5 sec, et j'ai vu des résultats similaires avec python-sqlite et SqlAlchemy. Ce qui me porte à croire que les performances ont été améliorées avec SQLite 3.