SQLite를 최적화하는 것은 까다 롭습니다. C 어플리케이션의 대량 삽입 성능은 초당 85 개의 삽입에서 초당 96,000 이상의 삽입까지 다양합니다!
배경 : 데스크톱 응용 프로그램의 일부로 SQLite를 사용하고 있습니다. 애플리케이션이 초기화 될 때 추가 처리를 위해 구문 분석되어 SQLite 데이터베이스로로드되는 XML 파일에 많은 양의 구성 데이터가 저장되어 있습니다. SQLite는 속도가 빠르며 특수한 구성이 필요하지 않으며 데이터베이스가 단일 파일로 디스크에 저장되므로 이러한 상황에 이상적입니다.
근거 : 처음에는 내가보고있는 성능에 실망했습니다. 데이터베이스 구성 방법과 API 사용 방법에 따라 SQLite의 성능이 크게 달라질 수 있습니다 (대량 삽입 및 선택). 모든 옵션과 기술이 무엇인지 파악하는 것은 사소한 일이 아니므로 동일한 커뮤니티의 조사 문제를 다른 사람들이 해결하기 위해 스택 오버플로 리더와 결과를 공유하기 위해이 커뮤니티 위키 항목을 작성하는 것이 현명하다고 생각했습니다.
실험 : 일반적인 의미의 성능 팁 (예 : “트랜잭션 사용” )에 대해서만 이야기하는 대신 C 코드를 작성하고 실제로 다양한 옵션의 영향을 측정 하는 것이 가장 좋습니다 . 우리는 간단한 데이터로 시작할 것입니다.
- 토론토시의 전체 운송 일정에 대한 28MB의 탭으로 구분 된 텍스트 파일 (약 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
다음 바인드 사용하여 그 진술에 대한 우리의 매개 변수를 한 번 우리의 SQL 문을 컴파일합니다 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 journal_mode = 메모리
평가하여 롤백 저널을 메모리에 저장하십시오 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.00 초 내에 864913 개의 레코드를 가져 왔습니다.
환상적인! 초당 72,000 개의 인서트 를 수행 할 수 있습니다.
인 메모리 데이터베이스 사용
킥을 위해 이전의 모든 최적화를 기반으로하고 데이터베이스 파일 이름을 재정 의하여 RAM에서 완전히 작업하도록하겠습니다.
#define DATABASE ":memory:"
10.94 초 내에 864913 개의 레코드를 가져 왔습니다.
데이터베이스를 RAM에 저장하는 것은 실용적이지는 않지만 초당 79,000 개의 삽입을 수행 할 수 있다는 점이 인상적입니다 .
C 코드 리팩토링
특별히 SQLite 개선은 아니지만 루프 char*
에서 추가 할당 작업을 좋아하지 않습니다 while
. 해당 코드를 신속하게 리팩터링하여 출력을 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 이상의 삽입으로 전환합니다!
INDEX 작성 후 INSERT vs. INSERT 작성 후 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 쿼리에 대한 유사한 데이터를 컴파일 할 것입니다.
답변
몇 가지 팁 :
- 트랜잭션에 삽입 / 업데이트를 넣습니다.
- 이전 버전의 SQLite의 경우 덜 편집증적인 저널 모드 (
pragma journal_mode
)를 고려하십시오 . 이NORMAL
다음이OFF
너무 OS가 충돌하는 경우 가능성이 손상지고 데이터베이스에 대해 걱정하지 않는 경우 크게 속도 삽입 높일 수있다. 응용 프로그램이 충돌하면 데이터가 정상이어야합니다. 최신 버전에서는OFF/MEMORY
설정이 응용 프로그램 수준 충돌에 안전하지 않습니다. - 페이지 크기로 재생하면 차이가 있습니다 (
PRAGMA page_size
). 더 큰 페이지 크기를 가지면 더 큰 페이지가 메모리에 유지되므로 읽기 및 쓰기 속도가 약간 빨라집니다. 데이터베이스에 더 많은 메모리가 사용됩니다. - 색인이있는 경우
CREATE INDEX
모든 삽입 작업을 수행 한 후 전화 를 고려 하십시오. 이것은 색인을 작성하고 삽입을 수행하는 것보다 훨씬 빠릅니다. - 쓰기가 완료 될 때 전체 데이터베이스가 잠기고 여러 판독기가 가능하더라도 쓰기가 잠기므로 SQLite에 동시에 액세스 할 수있는 경우 매우주의해야합니다. 최신 SQLite 버전에 WAL이 추가되어 다소 개선되었습니다.
- 공간을 절약하십시오. 작은 데이터베이스는 더 빠릅니다. 예를 들어, 키 값 쌍이있는
INTEGER PRIMARY KEY
경우 키를 가능 하면 키로 만들어 테이블의 내재 된 고유 행 번호 열을 대체하십시오. - 여러 스레드를 사용하는 경우 공유 페이지 캐시를 사용하여 로드 된 페이지를 스레드간에 공유 할 수 있으므로 값 비싼 I / O 호출을 피할 수 있습니다.
- 사용하지 마십시오
!feof(file)
!
답변
해당 인서트 SQLITE_STATIC
대신 사용하십시오 SQLITE_TRANSIENT
.
SQLITE_TRANSIENT
반환하기 전에 SQLite가 문자열 데이터를 복사하게합니다.
SQLITE_STATIC
주어진 메모리 주소는 쿼리가 수행 될 때까지 유효합니다 (이 루프에서는 항상 그렇습니다). 이를 통해 루프 당 여러 할당, 복사 및 할당 해제 작업을 줄일 수 있습니다. 아마 큰 개선.
답변
피하십시오 sqlite3_clear_bindings(stmt)
.
테스트의 코드는 바인딩이 충분할 때마다 바인딩을 설정합니다.
SQLite 문서 의 C API 소개 는 다음과 같이 말합니다.
sqlite3_step () 을 처음으로 호출하기 전에 또는 sqlite3_reset () 직후 에 응용 프로그램은 sqlite3_bind () 인터페이스를 호출하여 값을 매개 변수에 첨부 할 수 있습니다
. sqlite3_bind () 에 대한 각 호출 은 동일한 매개 변수의 이전 바인딩을 대체합니다.
sqlite3_clear_bindings
단순히 바인딩을 설정하는 것 외에도 호출해야한다고 말하는 문서에는 아무것도 없습니다 .
자세한 내용 : avoid_sqlite3_clear_bindings ()
답변
벌크 인서트
이 게시물과 스택 오버플로 질문에서 영감을 얻었습니다 .SQLite 데이터베이스에 한 번에 여러 행을 삽입 할 수 있습니까? -첫 Git 저장소를 게시했습니다 .
https://github.com/rdpoor/CreateOrUpdate
이는 ActiveRecord 배열을 MySQL , SQLite 또는 PostgreSQL 데이터베이스에 대량로드 합니다. 기존 레코드를 무시하거나 덮어 쓰거나 오류를 발생시키는 옵션이 포함되어 있습니다. 필자의 기초 벤치 마크는 순차적 쓰기 (YMMV)에 비해 속도가 10 배 향상되었습니다.
대용량 데이터 세트를 자주 가져와야하는 프로덕션 코드에서 사용하고 있으며 매우 만족합니다.
답변
INSERT / UPDATE 문을 청크 할 수 있으면 대량 가져 오기가 가장 잘 수행되는 것 같습니다 . 10,000 개 정도의 값은 YMMV … 몇 행만있는 테이블에서 잘 작동했습니다.
답변
읽기에만 관심이있는 경우 다소 빠른 (그러나 오래된 데이터를 읽을 수 있음) 버전은 여러 스레드 (스레드 당 연결)의 여러 연결에서 읽는 것입니다.
먼저 표에서 항목을 찾으십시오.
SELECT COUNT(*) FROM table
그런 다음 페이지를 읽습니다 (LIMIT / OFFSET).
SELECT * FROM table ORDER BY _ROWID_ LIMIT <limit> OFFSET <offset>
다음과 같이 스레드 당 계산되는 위치는 다음과 같습니다.
int limit = (count + n_threads - 1)/n_threads;
각 스레드마다 :
int offset = thread_index * limit
우리의 작은 (200mb) db의 경우 50-75 %의 속도가 향상되었습니다 (Windows 7의 경우 3.8.0.2 64 비트). 우리 테이블은 정규화되지 않았습니다 (1000-1500 열, 약 100,000 개 이상의 행).
스레드가 너무 많거나 너무 적 으면 벤치마킹하고 프로필을 작성해야합니다.
또한 우리를 위해 SHAREDCACHE는 성능을 느리게 만들었으므로 PRIVATECACHE를 수동으로 넣었습니다.
답변
cache_size를 더 높은 값으로 올릴 때까지 트랜잭션에서 이익을 얻지 못했습니다. PRAGMA cache_size=10000;