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도 볼 수 있음), DBMS의Buffer 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’ 항목의 누적 Value가7236385760처럼 큰 값을 가지고 있을 경우, 이 수치를 통해 문제 여부를 파악할 수는 없다. 그러나 금일 오전 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