SQLite의 초당 INSERT 성능 향상

프로그래밍 언어/Database

SQLite의 초당 INSERT 성능 향상

Rateye 2021. 7. 9. 10:23

728x90

질문 : SQLite의 초당 INSERT 성능 향상

SQLite를 최적화하는 것은 까다 롭습니다. C 애플리케이션의 대량 삽입 성능은 초당 85 개의 삽입에서 초당 96,000 개 이상의 삽입까지 다양합니다!

배경 : 우리는 데스크톱 애플리케이션의 일부로 SQLite를 사용하고 있습니다. 애플리케이션이 초기화 될 때 추가 처리를 위해 구문 분석되고 SQLite 데이터베이스에로드되는 XML 파일에 저장된 많은 양의 구성 데이터가 있습니다. SQLite는 빠르고 특별한 구성이 필요하지 않으며 데이터베이스가 단일 파일로 디스크에 저장되기 때문에 이러한 상황에 이상적입니다.

근거 : 처음에 나는 내가보고있는 성능에 실망했습니다. SQLite의 성능은 데이터베이스 구성 방법과 API 사용 방법에 따라 크게 다를 수 있습니다 (대량 삽입 및 선택 모두). 모든 옵션과 기술이 무엇인지 알아내는 것은 사소한 문제가 아니었기 때문에 동일한 조사의 문제를 다른 사람들에게 저장하기 위해 Stack Overflow 독자와 결과를 공유하는이 커뮤니티 위키 항목을 만드는 것이 현명하다고 생각했습니다.

실험 : 일반적인 의미의 성능 팁 (예 : "트랜잭션 사용!" )에 대해 간단히 이야기하기보다는 C 코드를 작성하고 실제로 다양한 옵션의 영향을 측정하는 것이 가장 좋다고 생각했습니다. 간단한 데이터로 시작하겠습니다.

토론토시의 전체 교통 일정에 대한 28MB TAB으로 구분 된 텍스트 파일 (약 865,000 개 레코드)
내 테스트 컴퓨터는 Windows XP를 실행하는 3.60GHz P4입니다.
이 코드는 Visual C ++ 2005에서 "Full Optimization"(/ Ox) 및 Favor Fast Code (/ Ot)가 포함 된 "Release"로 컴파일됩니다.
내 테스트 애플리케이션에 직접 컴파일 된 SQLite "Amalgamation"을 사용하고 있습니다. 내가 가지고있는 SQLite 버전은 약간 오래되었지만 (3.6.7),이 결과가 최신 릴리스와 비슷할 것이라고 생각합니다 (그렇지 않다고 생각되면 의견을 남겨주세요).

코드를 작성해 봅시다!

코드 : 텍스트 파일을 한 줄씩 읽고 문자열을 값으로 분할 한 다음 데이터를 SQLite 데이터베이스에 삽입하는 간단한 C 프로그램입니다. 이 "기준"버전의 코드에서는 데이터베이스가 생성되지만 실제로 데이터를 삽입하지는 않습니다.

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

코드를있는 그대로 실행하는 것은 실제로 데이터베이스 작업을 수행하지 않지만 원시 C 파일 I / O 및 문자열 처리 작업이 얼마나 빠른지 알 수 있습니다.

0.94 초에 864913 개의 레코드 가져 오기

큰! 실제로 삽입을하지 않는다면 초당 920,000 개의 삽입을 할 수 있습니다. :-)

파일에서 읽은 값을 사용하여 SQL 문자열을 생성하고 sqlite3_exec를 사용하여 해당 SQL 작업을 호출합니다.

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

SQL이 모든 삽입에 대해 VDBE 코드로 컴파일되고 모든 삽입이 자체 트랜잭션에서 발생하기 때문에 속도가 느려질 것입니다. 얼마나 느려요?

9933.61 초에 864913 개의 레코드를 가져옴

이런! 2 시간 45 분! 이는 초당 85 개의 삽입에 불과합니다.

기본적으로 SQLite는 고유 트랜잭션 내에서 모든 INSERT / UPDATE 문을 평가합니다. 많은 수의 삽입을 수행하는 경우 작업을 트랜잭션으로 래핑하는 것이 좋습니다.

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

38.03 초에 864913 개의 레코드를 가져옴

그게 낫다. 모든 인서트를 단일 트랜잭션으로 감싸는 것만으로도 성능이 초당 23,000 개의 인서트로 향상되었습니다.

트랜잭션을 사용하는 것은 큰 개선 이었지만 동일한 SQL을 반복해서 사용하는 경우 모든 삽입에 대해 SQL 문을 다시 컴파일하는 것은 의미가 없습니다. sqlite3_prepare_v2 를 사용 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;

16.27 초에 864913 개의 레코드 가져 오기

16.27 초에 864913 개의 레코드 가져 오기

좋은! 약간 더 많은 코드가 있지만 ( sqlite3_clear_bindings 및 sqlite3_reset 을 호출하는 것을 잊지 마십시오), 우리는 초당 53,000 개의 삽입으로 성능을 두 배 이상 향상 시켰습니다.

기본적으로 SQLite는 OS 수준 쓰기 명령을 실행 한 후 일시 중지됩니다. 이렇게하면 데이터가 디스크에 기록됩니다. synchronous = OFF 를 설정하면 SQLite에게 쓰기를 위해 데이터를 OS에 전달한 다음 계속하도록 지시합니다. 데이터가 플래터에 기록되기 전에 컴퓨터에 치명적인 충돌 (또는 정전)이 발생하면 데이터베이스 파일이 손상 될 가능성이 있습니다.

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

12.41 초에 864913 개의 레코드 가져 오기

이제 개선 사항은 더 작지만 초당 최대 69,600 개의 인서트가 있습니다.

PRAGMA journal_mode = MEMORY 평가하여 롤백 저널을 메모리에 저장하는 것을 고려하십시오. 트랜잭션은 더 빠르지 만 트랜잭션 중에 전원이 꺼 지거나 프로그램이 충돌하면 데이터베이스가 부분적으로 완료된 트랜잭션으로 손상된 상태로 남을 수 있습니다.

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

13.50 초에 864913 개의 레코드 가져 오기

초당 64,000 개의 삽입으로 이전 최적화보다 약간 느립니다.

이전 두 가지 최적화를 결합 해 보겠습니다. (크래시의 경우) 조금 더 위험하지만 우리는 데이터를 가져 오는 것입니다 (은행을 운영하지 않음) :

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

12.00 초에 864913 개의 레코드 가져 오기

환상적입니다! 초당 72,000 개의 삽입 을 할 수 있습니다.

킥을 위해, 이전의 모든 최적화를 기반으로하고 데이터베이스 파일 이름을 재정 의하여 전적으로 RAM에서 작업하도록하겠습니다.

#define DATABASE ":memory:"

10.94 초에 864913 개의 레코드 가져 오기

데이터베이스를 RAM에 저장하는 것은 실용적이지는 않지만 초당 79,000 개의 삽입을 수행 할 수 있다는 것이 인상적입니다.

특별히 SQLite 개선은 아니지만 while 루프 char* 할당 작업이 마음에 들지 않습니다. strtok() 의 출력을 sqlite3_bind_text() 직접 전달하도록 해당 코드를 빠르게 리팩터링하고 컴파일러가 작업 속도를 높이도록합니다.

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

참고 : 다시 실제 데이터베이스 파일을 사용합니다. 인 메모리 데이터베이스는 빠르지 만 반드시 실용적이지는 않습니다.

8.94 초 만에 864913 개의 레코드 가져 오기

매개 변수 바인딩에 사용 된 문자열 처리 코드를 약간 리팩토링하여 초당 96,700 회의 삽입을 수행 할 수있었습니다. 나는 이것이 충분히 빠르다고 말하는 것이 안전하다고 생각합니다. 다른 변수 (예 : 페이지 크기, 색인 생성 등)를 조정하기 시작하면 이것이 벤치 마크가 될 것입니다.

당신이 여전히 나와 함께 있기를 바랍니다! 우리가이 길을 시작하는 이유는 대량 삽입 성능이 SQLite에 따라 크게 다르며 작업 속도를 높이기 위해 어떤 변경이 필요한지 항상 명확하지 않기 때문입니다. 동일한 컴파일러 (및 컴파일러 옵션)를 사용하여 동일한 버전의 SQLite 및 동일한 데이터를 사용하여 코드와 SQLite 사용을 최적화하여 초당 85 개 삽입의 최악의 시나리오에서 초당 96,000 개 이상의 삽입으로 이동합니다!

SELECT 성능 측정을 시작하기 전에 인덱스를 생성 할 것임을 알고 있습니다. 아래 답변 중 하나에서 대량 삽입을 수행 할 때 데이터가 삽입 된 후 인덱스를 만드는 것이 더 빠르다는 것이 제안되었습니다 (먼저 인덱스를 만든 다음 데이터를 삽입하는 것과 반대). 해보자:

인덱스 생성 후 데이터 삽입

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

18.13 초에 864913 개의 레코드 가져 오기

데이터 삽입 후 색인 생성

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

13.66 초에 864913 개의 레코드 가져 오기

예상대로 한 열이 인덱싱되면 대량 삽입이 더 느리지 만 데이터가 삽입 된 후 인덱스가 생성되면 차이가 있습니다. 인덱스가없는 기준은 초당 96,000 개의 삽입입니다. 먼저 인덱스를 생성 한 다음 데이터를 삽입하면 초당 47,700 개의 삽입이 발생하는 반면 데이터를 먼저 삽입 한 다음 인덱스를 생성하면 초당 63,300 개의 삽입이 발생합니다.

시도해 볼 다른 시나리오에 대한 제안을 기꺼이 받아들입니다. 곧 SELECT 쿼리에 대해 유사한 데이터를 컴파일 할 것입니다.

답변

몇 가지 팁 :

트랜잭션에 INSERT/UPDATE를 넣습니다.
이전 버전의 SQLite - 편집성이 낮은 저널 모드(pragma journal_mode)를 고려합니다. NORMAL이 있고 OFF가 있습니다. 따라서 OS가 손상되더라도 데이터베이스가 손상될 수 있다는 우려가 크지 않으면 삽입 속도를 크게 높일 수 있습니다. 애플리케이션이 중단되더라도 데이터는 문제가 없습니다. 최신 버전에서는 OFF/MEMORY 설정이 애플리케이션 수준 충돌에 안전하지 않습니다.
페이지 크기를 가지고 노는 것도 차이를 만듭니다(PRAGMA page_size). 페이지 크기가 크면 큰 페이지가 메모리에 저장되므로 읽기 및 쓰기가 좀 더 빨라집니다. 데이터베이스에 더 많은 메모리가 사용됩니다.
인덱스가 있는 경우 모든 삽입을 수행한 후 CREATE INDEX 호출을 고려하십시오. 이것은 인덱스를 만든 다음 삽입을 수행하는 것보다 훨씬 빠릅니다.
쓰기가 완료되면 전체 데이터베이스가 잠기고 여러 판독기가 가능하지만 쓰기가 차단되므로 SQLite에 동시에 액세스할 수 있다면 매우 주의해야 합니다. 이 기능은 최신 SQLite 버전에 WAL이 추가되면서 다소 개선되었습니다.
공간 절약의 이점을 누려 보다 적은 데이터베이스로 더욱 빠르게 이동할 수 있습니다. 예를 들어, 키 값 쌍이 있는 경우 가능하면 키를 INTEGER Primary 키로 만들어 표의 묵시적인 고유 행 번호 열을 대체합니다.
여러 스레드를 사용하는 경우 공유 페이지 캐시를 사용해 볼 수 있습니다. 이렇게 하면 로드된 페이지를 스레드 간에 공유할 수 있으므로 비싼 I/O 호출을 방지할 수 있습니다.
feoff(파일) 사용하지 마!

여기 와 여기 에서도 비슷한 질문을했습니다.

출처 : https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite

728x90

'프로그래밍 언어 > Database' 카테고리의 다른 글

Sqlite3 데이터베이스에서 열 이름 목록을 얻는 방법 (0)	2021.07.12
관계형 데이터베이스에 계층 적 데이터를 저장하는 옵션 (0)	2021.07.12
PostgreSQL 데이터베이스로 SQL 덤프 가져 오기 (0)	2021.07.09
데이터베이스와 함께 애플리케이션 전송 하는 방법 (0)	2021.07.09
데이터베이스를 사용할 때 주의할 점 (0)	2021.07.08

현재글SQLite의 초당 INSERT 성능 향상

250x250

코딩하다 현타올 때마다 작성하러 오는 블로그

git, CSS, js, Python, Arrays, java, JavaScript, HTML, jQuery, c++, Android, node.js, php, Database, C#, json, github, Linux, version-control, String,

Today :
Yesterday :

코딩하다 현타올 때