SQLite 데이터베이스를 사용하다보면, 데이터가 많을 경우 처리 속도가 만족스럽지 못한 경우가 생길 수 있습니다. 그럴 때에는 무작정 다른 종류의 데이터베이스를 찾아보거나 다른 기술을 도입하려고 하기보다는, SQLite 데이터베이스를 사용하는 코드를 최적화해볼 필요가 있죠.
그럼 아래에 적용해볼 수 있는 방법들을 정리해보겠습니다.
예제 코드는 SQLite C Library를 기준으로 했습니다. 또 설명을 위한 예제이므로 필요없는 코드 일부, 에러처리 등은 생략했습니다.
1. INSERT와 UPDATE를 트랜잭션으로 묶기
SQLite 데이터베이스의 성능 향상을 고민할 때 가장먼저 생각해봐야하는 부분입니다.
SQLite는 모든 INSERT, UPDATE 구문을 각각 하나의 트랜잭션으로 처리하고 최적화합니다. 따라서, 함수나 반복문 단위로 여러 번 호출되는 경우 직접 트랜잭션으로 묶어주면 성능 향상을 기대해볼 수 있습니다.
먼저 아래와 같이 반복문 내에서 INSERT를 여러번 호출하는 코드가 있다고 해봅시다.
sqlite3_open("MyDB", &db);
sqlite3_exec(db, "CREATE TABLE MyData(A TEXT, B TEXT, C TEXT);", NULL, NULL, &err);
pFile = fopen("MyFile", "r");
while (fgets(sInput, BUFFER_SIZE, pFile))
{
sA = strtok(sInput, " ");
sB = strtok(NULL, " ");
sC = strtok(NULL, " ");
sprintf(sql, "INSERT INTO MyData VALUES ('%s', '%s', '%s');", sA, sB, sC);
sqlite3_exec(db, sql, NULL, NULL, &err);
n++;
}
fclose (pFile);
sqlite3_close(db);위와 같은 코드를 사용할 경우, INSERT 구문이 호출될 때마다 트랜잭션 처리가 이뤄지기 때문에 속도가 상당히 느려질 수 있습니다. 처리 속도를 빠르게 하고 싶다면 반복문을 감싸 하나의 트랜잭션으로 만들 수 있습니다.
트랜잭션은 BEGIN 또는 BEGIN TRANSACTION 으로 시작하고, END 또는 END TRANSACTION 으로 끝냅니다.
sqlite3_open("MyDB", &db);
sqlite3_exec(db, "CREATE TABLE MyData(A TEXT, B TEXT, C TEXT);", NULL, NULL, &err);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &err); // *
pFile = fopen("MyFile", "r");
while (fgets(sInput, BUFFER_SIZE, pFile))
{
sA = strtok(sInput, " ");
sB = strtok(NULL, " ");
sC = strtok(NULL, " ");
sprintf(sql, "INSERT INTO MyData VALUES ('%s', '%s', '%s');", sA, sB, sC);
sqlite3_exec(db, sql, NULL, NULL, &err);
n++;
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &err); // *
sqlite3_close(db);2. 캐시 크기 변경하기
SQLite 데이터베이스 내부적으로 사용하는 캐시의 크기를 알맞게 변경해주면 속도 향상을 기대해볼 수 있습니다. 특히, 1번처럼 트랜잭션으로 여러 구문을 묶어준 경우에도 캐시 크기를 늘려주면 효과를 볼 수 있습니다.
캐시 크기는 PRAGMA cache_size=SIZE 명령을 사용해 변경할 수 있습니다. 데이터베이스를 오픈한 직후에 실행해주면 됩니다.
참고로 여기서 SIZE의 값은 바이트 단위가 아니라, 페이지 단위입니다. 즉, 10000으로 설정한 경우 일반적인 4KB의 페이지 기준으로 10000*4KB = 약 40MB가 됩니다.
캐시 크기는 오픈한 파일들마다 적용되며, 뒤에서 설명할 캐시 공유를 사용하는 경우엔 프로세스마다 적용됩니다.
위에서 사용한 예제 코드를 수정해보겠습니다.
sqlite3_open("MyDB", &db);
sqlite3_exec(db, "PRAGMA cache_size=10000;", NULL, NULL, &err); // *
sqlite3_exec(db, "CREATE TABLE MyData(A TEXT, B TEXT, C TEXT);", NULL, NULL, &err);
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &err);
pFile = fopen("MyFile", "r");
while (fgets(sInput, BUFFER_SIZE, pFile))
{
sA = strtok(sInput, " ");
sB = strtok(NULL, " ");
sC = strtok(NULL, " ");
sprintf(sql, "INSERT INTO MyData VALUES ('%s', '%s', '%s');", sA, sB, sC);
sqlite3_exec(db, sql, NULL, NULL, &err);
n++;
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &err);
sqlite3_close(db);3. sqlite3_prepare_v2 사용하기
자주 사용되는 구문은 sqlite3_exec 함수를 사용하는 대신, sqlite3_prepare_v2 함수를 사용해 미리 컴파일해두면 속도 향상을 기대할 수 있습니다.
함수에 대한 자세한 설명은 SQLite 데이터베이스 C/C++에서 사용하기 1: 기본 포스트를 참고하세요!
계속해서 예제를 수정해보겠습니다.
sqlite3_open("MyDB", &db);
sqlite3_exec(db, "PRAGMA cache_size=10000;", NULL, NULL, &err);
sqlite3_exec(db, "CREATE TABLE MyData(A TEXT, B TEXT, C TEXT);", NULL, NULL, &err);
// *
sqlite3_stmt *res;
char sql[] = "INSERT INTO MyData VALUES (?, ?, ?);";
sqlite3_prepare_v2(db, sql, -1, &res, 0);
// *
sqlite3_exec(db, "BEGIN TRANSACTION", NULL, NULL, &err);
pFile = fopen("MyFile", "r");
while (fgets(sInput, BUFFER_SIZE, pFile))
{
sA = strtok(sInput, " ");
sB = strtok(NULL, " ");
sC = strtok(NULL, " ");
// *
sqlite3_bind_text(stmt, 1, sA, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 2, sB, -1, SQLITE_STATIC);
sqlite3_bind_text(stmt, 3, sC, -1, SQLITE_STATIC);
sqlite3_step(stmt);
sqlite3_reset(stmt);
// *
n++;
}
fclose (pFile);
sqlite3_exec(db, "END TRANSACTION", NULL, NULL, &err);
sqlite3_close(db);4. sqlite3_clear_bindings 사용하지 않기
SQLite 데이터베이스의 예제를 찾다보면, sqlite3_prepare_v2 함수를 사용할 때 sqlite3_clear_bindings를 매번 호출해주는 코드를 심심치 않게 볼 수 있습니다. 그러나 무심코 저 함수를 사용했다가는 처리 속도가 느려질 수 있습니다!
sqlite3_clear_bindings 함수는 컴파일된 구문(prepared statement)의 모든 바인딩을 NULL로 초기화합니다. 설명만 들으면 sqlite3_step 함수를 호출할 때마다 함께 호출해주어야 할 것 같지만, 그렇지 않습니다.
아래는 관련된 내용을 설명하는 SQLite C 인터페이스 설명 문서 6번 항목의 일부분입니다.
Prior to calling sqlite3_step() for the first time or immediately after sqlite3_reset(), the application can invoke the sqlite3_bind() interfaces to attach values to the parameters. Each call to sqlite3_bind() overrides prior bindings on the same parameter.
sqlite3_bind 함수에 대한 설명이지만, 다르게 말하자면 sqlite3_bind와 sqlite3_step 함수를 호출할 때에는 sqlite3_reset 함수로 충분하다는 뜻입니다. sqlite3_clear_bindings 함수는 언급도 되지 않는 것을 보아 사실상 관계 없다는 이야기겠죠. 😅
요약하자면, sqlite3_clear_bindings 함수는 필수가 아니며, 따라서 필요한 경우가 아니라면 호출하지 않는 것이 좋습니다.
sqlite3_clear_bindings 함수가 필요한 경우는, 바인드해야할 변수가 여러 개인데 대부분 NULL이고 그 중 몇 개만 바인드해야하는 경우입니다.
즉, (A, NULL, NULL, NULL)를 바인드한 다음에 (NULL, NULL, NULL, B)로 바인드하고 싶을 때 사용하는 유틸리티 함수인 것이죠.
5. Write-Ahead Logging 기능 활성화하기
SQLite 데이터베이스도 3.7.0 버전부터는 Write-Ahead Logging(WAL) 기능을 제공합니다! WAL 기능을 간단히 설명하면, 말그대로 로그를 먼저 작성한 뒤 데이터를 처리하는 방식으로, 트랜잭션 커밋마다 데이터 페이지를 디스크에 쓰지 않아도 되도록 해줍니다.
즉, 쓰기 작업이 많은 경우 활성화해주면 확연한 성능 향상을 볼 수 있습니다. (사실 특별한 경우가 아니면 그냥 항상 더 빠릅니다.)
WAL 기능은 PRAGMA journal_mode=WAL 구문으로 활성화 할 수 있습니다. 캐시 크기 조절과 마찬가지로 데이터베이스 오픈 직후에 호출해주면 됩니다.
덧붙여 WAL 기능 활성화와 함께 PRAGMA synchronous=NORMAL 구문을 호출해주면 추가적인 성능 향상을 기대할 수 있습니다. 자세한 내용은 위쪽 WAL 설명 링크의 2.3 Performance Considerations 항목을 참고하세요!
6. 캐시 공유 모드로 데이터베이스 열기
이번 항목은 멀티스레드 환경에서 SQLite 데이터베이스를 사용할 경우 참고할만한 내용입니다.
SQLite 데이터베이스는 기본적으로 스레드별로 캐시를 관리합니다. 따라서 멀티스레드 환경에서 스레드간에 캐시를 공유한다면 처리 속도가 빨라질 수도 있겠죠? (실제로 빨라지는지 느려지는지는 항상 확인해봐야합니다! 일반적으로 캐시 공유기능은 메모리나 IO 사용량 감소에 초점이 잡혀있기도 합니다.)
이러한 기대에 부응하기위해 SQLite 3.5.0 버전부터 캐시 공유 모드를 지원합니다. 캐시 공유 모드는 sqlite3_enable_shared_cache 함수를 사용해 프로세스 단위로 키고 끌 수 있습니다. 인자로 1을 전달하면 켜고, 0을 전달하면 끕니다.
또 sqlite3_open_v2 함수와 SQLITE_OPEN_SHAREDCACHE, SQLITE_OPEN_PRIVATECACHE 플래그를 사용해 데이터베이스 단위로 키고 끌 수도 있습니다. 이 경우 프로세스 단위로 설정한 값은 무시됩니다. 아래는 sqlite3_open_v2 함수를 사용해 캐시 공유를 사용하도록 데이터베이스를 오픈하는 예입니다.
sqlite3_open(
"MyDB",
&db,
SQLITE_OPEN_READWRITE | SQLITE_OPEN_CREATE | SQLITE_OPEN_SHAREDCACHE,
NULL);7. 디스크 동기화 끄기
PRAGMA synchronous=OFF 구문을 호출해 디스크 동기화 작업을 생략하도록 할 수 있습니다. 디스크 동기화 작업에는 꽤 많은 시간이 사용되기 때문에, 큰 성능 향상을 기대할 수 있습니다. 하지만 당연히 그 대가로 안정성을 포기해야한다는 사실을 잊으면 안됩니다!
SQLite 데이터베이스를 사용하는 어플리케이션에서 오류가 발생한 경우에는 데이터가 보호되지만, 운영체제 레벨에서 오류가 발생하거나 전원이 끊기는 경우 데이터베이스가 손상될 수 있습니다.
8. 데이터베이스 인덱싱
“데이터베이스 인덱싱을 마지막에 언급하다니!”
데이터베이스 인덱싱은 데이터 처리 성능과 관련된 아주 중요한 주제이지만, 이 글에서는 다루지 않을 예정이기 때문에 마지막 항목으로 언급만 하고 지나가겠습니다. 관심이 있으신 분들은 직접 조사해보시면 분명 큰 소득이 있을거에요!😉
마치며
간단히 정리만 하고 넘어가려던 글이 생각보다 길어졌네요. 어그로성 제목을 택한 것 같지만 저는 위 방법들을 통해 실제로 10배에 가까운 성능 향상을 겪었습니다. SQLite 외에 다른 데이터베이스도 마찬가지겠지만, 얼마나 공부하고 노력하느냐에 따라 같은 제품에서도 하늘과 땅 차이의 성능 차이가 나오는 것 같습니다. 역시 열심히 공부해야겠네요.😅
또 한 가지 언급하고 싶은 것은 측정의 중요성입니다. 조사를 해보면 문제에 대한 해결책은 항상 여러 가지가 있습니다. 하지만 가장 중요한 것은 적용 전 후로 실제 성능을 측정해보는 습관인 것 같습니다. 측정을 해보면 예상과 전혀 다른 결과가 나오는 경우가 적지 않기 때문이죠.
👍 😀
댓글 감사합니다^^