(오라클) 데이터베이스 성능 진단 및 평가

[제언]
요즈음 세계를 놀라게 한 한국팀의 선전으로 월드컵 열기가 뜨겁다. 한국팀의 선전은 네덜란드의 명장 히딩크가 한국의 축구국가대표팀의 감독을 맡은 후 내린 한국팀에 대한 정확한 진단과 한국민의 응원열기 그리고 선수들의 정신력이 혼연일체가 되어 이룬 결과이다. 그중에서도 한국팀에 대한 정확한 진단이 수훈갑이었음은 주지의 사실이다.
마찬가지로 데이터베이스 성능 튜닝에 있어서도 제대로된 진단과 평가의 선행이 얼마나 중요한지에 대해서는 두말할 필요가 없는 것이다.
DBA는 프로젝트가 끝나고 데이터베이스의 운영에 들어가게 되면서 여러가지 문제에 직면하게 된다. 그것은 관리운영적인 문제일 수도 있고, 성능적인 문제일 수도 있다. 또는 시스템 증설등과 같은 미래계획 수립을 위한 사이징이나 근거 수립을 대한 문제일 수도 있다.
어쨌든 데이터베이스 서버나 어플리케이션 튜닝의 처방을 원하든 시스템 증설의 처방을 원하든간에 선행되어야 할 부분은 데이터베이스 시스템에 대한 정확한 진단과 평가인 것만은 분명하다.
데이터베이스에 대한 진단 수행시 많이 DBA들이 어려워하는 부분이 모니터링이나 진단 스크립트 또는 툴들을 이용하여 각론적인 면에서 데이터베이스를 진단하고 평가하는 것은 그다지 어렵지 않으나, 총체적인 관점에서 시스템을 평가하고 문제의 원인을 정확히 파악하는데는 적잖은 어려움을 겪고 있다.
즉, 단순히 스크립트를 이용하여 적중률의 좋고 나쁨을 판단하고 일반화된 조치를 취하는 것은 어느 정도의 실력을 갖춘 DBA에게는 어려운 일이 아닐 것이다.
하지만 여러 단편적인 진단내용을 토대로 다양한 시스템 리소스와 정보시스템 레이어간의 상관관계를 고려하여 근본원인을 진단하고 총체적인 관점에서의 핵심 결론을 내리는 것은 상대적으로 어려운 일이다.
사실 한시간만을 진단해보고 결론을 내릴수도 있으며, 일주일의 기간을 갖고 진단하여 결론을 내릴 수도 있다. 하지만 단순한 모니티링 차원의 진단이 아닌 체계적인 접근과 분석을 통한 정확한 진단을 내리기 위해선 나름대로의 체계적인 접근 방법론과 시간을 요하게 된다.
단순한 모니터링 차원의 진단까지는 어떻게 보면 손쉽게 할 수 있는 부분일 수 있으나 이 진단 결과를 토대로 브레이크 다운하여 문제점에 접근하여 분석하고 총체적이며 핵심적인 결론을 내리기위해선 나름대로의 체계적 접근 방법론과 데이터베이스에 대한 지식외에 시스템 및 네트웍 어플리케이션등 정보시스템 전반에 걸친 지식이 필요하며, 또한 주어진 시스템 환경에 대한 정확하고 상세한 정보를 갖고 있는 것이 필요하다.
따라서 체계적인 데이터베이스 성능의 진단과 평가를 위해서 어떤 단계들이 필요하며, 어떠한 수행사항이 필요한지를 지금부터 기술적인 측면보다는 방법론적인 측면위주로 살펴보기로 하겠다.

- 진단 및 평가 Process -

[환경 셋업]
어떤 작업을 수행하든 마찬가지겠지만 데이터베이스 성능 진단을 위해서 가장 먼저 수행해야할 단계는 역시 환경 셋업 단계이다. 데이터베이스를 진단하기 위한 기본적인 업무 및 네트웍 환경을 셋업하는 단계로 대부분의 경우 이미 운영 관리중인 시스템을 대상으로 할 경우에는 필요없는 단계라 할 수 있다. 다만 새로운 시스템이나 타 업무 시스템에 대한 진단일 경우 기본적으로 인적자원 및 작업환경을 셋업하는 과정이 나름대로 필요하다.
담당자와의 상견례를 통한 Communication 채널 설정과 클라이언트의 데이터베이스 시스템 접속환경 파악 및 설정등이 이에 해당한다.

[환경 파악]
환경 파악은 모든 단계를 수행하는데 있어서 가장 기본적인 단계라 할 수 있겠다.
정확한 진단 및 평가를 내리기위해서는 대상이 되는 데이터베이스 및 정보시스템에 대한 정확한 정보가 필요로함은 당연한 일일 것이다.
환경파악에는 경우에 따라 다음의 자료 전체 또는 일부가 준비되어 있어야 하며, 이러한 자료들은 담당 DBA 또는 시스템 관리자나 개발 PM등을 통해서 확보할 수 있다.

l시스템 측면
- H/W 시스템 구성도 / 시스템 구성 상세
- 네트웍 구성도
- Application(S/W) 구성도
- DB 구성정보
- Disk Map

l데이터베이스 측면
- Disk와 Tablespace Mapping Structure
- Tablespace 및 업무구성 현황
- DB User 생성 기준 및 운영 현황
- Storage Option 활용 현황
- 노드별 업무 실행/운영 기준 및 현황(업무 Flow)
- 장애 내역
- 장애 탐지 및 조치 정책
- 백업 & 리커버리 정책 ( MTTR)
- 보안정책 현황

l어플리케이션 측면
- 파티셔닝 방안
- Optimizer 운영 및 Analyzing 현황
- 업무별 Parallelism 적용전략
- 악성 프로그램 추출 및 개선방안

이외에도 경우에 따라 필요한 자료는 가감이 될 수 있다. 예를 들어 이중 일부는 OPS(Oracle Parallel Server)환경으로 운영되는 시스템에만 필요한 자료이다.
Disk Map은 I/O성능 및 분산을 평가하는데 핵심적인 자료이므로 반드시 작성되어 있어야함에도 불구하고 인식부족 또는 바쁜 업무관계로 준비되어 있지않는 곳이 의외로 많은 실정이다. 이부분은 반드시 H/W 시스템 벤더나 Storage를 납품하는 벤더의 도움을 얻어 기본자료로 구비하고 있어야 한다.

[요구사항 수집]
이 단계도 어떤 업무를 수행하더라도 꼭 필요한 기본적인 단계이다. 운영중인 데이터베이스 시스템의 성능 및 운영에 대한 개선사항에 대해 다양한 Group을 대상으로 요구사항을 수집하는 단계다.
요구사항 수집은 면담이나 설문을 통해서 이루어질 수 있으며, 잘 정리된 정형화된 포맷을 이용하여 수행된다면 요구사항을 정리하기가 더 용이할 것이다.
면담이나 설문 대상은 일반적으로 다음과 같다.

nSystem Admin.
nDBA
nApplication 개발자 Group
n사용자 Group

[ Baseline 설정]
이 단계는 원래 시스템 진단을 수행하기 이전에 평소에 이미 어느정도 수립되어 있어야 하는 부분이다. 이 단계에서는 시스템을 진단하고 평가하기 위해서 평가항목에 대한 기준이 되는 값을 정하게 된다.
예를 들어 v$waitstat라는 뷰를 보면 db block에 대한 요청이 wait된 횟수를 알 수 있는데, 이 wait된 횟수는 시스템 규모나 트랜잭션량에 따라 db block대한 경합을 판단하는 기준 수치가 된다. 그러므로 진단을 하고자 하는 시스템에 대해 일반적인 로드가 걸린 상황에서 임의의 시간간격의 db block에 대한 wait 횟수를 측정하여 기준 수치로 설정해 놓는 것이다.
이러한 수치를 기준으로 데이터베이스 수집단계에서 수집된 데이터에 대한 경합여부등을 판단하는 근거가 되는 것이다.

[분석 데이터 수집]
분석을 위한 기본적인 데이타를 수집하는 단계이다.
기본적으로 데이터 수집은 시스템과 데이터베이스 두가지 측면에서 이루어지며, 데이터 수집을 위한 적절한 권한을 가지고 있어야 한다. 또한 유효한 데이터의 수집을 위해서는 업무의 Load가 가장 많이 걸리는 시간을 선택하여 적절한 시간 간격으로 수집하는 것이 중요하다.

<시스템>
시스템 데이터는 크게 CPU와 Memory, Disk I/O, Network I/O 및 시스템 기본정보를 수집하게 되며, OS별로 수집하는 명령이 틀리므로 적당한 명령의 조합으로 스크립트를 작성하여 수행하는 것이 편리하다.
CPU의 경우는 CPU Time과 Run Queue Size를 수집하게되며, CPU Time은 일반적으로 Idle time이 30%정도 이상인 것을 적절하다고 보며, Run Queue Size는 CPU당 2개이하를 적정 수치로 보고 있지만 이러한 부분은 Application 성격이나 데이터 수집 시각, 시스템 스펙등에 따라 달라질 수 있다.
즉, CPU Time의 경우 system이나 user process, Wait I/O(I/O에 대한 대기) 항목에 대한 CPU Time 배분의 정도에 따라 다양한 판단이 가능하며, 단순히 30% Idle Time만을 절대적인 기준으로 볼 수 없는 것이다.
Memory의 경우는 Free memory Size, Paging, Swapping, Swap file Usage등의 데이터를 수집하며, DISK와 Network은 각각 I/O Bottleneck을 체크하기위한 정보를 수집한다.
SUN Solaris 2.x를 기준으로 각 항목의 데이터를 수집하기 위한 명령을 정리해 보면 다음과같다.
lCPU : sar –u, sar –q, uptime, vmstat
lMemory : sar –r, sar –p, sar –w, swap –l, swap -s, vmstat
lDisk : iostat –D, vmstat
lNetwork : netstat -i
( 각 명령에 대한 용법과 설명은 글의 취지에서 벗어나므로 언급하지 않는다. )

시스템 기본 정보는 일반적으로 커널 파라미터에 대한 정보와 시스템 기본환경에 대한 데이터 수집으로 역시 SUN Solaris 2.x의 경우를 예로들면 다음과같다.
l커널파라미터 설정 파일 : /etc/system
l기본 정보 확인 명령 : psrinfo(CPU 개수 및 Online status 확인),
psrinfo –v (CPU 상세정보 확인), ipcs –ma (Shared Memory정보), ipcs –sa(Semaphore 정보), sar –g(파일시스템별 블록 사이즈), sysdef (시스템 정의), prtconf (시스템 설정 정보), dmesg (시스템 진단정보), prtconf | grep Mem (메모리 사이즈 확인), sysdef | grep bufhwm (버퍼캐쉬 사이즈 확인).
lmaxphys : adb -k (This should be executed as root user)

Shared Memory와 Semaphore에 대한 설정은 오라클 SGA영역과 Process 개수와 상관이 있으며, 파일시스템별 블록사이즈는 db block size와 관련이 있다. Maxphys 즉, OS의 Max I/O Buffer Size의 경우는 오라클에서 db_file_multiblock_io_count를 진단할 때 필요한 항목으로 Solaris의 경우는 Default로 128K size를 갖으며, Veritas File System을 사용하는 경우의 기본 size는 256K이다.
Virtual Memory 모델을 사용하는 현대의 UNIX에서는 OS메모리의 구성은 보통 4가지 영역으로 나뉘어 생각해 볼 수있는데 SYSTEM 영역, USER 영역, Buffer Cache 영역Buffer 그리고 Free memory 영역이 그것이다. Sun Solaris에서는 기본적으로 Physical Memory의 20%를 Buffer Cache Size로 할당한다. 이부분의 설정은 Paging이나 swapping과 관련이 있으며, 결국 오라클에서 SGA영역의 크기설정에 관계될 수 있는 부분이다.
디스크 구성에 관한 설정과 매핑은 다양한 유닉스 명령의 조합을 통해서 파악할 수 있으며, 기본적으로 RAID Level과 Striping Size, 파일시스템 타입(Raw/UFS/VxFS/Etc)등의 파악이 추후 I/O성능 및 분산의 평가를 위해서 파악이 되어야 한다.
이외에도 필요에 따라 다양한 명령을 통해 추가적인 시스템 설정사항을 파악할 수있으며, 이러한 부분은 결국 OS상에서 구동되는 하나의 Application인 오라클과 관련이 되는 것으로 기본적인 시스템 정보의 파악과 OS 시스템에 대한 이해는 오라클 데이터베이스의 올바른 진단의 기본이 되는 것이다.
시스템 기본 정보를 수집하는 것이나 커널설정에 관한 사항의 수집과 이해는 OS별로 제공되는 유틸리티도 틀리고, 기본값이나 구조도 틀리지만 중요한 것은 Unix 시스템의 기본적인 Virtual Memory모델과 Process 모델의 이해가 핵심이라는 것이다.
시스템에 대한 데이터를 수집함에 있어서 유의해야할 점은 일반적으로 시스템에서 보여주는 통계정보는 데이터베이스와는 달리 그때 그때의 Snapshot 정보를 보여주고 있다는 점의 유의하여 적절한 시간과 간격을 가지고 수집해야 한다는 점이다.

<데이터베이스>
데이터베이스에 대한 데이터 수집은 다양한 스크립트나 툴을 통해 이루어질 수 있다. 데이터베이스 버전이나 구성에 따라 MTS나 OPS에 관한 데이터 수집여부, 사용 스크립트나 툴의 설정 여부가 먼저 정의되어야 한다.
또한 기본적으로 한번만 수집해도 되는 데이터베이스의 구조와 같은 정보외에 성능에 관련된 통계정보의 경우는 업무 패턴에 따라 적절한 수집시기와 간격을 정의해야 한다.
일반적으로 데이터베이스의 통계 데이터를 수집할 때는 기본적으로 오라클에서 제공하는 시스템 뷰의 정보를 수집하며, 이러한 시스템 뷰는 일반적으로 데이터베이스가 start up된 이후의 누적된 데이터를 담고 있다. (OPS관련 시스템 뷰인 v$ping과 v$false_ping의 경우는 snapshot 데이터를 담고 있다.)
그러므로 업무 사이클에 맞는 적당한 기간과 데이터 사이즈 및 정확성을 감안한 적절한 시간 간격(일반적으로 5분, 10분, 20분)을 설정하여 시스템 뷰에 대한 데이터를 수집하고 주어진 시간 간격별로 수집된 데이터의 편차값을 구하여 이를 토대로 수집된 시간대별로 발생한 데이터베이스의 각종 통계정보를 구할 수 있다.
다음은 진단 환경에 따라 일반적으로 조회되어지는 시스템 뷰의 목록이다.

기본 통계정보를 위한 시스템 뷰
lv$sysstat
lv$system_event
lv$filestat
lv$rollstat
lv$librarycache
lv$waitstat
lv$latch

MTS를 위한 시스템 뷰
lv$shared_server
lv$queue
lv$circuit
lv$dispatcher
lv$mts

OPS를 위한 시스템 뷰
lv$lock_activity
lv$ping
lv$false_ping

이상의 성능 통계정보를 담고 있는 시스템뷰에 대한 데이터 수집외에 데이터베이스에 대해서 수집할 수 있는 기본적인 정보들은 다음과 같은 것들이 있다.
lSQL 사용에 대한 정보
lRedo Log 사용 정보
lDatabase 구조 정보 (TBS, Dataifle , Controlfile, Redo file, RBS, Sort Segment, Etc)
l보안 정보 (User, Application, Previlige, Role, Etc)
lStorage 설정 정보 및 Reorg가 필요한 Object 정보

이외에도 진단을 위해서 필요한 파일로서 Alert 파일과 init<SID>.ora 파일이 있을 수 있으며, 필요에 따라 추가적인 시스템 뷰에서 다양한 정보를 추가 수집해야 한다. 현재도 DBA들은 저마다 많은 진단 스크립트와 툴들을 가지고 있으며 필요에 따라 수행을 하고 있지만 중요한 것은 얼마나 많은 스크립트나 진단 툴을 가지고 있고 이를 수행하느냐가 아니고 그러한 스크립트나 진단툴을 자신만의 진단 방법론에 따라 어떻게 활용하느냐 하는 것이다.

[진단]
진단 단계에서는 요구사항 수집 단계에서 정리된 요구사항과 Alert 파일을 통해 핵심적인 문제점을 우선 파악하고, 수집단계에서 얻어진 각종 데이터를 근거로 하여 수행되어진다.
수집된 데이터의 값이 Baseline 값에 비해 비정상적인 값을 보이거나 미심쩍은 부분이 있을 경우 이 부분에 대해서는 브레이크 다운하여 추가적인 데이터 수집과 상세한 진단이 수행되어야 한다.
상세진단 수행시에는 스크립트 수행이나 오렌지나 토드와 같은 툴을 이용하여 추가적인 자료수집 및 진단을 수행할 수 있으며, 또한 환경 파악단계에서 수집된 자료와 담당 DBA나 SA 또는 개발자에 대한 문진(問診)을 이용하여 수행할 수 있다.
진단 수행시에는 여러 레이어 및 리소스간의 상관관계를 고려하여 숨겨진 원인을 볼 수 있는 시각이 필요하며, 이것은 진단 수행자의 정보시스템 전반과 데이터베이스에 대한 지식 따라 달라지게 되므로 평소 정보시스템 전반과 데이터베이스에 대한 대한 폭넓고 깊은 지식과 노하우를 쌓도록 노력한다.
진단 수행시에는 단순히 데이터베이스에 대한 성능 통계뿐아니라 다음과 같은 운영정책적인 면이나 정보시스템 Architecture와 같은 부분도 병행해서 점검해 보는 것이 필요하다.

l가용성 분석 및 평가
l보안정책 분석 및 평가
l전산자원운영 분석 및 평가
lJob Scheduling 효율성 분석 및 평가
lSystem Architecture 분석 및 평가

[보고서 작성]
진단수행이 완료되면 각 진단항목에 따른 분석 및 평가 보고서가 작성되어야 한다. 작성된 보고서는 데이터베이스 서버 및 Application 튜닝을 하기 위한 근거자료가 되며, 또한 DB운영 및 미래계획 수립을 위한 기초 자료가 된다.

[결언]
지금까지 데이터베이스 성능 진단 및 평가에 관해서 방법론적인 차원에서 단계별로 살펴보았다. 데이터베이스 진단시 이러한 단계 및 단계별 수행사항들이 항상 모두 수행되어야 하는 것은 아니다. 진단 대상 및 환경에 맞게 단계를 축약 또는 생략할 수 있으며, 또는 추가적인 단계가 필요할 수도 있다.
하지만 중요한 것은 구슬이 서말이라도 꿰어야 보배란 말이 있듯이 정보화시대에 기업의 정보시스템의 근간이 되는 데이터베이스라는 핵심 시스템을 정확히 진단하기위해선 단편적인 지식과 접근을 통한 방법이 아닌 그러한 지식들을 자신만의 방법론으로 체계화시킨 접근법이 좀더 총체적이고 정확한 진단을 수행할 수 있도록한다는 것이다.
그것은 결국 좀 더 견고한 데이터베이스의 운영과 좀 더 나은 성능향상 및 기업경쟁력 향상의 단초가 되는 것이다.

[출처] (오라클) 데이터베이스 성능 진단 및 평가|작성자 오토맨