아직도 수많은 기업에서 성능 최적화를 수행하면 서도 주어진 SQL만을 그대로 최적화하려고 하는 경우가 많다. 물론 주어진 SQL의 튜닝을 통해 성능을 최적화할 수 있는 것은 분명하다. 하지만, 우리가 생각을 전환하여 기존이 방식과 다른 방식으로 수행하여 엄청난 성능 향상을 기대할 수 있는 경우도 많다. 대용량 데이터베이스로 변하고 있는 시점에서 성능 최적화를 위해 사용자 생각의 전환은 반드시 필요한 요소다.
권순용 | kwontra@hanmail.net
우리 주위에는 프로젝트의 성능을 향상시키기 위해 SQL 최적화에 전념하는 사이트들이 많이 있을 것이다. SQL 최적화를 통해 크게 성능 향상을 기대할 수 있는 것은 사실이다. 예전에는 성능 저하가 발생하는 경우 SQL 튜닝 보다도 해당 시스템의 CPU 또는 디스크 등의 자원을 증설하는 부분에 초점을 맞추었었다. 이와 달리 SQL을 튜닝하여 성능을 최적화하고자 하는 것은 매우 고무적인 현상임에는 틀림 없다.
그 만큼 관리자들의 생각이 IT 선진화로 가는 것은 아닐까? 하지만 아직도 SQL 튜닝을 고정 관념에 맞춰 그리고 그 자체로 튜닝하고자 하는 경우가 많은 것 같다. 우리가 조금만 다르게 생각한다면 SQL 튜닝의 효과를 배가 시킬 수 있다는 것을 아는가? 주어진 SQL을 그대로 보지않고 다르게 보는 순간 우리에게는 새로운 세상이 펼쳐질 것이다.
데이터 삭제를 DELETE로 수행하지 않고 데이터의 갱신을 UPDATE로 수행하지 않는다면 우리에게는 새로운 세상이 펼쳐질 것이다. 이제부터 이와 같은 현상에 대해 하나하나 자세히 확인해 보자.
DML은 왜 성능을 저하시키는가?
우리가 데이터를 저장하기 위해서는 INSERT를 수행하게 되고 데이터를 제거하기 위해서는 DELETE를 수행하게 된다. 또한, 기존의 데이터를 변경하기 위해서는 UPDATE를 수행하게 된다. 이와 같은 사실은 개발을 한번이라도 한 사람이라면 아니 SQL에 관련된 책을 한번이라도 본 사람이라면 누구나 알 수 있을 것이다.
DML 작업을 수행한다면 그리고 DML 작업을 수행해야 하는 데이터가 매우 많다면 많은 시간이 소요될 거라고 누구나 생각할 것이다. 그렇다면 이와 같이 대용량의 데이터에 대해 DML 작업을 수행하는 경우 어떤 이유에서 많은 시간이 소요되는 것일까? 이에 대해서는 많은 사람들이 정확한 개념을 갖고 있지 못하는 것 같다. ‘나를 알고 적을 안다면 100전 100승이 되듯이’ DML 작업의 성능을 최적화하기 위해서는 DML 작업이 왜 성능을 저하시키는지를 알아야 할 것이다. DML 작업이 왜 성능을 저하시키는지 정확히 이해하지 못한다면 우리는 어떤 방법을 사용해도 성능을 향상시킬 수 없을 것이다.
그럼 첫 번째로 INSERT의 성능 저하를 확인해 보자. INSERT는 데이터를 저장하는 SQL 중 하나로 아래와 같은 이유에서 많은 데이터의 저장 시 성능을 저하시키게 된다.
·로그 기록
·HWM BUMP UP
·인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O
INSERT 작업은 위와 같이 4가지 현상에 의해 성능이 저하된다. 이는 어떤 데이터베이스를 이용해도 동일하게 발생하는 현상이다. 데이터베이스는 작업의 수행도 중요하지만 작업이 실패하거나 또는 다른 장애에 의해 시스템이 재기동 되는 등의 데이터베이스 장애에 대해 데이터를 보호해야 하는 중요한 책임을 가지고 있다.
이와 같은 이유에서 실제 데이터베이스에서 INSERT 작업을 수행하기 전에 어떤 작업을 수행하는지에 대한 로그를 기록해야 한다. 이와 같은 기법을 선 로그(LOG AHEAD) 기법이라고 한다. 실제 INSERT를 수행하기 전에 로그를 기록하기 때문에 우리는 언제든지 INSERT 작업 중 데이터베이스에 문제가 발생해도 복구가 가능하게 되는 것이다. 실제 INSERT 작업과 관계없는 로그를 기록해야 하기 때문에 INSERT의 성능은 저하된다.
그렇다면 HWM BUMP UP에 의한 성능 저하는 무엇을 의미하는 것인가? HWM BUMP UP은 오라클 데이터베이스의 내부적인 요소이다. 실제 INSERT를 수행하게 되면 해당 테이블에 할당되어 있는 공간에 데이터를 저장하게 되며 해당 공간을 익스텐트라고 부르게 된다. 익스텐트에는 HWM가 설정되어 있어 데이터는 HWM 앞의 블록에만 저장된다. 이 뜻은 무엇을 의미하는 것인가?
HWM 앞까지 데이터를 저장한 후에는 HWM가 뒤로 후진해야만 데이터를 INSERT할 수 있다는 의미가 된다. 이를 HWM BUMP UP이라 하며 많은 데이터를 INSERT하게 되면 HWM BUMP UP은 많은 횟수가 발생하게 될 것이다. 하지만 HWM BUMP UP은 고비용의 내부적인 작업이다. 따라서 대용량의 데이터를 저장한다면 HWM BUMP UP의 횟수 증가로 INSERT의 성능은 저하된다.
INSERT의 속도와 인덱스의 개수는 INSERT의 성능 향상을 위해 매우 중요한 요소이다. 데이터를 테이블에 저장하는 것은 여유 공간을 가지고 있는 데이터 블록에 해당 데이터를 저장하면 된다. 하지만 인덱스에는 정해진 위치가 존재하게 되므로 정해진 위치를 찾는 프로세스가 수행된다. 따라서 해당 테이블에 인덱스가 10개라면 이와 같이 저장되는 데이터에 대해 인덱스에서의 위치를 찾기 위해 정해진 위치를 찾는 프로세스가 10번 수행되어야 할 것이다. 이와 같기 때문에 인덱스의 개수가 많다면 INSERT의 성능이 저하되는 것은 당연한 사실일 것이다.
어떤 사이트에서 어떤 테이블에 10개의 인덱스가 존재했으며 이를 최적화하여 5개의 인덱스로 변경한 적이 있다. 단지 10개의 인덱스를 5개로 감소시키는 순간 SQL의 변경 없이 INSERT 작업은 4배정도의 성능이 향상되었다. 이는 4배의 성능 향상이 중요한 것이 아니라 인덱스가 INSERT 작업에 많은 부하를 발생시킨다는 중요한 사실을 우리에게 전해주는 것일 것이다.
롤백을 위한 로그 기록은 해당 작업을 수행한 후 작업을 취소하는 경우 이전 데이터로 복구하기 위해 이전 데이터의 값을 저장하는 것을 의미한다. 이와 같은 작업 또한 실제 데이터를 저장하는 작업과는 별개로 수행되므로 INSERT의 성능 저하를 발생시키게 된다.
HWM BUMP UP을 제외한 로그 기록, 인덱스 및 롤백을 위한 로그 기록은 모두 디스크 I/O를 발생시킨다. 또한, 실제 데이터를 저장하는 작업에서도 디스크 I/O가 발생하게 된다. 이와 같이 모든 단계에서 디스크 I/O가 발생하기 때문에 INSERT의 성능은 저하될 것이다.
두 번째로 UPDATE의 성능 저하를 확인해 보자. UPDATE는 이미 저장되어 있는 데이터에 대해 변경 작업을 수행하는 것이다. 이와 같은 UPDATE는 아래와 같은 요소에 의해 성능 저하가 발생하게 된다.
·로그 기록
·UPDATE 컬럼이 사용된 인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O
UPDATE의 경우에는 HWM BUMP UP은 발생하지 않게 된다. 또한, 인덱스의 개수도 해당 테이블에 존재하는 모든 인덱스는 아니며 UPDATE가 수행되는 컬럼이 사용된 인덱스의 개수를 의미하게 된다. UPDATE가 수행되면 해당 컬럼을 인덱스의 컬럼으로 구성하고 있는 인덱스만을 갱신하게 된다. 그렇기 때문에 UPDATE 컬럼이 사용된 인덱스의 개수에 의해 UPDATE 성능은 저하된다.
INSERT에 비해 UPDATE는 성능을 저하시키는 항목이 더 적다. 하지만, 동일한 양에 대해 INSERT와 UPDATE를 수행한다면 UPDATE가 성능 저하를 더 많이 발생시키게 된다. 이와 같은 이유는 왜일까? 분명히 성능 저하의 요소는 INSERT가 더 많기 때문에 INSERT가 더 많은 부하를 발생시킨다고 생각하기 쉽다. 이는 로그 기록과 롤백을 위한 로그 기록의 방식 차이 때문이다. INSERT 작업은 로그에 이전 데이터라는 것은 존재하지 않는다.
INSERT 작업이 수행된 데이터의 위치 정보만을 가지게 된다면 우리는 언제든지 롤백을 수행할 수 있으며 장애 시 복구도 어렵지 않게 된다. 하지만, UPDATE의 경우에는 이전 데이터의 값이 존재하기 때문에 이전 데이터를 로그에 기록하게 된다. 따라서 로그 기록 및 롤백을 위한 로그 기록에서 UPDATE가 INSERT에 비해 더 많은 데이터를 기록해야 하므로 디스크 I/O의 증가로 UPDATE의 성능은 INSERT의 성능보다 더욱 저하되게 된다.
세 번째로 DELETE 작업은 어떠한가? DELETE는 저장되어 있는 데이터를 삭제하는 기능을 수행하게 된다. 위와 같은 DELETE는 아래와 같은 항목들에 의해 성능이 저하된다.
·로그 기록
·인덱스의 개수
·롤백을 위한 로그 기록
·디스크 I/O
다른 항목은 INSERT 또는 UPDATE와 동일하다. 차이라면 로그 기록 및 롤백을 위한 로그 기록시 삭제되는 데이터의 이전 데이터와 이후 데이터를 모두 기록해야 한다는 것이다. 물론, 롤백을 위한 로그 기록 시에는 이전 데이터의 값과 위치 정보만을 가지게 된다. 결국, 이와 같은 이유로 동일한 양의 데이터를 DELETE하는 경우 INSERT에 비해 더 많은 디스크 I/O가 발생하게 되므로 성능은 저하되게 된다.
다양한 성능 저하의 요소를 가지는 DML 작업에서 대용량의 데이터에 대해서 어떤 방식으로 작업을 수행해야만 성능을 보장 받을 수 있겠는가?
INSERT의 성능 저하 요소를 감소시키자
일반적으로 DELETE 또는 UPDATE의 경우에는 INDEX의 개수만을 최적화하여 성능을 향상시킬 수 있다. 하지만, INSERT의 경우에는 위와 같은 성능 저하의 요소 중 아래와 같은 요소에 대해 획기적으로 감소시킬 수 있게 된다.
·로그 기록
·HWM BUMP UP
·롤백을 위한 로그 기록
위와 성능 저하 요소는 우리가 조금만 깊이 있게 고려한다면 최소화 시킬 수 있으며 위의 항목을 최소화 시킨다면 디스크 I/O는 자동으로 감소하게 된다. 그렇다면 어떻게 위의 성능 저하의 요소를 감소시킬 수 있겠는가?
첫 번째로 HWM BUMP UP과 롤백을 위한 로그 기록을 감소시키는 방법을 확인해보자. HWM BUMP UP을 제거하기 위해서는 HWM를 이동시키지 않고 데이터를 HWM 뒤에 존재하는 블록에 저장하면 될 것이다. 이와 같다면 롤백 또한 이전 데이터의 정보를 하나 하나 기록하는 것이 아니라 HWM의 위치 정보 하나만을 기록한다면 롤백 수행 시 HWM 위치 뒤에 존재하는 모든 블록을 제거한다면 롤백을 수행한 것과 동일한 현상이 발생할 것이다.
결국, HWM를 고정시키고 데이터를 HWM 뒤에 존재하는 블록에 저장시킨다면 HWM BUMP UP과 롤백을 위한 로그 기록에 의해 발생하는 성능 저하는 해결될 수 있을 것이다. 이와 같이 INSERT를 수행하는 방법이 바로 직접 로딩(DIRECT LOADING) 방식이다. 직접 로딩 방식을 사용한다면 HWM 뒤의 블록에 데이터를 저장하게 되므로 두 가지 문제는 모두 해결될 수 있을 것이다. 그렇다면 직접 로딩은 어떻게 사용하는 것인가?
·INSERT /*+ APPEND */ …… SELECT ……
위와 같이 SELECT를 수행하여 해당 데이터를 테이블에 INSERT하는 경우에 APPEND 힌트를 사용하여 직접 로딩을 수행하게 된다.
두 번째로 로그 기록을 확인해 보자. 우리가 테이블에 데이터를 저장하는 경우 일반적으로는 LOGGING 상태이므로 앞서 언급한 모든 로그를 기록하게 된다. 하지만, 직접 로딩(DIRECT LOADING) 기법을 사용하게 된다면 HWM 뒤에 존재하는 블록에 데이터를 저장하게 되므로 별도의 로그를 기록하지 않아도 복구 시 HWM 뒤의 블록에 존재하는 데이터를 제거하면 될 것이다. 이와 같은 이유에서 직접 로딩 방식의 경우에는 로그를 기록하지 않는 NOLOGGING 방식으로 데이터를 저장할 수 있게 된다.
결국, 위와 같이 NOLOGGING 상태에서 직접 로딩을 수행한다면 앞서 언급한 3가지의 성능 저하 요소를 대부분 제거할 수 있으며 이로 인해 디스크 I/O는 감소하게 된다. 상황에 따라 다르지만 대용량의 데이터에 대해 NOLOGGING 상태의 직접 로딩은 일반 INSERT에 비해 10배 이상 성능을 향상시킬 수도 있으며 그 이상의 성능 향상을 기대할 수도 있다.
DELETE와 UPDATE의 성능을 최적화하자
대용량의 데이터에 대해 DELETE 또는 UPDATE를 수행한다면 엄청난 성능 저하가 발생할 수 있다. DELETE 또는 UPDATE는 INSERT에 비해 더 많은 자원을 사용하게 되며 직접 로딩 또는 NOLOGGING 상태와 같은 방법이 존재하지 않게 된다. 많은 데이터에 대해서는 과거에나 지금이나 성능 저하를 감수할 수 밖에는 없을 것이다. 하지만, 이와 같은 대용량의 DELETE 또는 UPDATE에 대해서도 성능을 최적화 시키는 방법은 존재한다.
우리는 데이터를 변경하기 위해서는 항상 UPDATE를 사용해야 한다고 생각하고 데이터를 삭제하기 위해서는 항상 DELETE를 수행해야 한다고 생각한다. 일반적으로 생각한다면 당연한 사실일 것이다. 하지만, 이와 같은 고정 관념으로는 대용량의 데이터에서 더 이상의 성능 향상을 기대할 수 없을 것이다.
대용량의 데이터에 대해 UPDATE 또는 DELETE를 수행하는 경우 최적의 성능을 보장 받기 위해서는 기존의 사고 방식에서 벗어나야 할 것이다. 결국, 생각하는 방식의 전환만이 대용량의 데이터에 대해 UPDATE와 DELETE의 성능을 최적화하는 유일한 방법이 될 것이다.
대용량의 데이터에 대해 데이터의 변경에 대해 UPDATE로 작업을 수행하지 말고 데이터의 삭제에 대해 DELETE로 작업을 수행하지 말아야 한다. UPDATE는 INSERT로 변경하고 DELETE 또한 INSERT로 변경하는 순간 최적의 성능을 기대할 수 있을 것이다. UPDATE를 INSERT로 DELETE를 INSERT로 수행하는 것이야말로 우리의 기존 고정 관념을 파괴하는 행위일 것이다. 이제는 이와 같은 기존의 고정 관념을 파괴하여 성능을 최적화해야 할 것이다.
그렇다면 어떤 이유에서 UPDATE 또는 DELETE 대신 INSERT를 사용해야 하는가? 이유는 간단하다. INSERT는 UPDATE와 DELETE와는 달리 직접 로딩과 NOLOGGING이 가능하기 때문이다. 이와 같은 성능 향상의 요소는 우리에게 엄청난 혜택을 제공하기 때문이다.
그렇다면 어떻게 UPDATE를 INSERT로 변경하고 DELETE를 INSERT로 변경할 수 있겠는가? 물론, 데이터 삭제에 INSERT를 사용해야 하기 때문에 작업 절차는 복잡해 질 수 있다. 예를 들어, TEST 테이블의 크기가 100GB이며 그 중 50GB에 해당하는 데이터를 삭제해야 한다고 가정하자. 그렇다면 DELETE를 수행하는 순간 우리는 엄청난 시간을 기다려야 해당 작업을 종료할 수 있을 것이다.
DELETE를 INSERT로 변경한다면 어떻게 되겠는가? 우선, TEST 테이블과 동일 구조의 TEST_IMSI 테이블을 생성한 후 TEST 테이블로부터 데이터가 삭제된 후 남게 되는 데이터만을 조회하여 TEST_IMSI 테이블에 INSERT를 수행한다. 해당 작업에는 TEST_IMSI 테이블을 NOLOGGING 상태로 변경한 후 해당 테이블에 직접 로딩을 수행해야 할 것이다. 고성능의 디스크를 사용하는 시스템이라면 1GB에 1분 정동의 INSERT 시간이 소요된다.
50GB의 데이터를 INSERT하면 되므로 최적화된다면 50분 정도의 시간이 소요될 것이다. 이 얼마나 빠른 속도인가? 물론, 경우에 따라 병렬 프로세싱을 이용해야 할 수 도 있다. 이와 같이 작업을 수행했다고 모든 것이 종료되는 것은 아니다. TEST 테이블을 TEST_BACKUP으로 이름을 변경하고 TEST_IMSI 테이블을 TEST 테이블로 이름을 변경해야 할 것이다. 물론, 인덱스가 필요하다면 인덱스도 생성해야 할 것이다.
이와 같이 작업 절차는 복잡해 지지만 DELETE를 수행하는 것보다는 10배 아니 그 이상의 성능 향상을 기대할 수 있을 것이다. UPDATE의 경우도 이와 다르지 않다. UPDATE 후의 데이터를 임시 테이블에 INSERT를 수행하고 해당 테이블의 이름을 변경한다면 기존 테이블에는 UPDATE 후의 데이터가 저장되므로 INSERT를 이용하여 UPDATE를 대신할 수 있게 된다.
이와 같은 방식으로 기존의 방식에서 벗어날 수 있다면 우리는 최적의 성능을 기대할 수 있을 것이다.
우리가 가지고 있는 기존의 방식을 버린다는 것은 쉬운 일이 아닐 것이다. 하지만, 더 좋은 방법이 있다면 과감히 새로운 방법을 선택하는 것도 필요할 것이다. 데이터의 갱신과 제거를 UPDATE와 DELETE로 구현하는 것이 아니라 INSERT로 구현한다면 많은 사람들이 의아해 할지도 모른다. 하지만, 분명히 가능한 일이며 이를 통해 우리는 INSERT의 최고의 아키텍쳐인 NOLOGGING과 직접 로딩을 이용할 수 있다는 것을 명심하길 바란다.
제공 : DB포탈사이트 DBguide.net
