BLOTER.NET

Host DB2 애플리케이션 튜닝 가이드, Part 2

애플리케이션 성능에 영향을 주는 요인들

오은숙 컨설턴트 | ohesvan@empal.com
한국IBM DB2 Specialist로 DB2 데이터베이스 설계 및 애플리케이션 개발, 튜닝 업무를 진행했으며, 현재는 프리랜서로 DB2 설계 및 튜닝 관련 프로젝트를 수행하고 있다.

<연재 순서>
1. DB2 응답시간 분석에 대한 이해
2. 튜닝 flow에 대한 이해와 성능에 영향을 주는 요인

DB를 사용하는 곳이라면 어디나 그렇겠지만, 운영중인 DB 시스템에 대해 지속적으로 성능을 모니터링하고 튜닝 하는 것은 조금도 게을리해서는 안 될 업무다. 지난 회에 DB2 응답 시간 분석의 이해에 대해 알아본 데 이어, 이번에는 튜닝의 기본적인 흐름을 이해하고, 성능에 영향을 주는 요인에는 어떠한 것이 있는지 살펴보자.

튜닝 Flow에 대한 이해

모니터링과 튜닝은 크게 네 가지 측면에서 이야기할 수 있다. Access Path의 분석 및 튜닝, 비 효율적인 응답시간을 가진 업무에 대한 튜닝, DB 설계의 조정, DB2 시스템의 튜닝이 그것이다.
‘Access Path의 분석 및 튜닝’은 개발자들이 코딩된 SQL에 대한 성능 검증을 위해 수행해야 할 사항으로 잘 알려져 있다.
‘비효율적인 응답시간을 가진 업무에 대한 튜닝’은 일반적으로 대부분의 회사에서 주기적으로 전 업무를 대상으로, 혹은 특정 업무의 성능에 대한 점검이 필요하다고 생각했을 때 수행한다. 이 과정에서 우리는 응답시간의 어느 부분이 병목(bottleneck)인지, 이러한 문제의 원인이 어디에 있는지를 분석하게 된다.
그 분석 결과 데이터베이스 설계에 대한 변경이 필요하다고 판단될 때, 일반적으로 ‘DB 설계의 조정’을 하게 된다. 또는 DB2 시스템의 파라미터를 조정하는 ‘DB2 시스템의 튜닝’을 하게 된다.

애플리케이션 성능 개선을 위한 분석 요인

‘비효율적인 응답시간을 가진 업무에 대한 튜닝’을 수행함에 있어서 응답시간 성능과 관련해 분석의 대상이 되는 주요 요인과 그에 대한 몇 가지 고려 사항은 <그림2>를 참고한다.

<그림 2: DB2 애플리케이션 응답시간에 영향을 미치는 주요 요인>

CPU 시간에 영향을 미치는 요인
일반적으로 경과 시간(Elapsed Time)의 50% 이상이 CPU 시간으로 소요되는 프로그램에 대해서는 우선 SQL에 대한 Access Path의 분석이 필요하다 효율적인 Access Path를 가지고 있다면, 다음과 같은 요인이 CPU 시간의 증가 요인이 된다는 것을 상기할 필요가 있다.
- 프로그램 내 SQL 콜 수
- 처리되는 로(Rows) 수
- SELECT 하는 컬럼 개수
- 소트(Sort)의 수행
- 실행되는 GETPAGE 수
- 수행되는 잠금(Lock)의 개수
- Function의 수행

다수의 SQL Call을 수행하는 프로그램
오랫동안 DB2를 사용해 온 고객들 중에 간혹 아직도 파일 시스템을 사용하는 것처럼 SQL 코딩을 하는 경우를 볼 때가 많다.
최근에 튜닝을 수행한 프로그램 중에는 ‘GROUP BY’를 이용하면 하나의 SQL로 수행할 수 있는 것을 몇 만의 FETCH를 수행하는 경우를 본 적이 있다. 담당자에게 이유를 물어보니 선배의 프로그램을 받았는데 DB2는 그렇게 SQL을 수행해야만 하는 줄 알았다고 한다. (담당자는 오랫동안 오라클 DBMS를 사용했던 개발자다)
이 경우에 더욱 놀라운 것은 이러한 프로그램이 튜닝 없이, 오랫동안 사용되어 왔다는 것이다. 이것이 가능했던 이유는 이 프로그램의 경우 해당 SQL은 인덱스를 사용하여 한 건의 row를 가져오는 조건으로 운영돼, Access Path에는 이상이 없는 것으로 모니터링 되었기 때문에 한번도 튜닝의 대상이 되지 않았었던 것이다. 이제껏 대부분의 튜닝이 SQL의 Access Path에 대해 수행되다 보니, 이런 공백이 생긴 것이다. 특히 튜닝 대상 검토시 SQL Call 하나에 사용되는 CPU 시간을 고려하는데, 최근에 프로그램이 점차 모듈화되면서, 업무의 경로 길이(path length)가 매우 길어진 것도 SQL Call 수가 증가하게 된 하나의 요인이 되겠다.  

일반적인 SQL Call 수를 줄이기 위해 고려해야 할 사항은 다음과 같다.
- Correlated SUBSELECT는 되도록 Join으로 바꾸도록 한다
- Singleton SELECT가 가능한 SQL에 대해서는 DECLARE CURSOR 하지 않도록 한다
- UPDATE/ INSERT/DELETE를 위한 불필요한 SELECT는 하지 않도록 한다

대량 GETPAGE를 수행하는 SQL
‘GETPAGE’란 논리적인 I/O를 의미하며 결국은 물리적인 I/O의 원인이 된다. 또한 버퍼 풀 내에 있는 데이터(혹은 인덱스) 페이지의 사용으로 물리적인 I/O를 수행하지 않더라도 결과적으로 높은 CPU 비용(High CPU Cost)을 발생시킨다.
대량 GETPAGE를 수행하는 SQL은 대부분 다음의 원인으로 인해 Access Path가 비효율적인 경우에 발생한다. 따라서 다음의 요인에 대한 튜닝을 실행해야 한다.
- Tablespace Scan
- Non-Matching Index Scan
- Multi-Index Scan
- List Prefetch
- Sort
그 외의 업무적 요건에 의한 필요성일지라도, 성능에 영향을 준다는 것을 고려해야 한다.

대량 잠금 요청의 수행
많은 고객들이 초기 버전의 DB2가 S-LOCK을 거는 것이 성능에 영향을 준다는 것에 대해 불평을 하는 경우가 있다. (사실 S-Lock은 데이터의 보호를 위해 필요한 것인데도 불구하고 말이다.)
그런데 이후 버전에서 필요에 따라 S-Lock을 걸지 않을 수 있는데도 많은 개발자들이 “Lock Avoidance” 메커니즘이나 ‘Uncommitted Lock”을 사용하는 것에 대해 많은 걱정을 한다.(반드시 필요한 경우가 아니면 S-Lock을 걸지 않고 데이터를 ‘읽기’ 함으로써 성능을 좋게 할 수 있는데도 말이다)
앞의 CPU에 영향을 주는 요인에서도 이야기했듯이 잠금 요청은 DB2 CPU 사용을 높일 뿐만 아니라 메모리의 사용도 높인다. 따라서 자주 업데이트 되지 않고, 반드시 현 상태의 데이터를 갱신(Insert/ Update/ Delete)하지 않아도 되는 업무에 대해서는 “Lock Avoidance”나 ‘Uncommitted Lock”을 사용하여 잠금 요청 수를 최소화 하도록 한다.
잠금 관련해서는 향후 ‘잠금 고려사항’을 주제로 한 글에서 자세히 설명하도록 하겠다

애플리케이션 성능에 영향을 주는 요인

간혹 데이터베이스의 설계는 업무 요건을 반영한다는 중요한 사실을 잊는 경우가 많다. 즉, 많은 회사들이 업무 요건에 대한 지식을 가지고 프로그래밍 하는 개발자들이 데이터베이스의 파라미터 설계에는 전혀 관여를 하지 않는데, 이러한 상황은 결국 효율적인 데이터베이스 설계가 이뤄지지 않게 된다고 할 수 있다.
예를 들면 데이터는 순차적으로 입력이 되는데 ‘Freespace(Freespace는 향후에 일어나는 데이터의 입력을 위해 한 페이지에 남겨두는 빈 공간을 의미한다)’를 랜덤하게 설계한다면 공간의 낭비일 뿐 아니라 데이터 갱신 시 불필요한 I/O를 일으키는 원인이 될 수 있다.
또한 ‘개시일 + 부점코드 + 계정과목 + 계좌번호’의 컬럼으로 논리 ERD에서 정의된 Primary Key에 대해 생각해 보자. 만약 ‘계좌번호’ 자체만으로도 Uniqueness가 보장되고 테이블 사이의 Referential Integrity를 데이터베이스 측면으로 정의하지 않는다면, 이 경우 ‘계좌번호’만을 가지고 Unique 인덱스를 만드는 것이 훨씬 효율적이다. Referential Integrity가 필요하지 않은 Primary Key에 대한 Unique 인덱스는 불필요하게 인덱스 키를 길게 가져감으로써 비효율적인 인덱스일 뿐 만 아니라 물리적으로 인덱스의 크기를 크게 함으로서 불필요한 I/O를 일으키는 요인이 된다.

< 그림 3: 애플리케이션 성능과 관련된 여러 요인>

<그림 3>은 성능과 관련된 여러 요인을 정리한 것이다. 각 요인에 대한 자세한 설명은 향후 주제에 따라 서술하도록 하겠다. 다음 회에서는 SQL의 Access Path와 관련된 요인에 대해 서술하도록 하겠다.