DB Tuning 기본 원칙들

원칙의 힘

튜닝은 비공식적인 상식에 기초한다. 이는 쉬운 반면에 어려운 일이다. 튜닝은 튜닝하는 사람이 복잡한 공식이나 이론을 통해 씨름할 필요가 없기 때문에 쉽다. 많은 학자들과 연구원들은 일반적으로 수학적인 기초에 근거하여 튜닝과 쿼리 처리를 해결하려고 한다. 이러한 노력들이 더 복잡해지는 것은 일반적으로 실현 불가능한 가정에 근거하고 있기 때문이다.

튜닝은 상식이 내재하고 있는 원리와 지식을 통해 어플리케이션, DB 소프트웨어, OS, 물리적인 하드웨어 등을 넓고 깊게 이해하는 것이 필요하기 때문에 어렵다. 대부분의 튜닝 책들은 실질적인 원칙들을 제공하고 있지만 이들의 한계에 대해서는 언급하지 않는다.

예를 들어, 책에서는 트랜잭션 응답 시간이 중요한 경우 집합(aggregate) 함수(avg와 같은)를 절대 사용하지 말라고 한다. 내포된 의미는 그러한 함수는 상당한 양의 데이터를 스캔해야 하기 때문에 다른 쿼리를 막을 수 있다는 것이다. 따라서 이 규칙은 일반적으로 옳지만, 평균이 인덱스로 선택된 몇몇 tuple에 적용된다면 집합 함수를 사용해도 괜찮다. 이러한 예의 요점은 튜닝하는 사람은 규칙에 대한 사유, 즉, 많은 부분을 점유하고 있는 공유된 데이터를 접근하는 긴 시간의 트랜잭션은 동시 온라인 트랜잭션을 지연시킬 수 있다라는 사유를 이해해야만 한다. 잘 알려지지 않은 튜너들은 이러한 규칙들이 무엇을 의미하는지 알게 된다. 즉, 원칙 그 자체 보다도 원칙의 예로.

5가지 기본 원칙

다음의 5가지 원칙들이 성능시 고려한다.
1. 크게 생각하고, 작게 수정한다. (Think globally; fix locally.)
2. 파티션은 병목을 없앤다. (Partitioning breaks bottlenecs.)
3. 기동 비용은 높지만, 운영 비용은 낮다. (Start-up costs are high; running costs are low.)
4. 서버에 기인한 것은 서버에 넘겨준다. (Render unto server what is due unto server.)
5. 트레이드 오프를 준비하라. (Be prepared for trade-offs.)

1. 크게 생각하고, 작게 수정한다. (Think Globally; Fix Locally)
효과적인 튜닝 작업은 문제에 대한 명확한 인식과 최소한의 중재를 필요로 한다. 이는 정확한 수량을 측정하고 올바른 결론을 이끌어내는 것을 의미한다. 이를 잘 한다는 것은 의사들이 증명하는 것 만큼이나 어려운 일이다. 다음은 공통적인 잘못을 설명하고 있는 두가지 예제이다.

● 전반적인 튜닝의 공통적인 접근방법은 프로세서 사용(utilization), I/O 량(I/O activity), 페이징 등을 결정하는 하드웨어 통계를 먼저 살펴보는 것이다. 천부적인 튜너는 이러한 측정치 중에 하나(예를 들어, 높은 디스크 활용 상태)에 높은 값에 반응하여 하드웨어를 구매함으로써(예를 들어 더 많은 디스크 구매) 수치를 낮게할 것이다. 하지만, 이것이 부적절할 수 있다는 많은 경우가 있다. 예를 들어, 자주 사용하는 어떤 쿼리가 인덱스를 사용하는 대신에 테이블을 스캔하거나 로그가 자주 접근하는 어떤 데이터와 같이 디스크를 공유하는 것 때문에 높은 디스크 사용이 발생될 수 있다. 인덱스를 만들거나 서로 다른 디스크 간의 데이터 파일을 이동함으로써 추가 하드웨어를 구매하는 것보다 더 싸고 더 효율적인 방법이 될 것이다.
● 우리가 알고 있는 한가지 실질적인 경우는 DB 관리자가 DB 버퍼의 크기를 늘리는데 실패해서 많은 불필요한 디스크 접근을 허용하기 때문에 높은 디스크 사용이 발생되었다는 것이다.
● 튜너는 종종 특정 쿼리에 의해 점유되는 시간을 측정한다. 만일 이 시간이 높다면 많은 튜너들은 이를 줄이려고 할 것이다. 하지만, 그러한 노력은 쿼리가 단지 드물게 발생되는 것이라면 헛수고가 될 것이다. 예를 들어, 두개의 요소로 실행 시간의 1%를 걸리는 쿼리를 속도 높이는 것은 기껏해야 0.5% 정도 시스템의 속도를 향상시키게 된다. 이는 만일 쿼리가 다소 중요하다면 그러한 노력은 여전히 가치가 있다는 것이다. 따라서, 하나의 쿼리에 문제를 좁히고 그 한가지를 수정하는 것이 시도하는 가장 첫번째가 되어야 한다. 하지만, 그것이 중요한 쿼리라는 것을 보장해야 한다.

문제를 해결할 때, 또한 크게 생각해야 한다. 개발자들은 자신들 짠 쿼리를 가져가서 "빠르게 실행할 수 있는 인덱스를 찾아달라" 고 요청할 수 있다. 종종 어플리케이션의 목적이 더 단순한 해결책을 제시할 수도 있기 때문에 어플리케이션의 목적을 이해함으로써 더 잘 해결할 수 있다. 이는 설계자들 옆에 앉아서 전반적인 것을 이야기하고, 문제 이면에 있는 문제를 해결하도록 노력하는 치료사와 같은 역할이다.

2. 파티션은 병목을 없앤다. (Partitioning Breaks Bottlenecks)
느려진 시스템은 모든 컴포넌트들이 포화상태이기 때문에 거의 더 느려지지 않는다. 보통, 시스템의 한 부분이 전체적인 성능을 제한한다. 그러한 부분을 병목(bottlenet)이라고 한다.

병목에 대해 생각하는 좋은 방법은 고속도로 교통체증을 예로 들 수 있다. 교통체증은 보통은 도로 위에 있는 차량의 많은 수가 좁은 도록을 통과해야 한다는 사실에 기인한다. 또 다른 가능한 이유는 한쪽 도로로부터의 차량 흐름이 다른쪽 도로의 흐름과 병합이 되는 것이다. 두가지 어떤 경우이든 병목은 차선당 많은 수의 차량을 가지는 도로 네트워크의 한 부분이다. 병목을 없애는 것은 도로를 재배치하고 다음 두가지 전략 중에 하나를 적용함으로써 해결할 수 있다.

1. 도로가 거의 없는 구간의 고속도로의 구역을 통과하는 운전자로 하여금 더 속력을 내서 운정하도록 한다.
2. 차선당 도로가 줄어드는 차선을 더 만들거나 운전자에게 러시아워 시간대에는 피하도록 한다.

첫번째 전략은 지역적인 처치(예를 들어, 인덱스를 추가하거나 기존 인덱스를 더 잘 사용하는 쿼리를 다시 작성하는 등의 결정)에 해당되면 시도할 제일 첫번째 것이어야 한다. 두번째 전략은 파티셔닝에 해당된다.

DB 시스템에서 파티셔닝은 더 많은 자원에 대한 길을 나누거나 시간에 대한 길을 분산함으로써 시스템의 특정 컴포넌트의 부하를 줄이는 기법이다. 다음은 몇가지 예이다.

● 은행은 N개의 지점을 가지고 있다. 대부분의 고객들은 자신의 집근처의 지점에서 계정 정보를 접근한다. 만일 중앙집중 시스템이 과부화가 발생되었다면 자연적인 해결책은 지점 i 에 있는 고객의 계정정보를 하위시스템 i로 옮기는 것이다. 이것이 공간(혹은 물리적인 자원)에서의 파티셔닝의 한 형태이다.
● Lock 경쟁(contention) 문제는 보통 매우 적은 자원과 관련이 있다. 종종 비어 있는 리스트 (사용되지 않는 DB 버퍼 페이지의 리스트)는 데이터 파일 이전에 경쟁으로 고통을 받는다. 해결책은 각각의 lock의 동시성 경쟁을 줄이기 위해서 그러한 자원을 쪼개서 나누는 것이다. 비어있는 리스트의 경우 이것은 몇개의 비어있는 리스트를 만들어서 각각이 비어있는 페이지의 부분을 가리키는 포인터를 포함하도록 한다는 것을 의미한다. 비어있는 페이지가 필요한 쓰레드는 lock 이 발생될 수 있으며 임의적으로 비어있는 리스트를 접근할 수 있다. 이는 논리적인 파티셔닝(locka이 가능한 자원)의 형태이다.
● 많은 짧은("온라인") 트랜잭션과 동일한 데이터를 접근하는 몇몇 긴 시간의 트랜잭션을 가지고 있는 시스템은 lock과 자원 경쟁(contention)으로 인해 성능이 나빠질 것이다. 데드락은 긴 시간 수행되는 트랜잭션을 무효화시키고 긴 트랜잭션은 짧은 트랜잭션을 막을 것이다. 게다가 긴 트랜잭션은 버퍼 풀의 대부분을 차지하면 사용할 것이고, 결국 lock 경쟁이 없어진 상태임에도 불구하고 짧은 트랜잭션의 성능이 저하시킬 것이다. 한가지 가능한 해결책은 온라인 트랜잭션 활동이 거의 없을 때에 긴 트랜잭션을 수행하고 그러한 긴 트랜잭션을 직렬화하여(부하가 된다면) 서로 간섭하지 않도록 하는 것이다. (시간적인 파티셔닝) 두번째 방법은 긴 트랜잭션에(읽기만 하는 것인 경우) 분리된 하드웨어에 별도의 데이터(공간 파티셔닝)를 적용하는 것이다.

수학적으로 파티셔닝은 한 집합을 상호 disjoint(겹치지 않게 구획화) 부분으로 나눈 것을 의미한다. 위의 세가지 예제는 공간이나 논리적인 자원, 시간에 대한 파티셔닝을 설명하고 있다. 불행히도 파티셔닝은 항상 성능을 향상시키지는 않는다. 예를 들어, 데이터를 여러개 나누는 파티셔닝은 부분간 트랜잭션에 대한 부가적인 통신 비용을 발생시킬 수도 있다.
따라서, 대부분의 튜닝처럼 파티셔닝은 주의를 요한다. 여기서의 주요 내용은 다음과 같이 단순하다. 병목을 발견했다면 먼저 해당 컴포넌트를 속도를 빠르게 하고, 이것이 안될 때에는 파티셔닝을 하라.

3. 기동 비용은 높지만, 운영 비용은 낮다. (Start-Up Costs Are High; Running Costs Are Low.)
대부분의 사람이 만든 물체들은 기동하기 위해 상당한 자원을 소비한다. 이는 자동차나 어떤 종류의 빛을 밝히는 전구, DB 시스템에 적용된다.

● 디스크에서 read 오퍼레이션의 시작은 비용이 비싸지만, 일단 read가 시작되면, 디스크는 빠른 속도로 데이터를 제공할 수 있다. 따라서 단일 디스크 트랙에서 64K의 데이터를 읽는 것은 아마도 동일한 디스크에서 512 바이트의 데이터를 읽는 것만큼 두배의 비용보다 적을 것이다. 이는 자주 스캔되는 테이블은 디스크에서 연속으로 위치해야 함을 암시한다. 또한 수직적인(vertical) 파티셔닝은 중요한 쿼리들이 수백개의 컬럼을 가진 테이블에서 얼마되지 않은 컬럼을 select할 때 좋은 전략이 될 수 임을 암시한다.
● 분산 시스템에서 네트워크를 통해 메시지를 전달하는 대기시간(latency)은 단일 메시지에서 몇 바이트를 점차적으로 전송하는 증가 비용에 비교할 만큼 매우 높다. 순수한 결과는 1K의 패킷을 전송하는 것은 1바이트의 패킷을 전송하는 것보다 그렇게 비용이 비싸지는 않는다. 이는 작은 단위보다는 큰 단위로 데이터를 전송하는 것이 좋다는 것을 의미한다.
● 파싱하고, 의미 분석(semantic anaylsys)을 수행하고, 심지어 단순한 쿼리에 대해 접근 경로(access path)를 선택하는 비용은 중요하다. (대부분의 시스템에서 10,000 instruction 이상) 이는 자주 실행되는 쿼리는 컴파일 되어야 됨을 의미한다.
● C++이나 Java, Perl, COBOL, PL/1과 같은 표준적인 프로그래밍 언어의 프로그램이 DB 시스템을 호출하고 있다고 가정하자. 어떤 시스템(예를 들어 대부분이 관계형 시스템들)에서 커넥션을 열고 호출을 수행하는 것은 심각한 비용을 초래한다. 따라서, 표준적인 프로그래밍 언어의 루프 내에서 DB를 많이 호출(각각이 SELECT를 가지고 있음)하는 것보다는 단일 SELECT 호출하고 결과에서 looping을 실행하는 프로그래밍 훨씬 더 낫다. 다른 방법으로는 캐싱 커넥션을 사용하는 것도 방법이다.

위의 네가지 예는 기동 비용의 서로 다른 의미를 설명하고 있다. read의 첫번째 바이트를 얻는 것, 메시지의 첫번째 바이트를 전송하는 것, 실행에 대한 쿼리를 준비하는 것, 호출을 경제적으로 하는 것. 하지만 모든 경우에 있어 교훈은 다음과 같이 동일하다. 가장 최소의 가능한 기동을 원하는 효과를 얻어라.

4. 서버에 기인한 것은 서버에 넘겨준다. (Render unto Server What Is Due unto Server.)
데이터 기반 시스템으로부터 최적의 성능을 얻는다는 것은 해당 시스템의 DB 관리 부분을 단순하게 튜닝하는 것 이상이 필요하다. 가장 중요한 설계 이슈는 DB 시스템(서버)과 어플리케이션 프로그램(클라이언트) 간의 작업에 대한 할당이다. 특정 작업이 재배치되어야만 하는 곳은 다음의 세가지 주요 요인에 의존한다.

1. 클라이언트와 서버의 상대적인 계산 자원. 만일 서버가 과부하라서 다른 모든 것들도 동일하다면, 업무는 클라이언트에 이관되어야 한다. 예를 들어, 어떤 어플리케이션은 클라이언트 측으로 계산 기반의 작업이 분산된다.
2. 관련 정보가 위치하는 장소. 어떤 응답(예를 들어, 화면에서 값을 입력하는 것)은 DB에 어떤 변경이 발생할 때에 발생되어야(예를 들어 어떤 DB 테이블에 값을 저장) 한다고 가정하자. 잘 설계된 시스템은 어플리케이션의 poll 보다는 DB 시스템 내의 트리거 장치를 사용해야 한다. polling 시스템은 정기적으로 테이블이 변경되었는지를 알아보기 위해서 테이블을 쿼리한다. 반면에 트리거는 실질적으로 변경이 발생했을 때에만 이벤트를 발생하는데, 이는 상당한 부하를 줄인다.
3. DB 작업이 화면과 상호작용하는 여부. 만일 그렇다면, 화면에 접근하는 부분은 트랜잭션 외부에서 수행되어야 한다. 그 이유는 화면 상호작용은 많은 시간(최소한 수초)이 걸릴 수도 있기 때문이다. 만일 트랜잭션 T가 간격(interval)을 포함한다면, T는 다른 트랜잭션이 T가 가진 데이터를 접근하는 것을 못하게 할 것이다. 따라서, 트랜잭션은 다음 세가지 단계로 분리되어야 한다.
   a. 짧은 트랜잭션은 데이터를 조회한다.
   b. 상호작용하는 세션은 트랜잭션 영역(lock이 없는)이 외부의
       클라이언트 측에서 발생된다.
   c. 두번째 짧은 트랜잭션은 상호작용하는 세션 동안 발생되는 변경을 설치한다.

5. 트레이드 오프를 준비하라. (Be Prepared for Trade-Offs.)
어플리케이션의 속도를 높이는 것은 종종 메모리나, 디스크, 혹은 계산을 지원하는 자원에 대한 복합구성을 필요로 한다.
● Random Access 메모리의 추가는 시스템의 버퍼 사이즈를 증가시킨다. 이는 디스크 접근의 수를 감소시키며 따라서 시스템의 속도를 증가시킨다. 물론, Random Access 메모리는 아직 비어있지 아니다. (물론 임의적인 것도 아니다. 메모리를 순차적으로 접근하는 것이 임의로 군데군데 접근하는 것보다는 훨씬 빠르다.)
● 인덱스 추가는 종종 중요한 쿼리의 실행 속도를 증가시키기도 하지만, random access 메모리에 더 많은 디스크 저장소와 더 많은 공간을 필요로 한다. 또한 인덱스를 사용하지 않는 insert와 update에 대해서 더 많은 프로세서 시간과 더 많은 디스크 접근을 필요로 한다.
● 온라인 update의 분리된 긴 쿼리에 대한 임시적인 파티셔닝을 사용할 때 그러한 긴 쿼리에 대해서 너무나 적인 시간이 할당된다는 것을 발견하게 된다. 그런 경우, 긴 쿼리에만 적용되는 분리된 보관용 DB를 구축하는 것을 결정할 수 있다. 이는 성능 관점에서 예견된 해결책이지만, 새로운 컴퓨터 시스템의 구매와 유지보수를 야기시킬 수 있다.

어떤 DB 벤더의 컨설턴트는 엉뚱하게 다음과 같이 말할 수 있다. "속도를 원하십니까? 얼마나 지불하시겠습니까?"