|
0. 들어가면서.
최근 며칠간 DSN DB 서버는 내부적으로 획기적인 변화를 시도
했습니다.
가장 큰 변화는 서버를 8.0.2 버전으로 바꾸었다는 것이고,
드디어 커넥션 풀 기능을 실험적으로 사용하기 시작했습니다.
웹서버 환경에서 가장 이상적인 커넥션 풀 구현은
일단 웹서버의 프로세스 모델이 fork 방식의 하위프로세스를 만드는
방식이 아닌, 쓰레드를 사용하는 방식이고, 쓰레드를 만들어내는 메인
프로세스에서 원하는 만큼의 DB 커넥션을 보유하고 있고, 사용자의
웹서버 요청에 따라 현재 사용하고 있는 특정 커넥션을 웹서버
프레임워크로 넘겨주는 방식일겝니다.
일단 이 이상적인 커넥션 풀 기능을 제공하는 웹서버는 tomcat의
datasource 기능일 듯합니다. 물론 jsp 웹서버들은 거의 대부분 이런
기능을 서버 차원에서 제공해 줍니다. 하지만, apache + php 환경에서는
이런 이상적인 기능을 사용하려면, 일단 apache 서버가 2.0.x
이상이어야합니다. 1.3.x 버전에서는 쓰레드기능을 제공하지 않기
때문에 원천적으로 이런 방식의 커넥션 풀을 사용할 수 없습니다.
(공유메모리 영역에는 db 커넥션 포인트 같은 것은 둘 수 없다고
하네요.)
1. pgpool 기능 개요
pgpool 이라는 놈은
http://pgfoundry.org/projects/pgpool
페이지에서 진행중인 PostgreSQL 공개프로젝트입니다.
설치와 사용법에 대한 자세한 이야기는 아래 문서를 참조 하면
될터이고, 이곳에서는 이놈이 움직이는 대략적인 설명만
하겠습니다.
이것은 엄격하게 말한다면, 커넥션 풀이라고 말하기 힘듭니다.
차라리 커넥션 공유용 미들웨어라고 명명하는 편이 나을 듯합니다.
왜냐하면, 위에서와 같이 apache + php 환경 이라면, 매 페이지의
사용자 요청에 디비 서버로 접속을 했다가 끊었다를 반복하는 것과
마찬가지로, 이 pgpool 에서 제공하는 소켓을 열었다 닫았다 해야하기
때문입니다.
pgpool 사용에서 사용자를 편의를 고민한 부분은 사용자 측의 db
조작 관련 변경 작업은 오직 연결문자열 connection string 만 바꾸면
된다는 것입니다. 아이디어가 멋지지요.
윗 모든 것을 감안해 본다면, pgpool 프로세스가 움직이는 환경은
web 서비스를 운영하는 호스트에서 같이 운영되어야하며, web 서비스는
pgpool 접속을 유닉스 소켓 파일로 접속하는 것이 비용을 최소화 하는
방법일 것 입니다.
2. prepared query
우리말로, 미리 준비된 쿼리.
좀더 DB 전문용어를 포함해서, 쿼리 구문분석, 최적화, 재해석이
모두 끝나서 서버에서 실행바로 직전의 쿼리.
전문 상용 RDBMS 에서는 이것을 Dynamic Query 라고 하더군요.
일반적으로 DB 서버 프로지져 언어를 사용해서 사용자 정의 함수나
패키지를 작성할 때 많이 사용되는 개념입니다.
다음은 DSN에서 실행한 결과입니다.
select version() 1000번: 0.436초
prepare 1번, execute a 1000번: 0.406초
여기서 약 30ms 차이가 바로 prepare 작업으로 얻는 이익입니다.
:)
별차이가 없어 보이는 듯하지만, DB 서버 내부적인 차원에서 본다면
어마어마한 차이입니다.
그런데, 과연 웹서비스 환경에서 이 미리 준비된 쿼리가 필요할까?
이것이 문제입니다.
전통적인 웹 환경에서 프로그래밍을 해왔던 사람이라면, 이 질문에
쉽게 "아니오"라고 대답할 것입니다.
왜냐하면, 늘상 하나의 사용자 요청(request)에 대한 반응으로
작업하는 것 가운데, 똑 같은 패턴의 쿼리를 수번에서 수십번 반복하는
작업이 과연 얼마나 될 것인가? 이 질문에 답은 거의 없다 이기
때문입니다.
이 미리 준비된 쿼리를 사용해서 이익을 얻으려면, 위에서 말한
하나의 페이지를 만들어내는 작업에서 이런 반복되는 같은 패턴의
쿼리를 사용해야하기 때문입니다.
하지만, pgpool 같은 커넥션을 풀 기능을 사용한다면, 문제는
달라집니다.
DSN 같은 비교적 간단한 서비스일 경우, 웹서버에서 DB 서버로
요청하는 쿼리들은 지극히 한정적이며, 그 패턴은 항시 일정하고,
그리고 매우 자주 사용된다는 것입니다.
가장 대표적인 패턴이 게시물 목록을 페이지단위로 끊어서 보는
것이겠지요.
또한 어떤 게시물의 내용을 보는 것, 코멘트 자료를 가져오는 것,
... 아무튼 꽤 많은 쿼리들이 db 서버랑 웹서버가 항구히 붙어있다면,
prepared query 로 사용되는 것이 훨씬 낫겠다는 것을 짐작할 수
있습니다.
3. 문제점 발생
그럼 언제 쿼리를 준비해 놓을 것인가?
상식적인 수준에서 생각한다면, pgpool 에서 새로운 커넥션을 만들때
겠지요.
하지만, pgpool 에서는 어떤 쿼리를 미리 준비해야할 지
모릅니다.
즉, "이러.. 저러한 쿼리들을 미리 준비 하라" 이 작업은 결국,
웹서버에서 해야할 몫입니다.
그렇다면, 다시 원점으로 기존 웹프로그램 소스에
"만일 미리 준비된 이 쿼리가 없으면, 새로 준비하라"는 식의 작업이
필요해 지는데,
PostgreSQL 에서는 이 "만일 미리 준비된 이 쿼리가 있는지"를 알
길이 없습니다. :(
즉, prepare 구문이나, deallocate 구문을 서버로 보내서 서버에서
오류가 발생하는지 살펴보는 방법 뿐이지요.
4. wasprepared 함수 만들기
결국 이 작업은 함수가 맡기로 했습니다.
이 함수에 대한 자세한 이야기 http://database.sarang.net/?inc=read&aid=6047&criteria=pgsql&subcrit=devel&id=&limit=20&keyword=&page=1
페이지를 참조하십시오.
select wasprepared('plan1');
이 쿼리의 결과값이 참이 아닐 때만 prepare 작업을 진행시키도록
합니다.
5. prepared query 의 한계.
여러 시행착오 끝에 드디어 전면적인 prepared query 기능을
사용하기 위해서 자주 사용되는 쿼리들을 prepare 구문으로 등록하기
시작하면서 문제가 또 발생하기 시작했습니다.
prepare 구문은,
prepare 쿼리이름(인자자료형,...) as 퀴리구문;
이런 형태입니다. 이때, 미리 정의한 쿼리구문의 인자로 테이블
자체가 올 수 없습니다.
예를 들어서,
prepare findzipcode (zipcodetable, text) as select * from $1
where dong = $2;
이런 구문은 사용할 수 없게되지요. 즉, 웹프로그밍 속에서 테이블
이름 자체가 동적으로 할 당될 경우라면, 이 prepared query 기능을
사용하는데 꽤 많은 난관에 부딧치게 됩니다. 얼마나 많은 쿼리들이
미리 준비될지 모르기 때문에.
또 한가지 단점이 PostgreSQL의 옵티마이져는 like 구문은 그 검색
조건값이 어떤 모습인가에 따라서 실행계획을 결정합니다. 즉,
prepare findzipcode(text) as select * from zipcode where dong
like $1;
execute findzipcode('종로5가%');
이렇게 한다고 해도 원하는 인덱스를 사용하지 않을 것입니다.
왜냐하면, 앞에서 말했듯이 prepare 작업때 이미 실행계획까지 모두
준비되었는데, 윗 구문으로는 like 다음 조건값이 어떤것이 올지 모르기
때문에 인덱스를 사용하지 않겠다고 실행계획을 세우기 때문입니다.
즉, prepared query 를 작성할 요량이고, 그 검색 조건으로 like
연산을 사용한다면, 이것을 between .. and 구문이나, > .. <
연산으로 사용자가 직접 쿼리를 재작성해주어야합니다.
윗 경우라면,
prepare findzipcode(text) as select * from zipcode dong between
$1 and $1 || '힣';
이런식으로 '종로5가'부터 '종로5가힣'까지를 찾아라고 조정하는
것이지요.
아니면, PostgreSQL 내부 rewriter가 하는 방식 처럼,
prepare findzipcode(text,text) as select * from zipcode dong
>= $1 and dong < $2;
이렇게 하고, execute findzipcode('종로5가','종로5각');
이런식으로 끝부분 글자를 클라이언트가 계산해서 db 서버로
넘겨주는 방법도 있을겝니다.
다음, 한번 저장된 prepared query 는 세션이 끝나기 전까지,
deallocate 작업으로 직접 지우기 전까지 세션에 그대로 남아있습니다.
즉, 자료가 많이 바뀌어 쿼리 실행 계획을 변경해야함에도 불구하고,
prepared query 는 멍청하게 이것을 저장할 때의 그 실행계획그대로
움직을 것이다는 것입니다.
즉, 주기적으로 이 prepared query 들을 새로 만들어주어야합니다.
이것을 까먹어버리면, 프로그램은 어느날 갑자기 팍 ~ 늦어지는 사태가
발생할 것입니다. - 확실하게 예견되는 사태입니다.
6. 마치면서.
글이 장황했는데, 요지는 pgpool 프로그램을 통해서 완벽하지는
않지만 어느정도의 커넥션 풀 기능을 이용할 수 있으며, 이것을
사용함으로 prepared query 기능을 이용할 수 있는 장점이 있으나,
이것은 아주 섬세한 작업이 필요하다는 이야기입니다. 어느것을 일반
쿼리로 하고, 어느 것을 prepared query 로 할 것인가에 대한 것은
전적으로 db 작업자의 경험에 달려있는 것 같습니다.
아무튼 prepared query 를 사용하겠다면, 윗 글을 좀더 꼼꼼히
읽어가면서 차근하게 준비를 해야한다는 것입니다.
|
