안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번에는 3회에 걸쳐서 XpertMon 을 이용한 DB2 UDB OLTP 튜닝에 대해서 살펴보도록 하겠습니다.
크게 두가지로 나누어서 다룰 예정인데, 첫째는 DB2 UDB configuration 파라미터를 튜닝하는
방법이 될 것이고, 둘째는 문제 쿼리를 찾아내어 쿼리를 튜닝하는 방법이 될 것입니다.
이번에는 파라미터 튜닝 그 중에서도 버퍼풀에 관련된 튜닝에 대해 살펴보도록 하겠습니다.
XpertMon 을 이용한 DB2 UDB OLTP Tuning I
튜닝 환경 설정
OLTP 는 다양하게 복잡하고, 대부분 작은 트랜잭션들이 많이 수행되는 환경입니다.
주로 몇초 혹은 1초내에 끝나는 insert,update,delete,select 문장으로 이루어지게 됩니다.
여기서는 DB를 하나 작성하고, TYPE 2 JDBC Driver 를 이용하는 Performer라는 프로그램을
수행할 것입니다.
- 데이터베이스명 : BANK
- 테이블명 : account , auditlog
- 프로그램명 : Performer ( Java 프로그램)
- 사용 쿼리
- SELECT NAME, BALANCE FROM ACCOUNT WHERE ACCT_ID =
- UPDATE ACCOUNT SET BALANCE = ? WHERE ACCT_ID = ?
- INSERT INTO AUDITLOG VALUES (?, ?, ?, ?, ?)
- 테스트 시나리오 :
- db2stop/db2start/db2 connect 를 통해서 환경을 clear 합니다.
- performer 라는 프로그램을 100번 수행합니다. 프로그램은 실행되면 랜덤 파라미터를 가지고 동일한 트랜잭션을 반복수행합니다.
- 20번 수행된 시간을 Report 기능을 이용해서 체크하고, 향후 튜닝후 다시 체크하여 비교합니다.
모니터링 스위치
시스템을 튜닝하기 위해서는 모니터링 작업이 수행되어야 합니다.
모니터링 데이터는 기본적으로 수집되는 정보외에도 모니터 스위치를 ON 으로 설정해서
보다 상세한 정보를 수집할 수도 있습니다. 모니터링에는 오버헤드가 수반되므로
필요시에만 모니터 스위치를 ON으로 설정하는 것이 좋습니다.
XpertMon에서는 필요한 경우 자동적으로 해당 스위치를 ON 하여 자세한 정보를 수집하여
보여줍니다.
모니터 스위치는 인스턴스 단위나 application 단위로 on/off 설정이 가능합니다.
설정은 UPDATE DBM CFG 명령(인스턴스 단위)이나 UPDATE MONITOR SWITCHES 명령(application 단위)
으로 가능합니다.
DB2 명령창과 같은 application 단위로 스위치가 on으로 설정되면 해당 세션에서만 모니터링이
가능합니다. 예를들어 application 단위로 BUFFERPOOL, SORT, STATEMENT에 대한 모니터링을
application 단위에서 on으로 설정하려면 다음의 명령을 수행하면 됩니다.
$ db2 update monitor switches using BUFFERPOOL ON SORT ON STATEMENT ON
- CFG Component 를 이용하여 모니터 스위치를 ON 으로 설정
모니터 정보를 수집하는 데 사용되는 메모리인 모니터 힙을 증가시킬 필요가 있을 경우가
있습니다. 그런 경우 다음의 명령을 수행하십시오.
- CFG Component 를 이용하여 모니터 힙을 증가
현재 모니터 스위치의 설정 상태를 보기 위해서는 다음의 명령을 수행하십시오.
$ db2 get monitor switches
DB 파라미터 튜닝
Report 기능을 이용하여 파라미터를 튜닝하도록 하겠습니다.
튜닝할 파라미터는 다음과 같습니다.
- Buffer pools
- Asynchronous page cleaner and I / O servers
- Sorts
- Log buffer size
- Grouping COMMIT statements
버퍼풀
버퍼풀은 다 아시는대로 데이터베이스에서 read, write, update, delete 등의 트랜잭션
활동에서 데이터 조작(LOB이나 long 타입 제외)이 일어나는 메모리내의 영역입니다.
버퍼풀 경합은 데이터베이스 성능에 있어서 중요한 요소로 작용합니다. 만약 버퍼풀이
필요한 데이터를 메모리에 저장할 만큼 크다면 디스크 활동이 줄어들게 됩니다.
반대로 버퍼풀이 작다면 성능은 급격히 저하되고, DBM은 application이 요구하는 데이터를
디스크 활동을 통해 전해주기 위해 I/O 활동에 많은 시간을 보내게 됩니다.
현재 설정되어 있는 버퍼풀의 크기를 살펴보도록 하겠습니다.
- 현재 설정되어 있는 버퍼풀 확인
위의 XpertMon 화면에서 나타난대로 4096 * 1000page = 4MB 버퍼풀 한 개가 존재합니다.
버퍼풀로 모니터링해야 하는 내용은 버퍼풀 Hitratio 입니다. 100에 가까울수록 요구하는
데이터가 버퍼풀에 많이 존재한 경우이며, 이 경우 성능이 좋아지게 됩니다.
그럼 performer를 현재 상황에서 20번 수행한 후 Report 기능을 이용하여 버퍼풀 정보를
살펴보도록 하겠습니다.
- Report 기능을 이용하여 BUFFERPOOL REPORT 수집 시작
- 버퍼풀 Report 결과 화면 중 Activity 화면
현재 1000 페이지 버퍼풀로 65.67%라는 저조한 적중률이 나타난 것을 볼 수 있습니다.
SQL Report 를 통해서 20개 수행시간을 살펴보도록 하겠습니다.
- SQL Report 결과 화면 중 Application Summary 화면
총 20개의 Connect 이 수행되었고, 총 CPU 시간은 10.02초, 수행시간은 173.35초입니다.
그럼 SQL component를 이용하여 버퍼풀 크기를 온라인으로 증가시켜 보도록 하겠습니다.
- 버퍼풀 크기 증가 후 크기 확인
버퍼풀에 존재하는 데이터를 지우기 위해 connection 을 모두 force 한후 performer 를
20번 수행한 후 Report Component를 수행해보도록 하겠습니다.
- 버퍼풀 크기 증가 후 버퍼풀 Report 결과
65,67에서 97.49로 hit-ratio가 늘어났음을 볼 수 있습니다. 지금의 적중률은 매우 우수합니다.
일반적으로 버퍼풀 적중률은 80% 이상이면 적당한 것으로 봅니다. 만약 버퍼풀 적중률이
낮다면 버퍼풀 크기를 증가시켜 성능 향상을 기대할 수 있습니다.
SQL Report 를 수행하여 CPU 시간 및 수행시간의 변화도 살펴보도록 하겠습니다.
- 버퍼풀 크기 증가 후 SQL Report 결과
CPU 시간은 10.02 -> 8.81 로, 수행시간은 173.35에서 108.33 으로 줄어들었음을 볼 수 있습니다.
다른 메모리 버퍼와 비교해 볼 때, 버퍼풀은 데이터베이스의 성능에 가장 큰 영향을 줍니다.
하지만 버퍼풀은 데이터베이스 공유 메모리 세트의 일부라는 사실을 명심하시기 바랍니다.
32비트 환경에서 DB2 UDB의 데이터베이스 공유메모리의 한계는 AIX에서 1.75GB,
Sun Solaris 에서 3.35GB, HP-UX에서 0.75GB~1GB, 리눅스에서 1.75GB이며 Windows 에서는
평상시 2GB이고 boot.ini 에서 스위치를 enable 시키면 3GB 입니다. 따라서 버퍼풀과
다른 데이터베이스 메모리 풀과의 균형을 잘 맞춰 주시기 바랍니다.
Asynchronous I/O server and page cleaner
최적의 성능을 발휘하기 위해 버퍼풀과 디스크 사이에 읽고 쓰는 작업을 비동기적으로
수행하는 것이 좋습니다.
I/O 서버는 application에 의해 사용될 것으로 기대되는 데이터를 디스크에서 버퍼풀로
비동기적으로 옮겨둡니다 - 이를 prefetch라고 합니다.
프리페치는 에이전트가 데이터를 버퍼풀에서 찾도록 함으로써, application이 데이터가
디스크에서 메모리로 올라오도록 기다리는 시간을 없애 성능을 향상시킵니다.
반면, page cleaner는 데이터베이스 에이전트가 버퍼풀 페이지를 요구하기 전에 변경된
버퍼풀 페이지를 디스크에 기록합니다. 결과적으로 데이터베이스 에이전트는 버퍼풀의
빈 페이지가 생길 때까지 대기하지 않아도 되기 때문에 성능이 좋아집니다.
page cleaner는 changed pages threshold 에 도달하는 등 여러 가지 조건에 의해 동작합니다.
권장값은 다음과 같습니다.
- NUM_IOSERVERS = 시스템의 물리 장치 개수 + 1~2 개
- NUM_IOCLEANERS = CPU 개수
page cleaner 수를 너무 많이 지정하면 데이터베이스 서버의 큐를 압도하여 심각한 성능저하가
올 수 있음을 유의하십시오.
우선 Report 의 Bufferpool Report 기능을 통해서 Prefetch가 제대로 수행되는지
확인해보도록 하겠습니다.
- 버퍼풀 Report - Access Pattern Report 결과
Read 비율을 보면 Asynch 가 97.43%로 Prefetch가 잘 일어났음을 볼 수 있습니다.
또한 Asynch Request 마다 읽은 페이지의 수를 보아도 31.26으로 prefetch 값으로 설정한
32page 와 비슷한 것으로 보아 prefetch , 그중에서도 sequence prefetch가 적절히
일어났음을 볼 수 있습니다.
따라서 NUM_IOSERVERS 값이 적절하다고 볼 수 있습니다.
만약, Asynch 읽기 비율이 낮은 경우에는 다음과 같은 원인에 의할 수 있습니다.
- 한 건씩 읽고 쓰는 작업이 수행되면 Prefetch에 의한 이득이 없는 경우
- 데이터베이스에 Prefetch가 너무 적게 설정되어 있는 경우
- 테이블 스페이스에 컨테이너가 하나씩 밖에 없어 Prefetch가 일어날 수 없는 경우
이제는 page cleaner 가 효율적으로 사용되었는지를 확인하기 위해서 Report 기능 중에
DB Report 기능을 사용하여 확인하도록 하겠습니다.
- DB Report - Page Cleaner 관련 Report 결과
위에서 보면 Page Steal(%) 비율이 0%인 것을 볼 수 있습니다. Page Steal(%) 가 높으면
NUM_IOCLEANER 를 증가하거나, 버퍼풀 크기를 증가해야 합니다.
특히, V8.1 에서부터는 Page Clean 에 새로운 알고리즘이 추가되었습니다. 보다 빠르고
합리적인 알고리즘입니다. 이 알고리즘을 사용하면 Page Steal(%) 값이 의미없게 됩니다.
즉 모니터링할 필요가 없어지게 됩니다.
V8.1 이상인 경우에는 위의 Report Recommend 에서 볼 수 있듯이, 레지스트리에 새로운
page clean 알고리즘을 사용할 수 있도록 설정하십시오.
db2set DB2_USE_ALTERNATE_PAGE_CLEANING = ON
여기서는 더 이상의 NUM_IOCLEANERS 값에 대한 튜닝이 필요하지 않습니다.
추가적으로 OLTP 환경에서 CHNGPGS_THRESH의 기본값인 60%는 일반적으로 너무 높습니다.
20% ~ 40% 사이의 값이 적당합니다. 예를 들어, 2GB의 버퍼풀이 있고 변경된 페이지가
60%에 도달하면 1.2GB (2GB의 60%)의 데이터가 page cleaner에 의해 디스크에 쓰여져야 합니다.
이 경우 전체적인 시스템의 성능저하가 발생하게 됩니다. CHNGPGS_THRESH값을 낮게 설정하여
page cleaner가 더 자주 동작하게 - 대신 훨씬 적은 양의 데이터가 디스크에 기록되도록 -
하여 시스템의 성능저하를 줄일 수 있습니다.
XpertMon for DB2 UDB V2
DB 사업부
Tel : 02-2108-1458
Fax : 02-2108-1459
Mobile : 011-896-6545
E-mail :
hjlee@iteg.co.kr
URL :
http://iteg.co.kr
