메모장입니다.

현재 oracle을 mysql로 변환중인 업무를 진행중인데 oracle의 view 구문중 원래도 문제가 있는 구문이지만 mysql에서는 더욱 최악의 속도를 발생시켜서 고민을 하고 있던 와중에 발견한게 mysql 5.1에서도 oracle 처럼 파티셔닝을 구현할수 있다는 것이였다. 아래 내용을 잘 이해하면 앞으로 대용량 db를 처리하는데 상당한 도움이 될것이 분명하니 열심히 한번 해보자

MySQL Partitioning

정보는 흘러 넘치고 분석해야 할 데이터도 계속 커지면서 많은 데이터를 효율적으로 운영하기 위한 노력들은 계속 진행 중이다. 이 같은 추세에 발 맞춰 최근에는 어플라이언스 시장도 국내에서 계속 커지고 있다. 대용량 데이터를 처리하기 위해 테이블 파티셔닝은 DBMS의 필수 기능으로 꼽히는데 MySQL이 테이블 파티셔닝을 지원하지 않는 것은 그 동안 MySQL을 대용량 DB에서 쓰기 어려웠던 이유이기도 했다. Merge 테이블을 이용해 일부 비슷한 효과를 낼 수는 있었지만, 정식으로 테이블 파티셔닝을 지원한 것은 5.1 버전부터다. 지금부터 MySQL Partitioning에 대한 이야기를 시작해 보자.

5.1버전의 테이블 파티셔닝 지원은 타 상용 DBMS에 비해 아직은 부족한 수준임을 감안해도, 이제껏 MySQL의 파티셔닝 기능에 목말라 있던 사용자들에겐 단비와 같은 소식이다. 일반적으로 테이블을 파티셔닝한다면 다음과 같은 기대를 가지고 있을 것이다.

- 특정 데이터 집합의 추가/삭제가 간편하다.
- 데이터 검색 시 특정 파티션만 읽어서 속도를 향상시킬 수 있다.
- 병렬 처리가 가능하다.
- 한 테이블에 너무 많은 데이터가 입력되었을 시 발생하는 속도 저하를 방지할 수 있다.

그렇다면 위와 같은 기대를 어느 정도 충족해 주고 있는지 MySQL 파티셔닝 사용법과 특징에 대해 살펴보자. 먼저 자신이 사용하고 있는 MySQL에서 파티셔닝을 지원하는지 확인하는 방법은 <리스트 1>과 같다.

파티션 타입

MySQL에서 지원하는 파티셔닝 종류에 대해 살펴보자. 
 
기본적으로 MySQL은 RANGE, LIST, HASH, KEY 파티셔닝을 지원한다. 많이 사용되는 파티셔닝은 모두 지원한다고 보면 되겠다.

RANGE

특정 범위 단위로 파티셔닝할 때 적용되는 가장 흔히 사용하는 파티셔닝 타입 중 하나이다. Create table 구문 뒤의 PARTITION BY RANGE() 절 안에는 파티셔닝할 컬럼을, VALUE LESS THAN() 절 안에는 해당 파티션의 MAX 값을 기술하면 된다. <리스트 2>를 예로 들어보자.

 
이 테이블을 store_id 컬럼에 의해 Range 파티셔닝하고 싶다면 <리스트 3>과 같이 정의한다.

 
데이터가 들어갈 파티션은 VALUES LESS THAN 구문에 의해 결정된다. <리스트 3>의 예제에서는 store_id가 1~5까지는 p0 파티션, 6~10까지는 p1 파티션으로 들어간다. 그렇다면 store_id에 21 값이 들어오면 어떻게 될까? 이때는 해당 값이 들어갈 파티션이 지정되어 있지 않기 때문에 에러가 발생한다. 따라서 어떤 값이 들어오더라도 에러가 나지 않도록 <리스트 4>와 같이 MAXVALUE를 지정할 수 있다.

 
21보다 크거나 같은 값이 들어오면 p4 파티션에 데이터가 들어갈 것이다. MAXVALUE는 이 타입이 가질 수 있는 가장 큰 값을 의미하고 제일 마지막 파티션에 기술해야 하며 오직 하나의 파티션에만 사용할 수 있다. 그에 반해 NULL 값은 다른 값보다 작은 값으로 인식되어 제일 작은 값으로 정의된 파티션으로 들어간다. 파티션 기준이 되는 컬럼은 VALUES LESS THAN 의미처럼 크기 비교가 가능해야 한다. 컬럼이 문자인 경우에도 문자 연산을 통해 숫자만을 걸러 낼 수 있다면 Range 파티셔닝이 가능하다.

Ex) PARTITION BY RANGE ( YEAR(separated) )

Range 파티셔닝은 다음과 같은 경우 유용하다.

- 대량의 과거 데이터를 지우기를 원할 때 : delete로 많은 데이터를 지운다면 시간이 오래 걸리지만 파티션별로 drop하면 순식간에 데이터 삭제가 가능하다

ALTER TABLE employees DROP PARTITION p0;

- 시간이나 날짜별로 데이터를 관리하고 싶을 때
- 파티션된 컬럼을 조건으로 쿼리를 자주 실행시킬 때

SELECT COUNT(*) FROM employees 
WHERE YEAR(separated) = 2000 GROUP BY store_id;

MySQL은 재빨리 해당 파티션만 읽어서 결과를 얻어낼 수 있다.

LIST

List 파티셔닝은 여러 가지로 Range 파티셔닝과 비슷한 점이 많다. Range 파티셔닝처럼 각각의 파티션은 명시적으로 정의되어 있어야 한다. Range 파티셔닝과 가장 다른 특징은 파티션을 결정하는 컬럼 값이 범위가 아니라 특정한 값들의 집합이라는 것이다. 

 
Partition by list() 절에 파티셔닝할 기준 컬럼을 명시해 주고 values in() 절에 파티션을 구성할 값들을 지정하면 된다. VALUES IN() 절에 나열되는 값들은 연속될 필요가 없고 중간 값이 없어도 되지만 해당 파티션에 값이 하나도 없거나 여러 파티션에 중복된 값을 넣으면 에러가 난다. Range 파티셔닝에서의 MAXVALUE에 상응하는 것은 없고, value list에 null 값이 들어갈 수 있다. 삭제하고자 하는 데이터들이 해당 파티션에 있는 모든 데이터들이라면 역시 drop 구문을 이용해서 데이터를 삭제할 수 있다.

DELETE FROM employees WHERE store_id IN (4,12,13,14,18);

이와 같이 지우고자 하는 데이터가 pWest 파티션에 있는 모든 값이라면 해당 파티션을 drop함으로써 해당 값들을 모두 삭제할 수 있다.

ALTER TABLE employees DROP PARTITION pWest;

List 파티셔닝 역시 파티셔닝할 컬럼은 정수만 지원한다. 따라서 integer나 null이 아닌 값으로 파티셔닝하고 싶다면 파티션 기준 컬럼을 이러한 값으로 변환시켜야 한다. Job_code 값이 다음과 같이 들어온다고 가정해 보자

Management - D, M, O, P
Sales - B, L, S
Technical - A, E, G, I, T
Clerical - K, N, Y
Support - C, F, J, R, V
Unassigned “Empty”

이렇게 문자로 들어온다면 이것은 integer value-list로 표현할 수 없으므로, 이럴 때 ascii() 함수를 사용해야 한다.
 
list에 해당하는 값이 대소문자를 구별하지 않기를 원한다면 ucase 함수로 먼저 감싸고 ascii()를 사용하면 된다. 주의해야 할 점은 다음과 같은 insert 문은 실패한다는 것이다.

INSERT INTO employees VALUES
(224, ‘Linus’, ‘Torvalds’, ‘2002-05-01’, ‘2004-10-12’, ‘Q’, 21);

Q의 ascii 값은 81인데 list 파티션 정의에 81에 해당하는 파티션이 존재하지 않기 때문이다. List 파티셔닝은 VALUES LESS THAN(MAXVALUE)과 비슷한 catch-all에 대한 정의가 없으므로 모든 list 값은 반드시 해당하는 파티션이 미리 정의되어 있어야 한다. 또한 MySQL 버전에 따라서는 PARTITION BY LIST() 절에 함수를 사용하지 못할 수도 있다. 최근 버전인 MySQL(5.1.36)에서는 PARTITION BY LIST() 절에 함수를 이용하지 못한다. 그러므로 integer 형이 아닌 컬럼을 기준으로 List 파티셔닝을 하고 싶다면 반드시 먼저 자신이 사용하는 MySQL 버전에서 테스트 후 사용하기를 바란다.

HASH

파티셔닝은 필요한데 마땅히 파티셔닝할 기간이나 특정 값들의 집합을 정의하기 모호할 때 보통 많이 사용한다. 사용 목적도 그렇듯이 Range나 List로 파티셔닝된 테이블을 생성할 때와는 달리 각각의 파티션들을 직접 정의해 줄 필요가 없다. <리스트 7>과 같이 PARTITIONS 절 뒤에 사용할 파티션 개수를 적어 주면 된다.

 
PARTITION BY HASH() 절에 역시 숫자 형식의 컬럼 값이 들어오면 된다. 숫자 컬럼이 아닐 경우 함수를 사용해 숫자로 변형이 가능하다면 Hash 파티셔닝이 가능하다. 그렇다면 각각의 데이터는 어느 파티션으로 들어가는 것일까? 내부적으로 파티션은 0, 1, 2, 3과 같은 식으로 번호가 붙여지는데 표현식을 expr, 파티션 수를 num이라고 하면, 입력되는 row가 들어갈 파티션 번호는 다음의 식으로 결정된다.

IF(ISNULL(expr), 0, ABS(expr)) MOD num

따라서 컬럼이 auto_increment 컬럼과 같이 순차적인 값을 가지는 경우 파티션별로 데이터가 고르게 분산된다.

CREATE TABLE t1 (col1 INT, col2 CHAR(5), col3 DATE)
PARTITION BY HASH( YEAR(col3) )
PARTITIONS 4;

이러한 테이블에 col3 값이 ‘2005-09-15’라면 데이터가 어느 파티션에 들어갈지는 다음과 같은 공식으로 계산된다.

MOD(YEAR(‘2005-09-01’),4) = MOD(2005,4) = 1

MySQL은 또한 Linear Hash 파티셔닝을 지원한다. 문법은 partition by hash가 partition by linear hash로 바뀌는 것 외에는 동일하다. 

 
내부적으로 데이터를 파티셔닝하는 Hash 알고리즘으로 linear powers-of-two를 사용하며 참고로 LINEAR HASH() 절에 사용된 표현식을 expr, 파티션 수를 num, 들어갈 파티션 번호를 N이라고 했을 때 N을 구하는 방식은 다음과 같다.

1. V = POWER(2, CEILING(LOG(2, num))) 
2. N = expr & (V - 1)
3. While ( N >= num: ) 
V = CEIL(V / 2) 
N = N & (V - 1)

KEY

Key 파티셔닝은 Hash 파티셔닝과 비슷하다. Hash 파티셔닝은 사용자가 정의하는 함수가 이용되는 반면 Key 파티셔닝은 MySQL에 의해 제공하는 Hash 함수를 사용한다. MySQL Cluster는 Hash 함수로 MD5()를 사용한다.

 
특이하게 KEY() 절에 컬럼을 명시하지 않아도 에러가 나지 않는데 명시하지 않으면 PK가 파티셔닝 키로 사용된다. 만약 해당 테이블에 PK가 없다면 UK가 파티셔닝에 사용된다.

 
하지만 UK가 파티셔닝 키로 사용되려면 반드시 UK가 not null로 정의되어 있어야 한다. <리스트 10>의 경우는 id 컬럼이 UK이긴 하지만 not null로 정의되어 있지 않아 실패한 것이다. 다른 파티션과는 다르게 유일하게 Key 파티셔닝에서는 파티셔닝 키값으로 숫자나 null 이외의 값을 사용할 수 있다. <리스트 11>과 같이 파티셔닝 키가 되는 컬럼 자료형이 char이라도 굳이 integer로 변환할 필요가 없다.

 

SUBPARTITIONING

파티션 안에 또 다른 작은 파티션을 만들 수 있다. Range나 List 파티션 안에 서브 파티션으로 Hash나 Key 파티션을 만드는 것도 가능하다. 여러 가지 파티셔닝을 함께 사용할 수 있기 때문에 Composite 파티셔닝이라고도 알려져 있다.

 

Partition Managemt

MySQL 상에서 파티셔닝한다면 주로 성능보다는 관리적인 면에서 더 유용하다. 월별로 관리하는 데이터를 Range 파티셔닝했다면 매달 말에 다음 달에 대한 파티션을 미리 만들어야 할 필요가 생긴다.

 
<리스트 13>에 정의된 파티션 테이블에 2009년 10월 데이터를 쌓을 파티션을 추가하고 싶다면 다음과 같이 추가할 수 있다.

mysql> ALTER TABLE in_ptest ADD PARTITION 
-> ( PARTITION p200910 VALUES LESS THAN (to_days(‘2009-11-01’)) ); 
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0

또한 특정 파티션을 간편하게 제거할 수도 있다.

mysql> ALTER TABLE in_ptest DROP PARTITION p200902;
Query OK, 0 rows affected (0.57 sec)
Records: 0 Duplicates: 0 Warnings: 0

짧은 시간 내에 Add, Drop이 되므로 온라인 중에서도 사용할 수 있다. 또한 다음과 같은 작업을 필요한 파티션만 진행할 수 있다. 해당 파티션만 작업하기 때문에 범위가 작아져 전체 작업하는 것보다 속도 향상이 있다.

ALTER TABLE REBUILD PARTITION ;
ALTER TABLE OPTIMIZE PARTITION ;
ALTER TABLE ANALYZE PARTITION ;
ALTER TABLE REPAIR PARTITION ;
ALTER TABLE CHECK PARTITION ;

하지만 타 DBMS처럼 병렬 처리 효과를 얻기는 아직까지는 힘들다. <리스트 14>와 같이 서로 다른 두 파티션을 순차적으로 check할 때보다 제 각기 다른 세션에서 병렬로 처리할 때 속도가 빠를 것이라고 기대했지만 다음에서 볼 수 있듯이 실제 결과는 그렇지 않았다. 

 
<리스트 14>에서 p200903, p200904 파티션을 순서대로 check할 때는 10초, 20초 걸리던 것이 <리스트 15>와 <리스트 16>처럼 각기 다른 세션에서 한꺼번에 실행시켰을 때는 각각55초, 54초가 걸렸다. 또한 보통 테이블 파티셔닝을 하면 특정 파티션에 어떤 작업을 수행할 때 다른 파티션에는 영향을 미치지 않을 것을 기대하지만 아직 MySQL에서는 이러한 점을 지원하지 않으니 유의해야 한다.

 
 
기존의 파티션을 병합하거나 분리하는 작업도 REO RGANIZE PARTIION 구문을 이용하면 가능하다. <리스트17>은 p200901 파티션과 p200902 파티션을 병합하는 것이다. 파티션 Add와 Drop은 순식간에 이뤄져 애플리케이션 운영 중에도 가능하지만 Reorganize를 하면 작업 대상이 아닌 파티션에 어떤 영향을 미칠까?

 
<리스트 17>의 세션에서 실행되는 쿼리는 p200901,p200902 파티션만 관여한다. 따라서 해당 파티션만 lock이 걸리고 나머지 파티션은 사용할 수 있을 것으로 기대하겠지만 실제 테스트 결과 <리스트 19>의 결과를 보면 알 수 있듯이 해당 파티션뿐만 아니라 전체 파티션에 lock이 걸리는 걸 알 수 있었다. <리스트 19> 쿼리의 데이터는 p200909 파티션으로 들어감에도 불구하고 <리스트 17>의 쿼리가 끝날 때까지 대기한 후에 실행이 되었다. 따라서 파티셔닝 관리 작업 중 Add와 Drop 같이 순식간에 이뤄지는 종류를 제외하고는 항상 애플리케이션에 영향을 줄 수 있다는 점을 명시하고 작업해야 한다.

 
 
현재 테이블 파티셔닝 현황에 대해 조회해 보고 싶다면 Information 스키마의 Partitions 테이블을 참조하면 된다.

파티션 성능

MySQL은 Parallel Processing을 아직 지원하지 않는다. 그렇더라도 각각 파티션에 해당하는 작업을 여러 개의 프로세스로 돌리면 비슷한 효과를 얻을 수 있지 않을까 기대했었지만 앞에서 살펴본 바와 같이 그러한 결과는 얻을 수 없었다. delete 대신에 drop 명령어를 통한 성능 향상, 그리고 Partition Pruning을 통한 성능 향상 정도를 기대할 수 있겠다. 

 
Explain Partitions 구문을 사용하면 실행계획에 파티션 정보들을 보여준다. <리스트 20>의 세션에서는 파티셔닝 컬럼인 term이 where 조건에서 변형되었기 때문에 Partition Prunining이 일어나지 않았다. 따라서 <리스트 21>과 같이 파티션 키 컬럼은 그대로 두고 비교하는 상수 값만 변경해야 원하는 속도를 얻을 수 있다.

파티션 제약사항

글 중간 중간에 MySQL 파티셔닝을 사용할 때 제약사항들을 언급했었는데 현재 MySQL 5.1.36 버전에서의 제약사항들을 간단히 정리해 보면 다음과 같다. 아직까지는 생각보다 많다.

- 모든 파티션은 동일한 스토리지 엔진 사용 
모든 파티션은 동일한 스토리지 엔진을 사용해야 한다. 파티션별로 스토리지 엔진을 명시해도 에러가 나지 않는 것을 봐서는 향후에 파티션별로 다른 스토리지 엔진을 사용할 수 있게 되지 않을까라는 기대도 해본다. 명심할 것은 파티션별로 스토리지 엔진을 명시해도 에러만 나지 않을 뿐 실제로 다른 스토리지 엔진을 사용할 수 있는 게 아니란 사실이다.

- Merge, CSV, Blackhole은 파티셔닝이 불가능하다.
- 파티셔닝을 테이블이나 인덱스만 할 수는 없다. 테이블과 인덱스를 항상 같이 파티셔닝해야 한다.
- 파티션된 테이블은 foreign key를 지원하지 않는다.
- 파티션된 테이블은 fulltext index를 지원하지 않는다.
- 파티션된 테이블은 geometry 컬럼을 지원하지 않는다.
- Key 파티셔닝을 제외하고는 파티셔닝에 키가 되는 컬럼은 반드시 integer 값이거나 integer로 변환 가능해야 한다. null은 포함될 수 있다.
- 많은 테이블에 파티션을 사용하다 보면 스토리지 엔진으로부터 ‘Got error 24’와 같은 에러 메시지를 보게 될 것이다. 이때는 open_files_ limit 값을 늘려줘야 한다.
- 한 테이블당 서브파티션(subpartition)을 포함해 파티션의 최대 개수는 1,024개이다.

효과적인 사용은 사용자의 몫

데이터가 점점 더 대용량화되는 가운데 MySQL도 늦게나마 이런 특징들을 지원하기 시작했다. 아직까지 부족한 점도 많지만 MySQL은 끊임없이 발전하고 있고 이를 잘 활용하기 위한 기법들이 많이 소개되고 있으므로 단순히 이를 Oracle과 같은 상용 DBMS와 비교하는 것은 별 의미가 없다. MySQL은 나름의 영역에서 다양한 기능과 좋은 성능을 내 주고 있으며 어떤 서비스에 어떻게 사용할지는 사용자들의 몫이다. MySQL과 같은 오픈소스 DBMS가 한층 더 좋은 모습으로 나타날수록 사용자 입장에서는 반갑고 고맙기 그지없을 뿐이다.

참고자료
1. http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
2. http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning_with_dates.html
3. http://dev.mysql.com/tech-resources/articles/mysql_5.1_partitioning.html
4. http://dev.mysql.com/tech-resources/articles/testing-partitions-large-db.html

필자소개

오민석 msoh@hanmail.net｜(주)다음커뮤니케이션 DB기술팀 MySQL Cell. 게임 개발, 웹 개발, SE 등 넓고 얕게 여기저기를 기웃거리다가 DBA로 정착하고 나서야 자신에게 맞는 일을 찾았다. 최근까지 오라클 RAC 프로젝트를 이끌다가 MySQL 나름의 매력을 느끼고 현재 MySQL 관련 프로젝트를 진행하고 있다. 

출처 : 한국 마이크로 소프트웨어 [2009년 11월호]
제공 : DB포탈사이트 DBguide.net 

 MySQL에서 사용하는 log-bin이라는 옵션은 사용된 쿼리들이 로깅되는 파일이며 Innodb 혹은 Replication등에서 사용되곤 합니다.

하지만 이 파일의 문제는 무한정 늘어난다는 것입니다. relay-log-space-limit 같은 옵션이 있지만 이 옵션은 Replication에서 사용되는 relay-log-bin의 용량을 제한 하는 옵션이지 해결책이 되지 못합니다.

하지만 이 파일을 정리하는 방법이 있습니다. 우선 다음의 Query를 사용하는 것입니다.

          PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)
          

INTERVAL에서는 원하시는 로깅 시점을 기록하시면 됩니다. 위의 경우에는 30일 이전의 로깅 정보를 삭제하게 됩니다.

차례차례 해보도록 하겠습니다.

1. 현재의 하드디스크 용량 상태를 확인합니다.

          [root@Theeye db]# df -h 
          Filesystem            Size  Used Avail Use% Mounted on 
          /dev/sda1              18G  1.9G   15G  12% / 
          /dev/sda3              15G  803M   13G   6% /home 
          /dev/sda2              97G   78G   15G  85% /usr/local/mysql/db
          

데이터베이스 공간이 85%나 사용되어 곧 꽉찰지도 모른다는 불안감에 휩싸이게 되었습니다.

2. log-bin 파일을 확인해 봅시다.

          [root@Theeye db]# ls mysql-bin.* 
          mysql-bin.000001  mysql-bin.000002  mysql-bin.000003 ......  mysql-bin.000073
          

파일이 엄청나게 많이 있습니다.

3. 다음 명령어를 수행합니다.

          mysql -e "PURGE MASTER LOGS BEFORE DATE_SUB(CURRENT_DATE, INTERVAL 30 DAY)"
          

물론 mysql에 접속하셔서 위의 명령어를 입력하셔도 됩니다. Super권한을 가진 root계정으로 실행해 주세요.

4. log-bin 파일이 줄었나 확인해 봅시다.

          [root@Theeye db]# ls mysql-bin.* 
          mysql-bin.000041  mysql-bin.000042  mysql-bin.000043 ......  mysql-bin.000073
          

41번 이전의 파일들이 모두 삭제 되었습니다. 41번 부터가 최근 30일 이내의 로그정보가 남아있는 모양이군요.

5. 하드디스크 용량 상태를 확인해 봅시다.

          [root@Theeye db]# df -h 
          Filesystem            Size  Used Avail Use% Mounted on 
          /dev/sda1              18G  1.9G   15G  12% / 
          /dev/sda3              15G  803M   13G   6% /home 
          /dev/sda2              97G   40G   53G  43% /usr/local/mysql/db
          

확실히 용량이 많이 확보가 되었군요^^

주기적인 실행이 필요하실 경우에는 위의 명령어를 crontab에 등록하시면 됩니다. 일주일에 한번정도의 실행이 적당하겠네요.


출처 :  http://dev-world.springnote.com/pages/6494145