상세 컨텐츠

본문 제목

DB 성능 진단 어떻게 할 것인가? (1회)

DataBase/DBGuide

by 탑~! 2012. 12. 13. 10:45

본문

성능 문제 식별의 시작점은?

 

CPU, Memory, I/O, Network 4가지 지표를 사용하는 O/S에 비해, DBMS는 성능 지표가 대단히 많은 편이다오라클의 경우 11gR2(11.2.0.3) V$SYSSTAT 기준으로 통계 항목의 개수는 무려 622개에 달한다대기 이벤트(Wait Event)는 이보다 더 많은 1150 (v$event_name 기준)그렇다면 성능 문제 식별을 위해 이 모든 성능 통계 항목을 조사해봐야 할까물론 그렇지는 않다특별한 트러블슈팅이 아닌 한 이들 전체 통계 항목을 조사할 일은 거의 없다실제로 일상의 성능 문제를 판별하기 위해 필요한 통계 항목의 개수는 10개 내외로 많지 않다.

여기서 한가지 강조하고 싶은 것은 가장 중요한 성능 분석의 시작점은 바로 사용자의 체감 응답시간이라는 사실이다현업 사용자가 느리다고 하면 문제가 있는 것으로 판단하여 추가 분석을 해야 한다그러나 DBMS에서 특정 수치가 높다는 이유로 무조건 분석을 시작해야 하는 것은 아니다높은 수치가 항상 문제를 의미하지는 않는다성능 문제 판단의 기준은 항상 사용자의 체감 응답시간이라는 점을 잊지 말자.

 

Hit Ratio 더 이상 통용 가능한가?

DBA들이 입문하면서 OCP 같은 자격 시험을 치르면서 가장 먼저 배우는 방법이 바로 Hit Ratio에 따른 성능 측정법이다예를 들어 Buffer Cache Hit Ratio Physical I/O 대비 Logical I/O의 비율을 계산하여 Buffer Cache 적중률을 따진다이른바Physical I/O는 매우 나쁜 것이며, Buffer Cache Hit Ratio가 몇 % 이하일 경우 Buffer Cache를 늘려주어야 한다는 것이다.

실제로 과거 DBA 중에 현업 실무자가 성능 문제를 하소연할 경우단순히 Hit ratio 몇 개만 측정해본 후 문제 없다고 응답하는 경우가 많았다그러나 이러한 방법은 오늘날 더 이상 통용되지 않는데그 이유는 오늘날 메모리는 더 이상 고가의 자원이 아니라는 점 (최근에는 수십 기가바이트에서 수십 테라바이트의 메모리를 장착한 DBMS도 볼 수 있음), DBMSBuffer Cache 관리 알고리즘의 정교화스토리지 기술의 눈부신 발전 그리고 엑사데이터 같은 플랫폼의 출현으로 Physical I/O를 개선할 수 있는 많은 방법이 제시되고 있다는 점을 들 수 있다.

무엇보다도 OLTP 업무 쿼리의 대부분은 99% 이상의 Hit Ratio를 유지하고 있다고 볼 수 있다따라서 OLTP 쿼리는Physical I/O 문제 보다는 대량의 Logical Block I/O를 일으키는 쿼리가 더 문제다물론 Logical I/O Physical I/O보다 대단히 가볍지만온라인 쿼리에서 수만 블록 이상의 Logical I/O를 다량으로 일으킬 경우 높은 CPU 사용률과 래치 경합을 보일 수 있다따라서 기계적으로 Buffer Cache Hit Ratio를 측정하여 무조건 Buffer Cache를 늘리는 방법은 구시대의 유물로 폐기해야 할 것이다오히려 문제의 원인 쿼리를 찾아서 튜닝을 실시하는 것이 순서에 맞다. (게다가 11g부터 일정 크기 이상의 세그먼트에 대한 full scan은 자동으로 buffer cache를 거치지 않고 direct path read를 하도록 알고리즘이 개선되었다엑사데이터는 여기서 추가로 스마트스캔을 통해 Physical I/O를 최소화시킨다.)

 

Hit Ratio 방법의 또 다른 문제점은 드릴 다운이 어렵다는 점이다, Buffer Cache Hit Ratio가 낮게 나온다면 Physical I/O를 하는 원인 세션에서 더 나아가 원인 쿼리를 찾아서 튜닝을 해야 하는데, Hit Ratio는 이러한 추적 방법을 제공하지 않는다. Hit Ratio는 특정 기간 동안의 평균을 의미하므로 이른바 평균의 함정에 빠지기 쉽다특정 시간 동안의 측정치를avg하게 되면 높게 튀어 오른 문제 구간은 깎여나가 평균에 묻히게 되어 분석할 수 없게 된다.

 

만약 Physical I/O 문제를 겪고 있는 DBA가 있다면오라클 9i부터 지원하는 Keep Pool을 사용해보라고 권하고 싶다. Keep Pool은 파티션 단위로도 지정이 가능하므로월 말이 되면 자동으로 최신의 파티션을 Keep하도록 약간의 스크립트 작업을 거치면 항상 최근 데이터를 Keep pool에 유지할 수 있다. (물론 Keep pool LRU에 의해 age out이 발생할 수 있으므로 적절한 용량 산정을 해야 효과를 볼 수 있다. ) 또한, 기존의 Default Buffer pool과 별도의 래치를 사용하므로 이에 따른 경합도 감소시킬 수 있다.

 

데이터베이스 통계 항목 (Database Statistics)

이들 V$STATNAME의 통계 항목은 종류에 따라 CLASS로 분류되어 있는데 CLASS 분류는 다음과 같다.

예를 들어온라인 트랜잭션 성능 저하의 원인으로 Redo 성능이 의심이 간다면 이를 드릴다운 하기 위해 ‘2, Redo’에 대한 통계 항목을 살펴보면 될 것이다.

1, User

2, Redo

4, Enqueue

8, Cache

16, OS

32, Real Application Clusters

64, SQL

128, Debug

 

V$SYSSTAT V$SESSTAT의 통계 항목을 조사할 때 주의 사항이 하나 있다. V$SYSSTAT는 instance 기동 이후의 누적 값을 담고 있으며, V$SESSTAT의 세션이 맺어진 이후의 누적 값이라는 점이다따라서 V$SYSSTAT의 경우는 누적 값이 아닌 특정 시간 간격을 두고 스냅샷을 취한 델타 측정값이어야 한다예를 들어, V$SYSSTAT의 ‘Redo Size’ 항목의 누적 Value7236385760처럼 큰 값을 가지고 있을 경우이 수치를 통해 문제 여부를 파악할 수는 없다그러나 금일 오전 09:00 업무 시작 직후 5분간 트랜잭션 성능 저하와 함께 Redo size 항목의 value가 대단히 큰 값으로 증가했다면 문제를 의심해볼 수 있으며추가 분석을 위해 ASH AWR을 드릴다운 해볼 수 있을 것이다.

V$SESSTAT도 대단히 조심해야 한다왜냐하면 최근에 2계층 Client/Server 환경으로 구성되는 곳은 거의 없으며최소 3계층 이상의 n계층이며 Connection pooling을 사용하기에 특정 세션의 누적 값은 의미가 없다따라서 이 역시 델타로 특정 시간 구간의 값을 측정하여야 한다.

 

보통은 이러한 델타 측정을 위해 사용자 정의 스크립트(주로 PL/SQL을 활용)하여 주기적으로 V$SYSSTAT, V$SESSTAT의 값을 성능 수집 테이블에 채집한 후 분석하는 방식을 취하고 있다그러나 이들 딕셔너리 뷰를 조회하는 것은 DB에 많은 부하를 줄 수 있기에채집 간격(폴링 주기)를 초 단위로 짧게 가져가기 힘든 애로사항이 있으며보통은 몇 분 간격으로 채집하고 있다그러나 분 간격은 너무 넓어서 msec 단위로 수행되는 OLTP 쿼리에 대한 성능 문제를 분석하기에는 적합하지 않다. (너무 성긴 그물을 사용하면 그물 사이로 고기가 빠져나가는 것과 마찬가지로 볼 수 있음뒤에서 설명하겠지만오라클 10g 이후부터는 ASH AWR을 제공하고 있기에특별한 경우를 제외하고 이러한 사용자 정의 스크립트에 더 이상 의존할 필요는 없다.

 

대기 이벤트 (Wait Event)

대기 이벤트도 다음과 같이 종류에 따라 CLASS로 나눠져 있다.

----------- --------------

          0 Other

          1 Application

          2 Configuration

          4 Concurrency

          5 Commit

          6 Idle

          7 Network

          8 User I/O

          9 System I/O

최근에 가장 많이 쓰이는 방법으로 성능 문제 식별모니터링버그나 특정 이슈에 대한 전문적 트러블슈팅 등에 널리 사용되고 있다대기 이벤트에 대한 자세한 내용을 언급하는 것은 이 칼럼의 범위를 넘어서므로 개별 대기 이벤트에 대한 상세 사항은 관련 서적을 참고 하기 바란다.

 

대기 이벤트를 통한 문제 식별을 위해 가장 많이 사용되는 방법은 TOP 5 Wait event를 보는 것이다특정 이벤트가 갑자기TOP으로 올라와서 장시간 대기할 경우그 이유를 조사해봐야 한다.

다음은 최근 60초 동안의 TOP Wait event를 보여주는 쿼리다.

 

SELECT a.event#,

         b.name,

         a.event_id,

         a.wait_count,

         a.time_waited,

         a.num_sess_waiting,

         b.wait_class

    FROM v$eventmetric a, v$event_name b

   WHERE     a.event# = b.event#

         AND (a.wait_count > 0 OR a.time_waited > 0 OR a.num_sess_waiting > 0)

         AND b.wait_class <> 'Idle'

ORDER BY time_waited DESC

 

특정 세션 별로 대기 이벤트를 볼 수도 있는데오라클 10g부터는 V$SESSION에 EVENT, P1, P2, P3 칼럼이 추가 되어 손쉽게 추적해볼 수 있다. SQL 별로 추적하고 싶다면 V$SESSION SQL_ID 칼럼을 사용한다.

Active Session History를 통해서도 추적할 수 있도록 EVENT칼럼이 존재한다만약 위의 쿼리에서 특정 대기 이벤트가 갑자기 TOP으로 올라와서 장시간 머무를 경우, ASH를 통해서 원인 쿼리와 P1, P2, P3 칼럼을 통해 원인 분석을 시도 해볼 수 있다.


 SELECT *

  FROM v$active_session_history

 WHERE event = 'log file sync'  -- ASH를 통해 log file sync 이벤트를 대기한 세션을 조사함


마치며

다음 회에서는 좀 더 세부 레벨로 들어가서 SQL 레벨에서 성능을 측정하는 방법과 ASH, AWR에 대한 내용을 살펴보도록 하겠다.

튜닝에서 진단 과정은 매우 중요하다얼마나 문제점을 정확하고 빠르게 찾아내느냐에 따라 튜닝의 성패가 결정되기도 한다옛날 명의들은 방문을 열고 들어서는 환자의 낮 빛만 보고도 병의 원인과 경과를 파악했다고 하지 않은가? 최근 필자는 '마의(馬醫)'라는 드라마를 즐겨 보고 있는데, 거기나오는 의원들의 진단법이 DB 튜너들의 방법과 너무 흡사하여 깜짝 놀란 경험이 있다. 드라마에서 주인공이 과학적 접근법을 통한 추론과 증거 수집을 통해 문제의 근본 원인을 찾아내자, 너무나 쉽고 빠르게 문제가 해결 되버린 것이다. 이 과정은 DB 성능 전문가들이 진단하는 과정과 매우 흡사하다고 볼 수 있다.


물론 진단방법만 숙지한다고 하여 누구나 명의가 될 수 있는 것은 아니다. 풍부한 임상경험, 논리적 추론을 할 수 있는 사고 능력의 배양, 끊임없이 자신의 방법론을 갈고 다듬어야 할 것이다.


출처http://www.dbguide.net/knowledge.db?cmd=specialist_view&boardUid=168663&boardConfigUid=87&boardStep=0


'DataBase > DBGuide' 카테고리의 다른 글

DB 성능 진단 어떻게 할 것인가? (2회)  (0) 2012.12.13

관련글 더보기