ASP 용 데이터베이스 도우미 : DBHelper 클래스
|
|
강좌 최초 작성일 : 2006년 04월 15일|
강좌 전 태오의 잡담> 제가 안재우님의 작품을 소개하는 강좌를 올렸더니만, 금새 안재우님이 추가적인 여러 조언이 담긴 글을 자신의 블로그에 게재했네요. 좋은 내용이 많은 듯 하여, 허가를 받고 태오 사이트에도 그 글을 강좌형식으로 올려봅니다. ^^
이 강좌는 이전 강좌인 "ASP에서 클래스 다루기 및 DB Access Helper 소스공개!"의 보완 강좌입니다.
참고로, 안재우님의 블로그는 http://blog.naver.com/saltynut/입니다. 실은 얼마전 Taeyo(김태영)씨가 요청을 해서 예전 S사의 튜닝 프로젝트에서 만들었던 DBHelper라는 ASP용 데이터 액세스 헬퍼 클래스를 태요 사이트에 공개했습니다. 그랬더니, 많은 분들이 그에 대한 관심을 보여서 원 제작자로서 뭔가의 커멘트가 필요한 것 같다는 느낌이 들더라구요. 해서 이 글을 준비하게 되었습니다. 기존 강좌에서 설명한 대로 사실 DBHelper는 별다른 대단한 녀석은 아닙니다. 단지 ASP에서 권장되는 데이터 액세스 기법들을 활용해서 유틸리티 함수들을 묶은 스크립트 클래스에 불과합니다. 사실 클래스 자체보다는 그러한 바탕이 되는 기법들이 무엇이고 왜-어째서 이런 기법들을 사용해야 하는지가 더욱 중요한 것이겠죠. 이번 글에서는 실제 튜닝 프로젝트 사례를 바탕으로 튜닝 시나리오, ASP 데이터 액세스 기법 및 팁, 그리고 DBHelper에 대해 설명하도록 하겠습니다. 1. 프로젝트의 배경 S사는 Windows 2000, IIS 5.0인 웹 서버 10대, Windows 2000, SQL 2000 Enterprise로 클러스터링된 DB 서버 2대로 운영되는 중형 규모의 ASP 사이트였습니다. S사에서 제기해 온 문제는 서버의 성능이 너무 떨어진다는 것이었습니다. 조사 결과 웹 서버의 부하는 상당히 낮은 편인데 비해 DB 서버의 부하가 상당히 높은 편이었습니다. 사용자가 늘어나면서 이러한 성능 문제는 점점 더 심각해졌고, 그에 따른 Capacity 한계 때문에 S사는 W2K-IIS-ASP-SQL 기반의 플랫폼에 의구심을 가지고 있던 상황이었습니다. 논의 결과 2가지 사항에 크게 주목하게 되었습니다. 첫째는 DB 내부의 문제입니다. DB 구조 자체가 잘못 되었거나, 인덱스가 걸려 있지 않거나 혹은 잘못 걸려 있는 경우, 그리고 DB 구조 때문에 쿼리 자체가 상당히 복잡한 경우 등입니다. 즉 이 경우는 효율적인 DB 튜닝을 통해 성능을 개선할 수 있습니다. 테이블 구조 변경, 인덱싱, 자주 사용하는 쿼리를 SP로 변환하는 것 등이 해당 작업이 되겠습니다. 둘째는 웹 서버의 부하가 너무 적다는 것입니다. S사의 경우 부하가 매우 DB 서버에 편중되어 있으므로, 이를 웹 서버에 분담시킬 수 있는 방법을 고려하는 것이 바람직하다고 판단되었습니다. 이를 위해서는 애플리케이션의 코드를 분석하고, 데이터 액세스를 어떻게 하고 있는지를 파악하는 것이 필요합니다. 작업은 어디까지나 튜닝의 범위 내에서 수행하기로 했습니다. 즉 플랫폼을 교체한다거나 아키텍처를 완전히 갈아엎는다거나, 하드웨어를 추가로 구매하는 것은 고려하지 않기로 했습니다. 이에 따라 DB 튜닝과 애플리케이션 튜닝을 병행해서 작업을 하도록 결정하였습니다. DB 튜닝은 제 영역이 아니므로 이 글에서는 다루지 않도록 하겠습니다. 이런 쪽은 DB 전문가 분들께 문의해보시는게 빠르겠죠? ^^ 2. 튜닝의 기초 개념 : 부하의 분산 우리가 알아야 할 사항 중 하나는 기본적으로 애플리케이션의 로직 등에 문제가 없다고 가정할 때, 부하는 없어지지 않고 옮겨질 뿐입니다. 예를 들어 전체 부하가 100이라고 할 경우, 이 부하는 웹 서버와 DB 서버들에 각각 나누어져서 존재합니다. 따라서 분산 애플리케이션 튜닝에서 해야 할 사항 중 하나는 이러한 부하를 골고루 분산하는 것입니다. 여기에서 감안해야 할 사항은 확장성(Scalability)입니다. 통상적으로 웹 서버는 스케일 아웃(Scale-Out)을 통해 확장을 하기가 용이합니다. 즉 웹 서버의 대수를 늘리고 L4 등을 통해 로드밸런싱으로 묶기만 하면 분산이 가능합니다. 이에 비해 DB 서버의 경우 확장성은 상당히 제한적입니다. 통상적으로 스케일 아웃은 클러스터링을 통해서만 확장이 가능하며 클러스터링을 할 수 있는 서버의 수도 제한될 뿐더러 비용 역시 상당히 비싼 대가를 치러야 합니다. 그렇지 않으면 스케일 업(Scale-Up), 즉 서버의 사양을 업그레이드시키는 것으로만 해결이 가능합니다. 그러나 아쉽게도 Windows 2000 서버는 CPU 개수를 무한히 늘릴 수 없으며, 제한적입니다. DB 서버와 웹 서버는 사양 및 성능에 있어서 분명한 차이가 존재합니다. 일반적으로 DB 서버가 훨씬 더 뛰어난 사양을 가집니다. S사의 DB 서버와 웹 서버의 경우는 CPU 갯수만 하더라도 8CPU와 2CPU라는 성능 차이를 가지고 있었습니다. 얘기를 풀어나가기 위해 일단 각 DB 서버의 최대 부하 한계가 50, 최대 웹 서버의 부하 한계가 20이라고 대략 가정해 보도록 합시다.
현재는 DB 서버 쪽이 70, 웹 서버 쪽이 30인 상태였습니다. 이 경우 DB 서버는 1대당 35, 웹 서버는 1대당 기껏 4의 부하만을 가지고 있는 셈입니다. DB 서버는 자기 한계를 70%(35/50)를 이미 사용하고 있으며, 웹 서버는 자기 한계의 20%(4/20)만을 기껏 사용하고 있는 셈입니다.
우리가 바라는 것은 최소한 DB 서버 쪽을 40, 웹 서버 쪽을 60으로 만드는 것이었습니다. 이 경우 DB 서버는 1대당 20, 웹 서버는 1대당 6의 부하만을 가지면 됩니다. 이 경우 DB 서버는 40%, 웹 서버는 30%로 되는 셈입니다.
DB 서버 쪽을 30, 웹 서버 쪽을 70으로 만들면 안되나요? 아, 물론 가능할 수도 있습니다. 이 경우 DB 서버는 1대당 15, 웹 서버는 1대당 7의 부하를 가지게 됩니다. 이 경우 DB 서버는 30%, 웹 서버는 35%로 되는 셈입니다.
대신 마지막으로 알아두어야 할 사항은 이러한 부하를 한쪽으로 몽땅 옮길 수는 없으며, 옮길 수 있는 한계가 존재한다는 것입니다. 최소한 DB 서버에서만 수행가능한 작업, 웹 서버에서만 수행 가능한 작업은 반드시 존재한다는 것이죠. 애플리케이션과 시스템의 성격에 따라 1:9, 2:8, 3:7, ..., 8:2, 9:1 등은 존재할 수 있지만 0:10이나 10:0은 존재할 수 없다는 것을 의미합니다. 쉽게 설명하려고 노력했는데 이해가시죠? ^^ 참고로 튜닝에는 부하 분산만이 존재하는 것은 아니라는 것을 알아 두세요. 3. 기존 애플리케이션 분석 우선 기존 애플리케이션을 분석하기로 했습니다. S사 사이트는 페이지 수가 상당히 많은 편이며, 구조도 비교적 복잡한 편이었습니다. 그에 따라 디렉터리 구조도 나름대로 체계적으로 나누기 위해 노력한 편이었으며, 공통된 내용들은 .inc로 철저하게 분리해내려고 노력한 흔적이 많이 보였습니다. 즉, 일반적인 ASP 사이트보다 나쁘지는 않은(?) 상황이었다는 것입니다. S사는 DB 액세스를 하기 위해 크게 2개의 inc 파일을 사용하고 있었습니다. 즉 페이지의 앞 부분에 삽입된 inc에서는 ADO Connection 개체를 생성하고, 페이지 내에서는 이 Connection 개체를 가지고 DB 액세스를 수행한 후, 페이지의 뒷 부분에 inc 파일을 삽입하여 Connection을 닫고 Connection 개체를 Nothing 처리하는 전형적인 패턴을 사용하고 있었습니다. 이런 경우 inc 파일만 제대로 넣어준다면 커다란 문제는 생기지 않습니다. 결국 문제는 inc 파일들이 아니라 Connection을 가지고 실제 DB에 액세스하는 코드에 문제가 있는 셈입니다. 분석 결과.. 역시 코드가 문제였습니다. 가장 무엇보다 큰 문제는 DB 액세스를 위해 사용하는 코드가 전부 다 제각각이라는 점이었습니다. 사람들마다 사용하는 커서도 제각각이었으며, ADO 관련 상수를 쓰는 사람도 있고 아닌 사람도 있고, 심지어 어떤 사람은 자기가 직접 Connection을 만들어서 사용하는 사람도 있었습니다. 많은 수의 개발자가 작성하고, 시도 때도 없이 유지 보수를 위해 코드를 수정하는 경우에는 허다하게 일어나는 일입니다. 심지어 1번만 수행되면 되는 쿼리를 쓸데없이 while 루프 안에 집어 넣어서 한 페이지 내에서 동일한 쿼리가 수십번 실행되는 페이지도 존재했습니다. 두번째 문제는 대부분의 코드가 DB 서버에 부하를 많이 주는 방식이었습니다. 서버의 자원을 사용하는 Connected Recordset, 서버 사이드 커서 등이 사용되는 경우가 많았습니다. 또한 쿼리 자체도 SP도 아니며, 파라미터화된 쿼리도 아니며, 문자열 조합 쿼리가 거의 대부분이었습니다. 결과적으로 공통된 DB 액세스 코딩 패턴을 제공하고, DB 서버의 자원을 적게 쓰는 DB 액세스 기법들을 사용하고, 문자열 조합 쿼리를 파라미터화된 쿼리 또는 SP로 변환하는 것을 권고안으로 제시했습니다. 4. ASP 데이터 액세스 기법 살펴보기 여기서는 DBHelper에 쓰인 기법들을 자세히 살펴보도록 하겠습니다. DBHelper는 이러한 3가지를 사용하기 쉽도록 지원하기 위한 산출물인 셈이죠. 1) Connected Recordset vs. Disconnected Recordset 일반적으로 ADO에서 사용되는 레코드셋은 연결된 레코드셋(Connected Recordset)으로 레코드셋에서 작업을 하기 위해서는 Connection이 열려 있어야 하며, 레코드셋에서 MoveNext 또는 MovePrevious와 같은 작업을 하는 경우 DB와 계속적으로 상호작용을 하게 됩니다. 이 경우 오랜 시간 Connection 자원을 열어놓고 사용하게 되므로 자원적인 낭비와 궁극적으로는 DB 서버의 성능 저하를 초래하며, 커서를 사용하는 동안 DB와의 라운드 트립이 발생하게 됩니다. ADO은 연결된 레코드셋 외에 별도로 비연결 레코드셋(Disconnected Recordset)을 지원합니다. 비연결 레코드셋이라 함은 말 그대로 DB와 연결이 없는 레코드셋을 말합니다. 예를 들어 비연결 레코드셋에서 MoveNext, MovePrevious와 같은 레코드 네비게이션(navigation) 메서드들이 호출되더라도 DB와 어떠한 상호작용도 하지 않습니다. 이것이 가능케하기 위해서 비연결 레코드셋은 클라이언트 커서 라이브러리를 사용합니다. 즉, 레코드셋 객체의 CusorLocation 프로퍼티의 값이 adUseClient이어야만 비연결 레코드셋을 만들 수 있습니다. 비연결 레코드셋을 만드는 방법은 다음과 같이 간단하게 레코드셋 객체의 ActiveConnection 속성 값을 Nothing으로 설정하면 됩니다.
Disconnected Recordset의 장점은 다음과 같습니다.
- 클라이언트 커서를 사용하며, 자유로운 데이터 액세스가 가능합니다. DBHelper에서는 기본적으로 Disconnected Recordset을 사용합니다. 2) 커서(Cursor)의 사용 ADO에서 지원하는 커서에는 크게 4가지 종류가 있습니다. 이러한 커서를 적절히 사용하면 애플리케이션의 성능을 향상시킬 수 있습니다.
위에서 언급한 것처럼 대부분의 경우에는 Static 커서를 사용하는 것이 가장 적합합니다. DBHelper에서는 Static 커서를 사용합니다. 3) RecordCount의 사용 Static / Keyset 커서를 사용하는 경우, 레코드셋의 RecordCount 속성을 사용할 수 있습니다. 이 속성은 편리하기는 하지만 신중하게 사용해야 합니다. RecordCount 속성을 사용하는 경우 SQL 서버는 sp_cursoropen 저장 프로시저를 호출하여 커서를 열고 레코드들을 fetch 해 나가는데, RecordCount를 얻어내기 위해서는 전체 레코드를 최소한 1회 이상 fetch 해야 한다는 것을 의미합니다. 이 경우 불필요한 레코드 fetch가 증가될 수 있습니다. 성능이 문제가 되는 경우, RecordCount보다는 가급적이면 레코드의 개수를 Output 매개변수로반환해주는 SP를 사용하는 것을 권장합니다.
4) Client Cursor vs. Server Cursor ADO는 디폴트로 서버 커서, 즉 데이터베이스에서 제공하는 커서를 사용합니다. 그러나, 서버 커서에는 다음과 같은 단점들이 존재합니다.
- 데이터베이스의 커서 자원을 사용합니다. 위의 부하를 줄이려면, 클라이언트 커서를 사용하면 됩니다. 클라이언트 커서를 사용하면 조회된 데이터는 모두 클라이언트(웹 서버)로 복사되고, 클라이언트(웹 서버)의 메모리에 저장됩니다. 그 이후 레코드 간의 이동은 클라이언트 메모리에 저장된 레코드셋 내에서 발생됩니다. 클라이언트 커서를 사용하려면 Connection 또는 Recordset 객체의 CursorLocation 속성 값을 adUseClient 상수 혹은 3으로 설정하면 됩니다. 클라이언트 커서를 사용하면 다음과 같은 장점이 있습니다.
- 클라이언트 커서를 사용하면 레코드셋의 커서가 자동적으로 static으로 고정됩니다. 클라이언트 커서를 사용하는 경우, DB의 자원과 부하는 줄어드는 대신 웹 서버의 자원을 사용하고, 웹 서버에 부하가 옮겨지게 됩니다. 따라서 만약 현재 DB 서버의 부하는 적은데 웹 서버의 부하가 매우 큰 상황이라면 사용하지 않는 것이 좋습니다. DBHelper에서는 기본적으로 클라이언트 커서를 사용합니다. 5) 파라미터화된 쿼리(Parameterized Query) 동적인 쿼리를 작성하기 위해 일반적으로는 문자열 조합에 의한 쿼리를 사용하는 경우가 많습니다. 예를 들면 다음과 같겠죠.
strQuery = "SELECT * FROM Orders WHERE OrderDate >= '" & startDate & "' AND OrderDate < '" & endDate & "'" 일단 따옴표(')를 빼먹기 쉽다는 문제는 제쳐 두더라도, 이 쿼리는 SQL 서버 내에서 수행될 때 startDate와 endDate의 값이 변경될 때마다 매번 컴파일 과정을 거쳐야 합니다. 불필요한 SQL 컴파일은 DB의 부하를 가중시키켜 성능 저하로 이어지게 됩니다. 이 문제를 피하려면 파라미터화된 쿼리(Parameterized Query)를 사용하는 것이 좋습니다. 보다시피 따옴표가 사라지고 ? 또는 @로 시작하는 파라미터가 추가되었습니다. strQuery = "SELECT * FROM Orders WHERE OrderDate >= ? AND OrderDate < ?" 또는 strQuery = "SELECT * FROM Orders WHERE OrderDate >= @startDate AND OrderDate < @endDate" 매개변수를 가진 쿼리를 사용하는 경우에는 매개변수를 넣어주기 위해 반드시 Command 객체를 사용해야 합니다. (아래 예제 참조)
이렇게 매개변수를 가진 쿼리를 실행하면, SQL Profiler에서 SQL배치가 아닌 RPC가 수행되어 더욱 속도가 빨라집니다. 또한 다음과 같이 매개변수화 되므로 startDate, endDate의 값이 변경되더라도 SQL 컴파일은 한번만 일어나게 됩니다.
exec sp_executesql N'SELECT * FROM Orders WHERE OrderDate >= @P1 매개변수를 가진 쿼리는 코드 가독성을 높여주고, 성능 면에서는 좋긴 하지만 코딩량이 다소 늘어난다는 단점이 있어서 귀차니즘(?) 때문에 안 쓰는 사람들이 많습니다. 모든 쿼리를 매개변수를 가진 쿼리로 작성할 필요는 없지만, 최소한 자주 수행되는 쿼리의 경우 이렇게 작성하면 DB의 부하를 줄이고 성능을 향상시킬 수 있습니다. DBHelper에서는 이러한 코딩을 간편하게 할 수 있도록 지원합니다. 6) ADO 관련 상수 먼저 다음 코드를 봅시다. Rs.Open SQL, Dbcon, 0, 1 ASP 코딩에 능숙한 사람은 알겠지만 0은 커서를 adForwardOnly, 1은 락을 ReadOnly로 지정하는 것입니다. 하지만, 위처럼 상수값을 숫자로만 지정하면 코드의 가독성이 떨어집니다. 그러므로 실제로는 다음과 같이 해주는 것이 이상적입니다. Rs.Open SQL, Dbcon, adOpenForwardOnly , adLockReadOnly ADO에서는 위와 같은 상수를 사전에 정의한 파일을 제공합니다. 이 파일을 사용하기 위해서는 C:\Program Files\Common Files\System\ado에 있는 Adovbs.inc 파일을 include 해줘야 합니다. <!--#include file="C:\Program Files\Common Files\System\ado\adovbs.inc" --> 그러나 include를 사용하면 그만큼 페이지 컴파일에 대한 부하가 발생해서 성능이 저하게 됩니다. 이를 피하기 위해서는 type library를 사용하면 되는데 각 페이지에 다음과 같이 선언하면 됩니다.
<!--METADATA TYPE= "typelib" NAME= "ADODB Type Library" 이 코드를 아예 Global.asa에 추가하면 각 페이지에 추가할 필요없이 ADO 관련 상수를 마음대로 사용할 수 있습니다. 5. 튜닝 결과 4번 항목에서 제시된 기법들을 사용해서 DBHelper를 작성한 후, 이를 적용하도록 각 페이지를 수정하는 작업이 이루어졌습니다. 일부 쿼리는 SP 또는 파라미터화된 쿼리로 재작성되기도 했습니다. DB 튜닝과 함께 애플리케이션 튜닝을 병행한 결과는 어떻게 되었을까요? 예상했던 결과가 이루어졌습니다.
평균 CPU 사용량이 65%대이던 DB 서버는 31% 정도로 크게 감소했으며, 웹 서버는 20% 정도에서 30%로 사용량이 늘어나게 되었습니다. 이용자 수도 거의 비슷했으며 적용전, 적용후 각각 1달 가까이를 모니터링한 결과입니다. 이에 따른 예상 수치로 볼 때 S사는 현재 사용자가 2배로 늘어나도 감당할 수 있을 것이라는 새로운 Capacity를 가지게 되었습니다. 그에 따라 굳이 플랫폼을 변경하거나 하드웨어를 교체할 필요가 없게 됨으로써 시간과 비용을 절약할 수 있게 된 셈입니다. 따져보면 별다르게 한 것도 없는데 말이죠. ^^;; 여기까지가 장황한 튜닝기입니다. 그런데 DBHelper 썼는데 우리는 성능이 왜 개선 안되냐? 라고 돌 던지신다면 대략 낭패입니다. 예를 들어 DB 서버는 팽팽 놀고 웹 서버 부하는 큰데 DBHelper를 그대로 적용한다면 오히려 성능이 더 저하될 수도 있다는 것을 감안하셔야 할 것입니다. 이 경우는 오히려 DB 서버의 자원을 적극 활용하도록 DBHelper를 변경해서 써야 할 수도 있겠죠? 또한 이 코드를 보다 세련되게 잘 다듬어서 주시는 분들이 계시면 더욱 더 감사할 듯 합니다. ^^
|
