게시판 기능을 통해 오픈소스 DBMS를 살펴보자.
출처: 월간 마이크로소프트웨어 2009년 6월호
인터넷 환경에서의 게시판, 그리고 DBMS
어느새 인터넷하면 웹 환경을 떠올리게 되었고, 그만큼 웹 환경은 대중화되어 이제 대중의 생활 속에 자리잡고 있다.
웹 환경 초기가 html 문서 등에 의해 단순 출력되는 환경이었다면, 지금은 사용자가 직접 작성하고 검색해보는 입/출력 환경이 기본이 되었다. 이러한 웹 환경에서 데이터의 입/출력 및 검색 등의 작업을 하는 대표적인 유형이 게시판이다. 게시판을 지원하기 위해서는 글 쓰기, 글 읽기, 목록 보기, 메모 달기 등의 기능을 기본적으로 지원해야 하는데, 이렇게 쌓이는 글 정보를 관리하는 기반에는 항상 DBMS가 있다.
이러한 기능을 구현하기 위해 DBMS가 처리해야 할 작업을 오픈 소스 DBMS인 CUBRID, MySQL, PostgreSQL을 대상으로 DBMS 질의(query) 수준에서 살펴보고자 한다. 이와 같이 하면 각 DBMS가 가지는 특징을 사용자 입장에서 어느 정도 볼 수 있기 때문이다.
스키마 정보
<표1> 이 글에서 만들 게시판의 스키마 정보이다. 각 테이블의 Primary key는 컬럼 이름에 PK라고 명시하였다. comments 테이블의 경우는 doc_id, comment_no 2개의 컬럼이 Primary key로 사용된다.
<표 1> documents 테이블 스키마
컬럼 이름 |
데이터 타입 |
설명 |
doc_id (PK) |
int |
게시글을 구분하는 정수 값 |
doc_title |
string |
글 제목 |
preview_text |
string |
게시글 내용에 첫 2-3줄의 내용 |
writer |
varchar(30) |
글쓴이 |
posted_time |
timestamp |
글이 작성된 시간 |
read_count |
int |
조회 수 |
content |
string |
글 내용 |
<표 2> comments 테이블 스키마
컬럼 이름 |
데이터 타입 |
설명 |
doc_id (PK) |
int |
게시글을 구분하는 정수 값 |
comment_no (PK) |
int |
하나의 글에 달린 메모의 ID |
comment |
string |
메모 내용 |
documents 테이블의 doc_id 컬럼은 게시글의 번호를 나타내며 게시글을 구분하기 위하여 유일한 값을 가져야 한다. doc_id에 순차적으로 증가하는 일련 번호(AUTO_INCREMENT 혹은 serial)를 사용하면 DBMS에서 일련 번호를 자동으로 부여하므로 사용자가 doc_id를 유일한 값으로 입력하지 않아도 된다. 표 3은 DBMS별로 doc_id를 일련 번호로 지정하는 documents 테이블을 생성하는 질의문이다.
<표 3> documents 테이블 생성 질의문
DBMS |
SQL |
CUBRID |
CREATE TABLE documents (doc_id int AUTO_INCREMENT, …, PRIMARY KEY (doc_id)) |
MySQL | |
PostgreSQL |
CREATE TABLE documents (doc_id serial, …, PRIMARY_KEY (doc_id)) |
테이블 생성이 끝났으니 이제부터 게시판의 각 기능과 질의문을 살펴보자.
글 쓰기
글 쓰기는 새로운 글 추가와 메모 달기 이렇게 두 가지가 있다. 두 개의 글 쓰기 모두 SQL의 INSERT 문으로 수행한다. 새로운 글 추가는 doc_id의 documents 테이블에 새 내용을 추가하고, 메모 달기는 comments 테이블에 새 내용을 추가한다. documents 테이블의 doc_id 컬럼은 자동 증가 속성 (AUTO_INCREMENT 혹은 serial) 으로 정의되어 있으므로 INSERT 시 doc_id 컬럼을 생략해도 된다. 표 4는 새로운 글 추가와 메모 달기에 대한 질의문이다.
<표 4 >글 쓰기 질의문
DBMS |
SQL |
CUBRID |
INSERT INTO documents (doc_title, preview_text, writer, posted_time, read_count, content) values (?, ?, ?, ?, ?, ?); INSERT INTO comments (doc_id, comment_no, comment) values (?, ?, ?); |
MySQL | |
PostgreSQL |
글 읽기
글 읽기는 하나의 doc_id를 이용하여 documents, comments 테이블에서 정보를 가져오며, 조회 수 관리를 위해 documents 테이블의 read_count 컬럼의 값을 증가시킨다.
대부분의 게시판은 최근 몇 개의 글과 이슈가 되는 특정 글들에 읽기가 집중되는 경향을 보인다. 같은 글을 여러 사용자가 동시에 조회하게 되면 각 사용자 별로 조회 수를 증가시키는 UPDATE 문을 수행해야 한다. 이 때 하나의 글에 UPDATE가 집중되어 update lock대기로 인해 결국 글 읽기의 응답 속도가 늦어지게 된다. 이러한 문제점을 해결하기 위해서 조회 수 증가 시 update lock으로 인한 성능 저하를 회피하는 방안을 강구해야 한다.
CUBRID에서는 클릭 카운터(Click Counter)라는 기능이 있어 이러한 문제점을 간단히 해결할 수 있다. 클릭 카운터는 글 정보를 가져오는 SELECT 질의와 읽은 글의 조회 수를 증가하는 UPDATE 질의를 질의 하나로 수행할 수 있다. 같은 검색 조건을 두 번 탐색하지 않고 한 번만 탐색하기 때문에 성능 상의 이점이 있으며, UPDATE 질의 수행 시에 유지하는 update lock보다 짧은 기간 동안 update lock을 유지하기 때문에 동시성이 높고 응답 시간이 짧다.
표 5는 DBMS별 글 보기의 질의문이다. CUBRID 질의문의 incr(read_count) 부분이 클릭 카운터를 사용하는 부분이며, incr()의 인자로 주어진 read_count 컬럼을 해당 질의가 수행될 때마다 1씩 증가시킨다. MySQL과 PostgreSQL의 질의문에서는 직접 조회 수를 증가하도록 처리하였다.
<표 5> 글 보기 질의문
DBMS |
SQL |
CUBRID |
SELECT incr(read_count)…. FROM documents WHERE doc_id = ?; SELECT … FROM comments WHERE doc_id = ?; |
MySQL |
SELECT ….. FROM documents WHERE doc_id = ?; SELECT … FROM comments WHERE doc_id = ?; |
PostgreSQL |
목록 보기
목록 보기는 글 제목을 목록으로 만들어 한 화면에 보여주는데 지금까지 등록된 모든 글 제목을 한번에 모두 보여주기 보다는 정해진 개수씩 (예: 20개) 보여준다. 각 DBMS별 질의는 표 6과 같다. 게시판에서는 대부분 작성 날짜 등의 기준으로 정렬하여 보여주며 이를 구현하기 위하여 order by절을 사용하게 되는데, 일단 전체를 조회한 결과를 정렬한 후 그 중 주어진 범위 (예: CUBRID의 경우 ORDERBY_NUM() BETWEEN 20000 AND 20020, MySQL과 PostgreSQL의 경우 limit 20000, 20) 에 해당하는 글만을 추출하게 되면 질의 처리가 느려진다.
이 상황을 극복하기 위해서는 전체를 조회하지 않고 정렬된 상태의 일부만을 조회할 수 있어야 하며 이를 위해서는 인덱스를 잘 활용해야 한다. CUBRID경우에는 order by 대상에 인덱스를 생성하면 검색 결과가 해당 인덱스의 키 순서로 출력된다. 따라서, posted_time으로 인덱스를 생성하면 별도의 order by 작업 없이 정렬된 결과를 얻을 수 있다. 역순(desc)으로 정렬된 결과를 얻고 싶은 경우 인덱스의 키 값이 큰 값부터 작은 값 순으로 저장되는 역 인덱스(reverse index)를 생성하면 된다.
CUBRID에서 역 인덱스를 생성하는 예는 다음과 같다.
CREATE REVERSE INDEX documents_rev_idx ON documents (posted_tiime);
<표 6> 목록보기 질의 문
DBMS |
SQL |
CUBRID |
SELECT doc_title, preview_text, posted_time, read_count FROM documents ORDER BY posted_time DESC FOR ORDERBY_NUM() BETWEEN ? /* page_start_no */ AND ? /* page_end_no */; |
MySQL |
SELECT doc_id, doc_title, preview_text, writer, posted_time, read_count FROM documents ORDER BY posted_time DESC LIMIT ? /* page_start_no */, ? /* page_size */ |
PostgreSQL |
이와 같이 질의를 효율적으로 수행하기 위해서는 질의를 수정하거나, 필요한 인덱스를 생성해야 한다. 이러한 질의 튜닝 과정에서는 현재 질의를 DBMS가 어떻게 수행하는지를 확인해야 하며, 모든 DBMS는 질의 수행 계획을 보여주는 기능을 제공한다. 표 7은 각 DBMS별로 질의 수행 계획을 보는 방법이다.
<표 7> 질의 수행 계획 보기
DBMS |
SQL |
CUBRID |
SET OPTIMIZATION LEVEL 513; |
MySQL |
EXPLAIN SELECT * FROM comments; |
PostgreSQL |
효율적인 DB 활용
Connection pool 사용하기
응용 프로그램이 매번 연결을 요청할 때마다 연결을 생성하는 것은 오버헤드가 되는데, 이를 줄이기 위해 연결 요청을 최소화하는 방법으로 응용에서 연결 풀(connection pool)을 사용하는 방법이 있다. 연결 풀은 DBMS에 여러 개의 연결을 만들어 놓은 후, 응용 프로그램이 요청할 때 이 연결 풀에서 연결을 가져와서 사용하고, 사용 후에 연결을 풀에 돌려주는 방식으로 동작한다. 초기에 한 번만 연결하기 때문에 이후에 연결을 사용할 때는 오버헤드가 없다.
이러한 연결 풀은 DBMS에서 지원하는 기능은 아니며, 응용 프로그램에서 직접 연결 풀을 만들어 사용하거나, 별도의 클라이언트 호스트에서 드라이버가 제공하는 것을 사용하면 된다. JDBC의 경우 아파치 DBCP컴포넌트가 연결 풀 기능을 제공한다. 연결 풀을 사용할 때는 연결이 매번 새로 만들어 지는 것이 아니므로, 개발할 때 좀 더 많은 주의가 필요하다. 연결 풀의 구현에 따라 이전에 연결을 사용하던 상태가 남아 있을 수도 있고, 연결을 돌려줄 때 자신이 사용하던 Statement나 ResultSet들을 닫아주지 않으면 해당 연결이 사용 중으로 간주되어 재사용하지 못할 수 있다.
Statement pool 사용하기
게시판 응용은 동일한 질의문을 자주 사용하는 패턴을 보인다. 같은 질의가 수행될 때마다 매번 질의 컴파일을 수행하는 것은 비효율적이다. 이 과정을 생략하기 위해 DBMS는 질의 컴파일 결과를 저장해 두고 재사용하는 기능을 제공하는데, MySQL이나 PostgreSQL는 별도의 설정이 필요 없고, CUBRID는 cubrid_broker.conf에 statement_pooling을 “ON”으로 설정해 주어야 한다. 이 기능을 활용하기 위해서는 질의 풀(Statement pool)을 만들어 사용하여야 하는데, 질의 풀은 처음 질의를 수행할 때만 질의를 컴파일(prepare)하고 이후에는 수행(execute)만 한다.
이처럼 하면 중복된 질의 컴파일이 없게 되어 질의 수행 성능이 향상된다. 질의 풀도 연결 풀과 같이 응용에서 직접 만들어 사용하거나, JDBC의 경우 아파치 DBCP컴포넌트를 사용하면 된다. DBCP에서 질의 풀을 설정하는 방법은 다음과 같다.
<data-source class-name="org.apache.commons.dbcp.BasicDataSource">
<param name="driver-class-name" value="cubrid.jdbc.driver.CUBRIDDriver" />
<param name="username" value="public" />
<param name="password" value="" />
<param name="url" value="jdbc:cubrid:localhost:33000:test:::" />
<param name="max-active" value="10" />
<param name="pool-prepared-statements" value="true" />
</data-source>
Query Result Cache 사용하기
질의 결과 캐시(Query Result Cache)는 질의 결과를 저장해 두고 질의 조건까지 동일한 질의문이 입력될 경우, 모든 처리 과정을 생략하고 저장했던 질의 결과를 응용에게 전달하는 기능이다. 대부분의 경우 질의에 포함된 테이블의 내용이 변경이 되면 저장된 내용을 사용할 수 없기 때문에, 거의 변경이 없는 테이블에 접근하는 질의에 대해서만 질의 결과 캐시를 사용해야 하며, 그렇지 않으면 오히려 질의 결과 캐시를 사용하는 오버헤드만 증가하게 된다. 현재 이 기능은 PostgreSQL은 지원하지 않고, CUBRID, MySQL는 제공한다. 설정 방법은 CUBRID의 경우에는 cubrid.conf에 max_query_cache_entries, query_cache_mode, query_cache_size_in_pages 값들을 설정해 주면 되고, MySQL의 경우에는 my.cnf에 query_cache_limit, query_cache_min_res_unit, query_cache_size, query_cache_type 값들을 설정해 주면 된다.
지금까지 인터넷 게시판 구현에서 CUBRID, MySQL, PostgreSQL의 사용 방식을 비교해 보았다. 세 DBMS에서 사용되는 질의문은 글 읽기와 목록보기를 표현하는 데 있어 차이를 보였다. 비교 정리한 내용은 표 8과 같다.
<표 8>질의 문 비교 정리
기능 |
구분 |
SQL |
글 쓰기 |
CUBRID |
INSERT INTO document (doc_title, preview_text, writer, posted_time, read_count) VALUES ( ?,?,?,?,?); |
MySQL | ||
PostgreSQL | ||
글 읽기 |
CUBRID |
SELECT content, INCR(read_count) FROM documents WHERE doc_id = ?; |
MySQL |
SELECT content FROM documents WHERE doc_id = ?; UPDATE documents SET read_count = read_count + 1 WHERE doc_id = ?; | |
PostgreSQL | ||
목록 보기 |
CUBRID |
SELECT doc_title, preview_text, posted_time, read_count FROM documents ORDER BY posted_time DESC FOR ORDERBY_NUM() BETWEEN ? AND ?; |
MySQL |
SELECT doc_title, preview_text, posted_time, read_count FROM documents ORDER BY posted_time DESC LIMIT ?, ?; | |
PostgreSQL |
게시판 환경에서 DBMS상의 성능 튜닝은 주로 재사용 기능을 설정하는 방식이었다. 응용과의 연결 재사용은 DBMS에서 별다른 지원은 필요 없었고, DBMS에서 컴파일된 질의를 재사용하는 기능은 세 제품이 모두 지원된다. 그리고 질의 결과 캐싱은 CUBRID 와 MySQL만 제공 하고 있다. 다음은 이 기능들을 사용하기 위해서 DBMS에 설정해야 하는 값들을 정리한 표이다.
<표 9> 기능별 DBMS 설정하기
|
Connection Pool |
Statement Pool |
Result cache |
CUBRID |
응용에서 지원 |
cubrid_broker.conf에서 STATEMENT_POOLING=ON |
max_query_cache_entries |
MySQL |
상동 |
별도 설정 없이 지원 |
query_cache_limit query_cache_min_res_unit query_cache_size query_cache_type |
PostgreSQL |
상동 |
별도 설정 없이 지원 |
지원 안됨 |
참고자료
저자소개> 이동현 brightest_at_naver.com 메인 메모리 DB 업체에서 개발 업무를 수행한 바 있고, 국내 포털 업체에서 시스템 소프트웨어 분야의 경험과 기술력을 쌓았다. 현재는 NHN에서 오픈소스 DBMS인 CUBRID 개발과 관련된 업무를 맡고 있으며, 시간이 날 때마다 택견과 볼링을 즐긴다.