c - fast - sqlite many inserts




Melhorar o desempenho INSERT-por-segundo do SQLite? (6)

Em inserções em massa

Inspirado por este post e pela pergunta Stack Overflow que me levou até aqui - é possível inserir várias linhas de cada vez em um banco de dados SQLite? - Eu postei meu primeiro repositório Git :

https://github.com/rdpoor/CreateOrUpdate

qual bulk carrega uma matriz de ActiveRecords em PostgreSQL dados MySQL , SQLite ou PostgreSQL . Inclui uma opção para ignorar registros existentes, sobrescrevê-los ou gerar um erro. Meus benchmarks rudimentares mostram uma melhoria de velocidade de 10x em comparação com gravações sequenciais - YMMV.

Eu estou usando isso no código de produção, onde eu freqüentemente preciso importar grandes conjuntos de dados, e estou muito feliz com isso.

Otimizar o SQLite é complicado. O desempenho de inserção em massa de um aplicativo C pode variar de 85 inserções por segundo a mais de 96.000 inserções por segundo!

Background: Estamos usando o SQLite como parte de um aplicativo de desktop. Temos grandes quantidades de dados de configuração armazenados em arquivos XML que são analisados ​​e carregados em um banco de dados SQLite para processamento adicional quando o aplicativo é inicializado. O SQLite é ideal para essa situação porque é rápido, não requer configuração especializada e o banco de dados é armazenado em disco como um único arquivo.

Fundamentação da petição: Inicialmente, fiquei desapontado com o desempenho que estava a ver. Acontece que o desempenho do SQLite pode variar significativamente (tanto para inserções em massa quanto para seleções) dependendo de como o banco de dados está configurado e de como você está usando a API. Não foi uma questão trivial descobrir quais eram todas as opções e técnicas, por isso achei prudente criar essa entrada de wiki da comunidade para compartilhar os resultados com os leitores do Stack Overflow, a fim de salvar os outros o problema das mesmas investigações.

A Experiência: Em vez de simplesmente falar sobre dicas de desempenho no sentido geral (ou seja, "Usar uma transação!" ), Achei melhor escrever algum código em C e realmente medir o impacto de várias opções. Vamos começar com alguns dados simples:

  • Um arquivo de texto delimitado por TAB de 28 MB (aproximadamente 865.000 registros) da programação completa de trânsito para a cidade de Toronto
  • Minha máquina de teste é uma P4 de 3,60 GHz com o Windows XP.
  • O código é compilado com o Visual C ++ 2005 como "Release" com "Full Optimization" (/ Ox) e Favor Fast Code (/ Ot).
  • Estou usando o "Amalgamation" do SQLite, compilado diretamente no meu aplicativo de teste. A versão SQLite que eu tenho é um pouco mais antiga (3.6.7), mas eu suspeito que esses resultados serão comparáveis ​​com a versão mais recente (por favor, deixe um comentário se você pensar de outra forma).

Vamos escrever um código!

O código: Um programa C simples que lê o arquivo de texto linha por linha, divide a string em valores e insere os dados em um banco de dados SQLite. Nesta versão "baseline" do código, o banco de dados é criado, mas na verdade não inseriremos dados:

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

O controle"

Executar o código como-é, na verdade, não executa nenhuma operação de banco de dados, mas nos dará uma idéia de quão rápidas são as operações de E / S de arquivo C e de processamento de string.

Importado 864913 registros em 0.94 segundos

Ótimo! Podemos fazer 920.000 inserções por segundo, desde que não façamos inserções :-)

O "pior cenário"

Nós vamos gerar a string SQL usando os valores lidos do arquivo e invocar essa operação SQL usando 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);

Isso será lento porque o SQL será compilado no código VDBE para cada inserção e cada inserção ocorrerá em sua própria transação. Quão lento?

Importado 864913 registros em 9933.61 segundos

Yikes! 2 horas e 45 minutos! Isso é apenas 85 inserções por segundo.

Usando uma transação

Por padrão, o SQLite avaliará cada instrução INSERT / UPDATE em uma transação exclusiva. Se estiver executando um grande número de inserções, é aconselhável envolver sua operação em uma transação:

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

Importado 864913 registros em 38,03 segundos

Isso é melhor. O simples envolvimento de todas as nossas inserções em uma única transação melhorou nosso desempenho para 23.000 inserções por segundo.

Usando uma declaração preparada

Usar uma transação foi uma grande melhoria, mas recompilar a instrução SQL para cada inserção não faz sentido se usarmos o mesmo SQL over-and-over. Vamos usar sqlite3_prepare_v2 para compilar nossa instrução SQL uma vez e, em seguida, ligar nossos parâmetros a essa instrução 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;

Importado 864913 registros em 16.27 segundos

Agradável! Há um pouco mais de código (não se esqueça de chamar sqlite3_clear_bindings e sqlite3_reset ), mas mais que dobramos nosso desempenho para 53.000 inserções por segundo.

PRAGMA synchronous = OFF

Por padrão, o SQLite fará uma pausa após a emissão de um comando de gravação no nível do sistema operacional. Isso garante que os dados sejam gravados no disco. Definindo synchronous = OFF , estamos instruindo o SQLite a simplesmente transferir os dados para o SO para gravação e continuar. Há uma chance de o arquivo de banco de dados ser corrompido se o computador sofrer uma falha catastrófica (ou falta de energia) antes que os dados sejam gravados no disco:

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

Importado 864913 registros em 12.41 segundos

As melhorias agora são menores, mas chegamos a 69.600 inserções por segundo.

PRAGMA journal_mode = MEMORY

Considere armazenar o diário de reversão na memória, avaliando PRAGMA journal_mode = MEMORY . Sua transação será mais rápida, mas se você perder energia ou seu programa travar durante uma transação, seu banco de dados pode ficar em um estado corrompido com uma transação parcialmente concluída:

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

Importados 864913 registros em 13,50 segundos

Um pouco mais lento que a otimização anterior em 64.000 inserções por segundo.

PRAGMA synchronous = OFF e PRAGMA journal_mode = MEMORY

Vamos combinar as duas otimizações anteriores. É um pouco mais arriscado (em caso de falha), mas estamos apenas importando dados (não executando um banco):

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

Importados 864913 registros em 12,00 segundos

Fantástico! Conseguimos fazer 72.000 inserções por segundo.

Usando um banco de dados na memória

Apenas por brincadeira, vamos aproveitar todas as otimizações anteriores e redefinir o nome do arquivo de banco de dados, então estamos trabalhando inteiramente na RAM:

#define DATABASE ":memory:"

Importado 864913 registros em 10.94 segundos

Não é super prático armazenar nosso banco de dados na RAM, mas é impressionante que possamos executar 79.000 inserções por segundo.

Refatorando o Código C

Embora não seja especificamente uma melhoria do SQLite, não gosto das operações de atribuição extra char* no loop while. Vamos refatorar rapidamente esse código para passar a saída de strtok() diretamente para sqlite3_bind_text() , e deixar o compilador tentar acelerar as coisas para nós:

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: Estamos de volta ao uso de um arquivo de banco de dados real. Bancos de dados na memória são rápidos, mas não necessariamente práticos

Importado 864913 registros em 8.94 segundos

Uma pequena refatoração para o código de processamento de string usado em nossa associação de parâmetro nos permitiu executar 96.700 inserções por segundo. Eu acho que é seguro dizer que isso é muito rápido . À medida que começamos a ajustar outras variáveis ​​(ou seja, tamanho da página, criação de índice, etc.), este será o nosso benchmark.

Resumo (até agora)

Espero que você ainda esteja comigo! A razão pela qual começamos esse caminho é que o desempenho de inserção em massa varia de maneira tão violenta com o SQLite, e nem sempre é óbvio que mudanças precisam ser feitas para acelerar nossa operação. Usando o mesmo compilador (e opções de compilador), a mesma versão do SQLite e os mesmos dados, otimizamos nosso código e nosso uso do SQLite para passar do pior cenário de 85 inserções por segundo para mais de 96.000 inserções por segundo!

CRIE O ÍNDICE e depois INSERT vs. INSERT e depois CREATE INDEX

Antes de começarmos a medir o desempenho do SELECT , sabemos que estaremos criando índices. Foi sugerido em uma das respostas abaixo que, ao fazer inserções em massa, é mais rápido criar o índice depois que os dados foram inseridos (em vez de criar o índice primeiro e depois inserir os dados). Vamos tentar:

Criar índice e inserir dados

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

Importado 864913 registros em 18.13 segundos

Inserir dados e criar índice

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

Importado 864913 registros em 13.66 segundos

Como esperado, as inserções em massa são mais lentas se uma coluna é indexada, mas faz diferença se o índice for criado após a inserção dos dados. Nossa linha de base sem índice é de 96.000 inserções por segundo. Criando o índice primeiro, em seguida, inserir dados nos dá 47.700 inserções por segundo, enquanto inserindo os dados primeiro, em seguida, criar o índice nos dá 63.300 inserções por segundo.

Eu ficaria feliz em receber sugestões para outros cenários para tentar ... E estará compilando dados semelhantes para consultas SELECT em breve.



Eu não poderia obter nenhum ganho de transações até que eu aumentei cache_size para um valor mais alto, ou seja, PRAGMA cache_size=10000;


Importações em massa parecem ter o melhor desempenho se você puder dividir suas instruções INSERT / UPDATE . Um valor de 10.000 ou mais funcionou bem para mim em uma tabela com apenas algumas linhas, YMMV ...


Tente usar SQLITE_STATIC vez de SQLITE_TRANSIENT para essas inserções.

SQLITE_TRANSIENT fará com que o SQLite copie os dados da string antes de retornar.

SQLITE_STATIC informa que o endereço de memória que você forneceu será válido até que a consulta seja executada (o que neste loop é sempre o caso). Isso economizará várias alocações, cópias e desalocações de operações por loop. Possivelmente uma grande melhoria.


Várias dicas:

  1. Coloque inserções / atualizações em uma transação.
  2. Para versões mais antigas do SQLite - Considere um modo de diário menos paranóico ( pragma journal_mode ). Há NORMAL e, em seguida, há OFF , o que pode aumentar significativamente a velocidade de inserção, se você não estiver muito preocupado com a possível corrupção do banco de dados se o sistema operacional falhar. Se o seu aplicativo travar, os dados devem ficar bem. Observe que, em versões mais recentes, as configurações OFF/MEMORY não são seguras para falhas no nível do aplicativo.
  3. Jogar com tamanhos de página também faz diferença ( PRAGMA page_size ). Ter tamanhos de página maiores pode fazer com que as leituras e gravações sejam um pouco mais rápidas à medida que as páginas maiores são mantidas na memória. Note que mais memória será usada para o seu banco de dados.
  4. Se você tiver índices, considere chamar CREATE INDEX depois de fazer todas as suas inserções. Isso é significativamente mais rápido do que criar o índice e, em seguida, fazer suas inserções.
  5. Você precisa ter bastante cuidado se tiver acesso simultâneo ao SQLite, pois o banco de dados inteiro é bloqueado quando as gravações são concluídas e, embora vários leitores sejam possíveis, as gravações serão bloqueadas. Isso foi melhorado um pouco com a adição de um WAL em versões mais recentes do SQLite.
  6. Aproveite a economia de espaço ... bancos de dados menores são mais rápidos. Por exemplo, se você tiver pares de valores de chave, tente tornar a chave uma INTEGER PRIMARY KEY se possível, que substituirá a coluna de número de linha exclusiva implícita na tabela.
  7. Se você estiver usando vários encadeamentos, poderá tentar usar o cache de páginas compartilhadas , o que permitirá que páginas carregadas sejam compartilhadas entre encadeamentos, o que pode evitar chamadas caras de E / S.
  8. Não use !feof(file) !

Eu também fiz perguntas semelhantes here e here .





optimization