dbsummary로 Db2 성능 문제 원인 찾기

운영중인 시스템에서 성능 문제가 발생하는 경우 원인을 찾고 해결에 접근하는 것은 쉽지 않은 일입니다. 

Db2 운영 환경에서 monreport 모듈을 활용하여 성능 메트릭 정보를 간단히 수집하여 병목을 유발하는 원인을 찾아내는 방법에 대해 알아보겠습니다. 

dbsummary는 monreport 모듈 중 하나로 mon_get 함수에서 처리량, 소요 시간, 행 읽기 등에 필요한 모니터 데이터를 캡처하고 일정기간 동안에 동일한 데이터를 다시 캡처하여 델타 값을 수집하는 프로시저입니다. 이렇게 델타 값을 수집하는 데에는 타당한 이유가 있습니다. Db2가 추적하는 대부분의 유용한 성능 메트릭 정보들은 데이터베이스 활성화 이후 누적된 값이고, 누적된 합계만 보면 관심이 있는 급상승 또는 급락 또는 ​​기타 최근 동작을 파악하기 어렵기 때문입니다. dbsummary를 사용하면 성능에 문제가 되는 시점에 델타 값을  간단히 수집할 수 있습니다. 

dbsummary 프로시저의 사용법은 매우 간단합니다. 괄호안에 측정할 주기를 초 단위로 주고 호출하기만 하면 됩니다. 아무런 값도 주지 않으면 디폴트로 10초간의 델타값을 수집합니다.  원하는 모니터링 시간(초)를 괄호안에 입력할 수 있습니다. 

 

$ db2 call monreport.dbsummary();

Db2에서 총 소요시간은 처리시간과 대기시간의 합입니다. 처리시간은 엔진이 작업을 처리하는데 소요된 시간인 반면 대기시간은 엔진이 작업을 하기 위해 기다린 시간을 의미합니다. 대기시간에는 IO 대기, 네트웍대기, Lock 대기, CF대기(pureScale환경) 등이 있습니다.   그럼 이제부터 dbsummary 결과를 보면서 대기시간이 어디에 있는지 분석하는 방법을 살펴보겠습니다. 참고로 monreport 모듈에는 잠금 대기, Top SQL 등을 조사하기 위한 프로시저도 포함되어 있습니다.

 

(1단계) 성능 병목이 DB 내부에서 발생했는지 혹은 외부에서 발생했는지 확인

<그림1>에서  전체 처리시간(Total Time)은 104396, 클라이언트의 요청을 기다리는 시간(CIWT,Client_IDLE_WAIT_TIME)은 22058입니다.  클라이언트 대기 시간은  전체 소요시간중 약 20%를 차지하므로, 병목 현상이 Db2내에 있다고 볼 수 있습니다. 

<그림 1>

반면에 <그림2>는 클라이언트 대기시간이 전체 소요시간의 10배나 됩니다. 이 경우는 네트웍이나 애플리케이션에 문제가 없는지 점검해 볼 필요가 있습니다. 

<그림2>

단, <그림2>의 CIWT는 DB서버에 연결만 하고 일은 하지 않는 IDLE 연결을 포함합니다. 

따라서 <그림3> 의 DBSUMMARY 하단에 수집되는 Idle connection의 수를 구하여 <그림4>처럼 CIWT의 비율을 재 계산해야 합니다. 

<그림3>

<그림4>

IDLE Connection 시간을 제외했음에도 불구하고 CIWT는 8.4배로 여전히 높으므로, DB서버 외부인 네트웍 또는 애플리케이션에 문제가 없는지 점검할 필요가 있습니다. 그러나 만약 <그림1> 처럼 클라이언트 대기 시간 점검에서 Db2 내부 소요 시간이 훨씬 크다면 2단계 이하의 Db2내부를 살펴봅니다. 

 

(2단계)  전체 수행 시간 중 대기 시간 비율 확인

일반적으로 성능 메트릭을 분석할 때는 활동 메트릭 보다는 더 많은 유형의 데이터베이스 상호 작용을 다루는 요청 메트릭을 사용하는것이 바람직합니다. <그림5>에서  90295/104396=86.4(%) 로 대기시간 비율이 매우 크므로, 어떤 종류의 대기에 문제가 있는지 확인해야 합니다.  만약 대기시간율이 40% 이하라면 6단계로 이동해도 됩니다. 

<그림5>

(3단계)  버버풀 대기 시간 확인

시스템을 사용시간이 증가하면서 필요 데이터가 버퍼 풀에 상주하면 버포풀 읽기 시간은 점차 감소합니다.  

<그림6>은 전체 대기시간 중 버버풀 읽기시간(POOL_READ_TIME)이 99%(총 대기시간 86%의 99%) 으로 대기시간의 대부분을 차지하고 있습니다 (단, 표시되지 않는 대기 유형이 있을 수 있기 때문에 대기시간 카테고리 합은 100%보다 작을 수 있습니다) 

<그림6>

이와같이 일정 시간이 지나도 POOL_READ_TIME이 감소되지 않는다면 적절한 튜닝이 필요합니다.

•  버퍼풀 크기 증가 - Hit Ratio가 낮은 때
• 인덱스 생성 - 디자인 어드바이저 활용
• db2_parallel_io - 프리페치 크기 증가(비동기 읽기 증가)
• SSD와 같은 빠른 스토리지를 테이블 공간으로 사용

(4단계) 로깅 대기 시간 확인

Insert/Update/Delete 때마다 로그파일에 Write가 발생됩니다.  일반적으로 Write Heavy한 시스템은 로깅대기 비율은 증가합니다. 만일 이 값이 25%이상이면 로깅 병목이 있다고 볼 수 있습니다. 

<그림7>

<그림7>은 로깅대기시간이 전체 대기시간중 44%를 차지하므로 로깅 병목 현상의 원인을 찾기 위해 평균 로그 쓰기 시간을 확인할 필요가 있습니다. <그림8>은 처음 Db2가 시작한 이래 평균 로그 쓰기 시간을 나타냅니다.  이 값은  적정 수준인 2~5 ms 보다 크므로 느린 로깅이 로깅병목 현상의 원인이라고 볼수 있습니다. 

<그림 8>

보다 정확하게 평균 로그 쓰기 시간을 측정하려면, log_wirte_time(LWT), num_log_wirte_io(NLWI)을 간격을 두고 2회 수행하여 얻은 결과 값의 델타 값으로 측정하면 됩니다. (LWT2 – LWT1) / (NLWI2 – NLWI1)

로깅 병목 현상을 해결하기 위한 방안들입니다.  

• 스토리지 설계에서 로그 경로와 테이블공간 경로가  분리되어 있는지 여부
• LOGBUFSZ가 overflow가 발생하지 않을 정도로 충분히 큰지 확인 (2000정도)
• SSD와 같은 빠른 스토리지를 로그공간으로 사용

(5단계) 잠금 대기 시간 확인

대기 시간이 높지만(2단계) 버퍼 풀 대기(3단계) 나  로그 대기(4단계)에 문제가 없는 경우에는 잠금 대기 시간을 확인하는 것이 좋습니다. 잠금 대기가 문제라면 해결을 위해서는 애플리케이션 사용자의 도움이 필요합니다. 원인을 찾기 위해 잠금 대기와 관련된 SQL 문, 데이터베이스 개체 등을 파악하기 위해 monreport 모듈의 다른 루틴인 monreport.lockwait를 사용할 수 있습니다.

Monreport.lockwait는 dbsummary 와 같이 일정 기간 동안이 아니라 바로 지금 발생하는 잠금 대기 상황에 대한 정보를 표시헙니다.  따라서 dbsummary에 높은 잠금 대기 시간을 나타내면 다음단계로 잠금 대기가 문제가 되는 기간 동안 monreport.lockwait를 여러 번 호출합니다. 여러 번 수집해야 하는 이유는 그 중 하나에서 잠금 대기가 임의의 이벤트일 수 있기 때문입니다. 그러나 유사한 대기가 여러 번 발생하면 패턴을 찾았다고 의심할 더 많은 이유가 있습니다. 근본 원인에 집중하고 있습니다. monreport.lockwait를 호출할 때마다 유용한 정보를 많이 보여주는 보고서가 생성됩니다.

• 잠금 홀더와 웨이터 모두의 애플리케이션 핸들
• 잠금 유형 - 행, 테이블 등
• 잠금의 이름 - 어떤 테이블인지, 그리고 무엇을 잠그고 있는지에 대한 기타 정보
• 잠금이 유지되고 요청되는 모드(공유, 배타 등)
• 잠금 대기 중인 요청자의 SQL 문

이 정보는 잠금 대기 시간을 유발하는 SQL 문 이나 응용 프로그램을 진단하는 데 도움이 되므로 잠금 대기를 방지하는 방법에 대해 응용 프로그램 개발자와 보다 생산적인 대화를 나눌 수 있습니다. 잠금 대기가 문제로 판명되는 경우 찾아야 할 몇 가지 기본 원인에는 1) 애플리케이션의 불필요하게 높은 격리 수준, 2) 대규모 테이블 또는 인덱스 스캔으로 과도하게 큰 단위의 잠금이 사용된 경우, 3) 오랜 동안 많은 수의 잠금을 유지하는 작업등이 있습니다. 

 

(6단계) SQL 컴파일 시간 확인

성능이 느린데도 대기 시간이 문제가 아니라면(2단계) 처리 시간을 점검해야 합니다. 우선 SQL을 컴파일하는 데 많은 시간을 소비하고 있는지 여부입니다. 이것이 반드시 나쁜 것은 아닙니다. 예를 들어 복잡한 SQL과 높은 최적화 수준이 있는 웨어하우스 환경에서는 피할 수 없는 경우도 있지만 가능하면 피하는 것이 좋습니다. 대기 시간과 마찬가지로 dbsummary에는 처리 시간이 어떻게 소비되었는지에 대한 분석을 제공합니다(그러나 더 적은 범주로).

<그림 8>

<그림 8>에서 시스템은 SQL을 컴파일하는 데 처리 시간의 17%를 사용하고 실제로 SQL을 실행하는 데는 2%만 차지합니다. 이것은 가벼운 트랜잭션 SQL을 실행하는 환경에서는 낭비로 보입니다. 이와 같은 경우 monreport.pkgcache  또는 mon_get_pkg_cache_stmt을 활용하여 좀 더 철저하게 SQL을 살펴볼 필요가 있습니다. 파라미터 마커 대신 리터럴 값(예: "SELECT c1 FROM t WHERE c2 = 512", "SELECT c1 FROM t WHERE c2 = 811" 등)을 사용하여 SQL을 생성하는 애플리케이션이 있음을 짐작 할 수 있습니다. 리터럴 값이 있는 SQL은 SQL이 복잡하고 리터럴 값이 DB2가 최상의 계획을 선택하는 데 도움이 될 때 매우 유용합니다. 가장 좋은 해결책은 개발자와 대화를 나누고 리터럴을 사용하도록 코드를 변경할 수 있는지 확인하는 것입니다. 하지만 불행히도 애플리케이션을 변경하는 것은 자주 극복할 수 없는 문제인 것처럼 보입니다. 다행이도 Db2에는 불필요한 리터럴 사용으로 인한 문제를 일부 제거할 수 있는 'Statement Concentrator' 기능이 있습니다. DB레벨(STMT_CONC) 보다 애플리케이션 내 Connection 레벨에서 설정하는 것이 좋습니다. 

만약 추출된 SQL 구문들이 대부분 Parameter Marker를 사용했어도 컴파일 소요 시간이 크다면 Package Cache의 증가가 필요할 수 있습니다. 

 

(7단계) SQL 구문 확인

시스템 수준에서 큰 맥락의 원인을 살펴본 후 다음으로 할 일은 개별 SQL문을 살펴보고  심각한 문제가 있는지 확인하는 것입니다. dbsummary는 SQL문을 살펴보는 데 적합하지 않으므로 monreport.pkgcache를 사용하여 CPU 사용량, 실행 횟수, 행 읽기 수 등을 기준으로 SQL 문의 상위 10개 목록을 수집하여 분석하거나 mon_get_pkg_cache_stmt를 직접 쿼리하여 원하는 목록의 수집을 합니다. 

SQL을 조사하는 방법은 우선 SQL에서 참조하는 오브젝트의 카탈로그 통계를 확인하는 것입니다.정확한 통계가 없으면  옵티마이저는 최적의 액세스 플랜을 만들수 없기 때문입니다. 또한 종종 명령문 비용(CPU 사용량, 실행 시간 등)이 읽은 행 수에 비례하므로 이를 줄이기 위한 기술(예: 인덱싱, 파티셔닝, 다차원 클러스터링 등)는 전체 효율성과 실행 시간을 개선하는 데 도움이 됩니다. 이때 Design Advisor가 도움이 될 수 있습니다. 

 

맺음말

이상으로 Monreport.dbsummary를 활용하여 성능 병목 원인을 찾는 방법에 대해 알아보았습니다. 여기서 언급되지는 않았지만 dbsummary는 다른 성능 메트릭도 제공합니다. 예를 들어 입력을 받거나 결과 세트를 다시 보내는 동안 네트워크에서 대기하는 데 소요된 시간(클라우드 기반 환경에 매우 유용), 트랜잭션의 기본 처리량 및 초당 명령문, 패키지 캐시 활동, 연결당 및 구성원당 통계 등입니다.  

dbsummary는 큰 줄기에서 성능 병목원인을 빠르게 찿는데 도움을 제공합니다. 또한 디자인 어드바이저, mon_get 테이블 함수, monreport.pkgcache등으로 확장하여 가치있는 분석 결과를 얻기 위한 시발점이 될 수 있습니다. 

 

발췌 : https://www.idug.org/browse/blogs/blogviewer?BlogKey=13622e03-d274-48e2-9d8b-f949b3c7d1b7