SQLite 최적화는 까다롭습니다. C 애플리케이션의 대량 삽입 성능은 초당 85개에서 초당 96,000개 이상까지 다양합니다!
배경: 우리는 데스크톱 애플리케이션의 일부로 SQLite를 사용하고 있습니다. 애플리케이션이 초기화될 때 추가 처리를 위해 구문 분석되고 SQLite 데이터베이스에 로드되는 XML 파일에 저장된 많은 양의 구성 데이터가 있습니다. SQLite는 빠르고 특수한 구성이 필요하지 않으며 데이터베이스가 단일 파일로 디스크에 저장되기 때문에 이러한 상황에 이상적입니다.
근거: 처음에는 내가 보고 있는 성능에 실망했습니다. SQLite의 성능은 데이터베이스 구성 방식과 API 사용 방식에 따라 크게 달라질 수 있습니다(대량 삽입 및 선택 모두). 모든 옵션과 기술이 무엇인지 파악하는 것은 간단한 문제가 아니므로 동일한 조사의 수고를 다른 사람들에게 저장하기 위해 스택 오버플로 독자와 결과를 공유하기 위해 이 커뮤니티 위키 항목을 만드는 것이 현명하다고 생각했습니다.
실험: 일반적인 의미(예: "트랜잭션 사용!" )의 성능 팁에 대해 단순히 이야기하기 보다는 일부 C 코드를 작성하고 다양한 옵션의 영향을 실제로 측정하는 것이 가장 좋다고 생각했습니다. 몇 가지 간단한 데이터부터 시작하겠습니다.
- 토론토 시의 전체 대중 교통 일정에 대한 28MB TAB 구분 텍스트 파일(약 865,000개 레코드)
- 내 테스트 컴퓨터는 Windows XP를 실행하는 3.60GHz P4입니다.
- 코드는 Visual C++ 2005에서 "전체 최적화"(/Ox) 및 Favor Fast Code(/Ot)가 포함된 "릴리스"로 컴파일됩니다.
- 테스트 응용 프로그램에 직접 컴파일된 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개의 레코드를 가져왔습니다.
멋진! sqlite3_clear_bindings 및 sqlite3_reset 호출하는 것을 잊지 마세요 ), 초당 53,000개 삽입으로 성능을 두 배 이상 늘렸습니다.
PRAGMA 동기 = OFF
기본적으로 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 저널 모드 = 메모리
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개의 삽입으로 이전 최적화보다 약간 느립니다.
PRAGMA 동기 = OFF 및 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 synchronous = OFF", NULL, NULL, &sErrMsg); sqlite3_exec(db, "PRAGMA journal_mode = MEMORY", NULL, NULL, &sErrMsg);12초 만에 864913개의 레코드를 가져왔습니다.
환상적이야! 초당 72,000개의 삽입 을 수행할 수 있습니다.
메모리 내 데이터베이스 사용
간단히 말해서 이전의 모든 최적화를 기반으로 하고 데이터베이스 파일 이름을 재정의하여 완전히 RAM에서 작업하도록 하겠습니다.
#define DATABASE ":memory:"10.94초 동안 864913개의 레코드를 가져왔습니다.
데이터베이스를 RAM에 저장하는 것은 그다지 실용적이지 않지만 초당 79,000개의 삽입을 수행할 수 있다는 것은 인상적입니다.
C 코드 리팩토링
특별히 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개 이상의 삽입으로 전환했습니다!
CREATE INDEX 다음 INSERT 대 INSERT 다음 CREATE INDEX
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 쿼리에 대해 유사한 데이터를 컴파일할 것입니다.