SQLite 3탄 – 미리알고, 풀스캔을 방지하자.(1)

Posted on 2013-09-08 | by gywndi | Leave a Comment

처가 과수원 사과 수확을 하고 왔습니다. 매주 빡빡한 스케줄.. 연예인 못지않군요.^^ 그렇지만, 오늘도 역시.. SQLite 시리즈를 이어보겠습니다. ^0^ (힘들어서 2회에 걸쳐서 진행하겠습니다. ^^)

Overview

어떤 DBMS를 막론하고, Searching은 가장 중요한 요소입니다. 특히 데이터 건 수가 많을수록, 원하는 데이터를 찾아내기 위해 처음부터 끝까지 모두 파일을 뒤지게 된다면.. 쿼리 시간을 떠나 사용하는 전력 또한 상당할 것입니다. 특히 SQLite가 모바일 환경에서 대부분 활용된다는 상황에서, CPU 자원을 최소화한다는 것은 결과적으로 배터리 소모를 최소한으로 이룰 수 있음을 의미하죠. ^^

그래서 오늘은 SQLite에서 발생하는 Searching에 대해 포스팅을 하도록 하겠습니다.

Full Scan

풀스캔입니다. DBMS를 막론하고, 데이터 사이즈가 커질수록 “반드시” 지양해야 하는 스캔이죠. 풀스캔은 말 그대로, DB 테이블에 포함된 데이터를 처음부터 끝까지 체크하며 원하는 데이터를 추출하는 것을 의미합니다.

적절한 인덱스가 없는 경우에 발생하는데, 데이터를 RowID순으로 접근하며 테이블 데이터를 추출하죠. RowID란 SQLite 2탄 – 데이터와 인덱스 구조!!에서 소개드린 것처럼, 각 행 특정한 행에 접근하기 위한 “주소”와 동일한 역할을 하는 8Byte의 정수 키 값입니다. ^^

위 그림에서 fruit에 해당하는 인덱스가 없는 경우 RowID 순으로 처음부터 끝까지 fruit이 ‘Peach’인 데이터를 찾아서 결과를 리턴하게 되죠. 이것이 바로 풀스캔입니다. ^^ 쿼리 성능이 느리다면, 적절한 인덱스가 없어서 풀스캔이 발생하고 있는지를 확인하세요.

Lookup By RowID

바로 앞에서 설명한 풀스캔은 일반적으로 데이터가 커질수록 데이터 추출이 가장 느린 스캔 방법입니다. 그렇다면, 이것과는 반대로 아무리 데이터가 많아도 가장 빠른 데이터 추출을 방법은 무엇일까요? 그렇습니다. 바로 “RowID”로 접근하여 데이터를 바로 추출하는 것입니다.

RowID가 테이블 데이터의 위치를 지칭하는 주소와 같은 역할을 하니, 당연히도 가장 빠른 데이터 접근방법일 수밖에 없겠죠. ^^;

Lookup By Index

자~! 효율이 가장 떨어지는 것은 풀스캔이고, 가장 빠른 접근은 RowID를 통한 것인데.. 이 둘을 먼저 설명드린 이유는 바로 인덱스에 대한 얘기를 하기 위함입니다. 인덱스는 마치 책 가장 뒷편에 위치한 색인과 같은 의미로, 데이터가 위치한 주소를 저장하는 자료 구조입니다. 바로 이전 포스닝 SQLite 2탄 – 데이터와 인덱스 구조!!에서 인덱스의 구성에 대해서 간단하게 말씀드린 바 있습니다. SQLite에서 인덱스는 인덱싱할 칼럼을 “키”값으로, 그리고 접근할 데이터의 RowID를 “값”으로 “B-Tree”로 구성됩니다.

위 그림은 인덱스를 생성을 하였을 때 인덱스 데이터 구조에 대한 모습이며, 인덱싱한 칼럼을 기준으로 각각의 RowID가 저장되어 있는 것을 확인할 수 있습니다. ^^

데이터에의 접근은 두 단계로 이루어집니다. 먼저, 검색할 데이터를 기준으로 인덱스 데이터에 접근을 합니다. 만약 데이터가 있다면, 인덱스 Value인 RowID를 기준으로 이번에는 데이터 트리에 접근하여 최종적으로 원하는 데이터를 추출하는 것이죠. 자, 아래 그림을 보시죠. ^^

먼저 앞서 생성한 인덱스 자료 구조에 접근하여, RowID를 가져와서 실제적으로 원하는 데이터를 추출하는 단계를 보여줍니다. 앗! 그런데 저기서 logN은 무엇이냐고요? 특정 요청을 처리 시 소요되는 비용을 의미하는데, 트리에서는 평균적으로 logN번 과정을 거쳐서 원하는 데이터에 접근하게 되는 것이죠. 자세한 설명은 생략하겠습니다. ^^;;

위 그림에서 트리의 한 건 데이터에 접근하는 비용이 logN이고, 인덱스로 걸러지는 데이터가 K건이라면 (1+K)*logN 의 비용이 발생하게 됩니다. 수식이 나오니, 조금 어지럽군요. @.@ 다시 이에 대한 것을 그림으로..(역시 사람은 가시적인 것이 쵝오!! >_<)

오렌지에 걸러지는 데이터가 두 건이고, 이 두 건을 각각 데이터 트리로 접근하기 때문에 (1+2)*logN의 비용이 발생하는 것을 보여줍니다. ^^;

자! 그렇다면, 만약 인덱스 칼럼과 인덱스에 포함되지 않는 칼럼이 동시에 검색 조건으로 들어오면 어떻게 될까요? 과연 인덱스에 포함되지 않는 칼럼이 비용을 줄여주는 역할을 할까요? 아쉽지만, 세상에는 그러한 매직은 없습니다. ㅜㅜ 비용은 바로 이전 ‘Orange’로 검색한 것과 동일한 리소스를 소모하게 되는 것이죠. (1+2)*logN이고, 걸러진 데이터의 state를 다시 한번 판별하고, 원하는 데이터를 추출하는 것이죠.

여기서 인덱스에 대한 퀴즈 하나! 위와 같은 상황에서 아래와 같이 state칼럼에 인덱스를 추가한다면, 쿼리 성능이 좋아지는 효과가 있을까요? 즉, 두 개의 인덱스 자료구조를 동시에 참조하여, 단 한번에 원하는 데이터에 바로 접근 가능할까요?

아쉽지만, 이런 마술 또한 SQLite에서는 존재하지 않습니다. ㅜㅜ 아래 그림처럼 fruit 혹은 state 인덱스 중 하나에만 접근하여 데이터에 접근하게 됩니다. 물론, SQLite 내부적으로 분포도를 고려해서 처리되겠죠. ^^

그렇다면, 여기서 fruit와 state 칼럼 모두 포함하는 인덱스를 생성한다면 어떻게 될까요? 아래 그림처럼, 한번에 데이터에 접근하게 됩니다. ^^

이 경우 앞에서와 같이 fruit에만 조건이 있는 경우를 걱정할 수도 있겠지만, 인덱스의 멋진점은 바로 이것!! fruit로만 조건이 들어와도 데이터를 추출할 수 있습니다.

인덱스를 생성한 칼럼 순서로 Where 조건이 들어오면 정상적으로 인덱스를 타게 되는 것이죠. ^^ 위에서는 (fruit, state) 순으로 인덱스를 생성하였고, fruit이 Where 조건으로 들어왔기 때문에, 정상적으로 인덱스를 활용하게 되는 것이죠.

그렇다면, state만 검색 조건으로 들어온 경우는 어떨까요? 과연 (fruit, state)에 걸린 인덱스를 정상적으로 활용할 수 있을까요? (아~! 앞에서 state 칼럼에만 인덱스를 건 경우는 무시해주세요. 없다고 가정하세요. ^^;;)

결론은 “풀스캔”입니다. ㅜㅜ

Conclusion

오늘은 Full-Scan, RowID, Index를 통한 데이터 Searching에 대해 정리해보았습니다. 설명을 더욱 간결하고 쉽게 해야하는데.. 어떠셨는지.. 자~! 여기까지 정리하겠습니다.

Full-Scan은 RowID 순으로 데이터에 순차적으로 접근하여 원하는 데이터를 추출하는 방식으로 일반적으로 가장 느린 데이터 접근 방식이다.
데이터의 위치를 지칭하는 주소 역할을 하는 RowID로 데이터에 접근하는 것이 가장 빠르다.
인덱스는 인덱싱할 칼럼을 “키”값으로, 그리고 접근할 데이터의 RowID를 “값”으로 “B-Tree”로 구성된 자료구조이다.

아웅.. 정리하고 나니, 내용이 많지 않네요. ㅜㅜ 오늘 마무리 못한, 데이터 접근에 대한 얘기를 다음번 포스팅에서 남은 이야기를 정리하도록 할께요.

SQLite 2탄 – 데이터와 인덱스 구조!!

Posted on 2013-09-01 | by gywndi | 3 Comments

여름은 거의 막바지로 치닫은 지금, 벌초 시즌이 찾아왔네요. 시원할줄만 알았던 산속 아침이 왜이렇게 따갑고, 오늘따라 산은 왜이렇게 가파르게 느껴지던지.. 집에 돌아오자마자, 쓰러졌습니다. ^^;

Overview

지난 1탄에 이어 SQLite에 대해 간단하게 포스팅하려 합니다. 어떤 솔루션을 사용하든, DB를 사용함에 있어서, 논리적인 데이터 모델을 정확하게 알고 있는 것이 중요하다고 생각합니다.

SQLite 역시 Database이고, 데이터 또한 나름의 구조를 가지고 있고, 재미있는 특성이 있죠. ^^

Data 구조

먼저 Data의 생김새를 먼저 살펴보도록 하겠습니다. SQLite의 각각의 Row는 고유의 8Byte 정수 타입의 RowID를 가집니다. 그리고 RowID 순서로 데이터는 저장됩니다. RowID가 생소한 분이 계실 수도 있겠네요. ^^ 특히 MySQL을 거의 사용하셨던 분에게는 낯선 용어가 될 수도 있겠죠.

RowID는 말 그대로 Row의 ID를 의미하며, DBMS마다 생김새는 다르지만, 특정한 행에 접근하기 위한 “주소”와 동일한 역할을 하는 개념이라고 생각하면 됩니다.

SQLite Data

오라클에서라면, RowID 안에는 Row가 존재하는 물리적인 정보, 즉 블록 정보와 같은 항목들이 RowID에 포함이 됩니다. 이와 반대로 MySQL의 InnoDB에서는 RowID 역할을 Primary Key가 수행합니다. PK순으로 구성된 B-트리를 통해 데이터의 위치를 찾아가는 것이죠.

그렇다면, SQLite에서의RowID특성은 어떨까요? 바로 방금 말씀드린 것대로, 8바이트의 정수 타입으로 RowID가 일단은 “내부적”으로 관리됩니다. 백문이 불여일견!! SQLite에서 다음과 같은 테이블을 생성을 하고 데이터를 넣습니다.

CREATE TABLE test(
  no integer,
  id integer
);

sqlite> INSERT INTO test VALUES (45, 1);
sqlite> INSERT INTO test VALUES (40, 2);
sqlite> INSERT INTO test VALUES (10, 3);

3건의 데이터를 Insert한 이후에 전체 데이터를 다음과 같이 조회해보도록 하죠.

sqlite> SELECT * FROM test;
45|1
40|2
10|3

두둥! 당연히 위에서 넣은 순서로 데이터를 볼 수 있습니다. 그렇다면, 여기에서 RowID를 조회해볼까요? 동일한 조건에서 아래처럼 “rowid”를 포함하여 조회 쿼리를 수행합니다.

sqlite> SELECT rowid, * FROM test;
1|45|1
2|40|2
3|10|3

헉!! 새로운 데이터 1,2,3이 보입니다!! 이것이 바로 SQLite에서 8Byte 정수형 데이터 RowId입니다!! 앞서 RowID가 일단은 “내부적”으로 관리된다는 말에서 눈치 빠르신 분들은 다른 무언가가 있다는 것을 아셨을 것으로 생각되는데요, 그렇습니다. SQLite에서 RowID가 반드시 내부적으로만 관리되는 것이 아닙니다.

만약 Primary Key가 정수 타입인 경우, Primary Key가 RowID역할을 대체합니다. 다음 예제를 보시죠.

CREATE TABLE test(
  no integer primary key,
  id integer
);

sqlite> INSERT INTO test VALUES (45, 1);
sqlite> INSERT INTO test VALUES (40, 2);
sqlite> INSERT INTO test VALUES (10, 3);

앞 예제랑은 no칼럼이Primary Key로 선언되었다는 것 외에는 전~혀 다른 것이 없습니다. 그리고 데이터를 45, 40, 10 순으로 넣었으니 당근 결과 또한 넣은 순서로 나와야될 것이라고 생각하겠죠?

sqlite> SELECT * FROM test;
10|3
40|2
45|1

그러나 예상과는 다르게, 결과값이 순서대로 출력되지 않습니다. 왜그럴까요? 이번에는 RowID를 포함하여 조회해봅시다.

sqlite> SELECT * FROM test;
10|10|3
40|40|2
45|45|1

Primary Key가 없는 상태에서는 분명 1부터 순차적으로 증가했던 RowID가 이번에는 Primary Key와 동일한 값으로 저장이 되어 있습니다. 데이터는 RowID 순서로 저장되기 때문에 결과적으로 넣은 순서와는 다르게 보여지는 것이죠. ^^

만약 RowID값이 어플리케이션 로직과 밀접하게 연관이 되어있다면, (제 경우 오라클에서 조금이라도 빠르게 데이터에 접근하기 위해 RowID자체를 칼럼에 포함한 적도 있습니다. ^^;;) 반드시 명시적으로 RowID가 정확하게 관리가 되어야합니다. 이 경우 “INTEGER PRIMARY KEY AUTOINCREMENT”처럼 자동 증분하는 형태로 Primary Key를 정의한다면, 현재 RowID값은 어떠한 경우에도 변경되는 일은 없겠죠. ^^

Index 구조

인덱스 구조를 알아볼까요? 인덱스에 대한 설명은 심~플 그 자체입니다.

Index는 인덱싱을 하는 “타겟 칼럼을 Key”로 하고, “Value값으로 RowID값”을 가집니다. RowID가 데이터가 위치하는 정보를 포함하는 키이므로, 당연한 이야기겠죠. ^^;

너무 짧다고요? 그래서 인덱스에 대한 내용을 조~금 더 붙여봤습니다.

SQLite도 나름 실행계획이 있고, 실행계획을 바탕으로 빠르게 데이터를 접근하고자 통계 정보를 관리합니다. 통계 정보에 저장된 데이터 분포도 값을 기준으로 어떤 인덱스를 타야 가장 빠르게 데이터에 접근할 수 있는지를 “재빠르게” 결정하는 것이죠.

이것과 관련된 테이블이 바로 “sqlite_stat1″라는 놈입니다. 그런데 아쉽게도, 이놈은 SQLite DB파일이 만들어지면서 자동으로 생성되지는 않습니다. ㅜㅜ Analyze 이후에 생성 혹은 업데이트되는 테이블이죠. 만약, 아~무런 조작없이 SQLite DB파일을 만들고 테이블을 생성한 이후에 sqlite_stat1 테이블을 조회하게 되면, 없는 테이블이라고 나옵니다.

간단한 예제를 준비했습니다. ^^ 아래와 같은 테이블과 인덱스를 생성한 이후에 10 건의 데이터를 넣습니다.

CREATE TABLE test(
  no integer primary key,
  id integer,
  c1 integer,
  c2 integer
);
sqlite> CREATE UNIQUE INDEX ux01 ON test(id);
sqlite> CREATE INDEX ix01 ON test(c1);
sqlite> CREATE INDEX ix02 ON test(c1, c2);

sqlite> INSERT INTO test VALUES (1, 10, 3, 4);
sqlite> INSERT INTO test VALUES (2, 11, 3, 5);
sqlite> INSERT INTO test VALUES (3, 12, 3, 6);
sqlite> INSERT INTO test VALUES (4, 13, 3, 7);
sqlite> INSERT INTO test VALUES (5, 14, 3, 8);
sqlite> INSERT INTO test VALUES (6, 15, 3, 9);
sqlite> INSERT INTO test VALUES (7, 16, 3, 10);
sqlite> INSERT INTO test VALUES (8, 17, 3, 11);
sqlite> INSERT INTO test VALUES (9, 18, 3, 12);
sqlite> INSERT INTO test VALUES (10, 19, 3, 13);

그리고 sqlite_stat1 테이블을 조회해봅니다. 역시나, 없는 테이블이라고 나옵니다. (단, 이미 Analyze를 하시고, 있다고 우기시면, 저 울꺼예요!! -_-++)

sqlite> SELECT * FROM sqlite_stat1;
Error: no such table: sqlite_stat1

이제 아래처럼 Analyze를 수행하고, 바로 방금 쿼리를 똑같이 질의합니다. 엇! 무언가 결과가 나옵니다.

sqlite> SELECT * FROM sqlite_stat1;
test|ix02|10 10 1
test|ix01|10 10
test|ux01|10 1

“테이블명|인덱스명|데이터건수 카디널리티” 형태로 출력됩니다. 카디널리티(Cardinality)란 데이터 중복 건 수라고 간단하게 생각하면 되는데, Primary Key혹은 Unique Key인 경우는 당연히 1이되겠죠. 그리고 평균 카디널리티 값이 낮을수록 인덱스 효율이 좋다고 보면 됩니다. 더욱 자세한 내용은 패~스!

위에서 가장 하단의 ux01은 유니크 속성의 키입니다. 총 데이터는 10건이었고, 유니크 속성이므로 가장 뒤에는 1이 붙습니다. 효율 좋은 인덱스죠. 이와는 다르게 ix01을 보면 모두 동일한 값입니다. 이 경우 10, 최악의 효율을 자랑(?)하죠. 만약 ix02와 같이 두 개 칼럼을 붙여서 인덱스를 생성하였다면, 칼럼 순으로 카디널리티가 보여집니다. c1 혼자라면 최악의 효율이겠지만, c2와 함께라면 유니크 인덱스와 맞먹을 만큼 좋은 효율을 가집니다.

SQLite에서 쿼리가 자꾸 예상 밖으로 이상하게 실행되고 느리게만 나온다면, 한번쯤은 Analyze를 해보는 것도 좋은 생각입니다. 그러면 데이터 분포도를 참고하여 나름 최적의 실행계획으로 데이터를 추출하게 되죠. 단, Analyze는 자동으로 수행되지 않는다는 점과 모바일 환경에서는 상당한 부담이 될 수 있는 작업인만큼, 수행 시점을 “반드시” 심사숙고하여 결정하시기 바래요. ^^

Conclusion

사실 장황하게만 늘어놨지, 오늘 공유드린 내용은 참으로 간단합니다.

Data / Index는 모두 B- Tree로 관리
Data는 RowID 순으로 관리
Index는 RowID를 Value로 가짐

예상치 못한 결과에 당황하셨다면, 한번 쯤은 데이터 및 인덱스 구조를 한번 의심해보세요. 그리고 쿼리 결과가 이상하다면, 엄한 인덱스를 타고 있는지, 데이터 분포도가 어떤지도 파악해볼만한 요소입니다.

벌초 후 피곤해서, 간단하게 적는다는게 또 주저리주저리 늘어놨군요. ^^;

이어질 다음 SQLite 시리즈 포스팅도 기대(?)해주세요.

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

Posted on 2013-08-25 | by gywndi | Leave a Comment

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

SQLite?

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

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

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

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

Nested Loop
File Based Processing
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의 이 특징을 사용하여 트랜잭션을 사용할 수 있도록 제공하는 것이죠.

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

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

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

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

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

DELETE (Default)
Rollback Journal File을 매번 Create/Delete
TRUNCATE
Rollback Journal을 매번 Create/Delete하지 않고, 파일 사이즈만 0으로 설정 후 재활용
MEMORY
저널링 파일을 메모리에서 유지. SQLite를 사용하는 어플리케이션 장애 시 데이터 유실
PERSIST
저널링 파일을 지우지 않음.
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~!

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

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

Conclusion

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

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

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

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