목적
이 문서는 파티션 사용의 이점을 보여주는 MySQL 5.1에서 대용량 데이터베이스 성능을 어떻게 측정할 것인가에 대해 설명하고 있습니다.
테스트 데이터베이스는 미국 교통통계청에서 발표된 자료를 사용하였습니다. 현재는 약 1억 1천 3백만건 이상의 레코드로 구성되어 있습니다. (7.5 GB의 데이터와 5.2 GB의 인덱스를 가짐)
데이터 수집 및 불러오기
이번 테스트에는 미국 교통통계청의 자료를 사용하였습니다. 이 데이터는 CVS파일로 제공되었으며, 1987년 6월부터 2007년 6월까지의 자료입니다. (기록시점 기준)
데이터 수집, 데이터 구조의 설정, 데이터 불러오기 등의 세부사항은 MySQL Forge에 설명되어 있습니다.
문제점 열거
이번 테스트는 데이터베이스의 용량이 서버상의 물리적인 메모리(RAM)의 용량보다 크고, 마찬가지로 인덱스의 크기도 RAM보다 큰 데이터베이스에 영향을 미칩니다.
이번 테스트에 사용된 서버는 4GB의 램을 가지며, 인덱스의 크기는 5GB를 넘습니다.
이러한 문제의 근거는 데이터 웨어하우스(Data warehouse)가 당신이 어떻게든지 설치할 수 있는 램의 적당한 용량을 초과하여 수 테라바이트의 용량을 차지하는 데이터를 수집하기 때문입니다.
일반적인 데이터베이스 저장방식 에서는(특히 OLTP에서), 인덱스는 메모리(RAM)에 캐쉬되어 레코드 검색시 빠른 속도를 보장합니다. 데이터가 사용가능한 메모리(RAM)에서 저장될 수 없는 크기에 도달하였을 경우, 우리는 다른 접근 방법을 사용할 필요가 있습니다.
MySQL 5.1에서는 파티셔닝(partitioning)을 주요 기능 중에 하나로 제공하는데, 이는 검색속도 향상을 위해 하나의 테이블을 논리적인 부분으로 나누는 기술입니다.
MySQL 5.1에서 파티션의 사용은 원리상으로는 단순해보이지만, 최고의 성능을 내기위해 데이터를 설정하는 부분에서 주의해야 할 몇 가지 까다로운 점이 있습니다. 이 문서에서는 이러한 위험요소들을 설명하고, 최적의 성능을 내기위한 실용적인 조언을 제공하려고 합니다.
파티셔닝 개요
MySQL 5.1에서 파티셔닝의 이행은 무척 단순합니다.
당신은 다음과 같은 기준으로 데이터를 분할할 수 있습니다.
* 범위(range)
* 목록(list)
* 해쉬(hash)
* 키(key)
당신의 요구에 따라, 다른 파티션 종류를 선택할 수 있습니다. 이 문서에서 우리는 아마도 데이터 웨어하우싱(data warehousing)에서 가장 흥미있는 범위 파티션닝(range partitioning)에 대해 전념하고 있습니다.
MySQL 파티션기능을 효과적으로 사용하기를 원한다면, MySQL 파티셔닝에는 주의해야 하는 약간의 제약이 있습니다.
* 파티션의 값은 정수형이어야 합니다. (integer)
* 만약 테이블이 unique 키와 primary 키를 가지고 있다면, 분할되는 컬럼(the partitioning column)은 해당키의 일부가 되어야 합니다.
첫 번째 제약은 당신의 설계 결정에 가장 큰 영향을 미칩니다. 만약에 파티셔닝을 사용하려는 컬럼이 정수형이 아니라면, 당신은 그것을 변형할 수 있는 함수를 사용해야 합니다. 약간의 추가적인 제약이 매뉴얼에 서술되어 있듯이 파티션에 적용되지만, 여기서 그것은 관심 밖의 일입니다.
파티셔닝에 대해 좀 아셨나요?
데이터 컬럼 사용하기
이번 단락과 관련되는 것은 파티셔닝 되는 데이터 컬럼의 사용입니다. native data type은 지원하지 않기 때문에, 데이터를 정수형으로 변환해야 합니다. 허용된 함수의 목록을 더하여, 단지 두 개의 데이터 함수만이 파티션 프루닝(partiton pruning)을 유발할 수 있다는 사실을 고려해야 합니다. 따라서 데이터 컬럼을 조작해야 한다면, YEAR 또는 TO_DAYS 중에 하나를 사용할 필요가 있습니다.
YEAR() 함수를 사용할 경우, 파티셔닝은 용이하고, 쉽게 읽을 수 있으며, 수월합니다.
CREATE TABLE by_year (
d DATE
)
PARTITION BY RANGE (YEAR(d))
(
PARTITION P1 VALUES LESS THAN (2001),
PARTITION P2 VALUES LESS THAN (2002),
PARTITION P3 VALUES LESS THAN (2003),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
달(month)을 기준으로 파티셔닝을 하는 것은 좀 더 신중해야 합니다. 다음과 같은 두가지 이유로 MONTH()를 사용할 수 없습니다.
* MONTH의 경우 년도(year)를 포함하지 않기 때문에, 파티션의 개수가 12개로 제한됩니다.
* MONTH 함수는 partition pruning에 최적화되지 않았기 때문에, 끔직할 정도의 성능을 보입니다.
따라서 partition pruning에 최적화되어 있는 TO_DAYS와 같은 다른 함수를 사용하는 것이 필요합니다.
CREATE TABLE by_month (
d DATE
)
PARTITION BY RANGE (TO_DAYS(d))
(
PARTITION P1 VALUES LESS THAN (to_days('2001-02-01')), -- January
PARTITION P2 VALUES LESS THAN (to_days('2001-03-01')), -- February
PARTITION P3 VALUES LESS THAN (to_days('2001-04-01')), -- March
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
년도(year)에 의하여 분할되어진 것에 비해 읽을 때 이미 저것은 덜 분명합니다. 잘못된 점은 서버가 당신의 값들을 계속 유지하지는 않지만, 일치하는 정수값들만은 저장을 합니다. 다음은 위 테이블에서 당신이 얻을 수 있는 내용입니다.
show create table by_month\G
*************************** 1. row ***************************
Table: by_month
Create Table: CREATE TABLE `by_month`
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100
PARTITION BY RANGE (TO_DAYS(d))
(
PARTITION P1 VALUES LESS THAN (730882) ENGINE = MyISAM,
PARTITION P2 VALUES LESS THAN (730910) ENGINE = MyISAM,
PARTITION P3 VALUES LESS THAN (730941) ENGINE = MyISAM,
PARTITION P4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
만약 당신이 각각의 파티션이 의미하는 것이 어떤 것인지 읽기 쉬운 참조를 갖길 원하신다면, 월(month)별로 분할된 테이블을 생성하는데 사용되었던 스크립트의 복사본을 저장하실 것을 권해드립니다.
함수에 의한 분할, 컬럼에 의한 검색
데이터 컬럼을 이용하는 분할된 테이블들을 사용할 때 발생되는 하나의 공통적인 실수는 분할에서 사용되었던 동일한 함수를 통해 질의하는 것입니다.
예를 들어 만약 당신의 테이블이 다음과 같은 절(clause)로 생성되었고,
partition by range( YEAR(d) )
YEAR와 TO_DAYS 가 partition pruning에 최적이 되었다는 것을 당신이 들었다면, 이와 같은 질의(query)를 사용하는 것이 논리적으로 보입니다.
SELECT count(*) FROM by_year
WHERE YEAR(d) = 2000; # <-- ERROR !!
EXPLAIN 구문으로부터 당신이 볼 수 있는 것처럼, partition pruning은 실행되지 않습니다.
explain partitions select count(*) from by_year where year(d) = 2001\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: by_year
partitions: P1,P2,P3,P4
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 30123
Extra: Using where
해당질의는 전체테이블을 스캔합니다(full table scan). “partition pruning에 최적화”되었다는 의미는 WHERE 절에서 저 컬럼이 사용되어질 때, 검색은 파티션들을 사용할 것이라는 뜻입니다.
이것이 올바른 질의방법입니다.
SELECT count(*) FROM by_year
WHERE d BETWEEN '2001-01-01' and '2001-12-31';
그리고 이제야 partition pruning이 사용됩니다.
explain partitions select count(*)
from by_year
where d between '2001-01-01' and '2001-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: by_year
partitions: P2
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 30123
Extra:
이것은 해당질의가 full table scan을 발생시키지 않음을 보여주지만, 그것은 오직 하나의 파티션만을 사용할 것입니다.
기본키(primary key)와 인덱스의 사용
대용량 데이터셋을 가지고 있는 분할된 테이블을 테스트하는 동안에 발생한 문제 중에 한 가지는 기대했던 성능향상을 얻을 수 없었다는 점입니다. Robin Schumacher의 첫 번째 파티셔닝에 대한 글에서 예제들은 기본키들(primary key)을 가지고 있지 않은 테이블들을 비교합니다. 분할된 테이블은 확실하게 경쟁에서 승리합니다.
이는 일반적으로 발생할 수 있는 상황이기 때문에, 현재 테스트에서는 기본 키를 가지고 있는 테이블을 이용하여 시작했습니다.
그리하여 처음 1억 1천 3백만개의 열을 가지고 있는 데이터를 처음 비교했을 때, 기본 키를 가지고 있는 테이블을 사용했습니다.
인덱스가 메모리에 맞지 않을 정도로 큰 테이블상의 기본 키는 효과가 없습니다. 이와 같은 테이블로부터 레코드를 얻는다는 것은 빈번하게 디스크에 접근함을 의미합니다. 당신의 성능은 당신의 디스크와 프로세스의 속도에 전적으로 의존합니다.
데이터 웨어하우징 구성에서 다른 사람들이 하는 것을 고찰해보면, 인덱스 사용 없이 대용량 데이터 웨어하우징 설정을 구성하는 공통적인 습관을 발견할 수 있습니다.
이번에는 기본키를 가지고 있는 분할된 테이블과 기본키를 가지고 있지 않은 분할된 테이블 간의 성능비교를 또한 볼 것입니다.
테스트 방법
이번테스트에서 MyISAM, InnoDB, Archive 스트리지 엔진을 사용하는 대용량의 데이터 세트의 성능을 비교하고자 합니다.
각각의 스토리지 엔진에서 기본키를 갖는 (archive 스토리지 엔진은 제외) 분할되지 않는 테이블 한 개와 년도로 분할되는 테이블 한 개, 월로 분할이 되는 테이블 한 개를 생성하였습니다.
각각의 구성은 한 개의 테이블을 가지는 단 하나의 데이터베이스를 가지고 있는 MySQL 서버의 dedicated instance 상에서 테스트 되었습니다.
각각의 엔진에서 서버를 시작하고, 질의 모음을 실행하여 그것들의 결과를 기록한 다음에 서버를 중지하였습니다.
MySQL Sandbox를 사용하여 서버인스턴스를 생성하였습니다.
ID storage partitioned records size notes loading time (*)
1 MyISAM none 113 Mil 13 GB with PK 37 min
2 MyISAM by month 113 Mil 8 GB without PK 19 min
3 MyISAM by year 113 Mil 8 GB without PK 18 min
4 InnoDB none 113 Mil 16 GB with PK 63 min
5 InnoDB by month 113 Mil 10 GB without PK 59 min
6 InnoDB by year 113 Mil 10 GB without PK 57 min
7 Archive none 113 Mil 1.8 GB no keys 20 min
8 Archive by month 113 Mil 1.8 GB no keys 21 min
9 Archive by year 113 Mil 1.8 GB no keys 20 min
loading times on a dual-Xeon server.
큰용량과 작은용량의 데이터 세트에서 partitions의 효과를 비교하기 위해 9개나 더 instance를 생성하였고, 각각의 instance는 결과값이 각각 다르게 나오는지를 보기 위하여 2GB가 약간 안 되는 데이터를 포함합니다.
더 나은 비교를 위해 해당결과는 게다가 다른 데이터베이스 instance에서 기록되었습니다.
따라서 테스트 동안 아무 때나 동작하고 있는 instance가 2개 있었습니다. 결과를 가지고 있는 하나와 테스트되고 있는 하나.
이 테스트에서 사용된 질의는 두 가지 종류입니다.
* 총계 질의
SELECT COUNT(*)
FROM table_name
WHERE date_column BETWEEN start_date and end_date
* 특정 레코드 호출
SELECT column_list
FROM table_name
WHERE column1 = x and column2 = y and column3 = z
각각의 질의에서 다른 데이터의 범위를 가지는 쿼리들을 생성하였습니다. 각각의 범위에서 인접데이터 상의 추가적인 질의들의 세트를 생성하였습니다. 각각의 범위에서 첫 번째 질의는 맨 처음 그러한 범위가 적중했을 때를 의미하는 cold입니다. 동일한 범위에서 다음 질의는 해당범위가 이미 적어도 부분적으로 캐쉬 되었음을 의미하는 warm입니다.
이 테스트에서 사용된 질의들의 목록은 Forge에 있습니다.
결과
* 기본키를 가지고 있는 분할된 테이블
잘못된 시도부터 시작해보겠습니다.
테스트의 첫 번째 bench는 원래 테이블에서 사용된 것과 같은 복합적인 기본키를 가진 분할된 테이블을 사용하였습니다. 기본키의 총 용량은 5.5GB입니다. 성능향상은 커녕 기본키는 동작을 느리게 했습니다.
분할된 테이블에서의 질의들은 램(4GB)의 크기에 맞지 않는 기본키를 통한 인덱스 검색으로 인해 형편없이 작동했습니다.
이것은 기억하고 있어야 할 교훈입니다. 파티션들은 매우 유용하지만 올바르게 쓰여져야 합니다.
status myisam unpart myisam month myisam year
cold 2.6574570285714 2.9169642 3.0373419714286
warm 2.5720722571429 3.1249698285714 3.1294000571429
해결방법은 바로 눈 앞에 있었습니다만, 처음에는 그것을 보려고 하지 않았습니다. ARCHIVE 스토리지 엔진을 사용하여 같은 질의를 했을 때 결과를 보시기 바랍니다.
status archive unpart archive month archive year
cold 249.849563 1.2436211111111 12.632532527778
warm 235.814442 1.0889786388889 12.600520777778
달에 의해 분할된 테이블에 대한 결과는 MyISAM 테이블을 통해 얻는 결과보다 훨씬 나았습니다. 이 사실에 대해서는 나중에 더 이야기 하도록 하겠습니다.
* 기본키를 가지고 있지 않는 분할된 테이블
기본키를 가지고 있는 분할된 테이블에서의 성능이 워낙 안 좋았기 때문에, 서두르기로 결심하고 기본키를 제거하기로 했습니다.
중요한 원인은 이 테이블에서 기본키는 사용할 수 있는 키버퍼보다 크다는 것입니다.(또한 이 경우에서는 사용할 수 있는 총 RAM용량보다도 큽니다) 그러므로 키를 이용한 어떠한 검색은 디스크를 사용하게 될 것입니다.
새로운 접근법은 성공했습니다. 기본키 없이 단지 파티션만 사용했을 때, 원하는 결과를 얻었습니다. 성능에서 상당한 향상이 나타났습니다. 달로 분할된 테이블들은 70~90% 성능상의 이득을 얻었습니다.
status myisam unpart myisam month myisam year
cold 2.6864490285714 0.64206445714286 2.6343286285714
warm 2.8157905714286 0.18774977142857 2.2084743714286
차이점을 좀 더 부각시키기 위해, partition pruning의 장점을 얻을 수 있는 2개의 무거운 질의로 테스트하였습니다.
# query 1 -- aggregate by year
SELECT year(FlightDate) as y, count(*)
FROM flightstats
WHERE FlightDate BETWEEN "2001-01-01" and "2003-12-31"
GROUP BY y
# query 2 -- aggregate by month
SELECT date_format(FlightDate,"%Y-%m") as m, count(*)
FROM flightstats
WHERE FlightDate BETWEEN "2001-01-01" and "2003-12-31"
GROUP BY m
달로 분할된 테이블에서는 30~60%의 성능향상이, 년도로 분할되어진 테이블에서는 15~30%의 성능향상이 결과로 나타났습니다.
프로세서 요인
다른 서버에서 테스트를 시행하였을 때, 약간의 어려움이 발생하였습니다. 상기의 테스트는 Intel DualCore 2.3 Ghz CPU를 사용하는 컴퓨터에서 수행되었습니다. 새로운 서버는 dual Xeon 2.66 Ghz를 사용하여 좀 더 빠릅니다. 위의 테스트를 재실행하였더니 이런 놀라운 결과가 나왔습니다.
status myisam unpart myisam month myisam year
cold 0.05106342857142 0.6577062 1.6663527428571
warm 0.063645485714286 0.1093724 1.2369152285714
기본키를 가지고 있는 원본 테이블이 분할된 테이블 보다 더 빠릅니다. 분할된 테이블에서 얻었던 시간들은 느렸던 서버에서 얻었던 것과 같았지만, 원본테이블의 성능은 향상되어 파티션을 불필요하게 만들었습니다.
무엇을 해야 할까요? 이 서버에서는 인덱스의 이점을 또한 가지는 것으로 보이기 때문에, 분할된 컬럼상의 인덱스를 분할된 테이블들에 추가하였습니다.
# original table
create table flightstats (
AirlineID int not null,
UniqueCarrier char(3) not null,
Carrier char(3) not null,
FlightDate date not null,
FlightNum char(5) not null,
TailNum char(8) not null,
ArrDelay double not null,
ArrTime datetime not null,
DepDelay double not null,
DepTime datetime not null,
Origin char(3) not null,
Dest char(3) not null,
Distance int not null,
Cancelled char(1) default 'n',
primary key (FlightDate, AirlineID, Carrier, UniqueCarrier, FlightNum, Origin, DepTime, Dest)
)
# partitioned tables
create table flightstats (
AirlineID int not null,
UniqueCarrier char(3) not null,
Carrier char(3) not null,
FlightDate date not null,
FlightNum char(5) not null,
TailNum char(8) not null,
ArrDelay double not null,
ArrTime datetime not null,
DepDelay double not null,
DepTime datetime not null,
Origin char(3) not null,
Dest char(3) not null,
Distance int not null,
Cancelled char(1) default 'n',
KEY (FlightDate)
)
PARTITION BY RANGE ...
35%의 성능향상으로 결과는 아주 많이 만족스러웠습니다.
status myisam unpart myisam month myisam year
cold 0.075289714285714 0.025491685714286 0.072398542857143
warm 0.064401257142857 0.031563085714286 0.056638085714286
학습된 교훈
파티션을 테스트하는 것은 힘든 경험입니다. 성능향상을 얻는 것은 수월한 작업이 아닙니다. 제가 사실이라고 보는 것은 힘 안 드는 변화는 긴 시행착오가 된다는 것입니다.
만병통치약은 없습니다.
테이블에 파티션의 변화를 적용하는 것이 성능향상을 보장하지는 않습니다. 이득은 몇 가지 요소들에 의존하고 있습니다.
* 파티셔닝에서 사용하는 컬럼
* native 컬럼의 타입이 정수형이 아닌 경우, 파티셔닝에서 사용하는 함수
* 서버의 속도
* 메모리의 총합(RAM)
어떤 것도 당연한 일로 생각되어지지 않습니다.
실무에 변화를 적용하기 전에 벤치마크를 수행
당신의 데이터베이스 사용방법에 따라, 당신은 엄청난 성능상의 이득을 얻거나, 전혀 어떠한 이득도 얻지 못 할 수도 있습니다. 주의하지 않는다면, 또한 성능저하를 가질 수 있습니다.
이것을 고려하십시오: 당신의 질의가 항상 데이터의 범위를 포함하고 있다면, 월 단위 데이터에 의해 분할된 테이블은 당신에게 꿈과 같은 성능상의 이득을 줄 수 있습니다. 만약에 당신의 질의가 데이터의 범위를 포함하고 있지 않다면 그것은 full table scan이 될 수 있습니다.
Archive 테이블은 훌륭한 절충안이 될 수 있습니다.
Archive 테이블은 분할될 때에 커다란 성능상의 이득을 달성합니다. 또 다시 이건 당신의 사용에 좌우됩니다. 파티셔닝을 사용하지 않을 때, Archive 테이블에서의 어떠한 질의는 full table scan입니다. 만약에 당신이 변하지 않는 역사적인 데이터를 가지고 있고, 범위에 의한 통계적인 질의를 할 필요가 있다면, Archive 엔진은 탁월한 선택입니다. 그것은 원본의 10~20% 저장용량을 사용하고 원래 MyISAM 이나 InnoDB 테이블의 질의모음보다 더 잘 실행할 수 있습니다. 다시 그것은 벤치마크의 모든 문제입니다. 잘 개조되어 분할된 MyISAM 테이블은 유사한 Archive 테이블보다 훨씬 좋게 작동합니다만, 10배정도 많은 저장용량이 필요합니다.
요약
파티셔닝은 대용량 데이터베이스에서 성능상의 이점을 줄 수 있는 실마리입니다. 대용량이라는 말의 정의는 당신의 기준에서 하드웨어에 의존적입니다.
맹목적인 파티션 적용은 성능 향상을 이루는 데에 어떠한 책임도 지지 않지만, 앞서 나온 일부 벤치마크들의 도움으로 그것이 당신에게 맞는 해결책이 될 수도 있습니다.
이 문서는 파티션 사용의 이점을 보여주는 MySQL 5.1에서 대용량 데이터베이스 성능을 어떻게 측정할 것인가에 대해 설명하고 있습니다.
테스트 데이터베이스는 미국 교통통계청에서 발표된 자료를 사용하였습니다. 현재는 약 1억 1천 3백만건 이상의 레코드로 구성되어 있습니다. (7.5 GB의 데이터와 5.2 GB의 인덱스를 가짐)
데이터 수집 및 불러오기
이번 테스트에는 미국 교통통계청의 자료를 사용하였습니다. 이 데이터는 CVS파일로 제공되었으며, 1987년 6월부터 2007년 6월까지의 자료입니다. (기록시점 기준)
데이터 수집, 데이터 구조의 설정, 데이터 불러오기 등의 세부사항은 MySQL Forge에 설명되어 있습니다.
문제점 열거
이번 테스트는 데이터베이스의 용량이 서버상의 물리적인 메모리(RAM)의 용량보다 크고, 마찬가지로 인덱스의 크기도 RAM보다 큰 데이터베이스에 영향을 미칩니다.
이번 테스트에 사용된 서버는 4GB의 램을 가지며, 인덱스의 크기는 5GB를 넘습니다.
이러한 문제의 근거는 데이터 웨어하우스(Data warehouse)가 당신이 어떻게든지 설치할 수 있는 램의 적당한 용량을 초과하여 수 테라바이트의 용량을 차지하는 데이터를 수집하기 때문입니다.
일반적인 데이터베이스 저장방식 에서는(특히 OLTP에서), 인덱스는 메모리(RAM)에 캐쉬되어 레코드 검색시 빠른 속도를 보장합니다. 데이터가 사용가능한 메모리(RAM)에서 저장될 수 없는 크기에 도달하였을 경우, 우리는 다른 접근 방법을 사용할 필요가 있습니다.
MySQL 5.1에서는 파티셔닝(partitioning)을 주요 기능 중에 하나로 제공하는데, 이는 검색속도 향상을 위해 하나의 테이블을 논리적인 부분으로 나누는 기술입니다.
MySQL 5.1에서 파티션의 사용은 원리상으로는 단순해보이지만, 최고의 성능을 내기위해 데이터를 설정하는 부분에서 주의해야 할 몇 가지 까다로운 점이 있습니다. 이 문서에서는 이러한 위험요소들을 설명하고, 최적의 성능을 내기위한 실용적인 조언을 제공하려고 합니다.
파티셔닝 개요
MySQL 5.1에서 파티셔닝의 이행은 무척 단순합니다.
당신은 다음과 같은 기준으로 데이터를 분할할 수 있습니다.
* 범위(range)
* 목록(list)
* 해쉬(hash)
* 키(key)
당신의 요구에 따라, 다른 파티션 종류를 선택할 수 있습니다. 이 문서에서 우리는 아마도 데이터 웨어하우싱(data warehousing)에서 가장 흥미있는 범위 파티션닝(range partitioning)에 대해 전념하고 있습니다.
MySQL 파티션기능을 효과적으로 사용하기를 원한다면, MySQL 파티셔닝에는 주의해야 하는 약간의 제약이 있습니다.
* 파티션의 값은 정수형이어야 합니다. (integer)
* 만약 테이블이 unique 키와 primary 키를 가지고 있다면, 분할되는 컬럼(the partitioning column)은 해당키의 일부가 되어야 합니다.
첫 번째 제약은 당신의 설계 결정에 가장 큰 영향을 미칩니다. 만약에 파티셔닝을 사용하려는 컬럼이 정수형이 아니라면, 당신은 그것을 변형할 수 있는 함수를 사용해야 합니다. 약간의 추가적인 제약이 매뉴얼에 서술되어 있듯이 파티션에 적용되지만, 여기서 그것은 관심 밖의 일입니다.
파티셔닝에 대해 좀 아셨나요?
데이터 컬럼 사용하기
이번 단락과 관련되는 것은 파티셔닝 되는 데이터 컬럼의 사용입니다. native data type은 지원하지 않기 때문에, 데이터를 정수형으로 변환해야 합니다. 허용된 함수의 목록을 더하여, 단지 두 개의 데이터 함수만이 파티션 프루닝(partiton pruning)을 유발할 수 있다는 사실을 고려해야 합니다. 따라서 데이터 컬럼을 조작해야 한다면, YEAR 또는 TO_DAYS 중에 하나를 사용할 필요가 있습니다.
YEAR() 함수를 사용할 경우, 파티셔닝은 용이하고, 쉽게 읽을 수 있으며, 수월합니다.
CREATE TABLE by_year (
d DATE
)
PARTITION BY RANGE (YEAR(d))
(
PARTITION P1 VALUES LESS THAN (2001),
PARTITION P2 VALUES LESS THAN (2002),
PARTITION P3 VALUES LESS THAN (2003),
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
달(month)을 기준으로 파티셔닝을 하는 것은 좀 더 신중해야 합니다. 다음과 같은 두가지 이유로 MONTH()를 사용할 수 없습니다.
* MONTH의 경우 년도(year)를 포함하지 않기 때문에, 파티션의 개수가 12개로 제한됩니다.
* MONTH 함수는 partition pruning에 최적화되지 않았기 때문에, 끔직할 정도의 성능을 보입니다.
따라서 partition pruning에 최적화되어 있는 TO_DAYS와 같은 다른 함수를 사용하는 것이 필요합니다.
CREATE TABLE by_month (
d DATE
)
PARTITION BY RANGE (TO_DAYS(d))
(
PARTITION P1 VALUES LESS THAN (to_days('2001-02-01')), -- January
PARTITION P2 VALUES LESS THAN (to_days('2001-03-01')), -- February
PARTITION P3 VALUES LESS THAN (to_days('2001-04-01')), -- March
PARTITION P4 VALUES LESS THAN (MAXVALUE)
)
년도(year)에 의하여 분할되어진 것에 비해 읽을 때 이미 저것은 덜 분명합니다. 잘못된 점은 서버가 당신의 값들을 계속 유지하지는 않지만, 일치하는 정수값들만은 저장을 합니다. 다음은 위 테이블에서 당신이 얻을 수 있는 내용입니다.
show create table by_month\G
*************************** 1. row ***************************
Table: by_month
Create Table: CREATE TABLE `by_month`
`d` date DEFAULT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1 /*!50100
PARTITION BY RANGE (TO_DAYS(d))
(
PARTITION P1 VALUES LESS THAN (730882) ENGINE = MyISAM,
PARTITION P2 VALUES LESS THAN (730910) ENGINE = MyISAM,
PARTITION P3 VALUES LESS THAN (730941) ENGINE = MyISAM,
PARTITION P4 VALUES LESS THAN MAXVALUE ENGINE = MyISAM) */
만약 당신이 각각의 파티션이 의미하는 것이 어떤 것인지 읽기 쉬운 참조를 갖길 원하신다면, 월(month)별로 분할된 테이블을 생성하는데 사용되었던 스크립트의 복사본을 저장하실 것을 권해드립니다.
함수에 의한 분할, 컬럼에 의한 검색
데이터 컬럼을 이용하는 분할된 테이블들을 사용할 때 발생되는 하나의 공통적인 실수는 분할에서 사용되었던 동일한 함수를 통해 질의하는 것입니다.
예를 들어 만약 당신의 테이블이 다음과 같은 절(clause)로 생성되었고,
partition by range( YEAR(d) )
YEAR와 TO_DAYS 가 partition pruning에 최적이 되었다는 것을 당신이 들었다면, 이와 같은 질의(query)를 사용하는 것이 논리적으로 보입니다.
SELECT count(*) FROM by_year
WHERE YEAR(d) = 2000; # <-- ERROR !!
EXPLAIN 구문으로부터 당신이 볼 수 있는 것처럼, partition pruning은 실행되지 않습니다.
explain partitions select count(*) from by_year where year(d) = 2001\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: by_year
partitions: P1,P2,P3,P4
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 30123
Extra: Using where
해당질의는 전체테이블을 스캔합니다(full table scan). “partition pruning에 최적화”되었다는 의미는 WHERE 절에서 저 컬럼이 사용되어질 때, 검색은 파티션들을 사용할 것이라는 뜻입니다.
이것이 올바른 질의방법입니다.
SELECT count(*) FROM by_year
WHERE d BETWEEN '2001-01-01' and '2001-12-31';
그리고 이제야 partition pruning이 사용됩니다.
explain partitions select count(*)
from by_year
where d between '2001-01-01' and '2001-12-31'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: by_year
partitions: P2
type: system
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 30123
Extra:
이것은 해당질의가 full table scan을 발생시키지 않음을 보여주지만, 그것은 오직 하나의 파티션만을 사용할 것입니다.
기본키(primary key)와 인덱스의 사용
대용량 데이터셋을 가지고 있는 분할된 테이블을 테스트하는 동안에 발생한 문제 중에 한 가지는 기대했던 성능향상을 얻을 수 없었다는 점입니다. Robin Schumacher의 첫 번째 파티셔닝에 대한 글에서 예제들은 기본키들(primary key)을 가지고 있지 않은 테이블들을 비교합니다. 분할된 테이블은 확실하게 경쟁에서 승리합니다.
이는 일반적으로 발생할 수 있는 상황이기 때문에, 현재 테스트에서는 기본 키를 가지고 있는 테이블을 이용하여 시작했습니다.
그리하여 처음 1억 1천 3백만개의 열을 가지고 있는 데이터를 처음 비교했을 때, 기본 키를 가지고 있는 테이블을 사용했습니다.
인덱스가 메모리에 맞지 않을 정도로 큰 테이블상의 기본 키는 효과가 없습니다. 이와 같은 테이블로부터 레코드를 얻는다는 것은 빈번하게 디스크에 접근함을 의미합니다. 당신의 성능은 당신의 디스크와 프로세스의 속도에 전적으로 의존합니다.
데이터 웨어하우징 구성에서 다른 사람들이 하는 것을 고찰해보면, 인덱스 사용 없이 대용량 데이터 웨어하우징 설정을 구성하는 공통적인 습관을 발견할 수 있습니다.
이번에는 기본키를 가지고 있는 분할된 테이블과 기본키를 가지고 있지 않은 분할된 테이블 간의 성능비교를 또한 볼 것입니다.
테스트 방법
이번테스트에서 MyISAM, InnoDB, Archive 스트리지 엔진을 사용하는 대용량의 데이터 세트의 성능을 비교하고자 합니다.
각각의 스토리지 엔진에서 기본키를 갖는 (archive 스토리지 엔진은 제외) 분할되지 않는 테이블 한 개와 년도로 분할되는 테이블 한 개, 월로 분할이 되는 테이블 한 개를 생성하였습니다.
각각의 구성은 한 개의 테이블을 가지는 단 하나의 데이터베이스를 가지고 있는 MySQL 서버의 dedicated instance 상에서 테스트 되었습니다.
각각의 엔진에서 서버를 시작하고, 질의 모음을 실행하여 그것들의 결과를 기록한 다음에 서버를 중지하였습니다.
MySQL Sandbox를 사용하여 서버인스턴스를 생성하였습니다.
ID storage partitioned records size notes loading time (*)
1 MyISAM none 113 Mil 13 GB with PK 37 min
2 MyISAM by month 113 Mil 8 GB without PK 19 min
3 MyISAM by year 113 Mil 8 GB without PK 18 min
4 InnoDB none 113 Mil 16 GB with PK 63 min
5 InnoDB by month 113 Mil 10 GB without PK 59 min
6 InnoDB by year 113 Mil 10 GB without PK 57 min
7 Archive none 113 Mil 1.8 GB no keys 20 min
8 Archive by month 113 Mil 1.8 GB no keys 21 min
9 Archive by year 113 Mil 1.8 GB no keys 20 min
loading times on a dual-Xeon server.
큰용량과 작은용량의 데이터 세트에서 partitions의 효과를 비교하기 위해 9개나 더 instance를 생성하였고, 각각의 instance는 결과값이 각각 다르게 나오는지를 보기 위하여 2GB가 약간 안 되는 데이터를 포함합니다.
더 나은 비교를 위해 해당결과는 게다가 다른 데이터베이스 instance에서 기록되었습니다.
따라서 테스트 동안 아무 때나 동작하고 있는 instance가 2개 있었습니다. 결과를 가지고 있는 하나와 테스트되고 있는 하나.
이 테스트에서 사용된 질의는 두 가지 종류입니다.
* 총계 질의
SELECT COUNT(*)
FROM table_name
WHERE date_column BETWEEN start_date and end_date
* 특정 레코드 호출
SELECT column_list
FROM table_name
WHERE column1 = x and column2 = y and column3 = z
각각의 질의에서 다른 데이터의 범위를 가지는 쿼리들을 생성하였습니다. 각각의 범위에서 인접데이터 상의 추가적인 질의들의 세트를 생성하였습니다. 각각의 범위에서 첫 번째 질의는 맨 처음 그러한 범위가 적중했을 때를 의미하는 cold입니다. 동일한 범위에서 다음 질의는 해당범위가 이미 적어도 부분적으로 캐쉬 되었음을 의미하는 warm입니다.
이 테스트에서 사용된 질의들의 목록은 Forge에 있습니다.
결과
* 기본키를 가지고 있는 분할된 테이블
잘못된 시도부터 시작해보겠습니다.
테스트의 첫 번째 bench는 원래 테이블에서 사용된 것과 같은 복합적인 기본키를 가진 분할된 테이블을 사용하였습니다. 기본키의 총 용량은 5.5GB입니다. 성능향상은 커녕 기본키는 동작을 느리게 했습니다.
분할된 테이블에서의 질의들은 램(4GB)의 크기에 맞지 않는 기본키를 통한 인덱스 검색으로 인해 형편없이 작동했습니다.
이것은 기억하고 있어야 할 교훈입니다. 파티션들은 매우 유용하지만 올바르게 쓰여져야 합니다.
status myisam unpart myisam month myisam year
cold 2.6574570285714 2.9169642 3.0373419714286
warm 2.5720722571429 3.1249698285714 3.1294000571429
해결방법은 바로 눈 앞에 있었습니다만, 처음에는 그것을 보려고 하지 않았습니다. ARCHIVE 스토리지 엔진을 사용하여 같은 질의를 했을 때 결과를 보시기 바랍니다.
status archive unpart archive month archive year
cold 249.849563 1.2436211111111 12.632532527778
warm 235.814442 1.0889786388889 12.600520777778
달에 의해 분할된 테이블에 대한 결과는 MyISAM 테이블을 통해 얻는 결과보다 훨씬 나았습니다. 이 사실에 대해서는 나중에 더 이야기 하도록 하겠습니다.
* 기본키를 가지고 있지 않는 분할된 테이블
기본키를 가지고 있는 분할된 테이블에서의 성능이 워낙 안 좋았기 때문에, 서두르기로 결심하고 기본키를 제거하기로 했습니다.
중요한 원인은 이 테이블에서 기본키는 사용할 수 있는 키버퍼보다 크다는 것입니다.(또한 이 경우에서는 사용할 수 있는 총 RAM용량보다도 큽니다) 그러므로 키를 이용한 어떠한 검색은 디스크를 사용하게 될 것입니다.
새로운 접근법은 성공했습니다. 기본키 없이 단지 파티션만 사용했을 때, 원하는 결과를 얻었습니다. 성능에서 상당한 향상이 나타났습니다. 달로 분할된 테이블들은 70~90% 성능상의 이득을 얻었습니다.
status myisam unpart myisam month myisam year
cold 2.6864490285714 0.64206445714286 2.6343286285714
warm 2.8157905714286 0.18774977142857 2.2084743714286
차이점을 좀 더 부각시키기 위해, partition pruning의 장점을 얻을 수 있는 2개의 무거운 질의로 테스트하였습니다.
# query 1 -- aggregate by year
SELECT year(FlightDate) as y, count(*)
FROM flightstats
WHERE FlightDate BETWEEN "2001-01-01" and "2003-12-31"
GROUP BY y
# query 2 -- aggregate by month
SELECT date_format(FlightDate,"%Y-%m") as m, count(*)
FROM flightstats
WHERE FlightDate BETWEEN "2001-01-01" and "2003-12-31"
GROUP BY m
달로 분할된 테이블에서는 30~60%의 성능향상이, 년도로 분할되어진 테이블에서는 15~30%의 성능향상이 결과로 나타났습니다.
프로세서 요인
다른 서버에서 테스트를 시행하였을 때, 약간의 어려움이 발생하였습니다. 상기의 테스트는 Intel DualCore 2.3 Ghz CPU를 사용하는 컴퓨터에서 수행되었습니다. 새로운 서버는 dual Xeon 2.66 Ghz를 사용하여 좀 더 빠릅니다. 위의 테스트를 재실행하였더니 이런 놀라운 결과가 나왔습니다.
status myisam unpart myisam month myisam year
cold 0.05106342857142 0.6577062 1.6663527428571
warm 0.063645485714286 0.1093724 1.2369152285714
기본키를 가지고 있는 원본 테이블이 분할된 테이블 보다 더 빠릅니다. 분할된 테이블에서 얻었던 시간들은 느렸던 서버에서 얻었던 것과 같았지만, 원본테이블의 성능은 향상되어 파티션을 불필요하게 만들었습니다.
무엇을 해야 할까요? 이 서버에서는 인덱스의 이점을 또한 가지는 것으로 보이기 때문에, 분할된 컬럼상의 인덱스를 분할된 테이블들에 추가하였습니다.
# original table
create table flightstats (
AirlineID int not null,
UniqueCarrier char(3) not null,
Carrier char(3) not null,
FlightDate date not null,
FlightNum char(5) not null,
TailNum char(8) not null,
ArrDelay double not null,
ArrTime datetime not null,
DepDelay double not null,
DepTime datetime not null,
Origin char(3) not null,
Dest char(3) not null,
Distance int not null,
Cancelled char(1) default 'n',
primary key (FlightDate, AirlineID, Carrier, UniqueCarrier, FlightNum, Origin, DepTime, Dest)
)
# partitioned tables
create table flightstats (
AirlineID int not null,
UniqueCarrier char(3) not null,
Carrier char(3) not null,
FlightDate date not null,
FlightNum char(5) not null,
TailNum char(8) not null,
ArrDelay double not null,
ArrTime datetime not null,
DepDelay double not null,
DepTime datetime not null,
Origin char(3) not null,
Dest char(3) not null,
Distance int not null,
Cancelled char(1) default 'n',
KEY (FlightDate)
)
PARTITION BY RANGE ...
35%의 성능향상으로 결과는 아주 많이 만족스러웠습니다.
status myisam unpart myisam month myisam year
cold 0.075289714285714 0.025491685714286 0.072398542857143
warm 0.064401257142857 0.031563085714286 0.056638085714286
학습된 교훈
파티션을 테스트하는 것은 힘든 경험입니다. 성능향상을 얻는 것은 수월한 작업이 아닙니다. 제가 사실이라고 보는 것은 힘 안 드는 변화는 긴 시행착오가 된다는 것입니다.
만병통치약은 없습니다.
테이블에 파티션의 변화를 적용하는 것이 성능향상을 보장하지는 않습니다. 이득은 몇 가지 요소들에 의존하고 있습니다.
* 파티셔닝에서 사용하는 컬럼
* native 컬럼의 타입이 정수형이 아닌 경우, 파티셔닝에서 사용하는 함수
* 서버의 속도
* 메모리의 총합(RAM)
어떤 것도 당연한 일로 생각되어지지 않습니다.
실무에 변화를 적용하기 전에 벤치마크를 수행
당신의 데이터베이스 사용방법에 따라, 당신은 엄청난 성능상의 이득을 얻거나, 전혀 어떠한 이득도 얻지 못 할 수도 있습니다. 주의하지 않는다면, 또한 성능저하를 가질 수 있습니다.
이것을 고려하십시오: 당신의 질의가 항상 데이터의 범위를 포함하고 있다면, 월 단위 데이터에 의해 분할된 테이블은 당신에게 꿈과 같은 성능상의 이득을 줄 수 있습니다. 만약에 당신의 질의가 데이터의 범위를 포함하고 있지 않다면 그것은 full table scan이 될 수 있습니다.
Archive 테이블은 훌륭한 절충안이 될 수 있습니다.
Archive 테이블은 분할될 때에 커다란 성능상의 이득을 달성합니다. 또 다시 이건 당신의 사용에 좌우됩니다. 파티셔닝을 사용하지 않을 때, Archive 테이블에서의 어떠한 질의는 full table scan입니다. 만약에 당신이 변하지 않는 역사적인 데이터를 가지고 있고, 범위에 의한 통계적인 질의를 할 필요가 있다면, Archive 엔진은 탁월한 선택입니다. 그것은 원본의 10~20% 저장용량을 사용하고 원래 MyISAM 이나 InnoDB 테이블의 질의모음보다 더 잘 실행할 수 있습니다. 다시 그것은 벤치마크의 모든 문제입니다. 잘 개조되어 분할된 MyISAM 테이블은 유사한 Archive 테이블보다 훨씬 좋게 작동합니다만, 10배정도 많은 저장용량이 필요합니다.
요약
파티셔닝은 대용량 데이터베이스에서 성능상의 이점을 줄 수 있는 실마리입니다. 대용량이라는 말의 정의는 당신의 기준에서 하드웨어에 의존적입니다.
맹목적인 파티션 적용은 성능 향상을 이루는 데에 어떠한 책임도 지지 않지만, 앞서 나온 일부 벤치마크들의 도움으로 그것이 당신에게 맞는 해결책이 될 수도 있습니다.
출처 : http://www.waitfor.com/phpBB3/viewtopic.php?f=57&t=309
'개발 > MySql' 카테고리의 다른 글
| [MYSQL] Database 용량 확인 (0) | 2010.12.29 |
|---|---|
| [MYSQL] 'table is full' 에러 날 때 (0) | 2010.12.23 |
| [MYSQL] 상태 확인 (0) | 2010.11.01 |
| [MYSQL] SQLDUMP (0) | 2010.10.28 |
| [MYSQL] DB 생성 및 계정 설정 (0) | 2010.10.28 |
