Row 사이즈가 큰 테이블은 분리하여 트랜잭션 로그를 줄이자
조회 수 5958 추천 수 0 2010.08.13 02:36:34Row 사이즈가 큰 테이블은 분리하여 트랜잭션 로그를 줄이자
소개: 데이터 조회 작업이 주된 작업이면서 조회수를 업데이트 시키는 게시판 형태의 프로그램에 사용되는 테이블을 분리해서 트랜잭션 로그의 크기를 줄여 UPDATE 성능을 향상시키는 방법에 대해 소개한다.
적용 대상: CUBRID2008
CUBRID는 데이터의 변경(INSERT, UPDATE, DELETE 등)이 발생할 때 recovery를 위해 트랜잭션 로그를 생성한다. 특히 UPDATE, DELETE와 같이 기존 데이터의 변형이 발생하는 경우에는 이전 데이터에 대해 undo 로그를 생성하고 새로운 데이터에 대해 redo 로그를 생성한다.
CUBRID에서는 undo/redo 로그가 db_name_lgat, db_name_lgar000, db_name_lgar_t 이라는 볼륨 파일에 저장된다.
db_name_lgat가 현재의 undo/redo 로그가 기록된 active log 볼륨이고, 이 로그볼륨을 모두 사용하게 되면 db_name_lgar000, db_name_lgarxxx 형태로 active log를 백업하고 active log 볼륨을 재사용한다. 이 백업된 로그 볼륨을 archive log라고 한다.
active log가 꽉 차면 데이터의 변경 작업은 active log가 비워질 때까지 기다리게 된다.
위에서도 이야기 했듯이 active log를 비우기 위해서 archive log가 생성되는데 log 볼륨의 크기가 클수록 이 시간은 길어지게 된다.
이 시간을 줄이기 위해 CUBRID에서는 active log의 내용을 checkpoint 단위로 미리 복사해두는데 이때 사용되는 파일이 db_name_lgar_t이다.
create table board(
id integer not null,
category integer,
title varchar(250),
contents varchar(1073741823),
...
read_count integer,
regdate timestamp,
last_update timestamp)
위 테이블 생성구문은 일반적으로 사용되는 게시판의 테이블 생성구문이다.
게시판 형태의 응용의 특징은 주로 게시물 목록을 먼저 보여주고, 선택된 게시물의 상세내역 조회가 이루어지면서 해당 게시물이 얼마나 읽혔는지를 관리하기 위해 read_count와 같은 컬럼을 두고 읽혀질 때 마다 count를 업데이트 시키는 작업을 하는 것이다.
즉, 상세내역 보여 주면서 아래와 같이 UPDATE 구문이 매번 호출된다는 것이다.
UPDATE board SET read_count=read_count+1 WHERE id=1;
참고로 CUBRID에서는 위와 같이 조회와 함께 조회수 업데이트가 수행되는 작업을 하나의 SQL에서 처리해주는 클릭 카운터라는 함수를 제공한다. 자세한 사항은 CUBRID 매뉴얼>CUBRID SQL 설명서>연산자와 함수>클릭 카운터 함수 부분을 참고하기 바란다.
앞 트랜잭션 로그 소개에서 잠깐 설명했듯이 update, delete와 같이 기존 데이터의 변형이 발생하는 경우에는 이전 데이터에 대해 undo 로그를 생성하고 새로운 데이터에 대해 redo 로그를 생성한다. 따라서 위 UPDATE 구문 수행에도 동일하게 undo/redo 로그가 생성된다.
이때 undo/redo 로그에는 변경된 read_count에 대한 로그만 저장되는 것이 아니라 테이블 board 전체 컬럼에 대한 undo/redo 로그가 생성된다. 변경된 값은 integer 타입인 read_count만 변경되더라도 게시물의 상세내역이 들어있어 사이즈가 매우 큰 contents를 포함하여 해당 row 전체가 로그에 기록된다는 것이다. 이로 인해 수행한 작업에 비해 불필요한(?) 큰 작업이 수행되어 active log의 사용량이 많아지고, archive log의 생성이 빈번하게 되어 오버헤드가 발생하게 된다.
따라서 contents와 같이 사이즈가 큰 컬럼을 분리하게 되면 기록되는 로그의 양이 줄어들게 되고, 이는 UPDATE 수행 성능 향상으로 이어진다. 위 샘플 테이블은 아래와 같이 분리하여야 한다.
create table board(
id integer primary key,
category integer,
title varchar(250),
...
read_count integer,
regdate timestamp,
last_update timestamp);
create table board_contents(
id integer primary key,
contents varchar(1073741823))
테이블 분리 전과 후의 성능을 비교해보기 위해 위에 제시한 UPDATE 구문을 수행하여 보았다.
#테이블 분리 전
ssihil2@newTest1 tdb$ time csql -u dba -S -c "update board set read_count=read_count+1" tdb1
real 0m0.316s
user 0m0.048s
sys 0m0.101s
#테이블 분리 후
ssihil2@newTest1 tdb$ time csql -u dba -S -c "update board set read_count=read_count+1" tdb2
real 0m0.128s
user 0m0.019s
sys 0m0.079s
테스트 데이터가 많지 않아 큰 수치는 아니지만 대략 2배 이상의 차이를 보여주고 있다.
archive log가 생성되는 시점의 성능도 확인을 위해 위 update를 반복하여 수행하였더니 테이블을 분리하지 않았던 경우에는 archive log가 생성되는 시점에 0m0.588s, 0m0.697s 정도의 처리 속도를 보여 줬으며, 분리한 경우에는 동일한 횟수로 실행하였어도 archive log가 생성되지 않았고 0m0.154s 정도의 처리 속도를 보여 주었다.
이러한 테스트 결과로 비춰볼 때 row의 사이즈가 큰 테이블은 따로 분리하는 것이 성능에 유리한 구성이라는 것을 알 수 있다.