SQLite 1탄, 이것만은 알고 넘어가자!

Posted on | by gywndi

안녕하세요. 오랜만에 포스팅을 합니다. 그동안 너무 소홀하게 블로그 관리를 한 듯..ㅜㅜ 뜬금없기는 하지만, 당분간 제가 공유할 주제는 바로 SQLite입니다. ^^;

SQLite?

SQLite는 간단하게 말하면, 트랜잭션을 제공해주는 파일 기반의 DBMS입니다. 모바일 서비스가 대세가 된 요새, SQLite 역시 가장 많이 사용되는 DB로 떠오르고 있습니다. 대충만 적어도 쟁쟁한 회사들이 애용을 하고 있는 상황이죠. ^^

SQLite well-known users

일반적인 타 DBMS 처럼 데몬처럼 동작하는 방식이 아닌, 마치 라이브러리와 같이 프로그램에 직접 Embed하여 사용하죠. 그리고 간과하지 말아야하는 것은 SQLite는 기존 서버에서 구동하는 대형 DBMS를 대체하자는 개념으로 탄생한 DB가 아닙니다. 그것보다는 프로그램 내부에서 잦은 파일 접근(fopen)을 최대한으로 적게하자는 취지로 만들어진 DB이죠. 그렇지만 강력합니다. ㅎㅎ

그리고 중요한 것!! 오픈소스입니다. (공부하기 딱 좋은.. ^^;;)

SQLite에서 제가 판단하기에 가장 중요한 특징은 다음 세 가지라고 생각되는데요. (의견이 달라도 소심한 제게 뭐라고는 하지 마세요. ㅜㅜ)

  1. Nested Loop
  2. File Based Processing
  3. Transaction (Rollback Journal / WAL mode)

Nested Loop

SQLite는 Nested Loop 방식으로 데이터를 처리합니다.

예를 들어 다음과 같은 조인 쿼리 질의가 들어왔다고 생각해봅시다.

select *
from edge AS e,
     node AS n1
where n1.name = 'alice'
  and e.orig = n1.id

이와 같은 쿼리 경우에 무조건(!) Nested Loop로 동작하는 것이죠. for문으로 변경한다면 다음과 같습니다. ^^ 이해가 쉽죠? ㅎㅎ

foreach n1.name='alice' do:
  foreach e.orig=n1.id do:
    return n1.*, e.*
  end
end

이 특징을 반드시 이해를 해야 쿼리를 이쁘게(?) 작성할 수 있을 것이라고 생각됩니다.

File Based Processing

SQL은 파일 기반으로 동작합니다. 데이터 변경 시 잠금을 파일 기반으로 동작한다는 것이죠. 물론 파일 기반의 DBMS는 상당히 많습니다. 예를 들어 MySQL에서 MyISAM .. ㅎ

그런데!! SQLite이 가지는 가장 큰 특징이 있습니다.

바로 테이블 단위가 아닌 데이터베이스 단위로 잠금이 발생한다는 것입니다.!!

사실 생각을 해보면, SQLite에서 DB 자체가 파일 하나인데, 파일 기반 데이터베이스라면 당연히 DB 단위로 잠금이 발생할 수밖에 없겠죠?? ^^;; 즉, 아무리 테이블을 여러개로 나누어서 어플리케이션을 작성을 해도, 내부적으로는 잠금이 단일 프로세스로만 동작하기 때문에, Write 성능을 전혀 달라지지 않는다는 말이죠.

Transaction

트랜잭션.. SQLite에서는 트랜잭션 기능을 제공합니다. 트랜잭션과 밀접하게 연관된 Pragma(DB 파라메터와 유사한 의미)는 Journal Mode입니다.

SQLite에서는 Rollback Journal 방식을 통해 트랜잭션을 구현합니다. 사실 Journaling은 예기치 않은 DB 혹은 서버 에러에도 데이터 유실 혹은 파일 손상을 방지하기 위해, 문제 발생 시 원래 데이터로 복원하고자 하는데에 목적이 있습니다. SQLite는 Journaling의 이 특징을 사용하여 트랜잭션을 사용할 수 있도록 제공하는 것이죠.

데이터 변경은 다음 순서에 따라 이루어집니다.

  1. 원본 데이터를 별도의 파일에 저장
  2. 데이터 변경
  3. 백업 데이터 제거 = 커밋

즉, 데이터 변경하기에 앞서서 원본을 별도 파일에 저장(begin)을 하고, 정상적으로 데이터를 변경하면 원본 데이터 파일을 제거(commit)함으로써 트랜잭션이 이루어지는 것이죠.

SQLite Rollback Journal

만약 데이터 변경 도중 Rollback이 필요한 상황일지라도, 저널링 파일을 사용하여 변경 이전의 데이터 버전으로 복원이 가능합니다.

SQLite에서 Journal Mode는 총 6가지가 있습니다.

  1. DELETE (Default)
    Rollback Journal File을 매번 Create/Delete
  2. TRUNCATE
    Rollback Journal을 매번 Create/Delete하지 않고, 파일 사이즈만 0으로 설정 후 재활용
  3. MEMORY
    저널링 파일을 메모리에서 유지. SQLite를 사용하는 어플리케이션 장애 시 데이터 유실
  4. PERSIST
    저널링 파일을 지우지 않음.
  5. NONE
    데이터 파일에 직접 기록(문제 발생 시 데이터 복원 불가)

DELETE 모드가 Default이고, 만약 다른 모드로 변경하고자 한다면 다음과 같이  pragma를 변경합니다. 단, 다음과 같이 변경을 하였다 하더라도, 재 접속 시 여전히 DELETE모드로 동작하므로, 접속 시마다 원하고자 하는 Journal Mode로 변경해야한다는 점을 유의하시기 바랍니다.

## journal mode 변경
sqlite> pragma journal_mode = TRUNCATE;

위에서 저널 파일이 생기는 시점에는 다른 프로세스에서 데이터를 읽을 수 있는 Shared Lock이, 그리고 실제적으로 데이터를 Writing하는 과정에서는 Read 요청도 제한하는 Exclusive Lock 형태로 동작합니다. 여기서 Exclusive Lock을 획득하기 위해 현재 동작 중인 읽기 프로세스가 종료되기를 대기합니다. 그것도, 테이블 단위가 아닌 데이터베이스 파일 단위로.. ^^;;

즉, Write 요청이 많으면, Read까지 느려지는..반대로 Read요청이 많으면 Write가 느려지는.. 현상이 발생할 수 있습니다. 즉, Read와 Write 서로가 성능에 영향을 주고, 동시성 작업에서 문제가 생길 수 있다는 말이죠.

3.7.0에서는 이 문제를 해결해보고자, WAL(Write Ahead to Log) 방식을 제공합니다. 위에서 6가지라고 해놓고 5가지만 명시한 것은 WAL모드는 기존의 Rollback Journal Mode와는 엄격하게 다르게 동작하기 때문입니다.

아래와 같이 WAL 모드로 간단하게 변경할 수 있는데, 다른 Journal Mode와는 다르게 WAL 로 변경을 하게 되면, 이후에도 계속 WAL 상태로 머물러 있게 됩니다. 물론 다시 Journal Mode를 명시적으로 변경하지 않는 한 말이죠. ^^

sqlite> pragma journal_mode = WAL;

쓰기 요청이 들어오면, 변경 사항을 먼저 WAL-Log 파일에 기록합니다. 로그에 먼저 기록하는 것은 파일 마지막 위치에 내용을 붙이기만 하면 되기 때문에, 데이터 파일에 직접 기록하는 것보다는 훨씬 빠르고 가벼운 요청입니다. 그리고 WAL-Log 파일이 1000Page(Default)가 넘게되면, 일괄 데이터에 반영합니다. 리눅스 경우라면 일반적으로 1 Page가 4K이므로, 대략 4MB정도 쯤에 일괄 반영(Check Point) 작업을 수행한다고 보면 됩니다. 이 작업은 SAS 혹은 SATA와 같은 디스크에서 Random I/O를 최소화하기 위해 나름 순차적으로 데이터에 변경사항을 기록합니다. (스마트하죠.-_-;;)

대신 읽기 요청은 현재의 로그 파일과, 데이터 파일을 참조하여 처리합니다. 로그 파일 내부적으로 시점에 대한 정보가 저장되어 있는데, 이것을 바탕으로 읽은 시점의 버전 상태를 정하고 데이터를 읽게 되는 것이죠. 이것은 기존처럼 데이터에서 직접 읽는 방식보다는 소폭 성능이 떨어질 수도 있지만, 일반적으로는 전혀 성능 차이가 없다고 봐도 무관합니다. 사실, Write과  Read를 섞어서 성능을 테스트를 해보면, 오히려 WAL 이 기존의 Journal Mode보다 퍼포먼스가 좋게 나옵니다.

SSD에서 간단하게 성능 비교를 해 본 것입니다. 가장 성능이 좋게 나온 것은 Journal File을 메모리에 유지하는 “MEMORY” 모드입니다.(DELETE가 아니예요. ^^ 색깔이 비슷하다고 헷갈리지 마세요.) Memory 모드는 데이터 유실이 발생할 수 있으므로, Pass~!

Journal Mode에 따른 성능 비교

 

그렇다면 Random I/O에 약한 SATA 디스크에서는 어떨까요? 가장 위 보라색이 WAL 상태입니다.

Journal Mode에 따른 성능 비교(SATA)

 

물론 이 테스트는 데스크톱에서 수행한 것으로, 실제 모바일 환경에서는 다를 수 있습니다. 그러나, 크게 다를 것으로 생각되지는 않네요. ^^;

Conclusion

헥헥!! 최대한 간단하게 작성한다고 해도, 내용이 많네요. -_-;; 예전에는 SQLite를 가볍게만 생각을 하고, 깊이 공부해볼 시도도 해보지 않았지만, 파면 팔수록 배울 점이 상당한 DB인 것 같습니다.

SQLite는 데이터 처리를 Nested Loop 방식으로 동작하고, 파일 기반으로 데이터 접근을 하는 트랜잭션을 지원합니다. 그리고 트랜잭션을 Rollback Journaling 혹은 WAL mode 등을 통해 구현한다는 사실을 잊지 마세요. ^^ 그리고 Journal Mode에 따라 성능이 크게 달라질 수 있다는 점도 잊지 마시고요.

당분간, SQLite에 대한 내용을 지속적으로 포스팅할 생각입니다. 다음번 주제는 데이터 및 인덱스에 대한 내용을 정리해보도록 하겠습니다. (내용이 오늘 블로그처럼 주저리주저리 많겠지만, 열심히 정리해보도록 하겠습니다. ㅜㅜ)

무더운 날씨, 수고하셨습니다. ^^

Leave a Reply