상상플러스 :: MySQL 5.0, 스토리지와 SQL 분리한 DBMS

http://blog.naver.com/etwas0227/60022701812

MySQL 5.0, 스토리지와 SQL 분리한 DBMS 업계 다크호스

MySQL 5.0이 지난 2005년 11월에 발표된 이후 새롭게 추가된 성능에 대해서는 여러 번 소개되었으며 많은 곳에서 언급이 되고 있다. 하지만 실제 사용자들이 가장 많이 물어온 것은 과연 MySQL 5.0에서 얼마나 많은 성능향상이 있었는지에 대한 부분이었다. 이번 특집을 통해 MySQL 5.0이 이전 버전들에 비해 그리고 다른 데이터베이스와 비교할 때 어떤 성능을 나타내고 있는지 알아보고 어떠한 변화가 이러한 성능향상을 가져왔는지 알아보자.

그동안 MySQL은 가볍고 빠른 데이터베이스로 많이 알려져 왔지만 실제로 어떠한 성능을 내는지 비교 벤치마크가 이뤄진 적이 별로 없는 탓에 체감적인 성능으로만 이야기되는 경우가 많았다. 사실 MySQL 5.0 이전 버전까지는 다른 상용 데이터베이스에 비해 지원되는 기능이 많이 부족해서 같은 선상에 놓고 벤치마크를 진행하기가 어려웠던 것도 그 중 하나의 이유였다. 일반적인 데이터베이스 벤치마크가 트랜잭션의 지원을 기본으로 함에도 불구하고 MySQL의 기본 스토리지 엔진인 MyISAM은 트랜잭션을 지원하지 않았고, 트랜잭션을 지원하는 스토리지 엔진인 InnoDB 엔진 같은 경우 MyISAM보다 한참 떨어지는 성능을 낸다는 것이 일반적인 인식이었기 때문이다.

물론 아직도 업계에서 공식적인 성능 비교를 위해 많이 사용하는 TPC-C를 기반으로 하여 MySQL을 벤치마크 테스트를 할 수 있는 테스트 프로그램이 OSDL(Open Source Development Lab)의 DBT-2 외에는 전무하고 아직 DBT-2는 0.37 버전이라서 객관적인 성능 비교는 힘든 상태이지만 상대적인 비교를 통해 MySQL의 버전 별 성능과 엔진 별 성능 그리고 다른 데이터베이스와의 성능 차이를 알아보자.

MySQL 성능 벤치마크 테스트

앞에서 언급한 대로 벤치마크 테스트의 경우 TPC(Transaction Processing Performance Council)와 같은 공인된 기관에서 테스트 결과를 제시하는 것을 가장 신뢰할 수 있겠지만 현재 MySQL을 기반으로 수행된 TPC 테스트 결과가 없으며, 다른 공식적인 벤치마크 테스트 결과도 최소 6개월 이전의 데이터로서 현재 MySQL 5.0 버전이 출시된 상태에서는 그다지 실효성이 없는 상태이다. 그래서 이번에 제시되는 벤치마크 테스트는 MySQL 5.0의 정식버전 출시 이전에 MySQL 본사 벤치마크 테스트 랩에서 수행된 결과를 사용한다.

벤치마크 시나리오

이번 벤치마크는 기본적으로 MySQL의 성능을 측정하기 위해 만들어진 오픈소스 벤치마크 툴인 Sysbench(sysbench.sourceforge.net)를 기반으로 수행되었다. Sysbench는 데이터베이스가 동작하는 서버의 파일 I/O 성능, 스케쥴러 성능, 메모리 할당과 전송 속도 및 데이터베이스의 OLTP(Online Transaction Processing) 성능을 측정할 수 있으며 처음 만들어질 당시에는 MySQL을 위한 것이었으나 현재는 테스트의 스펙을 만족시키는 각종 데이터베이스들을 지원하고 있다. 이번 벤치마크 테스트는 다음과 같은 사항으로 수행되었다.

◆ CPU : 하이퍼쓰레딩이 가능한 인텔 2.0GHz 제온 CPU 4개
◆ 디스크 : 8개의 SCSI 디스크를 하드웨어 RAID0+1으로 RAID 구성
◆ 메모리 : 4GB
◆ 운영체제 : 레드햇 AS 3.0 업데이트 2. 커널 2.4.21-EL
◆ 테이블 크기 : 100만 건(300MB)

이번 테스트는 데이터베이스 자체가 낼 수 있는 최고의 성능을 알아보기 위해 Primary Key를 사용하도록 하였으며 가능한 모든 테이블의 데이터가 데이터베이스의 메모리 캐시를 사용할 수 있도록 하였다. 본 테스트는 MySQL 4.1.12과 5.0.7의 버전 간의 성능비교와 MySQL 4.1.12, MySQL 5.0.7, Oracle 10g, PostgreSQL 8, EnterpriseDB 8 등 여러 데이터베이스와의 성능비교를 비롯해 CPU 개수에 따른 확장성 테스트를 하는 것이 목적이다. 이번 테스트에 사용된 테이블 스키마는 다음과 같다.

CREATE TABLE `sbtest` (
`id` int(10) unsigned NOT NULL auto_increment,
`k` int(10) unsigned NOT NULL default '0',
`c` char(120) NOT NULL default '',
`pad` char(60) NOT NULL default '',
PRIMARY KEY (`id`),
KEY `k` (`k`);

그리고 한 번의 트래낵션으로 처리되는 테스트쿼리는 다음과 같다

◆ 특정 데이터 지정(10회) : SELECT c FROM sbtest WHERE id=N
◆ 특정 범위 지정(1회) : SELECT c FROM sbtest WHERE id BETWEEN N AND M
◆ 범위 지정 합계(1회) : SELECT SUM(c) FROM sbtest WHERE id BETWEEN N and M
◆ 범위 지정 후 Order by 수행(1회) : SELECT c FROM sbtest WHERE id between N and M ORDER BY c
◆ 범위 지정 후 DISTINCT 수행(1회) : SELECT DISTINCT c FROM sbtest WHERE id BETWEEN N and M ORDER BY c

MySQL 버전 간 성능비교

MySQL은 여러 가지 스토리지 엔진을 사용할 수 있다는 것을 들고 있다. 하지만 InnoDB 자체의 스토리지 엔진으로서의 우수성에도 불구하고 성능 문제로 InnoDB 엔진이 사용되지 않는 경우가 많았다. MySQL은 5.0 버전을 출시하면서 InnoDB 스토리지 엔진의 성능향상을 하나의 큰 변화로 내세웠는데 이번 벤치마크는 실제로 어떠한 성능차이를 보여주는지 알아보기 위해 행해졌다. MySQL의 버전 간 성능 비교는 4.1.12 표준 바이너리와 ICC(Intel C Compiler) 바이너리, 5.0.7 표준 바이너리와 5.0.7 커스텀 빌드간의 성능을 비교했으며 모두 InnoDB 스토리지 엔진을 사용했다.

<그림 1> MySQL 버전 별 InnoDB 성능

<그림 1>에서 확인할 수 있는 것과 동일한 피크 치에서의 성능 차이는 커스텀 빌드의 5.0.7과 4.1.12 표준 바이너리와는 약 20%, 5.0.7 표준 바이너리와 4.1.12 표준 바이너리와는 10% 정도의 차이를 보여주고 있다. ICC 바이너리의 경우 최고 성능은 떨어지나 꾸준히 부하를 증가하는 경우에도 일관된 수치를 보여준다는 장점이 있다.

다른 DBMS와의 벤치마크

이번 벤치마크는 MySQL 5.0과 오라클 10g, PorsgreSQL8, EnterpriseDB8과의 비교 벤치마크 테스트이다. 이 중에서 EnterpriseDB의 경우에는 조금 생소할 수도 있는데, EnterpriseDB는 PostgreSQL 기반의 상용 데이터베이스로써 오라클을 기반으로 작성된 애플리케이션을 전혀 수정하지 않고 사용할 수 있도록 한 데이터베이스이다. 벤치마크 테스트는 데이터베이스와 상관없이 동일한 방법으로 수행되었다. 물론 본 벤치마크 테스트의 경우 앞에서 설명한 바와 같이 100만 건의 레코드를 가진 테이블에서 Primary Key를 사용하는 특수한 환경에서 테스트되었기 때문에 실사용 시의 속도와는 차이가 있을 수 있다.

<그림 2> MySQL과 기타 데이터베이스와의 비교 벤치마크

<그림 2>의 결과를 간단히 정리하면 MySQL 5.0/InnoDB가 최상의 결과를 보이며, 오라클 10g는 6.5%, PostgreSQL 8은 23.47%, MySQL 5.0/MyISAM은 43.18%, EnterpriseDB 8은 46.02% 정도 느린 결과를 보이는 것을 알 수 있다. 이번 벤치마크에서 가장 뜻밖의 결과를 보인 테스트였다. 뜻밖인 부분은 MyISAM의 성능이 매우 떨어지는 것으로 나왔다는 것이었는데, 이는 이번 테스트가 Primary Key만을 사용하게 해서 대부분의 테이블 데이터를 메모리 버퍼를 통해 수행되게 하면서 나타난 결과라고도 볼 수 있다. 만일 이번 테스트가 1GB 정도의 메모리를 가진 낮은 사양의 서버에서 일반 키를 사용하게 수행했다면 전혀 다른 결과가 나왔을 수도 있을 것이다.

CPU 개수에 따른 확장성 테스트

이제까지의 테스트는 4개의 CPU에서 수행된 결과였다면 이번 테스트는 CPU 개수를 1개에서 4개까지 변화시키면서 시스템이 확장되는 것에 따라 데이터베이스의 성능이 고르게 증가하는지를 보기 위한 확장성 테스트이다. 이번 테스트는 가능한 디스크 I/O를 줄이고 CPU에 의존적인 데이터베이스 엔진 자체의 성능을 평가하기 위한 테스트였기 때문에 확장성 테스트에 매우 적합하다고 할 수 있다.

<그림 3> CPU 개수에 따른 데이터베이스 확장성 테스트

CPU가 하나일 때와 4의 CPU에 하이퍼쓰래딩을 켠 상태를 비교해봤을 때 오라클이 3.83로 가장 뛰어난 확장성을 보였으며, MyISAM을 제외한 3개는 비슷한 정도의 확장성을 보여줬다. 본 테스트에서도 MyISAM이 가장 떨어지는 확장성을 보여줬는데 이는 MyISAM이 작은 시스템에서 최적의 성능을 내도록 설계된 스토리지 엔진이기 때문으로 보인다.

MySQL의 성능을 좌우하는 기술

이번 테스트의 경우 사실 다른 데이터베이스와의 성능 비교를 위한 것이라기 보다는 MyISAM에 대한 정보만을 가지고 있는 MySQL 사용자들에게 InnoDB의 성능이 MySQL 5.0버전에서 얼마나 좋아졌으며 실제적으로 어떠한 성능을 내는지 보여주는 것에 중점을 맞췄다. 물론 MyISAM이 InnoDB에 비해 성능이 떨어지는 스토리지 엔진이라는 것이 아닌 어떠한 상황에서 더 좋은 성능을 낼 수 있는지를 설명하기 위한 것이다.

MySQL 데이터베이스 아키텍처

MySQL 5.0 버전의 출시와 함께 그 동안 여러 변화를 거쳐오던 MySQL의 전체 아키텍처가 확고하게 자리를 잡았다. 물론 5.1 버전까지 아키텍처 통합은 계속적으로 이뤄질 예정이지만 그 동안 스토리지 엔진 별로 실제 사용할 수 있는 기능들에 많은 차이가 있었던 것에 비해서 5.0 버전에서는 스토리지 레이어와 SQL 레이어를 확실하게 구분하면서 기본적인 기능들이 스토리지 엔진과 상관없이 동작하도록 했다. 또한 메모리 버퍼나 캐시, SQL 파서, 옵티마이저 등을 통합시키면서 성능향상을 이뤄냈다. 이를 통해 5.0에 새롭게 적용되는 스토어드 프로시저, 트리거, 뷰, 커서 등과 같은 기능들이 스토리지 엔진과 상관없이 어떤 스토리지 엔진에서도 동작하게 되었으며 앞으로 계속적인 통합과 분리 작업을 통해 데이터베이스의 성능 향상이 모든 스토리지 엔진에 동일하게 이루어지게 될 것이다.

<그림 4> MySQL의 5.0의 아키텍처 구성도

새로운 Greedy 옵티마이저 사용

MySQL 5.0에서 새롭게 소개되는 greedy 옵티마이저의 적용을 통해 적절한 쿼리 실행 계획을 수립하기 위해 필요한 시간을 비약적으로 단축했다. 이 새로운 옵티마이저로 인한 성능의 향상은 특히 여러 테이블이 조인되며 적절한 조인 키를 찾을 수 없을 때 발휘된다. Greedy 옵티마이저가 사용되기 전에는 적절한 쿼리 실행 계획을 찾기 위한 복잡도는 조인되는 테이블의 개수를 N이라고 할 때 N!로 계산되었다. 하지만 Greedy 옵티마이저가 적용됨으로써 이를 N!/(D-1)로 줄일 수 있게 되었다(D는 검색의 깊이). 물론 Greedy 옵티마이저가 언제나 모든 쿼리 실행 계획에 최선의 결과를 보여주는 것은 아니라는 것을 감안하더라도 많은 테이블이 포함되는 조인의 경우 최대 1000에 가까운 경우의 수를 줄여주게 된다. 이를 통해 많은 테이블을 한번에 조인할 경우 때때로 서버 자체가 멈춘 것 같이 느껴지던 현상을 대부분 해결했다.

조건절 사용 시의 성능 향상

이전 버전까지는 AND나 OR을 통해 비교할 경우 같은 키를 사용해야만 쿼리가 최적화되었지만 인덱스 조합 기법을 사용함으로써 서로 다른 키를 사용하는 AND와 OR 비교 역시 쿼리 최적화가 이뤄지게 되었다. 또한 인덱스 조합 기법은 AND와 OR 뿐만 아니라 >, < 등과 같은 모든 1:1 비교에 적용되었다. 이를 통해 「WHERE key1 > 4 OR key2 < 7」와 같은 조건문과 「WHERE (key1 > 4 OR key2 < 7) AND (key3 >= 10 OR key4 = 1)」와 같은 쿼리에도 다중 인덱스를 사용해서 결과를 얻을 수 있게 되었다. 그리고 이전 버전에서 NOT IN 또는 NOT BETWEEN을 사용할 경우 테이블 스캔을 하게 됨으로써 많은 시간이 소모되었던 것과 달리 범위 분석을 통해 최적화가 가능해 짐으로써 이와 같은 조건절을 사용하는 쿼리 역시 IN과 BETWEEN을 사용하는 쿼리와 같은 수준으로 성능이 향상되었다.

플러그인 가능한 다중 스토리지 엔진 구조

MySQL의 가장 큰 장점이라면 바로 다중 스토리지 엔진 구조라고 할 수 있다. 물론 이전 버전까지도 여러 가지 스토리지 엔진을 이용할 수 있었지만 5.0 버전이 발표되면서 향상된 주요 기능 중 하나로 집중적으로 제시하고 있는데 이는 이번 버전에서 스토리지 엔진 자체의 기능과 그 외 데이터베이스 기능들을 제대로 분리하기 시작했기 때문이다. 현재 5.0 버전에서는 10개에 이르는 스토리지 엔진을 지원하며 2개의 스토리지 엔진이 새롭게 지원되기 시작했다. 이러한 다중 스토리지 엔진의 장점이라면 실제적인 데이터베이스 사용 용도에 맞게 스토리지 엔진을 선택하여 최선의 성능을 발휘할 수 있다는 점이다. 예를 들어 MyISAM 스토리지 엔진의 경우 트랜잭션이 필요하지 않고 2CPU, 2GB 이하의 작은 시스템에서 다른 스토리지 엔진에 비해 좋은 성능을 보여주며 InnoDB의 경우 트랜잭션이 많고 쓰기 관련 작업이 많은 데이터베이스에서 그리고 CPU와 메모리가 많은 시스템에서 가장 좋은 성능을 발휘한다.

그리고 5.0에 새롭게 도입된 아카이브 스토리지 엔진의 경우 엔진 자체가 데이터를 실제 크기의 20% 정도로 자동으로 압축하여 주며 매우 빠른 INSERT 속도를 보임으로써 로그성 데이터의 처리에 최적화되어 있다. 다중 스토리지 엔진 구조의 또 하나의 장점은 사용중인 데이터베이스를 용도에 따라 다른 스토리지 엔진으로 간단히 변경할 수 있다는 점이다. 한 번 스토리지 엔진이 정해지면 계속 해당 스토리지 엔진만을 사용해야 하는 것이 아니라 예를 들어 처음 데이터를 쌓을 때는 InnoDB 엔진을 사용했다가 일정 기간이 지난 후에는 보관을 위해서 아카이브 스토리지 엔진으로 Alter Table 명령 한 번으로 변경할 수 있다는 것이다. 그리고 하나의 데이터베이스 내에서도 테이블의 용도에 따라 여러 스토리지 엔진을 혼용하는 것 역시 가능하다. 이와 같이 다중 스토리지 엔진 구조를 잘 활용하면 현재 가진 시스템 자원을 최적화하여 목적에 맞는 데이터베이스를 구축할 수 있다.

InnoDB 스토리지 엔진의 향상

이번 벤치마크 테스트에서도 확인할 수 있었지만 MySQL 5.0 버전에서는 InnoDB 스토리지 엔진에 대해 많은 향상이 이뤄졌다. 무엇보다 InnoDB 스토리지 엔진의 코드 자체를 50% 이상 새롭게 작성하며 스토리지 엔진 자체를 최적화해서 성능을 10% 이상 높였으며, 그 동안 다른 스토리지 엔진에 비해 떨어지던 데이터 저장 효율을 높임으로써 기존 3.X나 4.X 버전의 InnoDB 데이터를 5.0으로 마이그레이션하는 것 만으로 총 데이터 저장 공간을 15~20% 정도 절약할 수 있게 되었다. 또한 내부 저장 알고리즘 개선을 통해 다른 데이터베이스에서 많은 DBA들의 고민거리인 스토리지 재구성 작업이 사라지게 되었다. 그리고 데드락 시 락을 트래킹하는 범위를 200회로 제한함으로써 이전 버전에서 데드락 자동복구를 위해 락을 트래킹하는 도중에 메모리 부족으로 종종 발생하던 서버가 멈추는 현상을 제거했다. 기본적으로 지원되던 B-Tree 인덱스 외에 클러스터 인덱스를 지원하여 클러스터 인덱스가 적용될 경우에는 10배 이상의 속도를 향상시킬 수 있다.

용도에 맞는 데이터베이스의 사용

필자가 MySQL에 대해 설명할 경우 많은 사용자들이 다른 데이터베이스와 기능을 1:1로 비교하고 싶어하며, 성능도 1:1의 절대적 비교를 원하곤 한다. 하지만 필자는 꼭 필요한 경우가 아니면 다른 데이터베이스와의 직접적인 비교를 삼가한다. 많은 경우 실제로 필요한 기능이 아님에도 불구하고 기능이 많으면 많을수록 좋은 게 아니냐는 식의 비교가 될 경우가 있기 때문이다. 물론 사람의 욕심이라는 것이 가능하다면 더 많은 것을 원하는 것이 당연하겠지만 물건을 구입할 때도 마찬가지듯이 현재 자신이 필요한 용도와는 상관없이 욕심을 내는 경우 낭비가 이뤄지게 되는 것이 사실이다. MySQL 역시 아직 모자라는 부분이 많다. 하지만 사용할 데이터베이스의 용도를 확실히 정하고 그에 맞게 사용된다면 비용 절감과 성능향상이라는 두 마리 토끼를 잡을 수 있을 것이라 믿는다.

[MySQL 5.0 최신 성능 관리팁] 커스텀 빌드로 최적의 성능 끌어내기

최적의 빌드로 최선의 성능을
MySQL을 있는 그대로 사용하는 것과 기본적인 튜닝을 하는 것은 최소 25%에서 많은 경우 75% 이상의 성능 차이를 보인다. MySQL을 사용하면서 기본적으로 할 수 있는 성능향상 팁들에 대해 간략하게 알아보자.

MySQL 컴파일하기
이번 벤치마크에서도 최고의 성능을 낸 것은 커스텀 빌드된 MySQL이였다. 물론 기본적으로 제공되는 바이너리 버전 역시 안정성이 고려되는 범위 내에서 최선의 성능을 낼 수 있도록 컴파일되어 있다. 하지만 아무래도 안정성을 먼저 고려하기 때문에 GCC의 경우 아직 2.9대를 사용하고 있으며, 기본적인 기능도 모두 포함시켜서 컴파일하게 된다. 현재 출시되는 레드햇 리눅스의 경우 GCC 3.4 버전 이상을 사용하고 있고, 이와 같이 높은 GCC 버전에서 적절히 컴파일될 경우 더 나은 성능을 나타내게 된다. 그리고 사용자에게 필요 없는 기능은 제외하고 컴파일 함으로써 성능 향상을 꾀할 수 있다. 다음은 이번 벤치마크에 사용된 컴파일 옵션이다. 물론 이는 제온 CPU에 최적화 된 옵션이므로 다른 플랫폼에서는 차이가 날 수 있다. 플랫폼 별 옵션에 대해서는 바이너리 버전을 다운받은 압축 파일 내에 bin 디렉토리의 mysqlbug라는 파일을 확인하면 바이너리 버전을 컴파일할 때 사용한 컴파일 옵션을 확인할 수 있다.

CFLAGS="-g -O3 -fno-omit-frame-pointer " CXX="gcc" CXXFLAGS=" -g -felide-constructors -fno-exceptions -fno-rtti -O3 -fno-omit-frame-pointer" CXXLDFLAGS="" \ ./configure --prefix=/usr/local/mysql \ --enable-assembler \ --with-extra-charsets=complex \ --enable-thread-safe-client \ --with-readline \ --with-big-tables \ --enable-local-infile

MySQL의 기본 설정 파일 셋팅하기
MySQL을 설치하고 난 후 mysql 디렉토리 내에 support-files이라는 디렉토리를 보면 MySQL에 할당할 수 있는메모리 양에 맞춘 추천 configuration 파일이 5개 존재한다. my-small, medium, large, huge, innodb-heavy-4G 이렇게 5개 파일이 있으며 각각은 64MB 이하, 128MB 이하, 512MB 이하, 2GB 이하, 4GB 이하를 위해 제공되며 이 중 innodb-heavy-4G를 제외한 나머지 4개 파일은 MyISAM만을 사용하는 것을 기본으로 제공되고 있다. 물론 이 기본 설정 파일들은 시스템에서 MySQL을 메인으로 사용한다는 것을 전제하고 있기 때문에 다른 애플리케이션의 비중이 더 클 경우 MySQL에 어느 정도 메모리를 할당할 수 있을지를 먼저 결정해야 한다. MyISAM과 InnoDB를 모두 사용할 경우 앞에서 이야기한 메모리 양의 두 배가 필요하므로 주의해야 한다. 이 중 사용자가 할당 가능한 메모리 양에 맞는 cnf 파일을 선택하여 유닉스 계열인 경우 /etc/my.cnf로 이름을 변경하여 옮겨 놓으면 기본적인 셋팅은 마무리된다. InnoDB만 사용할 경우 innodb-heavy-4G가 매우 잘 설정된 설정파일의 예제이므로 이를 자신이 사용할 메모리 양에 맞게 비율을 맞춰 줄여주는 것도 좋은 방법이다. 물론 사용자의 데이터베이스 사용용도에 딱 맞춘 튜닝을 위해서는 추가적인 모니터링 작업과 튜닝작업이 필요하지만 기본 셋팅만 잘 해 놓아도 하지 않는 것에 비해 15% 이상의 성능향상이 가능하므로 반드시 설정 파일 셋팅을 가장 먼저 신경쓰도록 하자.

MySQL 5.0, 스토리지와 SQL 분리한 DBMS 업계 다크호스

상상플러스 - 최근 공지