(MySQL) RDB 성능 향상 팁

RDB 대용량 테이블의 성능향상을 위한 팁

엔터프라이즈 어플리케이션에 있어서의 성능문제의 80%는 DB에서 발생하는것으로 알려져 있다. 인덱스 미설정에서 오는 Full Table Scan과 같은 단순한 문제라면 해결이 쉽지만 레코드수가 수억건에 이르는 대용량 테이블이 가져오는 성능저하는 어플리케이션 수정만으로는 대응이 쉽지 않은 경우가 많다. 이번 포스팅에서는 수억건 이상의 레코드를 지니는 대용량 RDB를 운영함에 있어서 적용 가능한 성능 향상 팁에 대해서 알아보고자 한다.

성능향상의 핵심은 밸런스

오라클, SQL Server, MySQL과 같은 관계형DB(이하 RDB) 성능을 좌우하는 세가지 요소는 I/O,  메모리, 그리고 CPU이다. RDB성능 튜닝은 이 세가지 자원에 걸리는 부하를 적절하게 분산시켜 비용대비 최적의 성능을 뽑아내는것이 핵심이다.

• I/O: 디스크에 파일 형태로 영속화된 데이터의 읽기/쓰기에 해당한다. 데이터베이스라는것이 결국 데이터를 찾아서 읽고 쓰는것이 주된 역할인 만큼 세가지 요소중에 가장 큰 비중을 차지한다. RDB에서 저장장치로 주로 사용되던 하드디스크의 경우 오랜기간 성능개선이 정체되어 있었는데 SSD의 등장으로 비약적인 발전을 이루고 있다. 
• 메모리: 적절하게 설정된 메모리는 캐쉬를 통해 I/O의 부담을 크게 줄여주는 역할을 한다. 특히 빈번하게 발행되는 SQL의 경우 캐쉬 히트율 (Cache hit ratio)은 SQL성능을 결정적으로 좌우하는 경우가 많다. OS자체가 지닌 메모리와는 별도로 DB가 사용 가능한 메모리는 DB설정에 의존하므로 빈번히 실행되는 SQL임에도 불구하고 캐쉬 히트율이 낮을 경우는 반드시 DB서버 어플리케이션에 할당된 메모리 용량을 체크해 보아야 한다.
• CPU: CPU는 처리속도 전반에 관여하므로 당연히 성능에 주는 영향도 크다. 최근들어서는 멀티 코어 프로세스가 일반화 되어 처리속도가 크게 향상된 관계로 CPU로 인해 처리속도가 늦어지는 경우는 드문편에 속하지만, 취득한 데이터를 소팅한다던지 특정한 포멧으로 가공하는등의 데이터 가공을 대량으로 실시하는경우 성능저하를 가져오기도 하므로 프로파일링 데이터를 확인할 때에는 CPU Time의 비율을 반드시 확인해 주어야 한다.

성능과 관련된 세가지 요소중에 I/O를 맨 위에 올려놓은것은 이 글의 주제가 대용량 테이블의 성능 향상에 대한 것이기 때문이다. 대용량 테이블의 성능문제는 I/O의 오버헤드가 중심에 위치하게 되며 해결의 실마리는 이 오버헤드를 CPU와 메모리에 적절하게 분산시키는데에 있다.

수억건 이상의 데이터를 다루는 테이블에서의 성능문제

최근 필자가 수행했던 컨설팅 업무에서 발생한 사례를 소개해 보겠다. 문제가 된 것은 월 단위로 수행 되는 일괄처리(batch process) 어플리케이션의 성능이었는데, 운영 환경의 로그와 DB프로파일링 레포트(Oracle AWR Report)를 조사해 본 결과 원인은 수억건의 데이터를 지니고 있는 어느 테이블과 관련된 검색SQL의 실행 시간이 전체 일괄처리 시간의 상당부분을 차지하고 있다는것이 밝혀졌다. 처음에는 수천개에 해당하는 IN구를 포함한 비 효율적인 SQL 구조를 성능저하의 원인으로 지목하였지만, 실행 계획 분석등을 통해 근본적인 원인이 복수의 대용량 테이블을 조인하여 사용하는 관계로 엄청난 양의 I/O가 발생하는데 있음을 확인할 수 있었다.

 일반적으로 대용량 테이블이라 하더라도 인덱스가 제대로 작동하고 있다면 검색 자체가 성능저하를 가져 오지는 않는다. 다만, 복수의 JOIN과 같이 대량의 네스티드 루프가 발생한다면 엄청난 양의 I/O가 발생하고 이것이 RDB전체의 성능을 크게 저하시키는 원인이 되기도 한다.

대용량 테이블 성능개선 팁

SSD

수백퍼센트의 극단적인 성능향상을 추구하는 고객에게 가장 확실한 해결책으로 SSD 도입을 권하는것은 이미 DBA사이에서는 2년전부터 대세로 자리잡았다. 그만큼 SSD는 하드디스크와 비교하여 엄청낭 성능향상을 기대할 수 있는데, 특히 DB성능을 구성하는 중요한 요소인 랜덤 억세스에 대해서는 거의100배가 넘는 성능향상을 보이기도 한다.

A Comparison of Solid State Drives to Serial ATA (SATA) Drive Arrays for Use with Oracle

DB저장장소로서 SSD를 고려할 때에는 비용적인 문제와 안정성이 중요하게 고려되어야 한다. SSD는 하드디스크와 비교하여 값이 매우 비싸고 안정성부분에 있어서 기대 수명이 HDD의 절반밖에 되지 않기 때문이다.

안정성 문제에 대한 해결방안으로 레이드 구성이 유효하다 할 수 있는데, 여기서 한가지 주의해야 할 점은 동일한 공정으로 생산된 제품의 경우 비슷한 시기에 수명이 다할 확율이 높으므로 가급적 다른 LOT넘버를 지닌 제품으로 레이드를 구성해야 한다는 점이다.

또 한가지 SSD 사용을 위한 전략은 속도가 빠른 SSD를 캐쉬전용으로 사용하고 영속저장소로는 HDD를 이용하는 방법이다. 이러한 경우 안정성을 크게 높이면서도 속도에 이득을 가져 올 수 있으면서 비용적인 측면에서도 억세스가 덜 일어나는 영속저장소에 HDD를 활용 가능하다는 점에서 높은 가격대비성능을 기대 할 수 있다. 오라클의 경우 이미 이러한 방식의 사용을 지원하며 SQL Server또한 2014버전부터 이러한 방식을 지원할 예정이다.

Accelerating Oracle database performance with SSD

테이블 압축

테이블 압축은 테이블 데이터를 압축해서 보관함으로서 파일 I/O를 감소시키는것을 목적으로 한다. 오늘날 멀티코어가 보편화 됨에 따라 CPU자원은 상당히 여유가 있는 대신, 처리해야 하는 데이터의 양은 크게 증가하여 I/O 병목현상이 흔히 발생하고 있다. 테이블 압축의 근본적인 아이디어는 I/O의 오버헤드를 상대적으로 여유가 있는 CPU로 전가하는것으로, 데이터의 내용이나 CPU의 구성에 따라 다르기는 하지만 필자의 경험상 테이블 용량은 1/3이하에 탐색속도는 30%이상 향상을 가져 올 수 있있다.
 테이블 압축은 단순히 디스크에 저장되는 세그먼트화일의 크기만이 아니라 메모리에 로드되는 데이터의 양도 크게 증가시키기 때문에 캐쉬매모리를 크게 증가시키는 효과를가져옴으로서 캐쉬 히트율을 늘려준다.
 테이블 압축을 하는경우 읽기, 쓰기, 업데이트의 성능변화가 각각 다르게 나타나며 RDB의 제품에 따라 다른 특징을 지니므로 이를 고려햐여야 한다. 오라클의 경우 읽기에는 상당한 성능향상을, 쓰기(삭제)는 변화가 없으며, 업데이트는 비압축보다는 약간 느려지는것으로 알려져 있다.

Advanced Compression with Oracle Database 11g 

SQL Server 2008 Page Compression: Compression ratios with real-world databasesSQL Server 2008 Data Compression: Impact of Data DistributionSQL Server 2008 Page Compression: Performance impact on table scansSQL Server 2008 Page Compression: Performance impact on insertsSQL Server 2008 Page Compression: Using multiple processors

RDB에 있어서의 테이블 압축은 위에서 소개한 SSD와 더불어 사용할 경우 성능향상과 비용이라는 두마리 토끼를 잡는데 매우 유용하며, Full Table Scan이 발생하는 경우 SSD + 압축으로 200배의 성능향상을 이끌어 낼 수 있다는 기사도 찾아 볼 수 있다.

Oracle 11g Data Compression Tips for the Database Administrator

테이블 파티셔닝

테이블의 데이터를 특정 조건(날자나 ID등)으로 분리할 수 있다면 테이블 파티셔닝이 성능향상에 효과적일 수 있다. 다만, 파티셔닝은 한가지 조건으로만 가능하므로 다양한 조건으로 검색되는 테이블의 경우 파티셔닝이 특정 조건에서는 유리하게 작용하지만 다른 조건에서는 불리하게 작용 될 수도 있다.

일반적으로 수억건에 이르는 데이터를 지니는 테이블이라 할 지라도 실제 빈번하게 억세스되는 유효 데이터의 건수는 얼마 되지 않는 경우가 많다. 이러한 유효 데이터와 통계등의 수치취득을 위해 남겨놓는 데이터를 별도 파디션으로 분리하여 관리하고 여기에 SSD나 압축과 같은 테크닉을 첨가한다면 대단히 효율적인 데이터베이스의 구축이 가능할 것이다.

Partitioning in Oracle Database 11g

결론

성능문제는 대부분 시스템이 운영되고 일정시간이 흐른 후에 주요 과제로 부각되기 시작한다. 따라서, 시스템이 가진 대부분의 문제점들이 발생에서 해결까지의 시간이 비용과 정비례한다는 사실을 고려 할때 성능 문제의 해결에는 많은 비용이 발생기 마련이다. 

 DB튜닝에 의한 어플리케이션 성능 개성은 어플리케이션 구조의 변경없이 가능한 경우가 많기 때문에 성능문제가 발생되었을 경우 우선적으로 고려되는 경우가 많은데, 그렇다고 해도 아무런 고려 없이 DB부터 들여다 보는것은 피해야 한다.

 성능개선의 작업방향을 결정할 때에는 DB이외에도 관계되는 요소들이 많으므로 프로파일링과 코드레뷰를 병행하여 각각의 모듈에서 어느정도의 처리비용이 발생하고 있는지 입체적으로 살펴볼 필요가 있다. 때에 따라서는 간단한 캐쉬사용만으로도 불필요한 DB접속을 줄여 전체 성능의 비약적 향상을 가져 올 수 있는 경우가 흔히 있기 때문이다.

참고: http://www.moreagile.net/2014/03/rdb.html