SQLite의 초당 삽입 성능을 향상시킵니다
질문
SQLite를 최적화하는 것은 까다 롭습니다.C 응용 프로그램의 대량 인서트 성능은 초당 85 개의 인서트에서 초당 96,000 개 이상의 인서트로 다양합니다!
배경 : 우리는 SQLite를 데스크톱 응용 프로그램의 일부로 사용하고 있습니다.응용 프로그램이 초기화 될 때 추가 처리를 위해 구문 분석되어 SQLite 데이터베이스에 구문 분석되고로드되는 XML 파일에 많은 양의 구성 데이터가 있습니다.SQLite는 빠른이 상황에 이상적이며, 특수한 구성이 필요하지 않으며 데이터베이스가 디스크에 단일 파일로 저장됩니다.
이론적 근거 : 처음에는 내가보고있는 공연에 실망했다.데이터베이스가 구성된 방법과 API를 사용하는 방법에 따라 SQLite 성능이 크게 (대량 삽입 및 선택 모두에 대해 모두) 크게 다를 수 있음을 밝힙니다.모든 옵션과 기술이 무엇인지 파악하는 것은 사소한 문제가 아니 었습니다. 그래서 나는 다른 사람들이 같은 조사의 문제를 구하기 위해 스택 오버플로 독자들과 결과를 공유하기 위해이 커뮤니티 위키 입장을 만들려면 신중하게 생각했습니다.
실험 : 일반적인 의미의 성과 팁 (즉, "트랜잭션을 사용하십시오.")의 성과 팁에 대해 단순히 말하기보다는 C 코드를 작성하고 실제로 다양한 옵션의 영향을 측정하는 것이 가장 좋습니다.우리는 몇 가지 간단한 데이터로 시작할 것입니다.
- A 28 MB TAB-delimited text file (approximately 865,000 records) of the complete transit schedule for the city of Toronto
- My test machine is a 3.60 GHz P4 running Windows XP.
- The code is compiled with Visual C++ 2005 as "Release" with "Full Optimization" (/Ox) and Favor Fast Code (/Ot).
- I'm using the SQLite "Amalgamation", compiled directly into my test application. The SQLite version I happen to have is a bit older (3.6.7), but I suspect these results will be comparable to the latest release (please leave a comment if you think otherwise).
몇 가지 코드를 작성 해 봅시다!
코드 : 텍스트 파일을 읽는 간단한 C 프로그램은 문자열을 값으로 분할 한 다음 데이터를 SQLite 데이터베이스에 삽입합니다.이 "기준선"코드 버전에서는 데이터베이스가 작성되지만 실제로 데이터를 삽입하지 않습니다.
/*************************************************************
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;
}
제어"
코드를 AS-IS를 실행하는 것은 실제로 데이터베이스 작업을 수행하지는 않지만 RAW 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);
864913 년 864913 년 38.03 년에 기록 초월
그게 낫다.단일 트랜잭션의 모든 인서트를 모두 감싸는 것은 초당 23,000 개의 인서트로 성능을 향상 시켰습니다.
준비된 문장을 사용합니다
거래를 사용하는 것은 엄청난 개선 이었지만 동일한 SQL을 사용하여 동일한 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;
864913 수입 16.27에 기록 초월
멋진!조금 더 많은 코드가 있습니다 (sqlite3_clear_bindings 및 sqlite3_reset을 호출하는 것을 잊지 마십시오). 그러나 우리는 초당 53,000 개의 인서트로 성능을 두 배로 늘 렸습니다.
PRAGMA 동기 = OFF.
기본적으로 SQLite는 OS-Level Write 명령을 실행 한 후에 일시 중지됩니다.이는 데이터가 디스크에 기록되도록 보장합니다.동기식 = 끄기를 설정하면 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);
864913 년 12.41 년에 수입 된 864913 초월
이제 개선 사항이 더 작지만 초당 69,600 개의 인서트가 있습니다.
pragma journal_mode = 메모리
pragma journal_mode = 메모리를 평가하여 롤백 저널을 메모리에 저장하는 것을 고려하십시오.트랜잭션이 더 빨라지지만 트랜잭션 중에 전원이 끊어 지거나 프로그램 충돌이 손상된 경우 부분적으로 완성 된 트랜잭션이있는 손상된 상태로 남아있을 수 있습니다.
/* 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 JURNAL_MODE = 메모리
이전 두 가지 최적화를 결합합시다.그것은 (충돌이있는 경우) 조금 더 위험합니다. 그러나 우리는 단지 데이터를 가져 오는 것입니다 (은행을 실행하지 않음).
/* 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);
864913 년 864913 년 12시에 기록 초월
환상적인!우리는 초당 72,000 개의 인서트를 수행 할 수 있습니다.
메모리 내 데이터베이스를 사용합니다
킥을 위해서만 이전의 최적화를 모두 빌드하고 데이터베이스 파일 이름을 재정의하여 RAM에서 완전히 작동합니다.
#define DATABASE ":memory:"
864913 년 864913 년 10.94 년에 기록 초월
우리 데이터베이스를 RAM에 저장하는 것은 슈퍼 실용이 아니지만 초당 79,000 개의 인서트를 수행 할 수 있다는 인상적입니다.
리팩토링 C 코드
구체적으로 SQLite 개선이 아니지만 While 루프의 추가 char * 할당 작업이 마음에 들지 않습니다.SQLITE3_BIND_TEXT ()에 STRTOK ()의 출력을 직접 전달하고 컴파일러가 우리를 위해 일을 속도를 높이려고하도록하는 코드를 신속하게 보냅니다.
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의 사용을 최적화하여 초당 96,000 개가 넘는 96,000 개 이상의 인서트에서 85 개의 삽입물의 최악의 시나리오로 이동했습니다!
인덱스 작성 다음 삽입 물을 삽입 한 다음 인덱스를 만듭니다
선택 성능을 측정하기 전에 우리는 인덱스를 만들 것입니다.대량 인서트를 수행 할 때 데이터가 삽입 된 다음 데이터를 삽입 한 다음 데이터를 삽입하는 것과 반대로 데이터가 삽입 된 후 인덱스를 만드는 것이 더 빠르게 발생합니다.해보자:
색인을 생성 한 다음 데이터를 삽입하십시오
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 년 기록 수입 초월
예상대로, 하나의 열이 인덱싱되면 벌크 인서트가 느려지지만 데이터가 삽입 된 후 인덱스가 생성되면 차이가 발생합니다.No-Index Baseline은 초당 96,000 개의 인서트입니다.색인 생성 먼저 데이터를 삽입 한 다음 초당 47,700 개의 삽입물을 제공하는 반면 먼저 데이터를 삽입하면 인덱스를 생성 한 다음 초당 63,300 개의 삽입물을 제공합니다.
기꺼이 다른 시나리오에 대한 제안을 기꺼이 시도하고 곧 선택 쿼리에 대해 유사한 데이터를 컴파일 할 것입니다.
답변
몇 가지 팁 :
- Put inserts/updates in a transaction.
- For older versions of SQLite - Consider a less paranoid journal mode (
pragma journal_mode). There isNORMAL, and then there isOFF, which can significantly increase insert speed if you're not too worried about the database possibly getting corrupted if the OS crashes. If your application crashes the data should be fine. Note that in newer versions, theOFF/MEMORYsettings are not safe for application level crashes. - Playing with page sizes makes a difference as well (
PRAGMA page_size). Having larger page sizes can make reads and writes go a bit faster as larger pages are held in memory. Note that more memory will be used for your database. - If you have indices, consider calling
CREATE INDEXafter doing all your inserts. This is significantly faster than creating the index and then doing your inserts. - You have to be quite careful if you have concurrent access to SQLite, as the whole database is locked when writes are done, and although multiple readers are possible, writes will be locked out. This has been improved somewhat with the addition of a WAL in newer SQLite versions.
- Take advantage of saving space...smaller databases go faster. For instance, if you have key value pairs, try making the key an
INTEGER PRIMARY KEYif possible, which will replace the implied unique row number column in the table. - If you are using multiple threads, you can try using the shared page cache, which will allow loaded pages to be shared between threads, which can avoid expensive I/O calls.
- Don't use
!feof(file)!
또한 여기에서 비슷한 질문을했습니다.
답변
그 인서트에 대해 SQLite_Transient 대신 sqlite_static을 사용해보십시오.
SQLITE_TRANSIENT로 인해 SQLITE가 리턴 전에 문자열 데이터를 복사합니다.
sqlite_static은 쿼리가 수행 될 때까지 (이 루프에서 항상 사례)가 수행 될 때까지 유효한 메모리 주소가 유효합니다.이렇게하면 루프 당 작업을 여러 번 할당하고 복사하고 할당 해제합니다.아마도 큰 개선이 가능합니다.
답변
SQLIte3_clear_Bindings (STMT)를 피하십시오.
테스트의 코드는 매번 바인딩을 설정합니다.
SQLite 문서의 C API 소개는 다음과 같습니다.
sqlite3_step ()을 처음 또는 즉시 호출하기 전에 sqlite3_reset () 이후에 응용 프로그램이 호출 할 수 있습니다. sqlite3_bind () 인터페이스는 값을 매개 변수에 연결합니다.각 sqlite3_bind () 호출은 동일한 매개 변수에서 이전 바인딩을 재정의합니다.
SQLIte3_clear_Bindings의 문서에는 바인딩을 설정하는 것 외에도 전화해야합니다.
자세한 내용 : inven_sqlite3_clear_bindings ()
답변
대량 인서트에
이 게시물에서 영감을 얻었고 스택 오버플로 질문에 의해 여기에서 나를 이끌어 냈습니다. SQLite 데이터베이스에서 한 번에 여러 행을 삽입 할 수 있습니까?- 첫 번째 Git 저장소를 게시했습니다.
https://github.com/rdpoor/createorupdate.
대량은 MySQL, SQLite 또는 PostgreSQL 데이터베이스에 ActiveRecords 배열을로드합니다.기존 레코드를 무시하고 덮어 쓰거나 오류를 발생시키는 옵션이 포함됩니다.내 기초적인 벤치 마크는 순차 쓰기에 비해 10 배 속도 향상을 보여줍니다 - YMMV.
나는 대형 데이터 세트를 자주 가져야하는 프로덕션 코드에서 그것을 사용하고 있습니다. 나는 그것에 꽤 행복합니다.
답변
대량 가져 오기는 삽입 / 업데이트 문을 청구 할 수있는 경우 가장 잘 수행되는 것 같습니다.10,000의 가치는 몇 행, ymmv만이있는 테이블에 나에게 잘 작동했습니다 ...
답변
읽는 것에 대해서만주의를 기울이면 다소 빠르지 만 낡은 데이터를 읽을 수 있습니다.) 버전은 여러 스레드 (실당 연결부)의 여러 연결에서 읽는 것입니다.
먼저 테이블에서 항목을 찾습니다.
SELECT COUNT(*) FROM table
그런 다음 페이지를 읽습니다 (제한 / 오프셋) :
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를 넣었습니다 (전 세계적으로 우리를 위해 전 세계적으로 활성화되었으므로)
출처:https://stackoverflow.com/questions/1711631/improve-insert-per-second-performance-of-sqlite
최근댓글