분석 목적과 대상 설정에 따른 튜닝 방법론

원본출처 : http://www.zdnet.co.kr/develop/backend/db/article.jsp?id=47843&page=2&forum=0

매우 잘 구성된 데이터베이스라고 해도 부분적인 수정은 불가피하다. 사용자의 요구 사항이 끊임없이 변화하고 로직 상의 변화를 예측하기는 어렵다. 그렇다고 예기치 못한 상황이 발생할 때마다 재개발에 착수하기란 더더욱 어렵다. 따라서 많은 기업 고객들이 기존의 정보시스템을 그대로 유지하면서, 현재 시스템에서 가장 심각한 문제 원인을 분석하고 최적화 방안 수립에 관심을 갖고 있다.

김성록 (on the NET)

2002/04/13

RDBMS (Relational DataBase Management System)를 기반으로 한 애플리케이션 개발과 서비스 제공이 보편화됐지만, 실제 업무에 대한 이해와 풍부한 경험이나 지식을 두루 갖춘 개발자는 여전히 부족한 상태다. 때문에 기업 정보시스템의 핵심인 RDBMS를 최적화해 구현하기란 상당히 어렵다. 이를 극복하기 위해 많은 업체들은 SQL 구현이나 성능에 가장 중요하고 기본이 되는 초기 업무 데이터베이스 설계를 전문 업체에 의뢰하기도 한다.

데이터베이스 성능 분석

오늘날 데이터베이스 환경은 다층 구조의 복잡성과 대용량 데이터, 엄청난 사용자 증가 현상을 띠고 있다. 데이터베이스 시스템을 관리하는 관리자는 기업내 데이터베이스 성능을 분석하는데 어려움을 겪고 있다. 갈수록 환경 변화와 복잡성이 늘고 있어, 특정한 단일 요소의 분석만으로는 최적화된 시스템 구축을 실현하기에 무리가 따른다.

데이터베이스 성능을 분석하는 목적은 현재의 데이터베이스 성능과 향후의 성능을 비교 분석해, 향후에 발생할 수 있는 문제 요소를 정의하는데 있다. 여기에는 현재의 시스템 성능 최적화만이 아닌, 향후의 안정적인 시스템의 성능과 가용성을 보장하는 것도 포함된다.

성능을 분석하고 진단하는 방법으로는 SLA(Service Level Agreement), 튜닝 등이 있다. 튜닝은 현재의 시스템을 분석해 향후에도 최적화된 응답속도를 보장하는 행위를 지칭하고, SLA는 최종 사용자 입장에서 과거 시스템의 성능과 데이터를 바탕으로 성능 기준점을 추출해 성능과 기준점과의 비교를 통한 성능 분석 방법이라 할 수 있다.

튜닝은 모든 요청의 시작점인 클라이언트에서 시작한다. 클라이언트에서 시작해 애플리케이션 서버와 데이터베이스 서버를 경유, 다시 최종점인 클라이언트로 되돌아오는 응답 속도(Response Time)을 최적화하는 것을 말한다.

이 때 특정 부분만을 특화해 분석하는 행위가 아닌, 프리젠테이션 로직이 구현된 클라이언트와 비즈니스 로직이 구현된 애플리케이션 서버, 실제적인 처리(data process)가 이뤄지는 데이터베이스 서버, 그리고 각 노드 간의 네트워크 타임을 고려한 전반적인 분석이 이뤄져야한다.

최적화된 응답 속도 보장하는 튜닝

각 노드 간의 네트워크 타임은 네트워크의 대역폭, 전송량, 실패율 등에 대한 점검과 함께 노드간에 불필요한 데이터가 전송되는 지에 대해서도 점검할 필요가 있다. 또 추이 분석이나 데이터의 증가량, 사용자 증가에 따른 향후 상황을 예측하기 위해서는 반드시 누적 데이터가 필요하다. 이런 자료를 근간으로 현재와 미래의 상황에 대한 네트워크 사용량 분석 등이 요구된다.

프리젠테이션 부분을 담당하는 클라이언트에서는 시스템 사양이 중요한 역할을 차지한다. 환경이 복잡해질수록 프로그램은 무거워지고, 클라이언트의 메모리나 CPU 사양에 따라 화면에 처리되는 시간까지도 차이가 나기 때문이다.

비즈니스 로직을 담당하는 애플리케이션 서버는 비즈니스 로직 처리 시간이 상당히 중요한 부분을 차지한다. 연결 처리 부분도 무시할 수 없지만 대부분의 시간이 비즈니스 로직 처리에 소요된다고 봐도 무방하다.

실질적인 데이터가 처리되고 가장 중요한 분석 대상이 되는 데이터베이스 처리 시간을 살펴보면, 복합적인 요소들이 분석 대상이 된다. 그러나 시스템 환경이 아무리 복잡하다해도 데이터베이스에서 일의 주체는 프로세스다. 이런 서버 프로세스가 시스템 자원(CPU, 메모리, I/O, 네트워크)을 빌어 일을 수행하고, 또 데이터베이스 내부 자원(latch, enqueue 등)의 허락을 받고 일(SQL)이 처리된다.

분석 요소는 두 가지로 압축할 수 있다. 허락받을 때까지의 대기 시간(queue time), 허락을 받고 일을 하는 처리 시간(service time)으로 분류할 수 있다. (그림 1)은 복잡한 환경을 3계층 구조로 정의해 각 처리 시간을 도표화한 것이다.

1

사용자 입장에서 처리 시간을 분석하면 많은 요소들이 있지만, 실제 사용자가 느끼고 있는 처리 시간의 90%가 데이터베이스 서버 내에서 소요되는 시간이다. 물론 복잡한 웹 기반 환경에서는 다른 결과를 초래할 수 있겠지만, 이는 정상적이고 최적의 웹 환경을 구축하는데 미숙했기 때문이라고 봐야할 것이다.

웹 환경 구성상의 문제로 인한 분석도 성능 관리 최적화의 일부분이지만, 전체적인 흐름에 다소 벗어나기 때문에 추후에 다루도록 하겠다. 그러나 웹 환경 구성의 최적화를 구현하는 분석 방법도 상당히 중요하다는 점을 간과해서는 안된다.

결국 응답 속도를 줄이는 가장 빠르고 가장 효과적인 방법은 아무리 복잡한 환경이라 하더라도 결국 데이터베이스 처리 시간을 줄이는 방안이라고 할 수 있다.

3가지 데이터베이스 튜닝 방법론

가장 많이 알려진 튜닝 방법론은 모델링, 애플리케이션 튜닝, 데이터베이스 튜닝, 운영체제 튜닝 등 4가지로 구분한 시스템 분석 방법론이다. 필자는 앞의 4가지 시스템 분석 방법론과 동일한 내용을 다루고 있지만, 각도를 달리한 방법론을 소개하고자 한다.

데이터베이스 튜닝 방법론은 크게 비율 기반 분석(Ratio based Analysis), 대기 이벤트 기반 분석(Wait Event based Analysis), 응답 시간 분석(Response Time Analysis) 등 3가지로 구분할 수 있다. 결론부터 말하면 가장 효율적인 데이터베이스 튜닝 방법론은 응답 시간 분석이라는 것이 업계의 일반적인 평가다. 지금부터는 각각의 방법론에 대해 구체적으로 소개하겠다.

·비율 기반 분석

주로 90년대 초반에 데이터베이스 컨설턴트들이 사용했던 분석 방법론으로, 적은 용량의 데이터베이스에서는 효율적인 데이터베이스 튜닝 방법이었다. 예를 들면, 데이터베이스 성능을 분석하기 위해서 데이터베이스 시스템을 관리하는 DBA가 주로 사용하는 방법은 데이터베이스 버퍼, 캐시 히트 비율로 이 값이 90% 이상이면 데이터베이스 성능에 문제가 없다는 식의 접근 방식이다.

낮은 하드웨어 비용으로 인해 많은 데이터베이스 시스템의 데이터베이스 버퍼 캐시 히트율이 99%가 넘는 경우가 많이 있다. 이런 시스템에서도 데이터베이스 성능에 저하하는 병목 현상이 존재한다.

따라서 복잡한 데이터베이스 시스템 환경에서 비율 기반 분석으로는 효율적으로 데이터베이스 성능을 분석하는 데 한계가 있으며, 데이터베이스 성능을 분석하는 절대적인 기준이 될 수는 없다라고 말할 수 있다.

·대기 이벤트 기반 분석

주로 90년대 중반부터 사용한 분석 방법으로, 비율 분석 방법보다는 좀 더 발전된 형태의 데이터베이스 튜닝 방법론이라 할 수 있다. 오라클 7.0.12 버전부터 추가된 다이내믹 퍼포먼스 뷰 기능은 세션 레벨에서, 데이터베이스 레벨에서 대기 시간와 관련된 성능 정보를 가지고 있다.

데이터베이스에 연결된 동시 세션들이 데이터베이스의 어떤 자원에 대해 대기하고 있는가를 진단하고 이런 대기 시간을 줄이는 접근 방법이다.

이 분석 방법은 응답 시간의 2가지 구성 요소 중에서 대기 시간만을 분석하는 방법으로, 응답 시간의 서비스 시간과 운영체제 자원에 대한 대기 시간은 고려되지 않는다. 특히 서비스 시간의 비중도가 큰 데이터베이스 시스템을 분석하는 데에는 한계가 있다.

.응답시간 분석

앞에서 언급된 두 가지 분석 방법은 데이터베이스 성능을 효율적으로 분석하고 튜닝하는데 있어 다소 한계를 갖고 있다. 현재까지는 응답 시간 분석 방법론만이 가장 효율적인 데이터베이스 튜닝 방법이라고 말할 수 있다.

이 방법론은 주로 2000년대부터 사용한 분석 방법으로, 기존 대기 분석 방법에 응답 시간의 서비스 시간을 포함하는 가장 우수한 데이터베이스 튜닝 방법이라고 할 수 있다. 여기에서 말하는 응답 시간은 다음과 같이 정의할 수 있다.

응답 시간 = 처리 시간 + 대기 시간

만약 데이터베이스 시스템의 서비스 시간이나 대기 시간이 높다면, 시스템 하드웨어 용량과는 관계없이 데이터베이스 성능에 문제가 발생한다. 예를 들면, 특정 데이터베이스 시스템의 서비스 시간이 응답 시간의 80%를 차지한다고 가정해 보자. 대기 시간의 50%을 개선하면 전체 시스템의 응답 시간은 약 10%가 개선되나, 서비스 시간의 50%을 개선한다면 전체 시스템의 응답 시간은 40% 이상이 개선될 것이다.

따라서 서비스 시간에 대한 비중이 큰 데이터베이스 시스템인 경우, 대기 시간보다는 서비스 시간을 줄이는 것이 낫다. 또 대기 시간에 대한 비중이 큰 데이터베이스 시스템인 경우에는 서비스 시간보다는 대기 시간을 줄이는 것이 전체 시스템의 응답 시간을 개선하는 효율적인 접근 방법이라고 말할 수 있다.

응답 시간 분석 상세 분석

가장 효율적인 데이터베이스 튜닝 방법론으로 평가받는 것은 응답 시간 분석 방법론이다. 데이터베이스 처리 시간은 처리 시간과 대기 시간으로 나눌 수 있다. 이 방법론을 이용해 고객의 복잡한 시스템을 분석할 경우, 일자별 혹은 시간대별로 응답 속도를 먼저 분석한다.

가장 중요한 요소는 분석 대상 서버의 응답 시간 추이 분석이다. 아무리 엉망인 서버라고 해도 실질적인 사용자가 불만을 표출하지 않으면 대개의 경우 문제 요소를 내포한 채 그냥 시스템을 운영하고 있다. 고객이 성능 문제로 인한 불만을 토로하기 시작하면 그제서야 성능에 대해 관심을 갖는 것이 아쉽게도 국내 성능 관리의 현 실정이기 때문이다.

응답 시간의 추이를 분석하면서 고객과 어느 순간에 시스템이 최대한 사용됐는지를 파악하고 그 당시의 시스템 응답 시간 분석을 시작한다. SQL 단위, 프로그램, 세션, 오라클 데이터 파일, 데이터베이스, 시스템 단위로 처리 시간과 대기 시간의 비중을 분석한다.

그리고 분석 대상 서버가 처리 시간에 집중됐는지, 아니면 대기 시간에 집중됐는지를 분석한다. 처리 시간에 집중됐을 경우는 어느 프로그램, 어느 SQL에서 처리 시간을 집중적으로 사용했는가를 분석하고, 단위 SQL 튜닝을 통해 수행 시간의 향상과 수행 비중을 계산해 어느 정도의 처리 시간을 줄일 수 있는가를 분석한다.

물론 단위 SQL 튜닝 과정에서는 집중 사용된 시간대의 수행 속도와 튜닝 단계의 수행 속도, 사용자 수, 변수 사용 유형 등 가변 요소가 많기 때문에 정확한 수치를 측정하는 것은 불가능하지만, 단순 SQL 뿐 아니라 복합 요소를 분석해 처리 시간의 요소를 예측하고 향후 사용자나 데이터의 증가량에 관계없이 안정적인 운영을 할 수 있도록 해야 한다.

처리 시간은 시스템의 한정적인 자원을 사용하기 때문에 급격히 증가할 수는 없다. 따라서 일정 수준에 도달하면 정적인 값(static status) 유지하게 될 것이고, 항상 대기 시간이 급증하는 형태를 보여주기 때문에 처리 시간이 집중되는 시스템은 문제 발생 초기라는 분석이 가능하다. 따라서 이런 단계에서의 최적화가 선행돼야 최소의 비용으로 최적의 시스템을 유지할 수 있다. (그림 2)는 응답 시간과 쓰루풋과의 상관 관계를 도식화해서 나타낸 것이다.

[img2]

그렇다면 대기 시간이 처리 시간의 몇 배가 넘는 시스템일 경우 어떻게 분석할 것인가. 상당히 풀기 어려운 문제이다. 대기 시간은 크게 시스템 자원에서 발생한 대기 시간과 오라클 내부 자원에서 발생한 대기 시간으로 나눌 수 있다. 이런 대기를 유발시키는 항목을 명확하게 구분하는 것은 상당히 어렵지만, 사용자 수, 수행된 SQL, 오라클 환경과 시스템 환경, 파일의 분산 정도, 오브젝트 구조 등으로 분석 대상을 압축하는 것은 가능하다.

대기 시간 분석시에 가장 먼저 접근해야 하는 항목은 이런 자원 대기 현상이 발생한 위치가 시스템인지 데이터베이스인지를 분석하는 항목이다. 시스템이라면 CPU, 메모리, I/O 중에 어느 요소가 가장 심각한 대기 시간을 보여 주는가를 분석해야 한다. 이런 분석을 수행하는 것은 대기 시간의 정확한 원인을 분석하고 대기 시간을 최소화해, 전체 응답 시간을 개선하는데 목적이 있다.

CPU 대기가 대부분인 경우는 처리 시간 분석과의 연계 분석이 가능한데, 이는 처리 시간을 줄이는 분석 방법과 일치한다. 줄일 수 있는 만큼 줄이고 이후 증설을 고려해야 한다.

메모리 대기가 많다면 이는 오라클 환경과 시스템 환경 구성 분석에 집중해야 한다. 오라클 SGA(System Grobal Area) 영역과 메모리 관련 패러미터(sort, hash, parallel), 메모리를 사용하는 애플리케이션의 사용 비중도와 사용 시간대, 사용 유형을 집중 분석하고, 시스템 환경에서는 커널 메모리와 유닉스 버퍼 캐시의 크기(가변 메모리 사용중일 때는 특히 주의), 페이징과 스와핑의 비율등에 대한 분석이 필요하다. 즉 사용하고 있는 메모리를 줄여서 최적화하는 방안과 최후의 대처 방안인 가용 메모리를 늘리는 방안을 고려해야 할 것이다.

I/O 대기 시간이 많다면, 이는 가장 많은 튜닝 시간이 소요되는 항목으로 정확한 원인 분석이 필수적이며 분석 순서도 준수해야 한다. 가장 먼저 분석해야 할 사항은 애플리케이션의 사용 유형과 사용 시간대 분석이다. 우선 I/O 대기 시간이 급증하는 시점의 원인이 사용자의 증가 때문인지, 수행되는 애플리케이션의 수가 많아서인지, 아니면 배치와 온라인이 공존하는 시간대인지를 분석해야 한다. 그다음 애플리케이션이 배치, 온라인, 온라인성 배치인지 정의한 후 해당 시간대에 이들 애플리케이션이 수행돼야만 하는 정당성을 검증해야 한다.

이런 분석 작업이 완료되면 반드시 애플리케이션 분석을 수행해야 한다. 이를 통해 실행 계획을 검증하고 적절한 수행 계획을 보장하는가에 대해 점검해야만 실행 계획이 최적화될 수 있도록 유도할 수 있다. 다음 진행 사항은 애플리케이션 튜닝 후 약간의 시간동안 데이터를 축적한 후 데이터 파일별 I/O 대기 시간을 분석하고, 대기가 집중되는 데이터 파일을 찾아내 데이터 파일을 사용하는 애플리케이션과 SQL을 다시 분석해야 한다.

그리고 오브젝트의 분산, 파티션, 데이터 파일 재분산 등의 최후 작업을 수행하고 향후에 디스크나 컨트롤러의 추가, 더 빠른 디스크로의 교체 등의 튜닝 단계를 거쳐야 한다.

데이터베이스 자원 대기 분석은 오라클 내에서 발생하는 내부 자원의 경합으로 버전마다 차이가 있다. 하지만 200여 개의 대기 정보를 누적 뷰, 현재 뷰 상태로 오라클 내에서 제공하고 있다. 많은 요소가 있지만 분석 단계에서 참조하는 항목은 정형화된 30∼40여개의 항목으로 압축된다. (그림 3)은 200여 개의 항목이 각 항목별로 그룹화된 항목을 보여주고 있는데, 항목별 요소 분석 후 세부 항목별 상세 분석을 진행한다.

[img3]

가장 먼저 수행해야 할 항목은 OPS(Oracle Parallel Server) 여부에 대한 항목이다. OPS일 경우, 단일 인스턴스 분석 외에 다른 인스턴스의 분석도 병행돼야 하고 발생되는 대기 항목 자체도 좀 더 많기 때문이다.

락 대기(Lock Wait)가 집중 발생됐다면 비즈니스 로직 플로우를 최우선적으로 검토해야 한다. 이후 락을 유발하는 애플리케이션의 수행 추이 분석을 실시한다. SQL 단위 분석이나 프로그램을 분석할 때 특정 일자에 급격하게 느려지는 현상이 발생한다면, 이는 대개 오브젝트 변경 작업이나 데이터의 급격한 증가로 인한 통계 정보의 부적절 등이 원인인 경우가 많다. 따라서 반드시 애플리케이션 튜닝을 통해 최적화된 실행 계획을 보장해야 한다. 버퍼 대기는 보통 내부 락 대기와 동시에 발생하는 것이 특징인데, 버퍼 캐시 크기, 비효율적인 I/O(LRU의 장점을 무시하는 SQL) 등이 주요 원인이 되며, 이를 해결하기 위해서는 애플리케이션과 오라클 파라미터의 병행 분석을 수행해야 한다.

공유 풀 대기도 역시 내부 락 대기와 동시에 발생하는데 공유 풀의 크기와 애플리케이션의 수행 유형과 빈도를 병행 분석해야 한다. 특히 SQL의 사용 과다로 인한 라이브러리 캐시, 공유 풀 래치(shared pool latch)의 과다 발생이나 온라인중의 오브젝트 변경 작업으로 인한 라치 과다 발생 등을 주의해야 한다. 조치 방법이 있기는 하지만, 상당히 정형화되고 제한적인데다가 많은 시간 투입을 요구하기 때문에 정확한 분석과 검증을 지속해야 한다. 이 외에도 많은 대기 항목들이 있으나 위에 서술한 사항들은 필자가 사이트의 시스템을 분석해 보면서 일반적으로 발생하는 경우를 중심으로 필자의 분석 방법론을 연계시켜 설명했다.

분석 요소 파악 후 튜닝 목적 설정

데이터베이스 성능 분석은 상당히 많은 분석 요소를 갖고 있다. 데이터베이스 입장에서 보면, 응답 시간을 분석한 후 시스템, 데이터베이스, 프로그램, SQL 레벨에서의 처리 시간, 대기 시간의 비중 분석을 통해 튜닝의 목적과 대상을 정하고 각 단계별로 하나씩 해결해 나가는 방법론을 갖고 있다. 이 때 처리 시간과 대기 시간을 줄여, 결국 응답 시간을 줄이는 형태로 분석과 검증의 형태를 병행해야 한다는 것이 중요하다.

또한 특정 시간대의 분석만 중요한 것이 아니라 현재의 상태를 기준으로 과거의 성능 추이를 분석해 기준점을 설정하고 기준 포인트를 넘는 상황이 발생할 경우 정확한 원인 분석으로 SLA 뿐만 아니라 사용자의 불만을 최소화하는 형태의 분석이 단순 애플리케이션 튜닝보다 더 중요하다고 말할 수 있다.

시스템의 성능 관리란 어제와 오늘의 차이점, 오늘과 내일의 차이점 등을 분석·예측해 미래 지향적인 시스템 운영의 최적화를 실현하는 것이라고 볼 수 있다. 다음 호에서는 애플리케이션 중심의 성능 분석과 튜닝에 대해 살펴보겠다. @