안녕하세요. DB2 및 XpertMon 사용자 여러분!
㈜ 아이티엑스퍼트그룹 DB 사업부입니다.
이번주는 XpertMon 을 이용한 DB2 UDB OLTP 튜닝 2번째로 , 저번에 이어서
나머지 configuration 파라미터 튜닝에 대해서 살펴보도록 하겠습니다.

---

XpertMon 을 이용한 DB2 UDB OLTP Tuning II

Sort
DB2 UDB에는 shared sort와 private sort 의 두가지 기본 sort 방식이 있습니다.
Shared sort는 INTRA_PARALLEL 데이터베이스 관리 프로그램 구성 매개변수가 ON 으로
설정되어 있든지, concentrator 가 활성화 되어 있을 때만 가능합니다.
(concentrator는 MAX_CONNECTIONS 값이 MAX_COORDAGENTS 값보다 크게 설정되어 있을 경우
활성화 됩니다.) sort에 여러 subagent가 사용될 경우 shared sort 가 일어납니다.
shared sort 에 사용되는 메모리는 데이터베이스 공유 메모리 세트에서 할당됩니다.

INTRA_PARALLEL이 OFF로 설정되어 있고, concentrator가 비활성화 된 경우, 모든 sort는
private sort입니다. load 와 index 생성 작업은 INTRA_PARALLEL 매개변수에 관계없이
인덱스 키 정렬에 항상 private sort를 사용합니다. private sort 에 사용되는 메모리는
에이전트의 private 메모리에서 할당됩니다. 따라서 private sort는 단일 에이전트에
의해서만 이루어 집니다.
shared sort 에 있어서 SHEAPTHRES_SHR 데이터베이스 구성 매개변수는 데이터베이스 단에서
동시에 sort에 사용될 수 있는 데이터베이스 공유 메모리의 hard-limit 입니다.
동작중인 active sort 에 의해 사용되는 공유메모리가 이 값에 도달하면, subagent sort는
SQL0955값을 떨어뜨리며 실패하게 됩니다. 만약 SHEAPTHRES_SHR 값이 0 이면, shared sort 의
임계값은 sort 메모리를 지정하는데 사용되는 다른 파라미터인 SHEAPTHRES 데이터베이스
관리 프로그램 매개변수의 값으로 설정됩니다.
shared 및 private sort 에 있어서 SORTHEAP 데이터베이스 구성 매개변수는 단일 sort 에
사용될 수 있는 4K 페이지의 최대값을 나타냅니다.
sort는 두 단계로 이루어집니다.

• sort 단계
  sort는 overflow나 non-overflow 둘 중에 하나로 수행됩니다. sort 된 데이터가 sort가
  수행될 때마다 할당되는 메모리 블록인 sort heap에 다 들어갈 수 없으면, 데이터베이스의
  Temporary Tablespace로 overflow 됩니다. overflow가 일어나지 않는 sort가 성능 면에서는
  낫습니다.
  
• sort 결과 반환 단계
  반환되는 데이터는 pipe 또는 non-piped 형태로 반환됩니다. sort 된 데이터가 최종과정을
  저장할 임시테이블 없이 바로 반환된다면 piped sort 가 됩니다. 그렇지 않고 임시 테이블을
  필요로 할 경우에는 non-piped sort 가 됩니다. piped sort 가 non-piped sort 에 비해
  항상 우수한 성능을 보입니다.
  

권장값은 다음과 같습니다.

Sort 권장값

• 일반적으로 SHEAPTHRES 값은 최소한 SORTHEAP 은 10배 이상으로 합니다.
• INTRA_PARALLEL이 ON인 상태에서 인덱스를 생성하기 위해서는, SHEAPTHRES 값을 SORTHEAP * SMP degree 이상으로 설정해 주는 것을 잊지 마십시오.
• LOAD에 있어서, index key sort는 한 프로세스(SMP일 경우 db2lrid, serial일 경우 db2lfrm0)의 private 메모리 공간에서 동시에 일어나게 됩니다. 이때 SHEAPTHRES 값을 (SORTHEAP * LOAD 테이블의 index 수) 이상으로 설정해 주는 것을 잊지 마십시오.또한 AIX에 있어서 LOAD의 index key sort 시 사용할 수 있는 private 메모리 최대값은 250MB입니다.

Sort 모니터링
다음과 같이 설정합니다.

• Sortheap 값을 16으로 설정
• 수행 SQL : select acct_id,balance from account order by acct_id

Report 기능을 이용해서 Sort 모니터링을 수행한 결과는 다음과 같습니다.

• DB Report - Sort Report 결과
  

위에서 보면 sort overflow 비율은 100%로 나타났습니다. OLTP 환경에서 Sort Overflow가
3%이상이면 SORTHEAP 값을 늘려주거나, 동시에 SHEAPTHRES 값을 증가시킬 필요가 있습니다.
늘려주는 방법은 2배 혹은 3배씩 늘리면서 더 이상 모니터링이 필요없을 때 까지 늘리면
됩니다. 모니터링을 통해 문제의 원인이 되는 SQL의 내용을 확인하거나, 적절한 index를
달아 주는 방법도 고려해야 하는 대상입니다.

• SQL Report 결과
  

LOG
사용한 로그의 양을 근거로 LOG 파라미터를 변경할 수 있습니다.

사용량을 근거로 Log 관련 파라미터를 설정할 수 있습니다. Secondary LOG 사용률이 높다면,
Primary LOG 개수를 늘려 LOG를 미리 할당하는 것이 좋습니다.

LOG Buffer
로그버퍼는 DB2 UDB 엔진이 각각의 로그 정보를 바로 디스크에 쓰는 대신 메모리에 보관하는
저장영역입니다. 로그 레코드가 디스크에 쓰이는 것은 다음중의 한 경우를 만족할 때 입니다.

• 한 트랜잭션이나 COMMIT 되거나 트랜잭션 그룹이 MINCOMMIT 구성 매개변수에 의해 COMMIT 되었을 때,
• 로그버퍼가 가득 찼을 때
• 기타 데이터베이스 관리 프로그램 내부의 이벤트에 의해서

LOG Buffer 권장값
현재 XpertMon 에서는 다루고 있지 않지만, OLTP 에서 default LOG BUFFER 는 매우 작은
값이므로, 최소한 256 이상으로 늘려주시는 것이 좋습니다.
로그버퍼의 크기는 LOGBUFSZ 데이터베이스 구성 매개변수로 지정됩니다. 로그 디스크에서
상당량의 정보를 읽어야 하는 경우나, 디스크 사용이 빈번한 경우 로그 버퍼의 크기를
늘려 주십시오. LOGBUFSZ 값을 증가시킬 때는 로그 버퍼가 데이터베이스 힙 영역을
사용하기 때문에 DBHEAP 매개변수도 함께 고려해 주시기 바랍니다.

데이터베이스 스냅샷을 이용하여 LOGBUFSZ 값이 적절한지 확인 할 수 있습니다.

% db2 get snapshot for database on
Log pages read = 0
Log pages written = 6721

Log pages read 항목은 logger에 의해 디스크에서 읽혀진 로그 페이지 수이고,
Log pages written 항목은 logger에 의해 디스크에 쓰여진 로그 페이지 수입니다.
Log pages read 와 Log pages write 의 비율은 되도록 작아야 합니다. 이상적으로는
Log pages read 가 없는 것이 좋습니다. 만약 Log pages read 값이 크다면, LOGBUFSZ 값을
증가시켜 주어야 합니다.

LOCK 메모리
Lock 메모리는 데이터베이스 공유메모리로서 어플리케이션이 데이터를 읽을 경우
기본적으로 해당 Object 에 Lock 이 걸리게 됩니다.
비트별 Lock 메모리 사용량은 다음과 같습니다.

• 32bit 플랫폼인 경우
  - 해당 Object에 아무런 Lock 도 걸려있지 않을 경우 72Byte 가 사용됨
  - 해당 Object에 이미 Lock 이 존재하는 경우 36Byte 가 사용됨
  
• 64bit 플랫폼인 경우
  - 해당 Object에 아무런 Lock 도 걸려있지 않을 경우 112Byte 가 사용됨
  - 해당 Object에 이미 Lock 이 존재하는 경우 56Byte 가 사용됨
  

LOCK 모니터링

Lock 과 관련된 파라미터는 DB CFG 의 LOCKLIST 입니다. 최대 lock memory 사용량을
가지고 현재 locklist 값이 적절한지 판별할 수 있습니다. Performer 프로그램을 수행하였을
경우 최대 3KB 의 메모리를 사용하였고, 이것은 설정된 값의 0.75%에 해당하므로
locklist 값을 증가할 필요는 없습니다.
또 Lock 과 관련된 파라미터 값이 존재하는데 그것은 LockWait Time 을 설정하는 것입니다.
DB CFG 의 LOCKTIMEOUT 값을 초단위로 설정하면 해당 초만큼 lock 을 기다리다 lock을
획득할 수 없는 경우 timeout 으로 application이 종료됩니다. 위의 평균 locktimeout 값을
근거로 LOCKTIMEOUT 파라미터 값을 설정하십시오. OLTP 의 경우에는 30~60가 적절합니다.

---

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