웹 게시판 구축의 밑거름 오픈소스 DBMS(DBguid.net)

출처 :  http://www.dbguide.net/knowledge.db?cmd=view&boardUid=126306&boardConfigUid=19&boardStep=&categoryUid=209

웹 게시판 구축의 밑거름

오픈소스 DBMS

 

어느새 우리는 인터넷하면 웹 환경을 떠올리게 되었고, 그만큼 웹 환경은 대중화되어 이제 대중의 생활 속에 자리 잡고 있다. 
웹 환경 초기가 html 문서 등에 의해 단순 출력되는 환경이었다면, 지금은 사용자가 직접 작성하고 검색해보는 입출력 환경이 기본이다. 이러한 웹 환경에서 데이터의 입출력 및 검색 등의 작업을 하는 대표적인 유형이 바로 게시판이다. 게시판을 지원하기 위해서는 글 쓰기, 글 읽기, 목록 보기, 메모 달기 등의 기능을 기본적으로 지원해야 하는데, 이렇게 쌓이는 글 정보를 관리하는 기반에는 항상 DBMS가 있다.

 

이러한 기능을 구현하기 위해 DBMS가 처리해야 할 작업을 오픈소스 DBMS인 CUBRID, MySQL, PostgreSQL을 대상으로 DBMS 질의(query) 수준에서 살펴보고자 한다. 이와 같이 하면 각 DBMS가 가지는 특징을 사용자 입장에서 어느 정도 볼 수 있기 때문이다.

 

스키마 정보

 

<표 1>은 이 글에서 만들 게시판의 스키마 정보다. 각 테이블의 Primary key는 컬럼 이름에 PK라고 명시했다. comments 테이블의 경우는 doc_id, comment_no 2개의 컬럼이 Primary key로 사용된다.

 

컬럼 이름	데이터 타입	설명
doc_id (PK)	int	게시글을 구분하는 정수 값
doc_title	string	글 제목
preview_text	string	게시글 내용에 첫 2-3줄의 내용
writer	varchar(30)	글쓴이
posted_time	timestamp	글이 작성된 시간
read_count	int	조회 수
content	string	글 내용

<표 1> documents 테이블 스키마

 

컬럼 이름	데이터 타입	설명
doc_id (PK)	int	게시글을 구분하는 정수 값
comment_no (PK)	int	하나의 글에 달린 메모의 ID
comment	string	메모 내용

<표 2> comments 테이블 스키마

 

documents 테이블의 doc_id 컬럼은 게시글의 번호를 나타내며 게시글을 구분하기 위해 유일한 값을 가져야 한다. doc_id에 순차적으로 증가하는 일련번호(AUTO_INCREMENT 혹은 serial)를 사용하면 DBMS에서 일련번호를 자동으로 부여하므로 사용자가 doc_id를 유일한 값으로 입력하지 않아도 된다. <표 3>은 DBMS별로 doc_id를 일련번호로 지정하는 documents 테이블을 생성하는 질의문이다.

 

DBMS	SQL
CUBRID	SQL CUBRID CREATE TABLE documentsint AUTO_INCREMENT, …, PRIMARY KEY (doc_id))
MySQL
PostgreSQL	CREATE TABLE documents (doc_id serial, …, PRIMARY_KEY (doc_id))

<표 3> documents 테이블 생성 질의문

 

테이블 생성이 끝났으니 이제부터 게시판의 각 기능과 질의문을 살펴보자.

 

글 쓰기

 

글 쓰기는 새로운 글 추가와 메모 달기 이렇게 두 가지가 있다. 두 개의 글 쓰기 모두 SQL의 INSERT 문으로 수행한다. 새로운 글 추가는 doc_id의 documents 테이블에 새 내용을 추가하고, 메모 달기는 comments 테이블에 새 내용을 추가한다. docu ments 테이블의 doc_id 컬럼은 자동 증가 속성(AUTO_ INCREMENT 혹은 serial)으로 정의돼 있으므로 INSERT 시 doc_id 컬럼을 생략해도 된다. <표 4>는 새로운 글 추가와 메모 달기에 대한 질의문이다.

 

DBMS	SQL
CUBRID	INSERT INTO documents (doc_title, preview_text, writer, posted_time, read_count, content) VALUES(?, ?, ?, ?, ?, ?); PostgreSQL INSERT INTO comments (doc_id, comment_no, comment) VALUES (?, ?, ?);
MySQL
PostgreSQL

<표 4> 글 쓰기 질의문

 

글 읽기

 

글 읽기는 하나의 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의 질의문에서는 직접 조회 수를 증가시키도록 처리했다.

 

DBMS	SQL
CUBRID	SELECT incr(read_count)…. FROM documents WHERE doc_id = ?;
MySQL	SELECT … FROM comments WHERE doc_id = ?; SELECT ….. FROM documents WHERE doc_id = ?; SELECT … FROM comments WHERE doc_id = ?; PostgreSQL UPDATE FROM documents SET read_count = read_count + 1 WHERE doc_id = ?;
PostgreSQL

<표 5> 글 보기 질의문

 

목록 보기

 

목록 보기는 글 제목을 목록으로 만들어 한 화면에 보여주는데 지금까지 등록된 모든 글 제목을 한번에 모두 보여주기보다는 정해진 개수씩(예: 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에서 역 인덱스를 생성하는 예는 다음과 같다.

 

이와 같이 질의를 효율적으로 수행하기 위해서는 질의를 수정하거나, 필요한 인덱스를 생성해야 한다. 이러한 질의 튜닝 과정에서는 현재 질의를 DBMS가 어떻게 수행하는지를 확인해야 하며, 모든 DBMS는 질의 수행 계획을 보여주는 기능을 제공한다. <표 7>은 각 DBMS별로 질의 수행 계획을 보는 방법이다.

 

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 <표 7> 질의 수행 계획 보기 DBMS SQL CUBRID SET
PostgreSQL

<표 6> 목록보기 질의문

 

DBMS	SQL
CUBRID	SET OPTIMIZATION LEVEL 513;
MySQL	EXPLAIN SELECT * FROM comments;
PostgreSQL

<표 7> 질의 수행 계획 보기

 

효율적인 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 값들을 설정하면 된다.

 

기능	구분	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

<표 8> 질의문 비교 정리

 

요약 및 정리

 

지금까지 인터넷 게시판 구현에서 CUBRID, MySQL, PostgreSQL의 사용 방식을 비교해 보았다. 세 DBMS에서 사용되는 질의문은 글 읽기와 목록보기를 표현하는 데 있어 차이를 보였다. 비교 정리한 내용은 <표 8>과 같다.

 

	Connection Pool	Statement Pool	Result cache
CUBRID	응용에서 지원	cubrid_broker.conf 에서 STATEMENT_ POOLING=ON	max_query_cache_ entries query_cache_mode query_cache_size_in_ pages
MySQL	상동	별도 설정 없이 지원	query_cache_limit query_cache_min_ res_unit query_cache_size query_cache_type
PostgreSQL	상동	별도 설정 없이 지원	지원 안 됨

<표 9> 기능별 DBMS 설정하기

게시판 환경에서 DBMS상의 성능 튜닝은 주로 재사용 기능을 설정하는 방식이었다. 
응용과의 연결 재사용은 DBMS에서 별다른 지원은 필요 없었고, DBMS에서 컴파일된 질의를 재사용하는 기능은 세 제품이 모두 지원된다. 그리고 질의 결과 캐싱은 CUBRID와 MySQL만 제공하고 있다. <표 9>는 이 기능들을 사용하기 위해 DBMS에 설정해야 하는 값들을 정리한 것이다.

 

 

참고자료 1. http://www.redhat.com/docs/manuals/database/RHDB-2.0Manual/admin_ user/overview.html 2. http://www.postgresql.org/docs/7.4/interactive/connect-estab.html 3. http://www.castor.org/jdo-pooling.html#PostgreSQL-7.3-and-later 4. http://www.postgresql.org/docs/7.4/static/jdbc-datasource.html 5. http://www.postgresql.org/docs/8.3/static/plpgsql-implementation.html 6. http://blog.pages.kr/30 7. http://www.cubrid.com/zbxe/bbs_developer_tutorial/32358/page/2 8. http://www.cubrid.com/online_manual/cubrid_814/index.htm