DB2 for LUW에서 성능개선을 위해 Snapshot 활용하기

안녕하세요.  DB2 및 XpertMon 사용자 여러분! ㈜ 아이티엑스퍼트그룹 DB사업부입니다.

현재 저희 XpertMon 개발부서는 고객 분들이 원하시는 사항들을 좀 더 수렴하고 받아드리기 위해 많은 노력을 하고 있으며, XpertMon의 실시간 모니터링을 통해 얻어지는 History를 업무속성(DPF, OLTP, Batch …) 에 따라 적절한 KPI를 적용하여 보다 나은 성능관리 및 튜닝에 힘쓰고 있습니다. 차후 추가되는 기능들에 대해서는 뉴스레터를 통해서 자세하게 말씀 드리도록 하겠습니다.

이번 주는 “DB2 for LUW에서 성능개선을 위한 방법 중의 하나로 Snapshot 정보활동” 에 대해 소개하도록 하겠습니다.

아무쪼록 많은 도움되시길 바라며, 기존 뉴스레터들은 회사 홈페이지 ( www.iteg.co.kr) 게시판에 들어오시면 보실 수 있습니다. 혹시라도 알고 싶은 내용이나 주제가 있다면 메일로 보내주십시오. 참조하여 반영하도록 하겠습니다.

AGENDA

1.     성능개선 Tuning 위한 Focus

2.    Snapshot Monitor SQL Table Function

A.        Snapshot Monitor SQL Table Function 이란

B.        Snapshot Monitor SQL Table Function을 이용한 정보 선택과 수집

3.     Snapshot을 이용한 튜닝사례

4.     V9에서 제공 되는 SQL administrative view

Point, Click and Run: Using snapshots to Improve Performance in DB2 for LUW

1.      성능개선 Tuning

Tuning in a vacuum

Ø        요즘의 Database 이용추세는 OLTP성과 Batch Job이 함께 운영되는 경우가 많습니다. OLTP를 빠르게 튜닝 하면 Batch Process가 느려지거나, 그 반대로 Batch process를 튜닝 하면 OLTP가 느려지는 경우가 종종 있습니다. 하나의 Process 또는 SQL 문장을 튜닝 하면 다른 사용자와 응용프로그램에 원치 않는 결과를 가져올 수 있기 때문입니다.

이처럼 localized tuning은 좋은 이점도 있지만 다른 것에 부정적인 영향을 줄 수 있습니다. (ex 특정 Application이 주로 select 작업을 수행하여 인덱스를 생성 하게 되면 빠르게 읽게 되지만, 다른 Application 즉 Insert, update 작업을 주로 하는 Application는 더 많은 일을 해야 하므로 느려질 수도 있습니다.)

그러므로, 성능개선 Tuning이란 전체적인 모니터링과 지속적인 Application구성요소에 대해 개선하는 것이라 말할 수 있습니다.

Ø        한번 더 강조하면, Hardware, OS, CPU, I/O bound, Application (설계와 SQL) 및 Network 문제를 전반적으로 관찰하여 성능개선 Tuning 을 해야 합니다..

u       튜닝 사례에 관련하여 스냅샷은 하나의 방법론적 기법입니다.

2.       Snapshot Monitor SQL Table Function

A.  Snapshot Monitor SQL Table Function 이란

Ø        Get Snapshot명령어의 실행 결과와 동일한 Snapshot 정보를 테이블의 형태로 조회하는 테이블 함수를 말하며 해당 정보에 관련 된 monitor switch 를 on 시켜야만 해당 정보가 수집됩니다.

Ø        테이블 함수는 명령문의 From 절에서만 사용 가능하므로, DB명을 입력 하지 않으려면 ‘’ 또는 cast (null as char) 값을 사용하면 되며, 입력하지 않았을 시에는 현재 접속중인 DB의 정보를 리턴 합니다.

Ø        현재 Partition No. 에서 조회하고 싶을 때는 -1 또는 null로 입력하며, 모든 Partition 에 관해 조회하고 싶을 때는 -2를 partitionnum 자리에 대체합니다.

Ø        DB2 LUW 8.2에서는 21개의 테이블 함수가 제공됩니다

u       명령어

u       사용 예제

u       결과 (Xpertmon을 이용하여 보다 쉽게 결과 값을 확인 할 수 있습니다.)

B.  Snapshot Monitor SQL Table Function를 이용 한 정보 선택과 수집

Ø        테이블 함수를 이용하여 영구적인 table을 생성가능하며, 이럴 경우 데이터가 남아 있게 됩니다. 테이블 생성시 목표가 무엇이며, 어떤 데이터를 측정하여 수집 할 것인가에 대한 Plan을 계획한 후, insert into ~~ (select ~) 문장을 사용 하여 특정 데이터를 수집하여 테이블에 저장하면 됩니다. 이때, AT 명령어 혹은 crontab명령어를 사용하여 스케줄을 예약하여 특정 시간 또는 특정 기간 동안 정보 수집이 가능하게 할 수도 있습니다.

u       사용 예제  

3.      Snapshot을 이용 한 튜닝사례

A.  스냅샷을 이용한 unused 인덱스삭제 사례 1

Ø         알려진 문제점을 파악하여 정확한 Tuning 목표가 무엇이며, 어떤 것을 측정 할 것 인지에 대해 우선 결정합니다.

Ø         사용 하지 않는 indexes가 성능에 영향을 미칠 수 있으므로, 사용되지 않는 인덱스를 삭제 하여 성능을 높일 수 있습니다.

Ø         자세히 어떤 정보를 수집 할 것 인지를 결정하고, 수집을 위해서는 어떤 Snapshot을 이용해야 하며, 수집된 측정값을 저장할 테이블을 만들어 데이터를 넣을 프로세스를 만들어 줍니다.

                                                                    i.         해당 Snapshot Table을 생성합니다. (Create TBLSNP table)  

                                                                   ii.        특정기간 동안 특정기간 동안 ROWS_WRITTEN활동이 높은 Top 10의 Table을 찾습니다.

(ROWS_WRITTEN의 사용량이 많은 테이블을 선택합니다.
 

                                                                 iii.        Top 10의 테이블에 대해 Snapshot을 이용하여 dynamic SQL 구문을 수집하여, 성능에 미치는 영향과 상응하는지 확인합니다.

                                                                 iv.         SQL구문을 저장할 테이블을 만든 후 Top 10 의 SQL 구문을 수집합니다.        
  

 
Ø         데이터베이스 환경을 파악 할 정도의 일정기간 동안 실행하여 데이터 수집합니다.

u       Cron  job, AT process를 이용

u      환경이 파악될 수 있을 정도의 필요 데이터를 기간을 두어 수집

u      db2explain 및 손쉽게 Excel을 이용해 진단 및 불필요한 인덱스를 식별해 냄.

ü       현재 explain을 백업 받은 후 현재 explain 테이블을 삭제 한다. (explain 테이블은 누적 값을 가진다. 따라서 Explain테이블을 새로 만들어 데이터를 초기화 한다. 사용자 ID를 스키마 명으로 하는 explain 테이블 이 있어야 합니다. )

ü       db2explain기능을 통해 하나로 수행시켜 Explain table에 저장합니다.  
 

ü       월말 작업이나 분기 작업에 쓰는 쿼리들도 함께 수행하여 줍니다.

ü       access plan을 통해 사용되지 않는 인덱스를 확인합니다. 

ü       Explain 테이블의 값은 누적 값이므로 특정 값을 비교 할 때는 델타 값을 이용하여야 합니다. Excel은 ODBC를 이용하여 Database에서 데이터를 가져올 수 있습니다.

è      ODBC 등록: 제어판 –관리도구-데이터 원본(ODBC) - 시스템 DSN 선택-추가 (D) 

è      Excel 불러 오기: MS Excel 실행 – 데이터 – 기타원본 – Microsoft Query – 데이터원본 선택 

è    대부분의 조직은 운영 환경 DB Object에 대해 엄격한 변경 관리 절차를 확인합니다.

ü        Object 삭제 절차를 확인 후 해당 인덱스의 DDL을 축출 후 인덱스를 삭제 해주면 됩니다.

B.  스냅샷을 이용한 사례 2

Ø         Table 특성을 분석하여 업무 적용을 할 수 있습니다.

snapshot_table에 있는 Rows_read, Rows_Written 두 데이터 값을 수집합니다..

read 작업이 많이 일어나고 write 작업이 거의 없거나 없을 경우에는 Replicated Table 만들어 사용하면 업무를 분산할 수 있어 성능개선에 많이 도움을 줍니다.

Ø         Explain 테이블의 값은 누적 값이므로 값을 비교 할 때는 Delta 값을 이용하도록 합니다.  Excel은 ODBC를 이용하여 Database에서 데이터를 가져올 수 있습니다.

C.  위 사례를 통한 결론

Ø         1주일 정도의 업무 기간 중 Snapshot을 통해 수집된 데이터를 이용해 삭제 된 인덱스는 ad-hoc 같은 쿼리 때문에 생성되었을 수도 있고 프로세스의 변경에 의해 사용되지 않을 수도 있습니다.

그러므로, 이러한 인덱스의 삭제는 Batch 또는 USER 프로세스에는 영향을 주지 않습니다. 인덱스를 삭제함으로써 인덱스에 기록하는 양이 적어지고, 스토리지 사용이 줄어 들게 되며, Runstats 및 Backup 수행시간도 단축됩니다.  

Ø        그러나 위의 내용은 UNIQUE INDEX는 제외 대상입니다. UNIQUE INDEX는 속도를 높이기 위하여 생성하는 것이 아니라 데이터의 유니크 상태를 유지 하는데 그 목적이 있으므로, access plan에 직접적인 영향을 주지는 않는다. 필요하지 않는 인덱스를 삭제 하는 것은 resource를 반환해주고 Insert/update에서 발생하는 오버헤드를 줄이는데 상당한 효과가 있습니다.

Ø        업무 시간에 read 작업이 많이 일어나고 write 작업이 거의 없거나 없을 때, 즉 High read /row write 일 경우에는 Table Cron을 만들어 사용 할 수도 있습니다. 어떤 Table에서는 새로운 Job이 주로 읽기만 하고 refresh는 하루에 한번만 해도 된다면.  Cron을 만들어서 Cron Table에서 Job을 수행 하도록 한다면 분산처리가 될 수 있습니다.

Ø         Snapshot 정보수집을 통해 Object와 연관된 Application에 관해 알 수 있으며, DB문제가 아닌 Application 설계문제를 알려 줄 수 있고, 시스템 전반적인 것으로 관찰하기 때문에 예상치 못한 부작용을 최소화 할 수 있습니다.

4.      V9에서 제공 되는 SQL administrative view

Ø         DB2 V9 for LUW에서부터는 administrative view가 지원되어, 테이블 함수보다 더 많은 정보를 제공합니다.  각 View들의 SYSIBMADM 스키마로 구성되어 있습니다..

Ø         분류기준에 따른 Administrative View

Ø         참고사이트

http://publib.boulder.ibm.com/infocenter/db2luw/v9/topic/com.ibm.db2.udb.admin.doc/d
oc/r0006117.htm?resultof=%22%53%4e%41%50%42%50%5f%50%41%52%54%22%20