MySQL Partition 기능과 활용

MySQL Partition 기능과 활용

2013/02/12 15:51

Partition 은 분리, 분할 한다는 의미이다.

정보는 많아지고 처리해야할 데이터도 점점 많아지고 있으며, 이러한 데이터의 빠른 처리를 위한 방법 중 하나로 MySQL에서는 Partition 을 지원하고 있다.

MySQL 에서 Partition은 저장할 데이터를 분할하여 관리함으로써 보다 빠른 데이터처리를 할 수 있도록 도와 준다.

MySQL 에서 Partition은 V5.1부터 가능하다.

Partitioning하지 않고 하나의 큰 테이블로 사용할 경우 그 만큼 인덱스도 커지고, 물리적인 공간도 많이 필요해진다.

Partition을 하게 되면 데이터와 인덱스를 조각화하여 물리적 공간을 효율적으로 사용할 수 있게 만들어 준다.

-. 100만명의 회원 중 주로 활동하는 회원이 20~30%인 경우, 이들의 데이터를 위킹셋(Working Set)이라 하는데, 이 데이터를 구분하여 Partitioning하게 되면 효과적인 성능개선이 가능할 것이다.

MySQL 파티션의내부 처리

create table tb_article (

article_id int null,

reg_date datetime not null,

..

..

primary key(article_id)

)

partition by range(year(reg_date)) {

  partition p2011 values less than(2012),

  partition p2012 values less than(2013),

  partition p2013 values less than(2014),

  partition p9999 values less than maxvalue

);

위와 같은 구조를 가진 테이블에서 insert 와 update에 대한 내부 처리는 각각 아래와 같다.

-. INSERT

INSERT 요청이 되면 파티션키가 되는 컬럼의 값을 이용하여 파티션 표현식을 평가하여 레코드가 저장될 파티션을 결정 후 해당 파티션에 데이터를 입력한다.

-. UPDATE

UPDATE 요청이 될 경우 쿼리의 WHERE조건에 파티션의 키값이 조건으로 존재한다면 그 값을 이용하여 저장된 파티션에서 빠르게 검색할 수 있지만, 그렇지 않은 경우 모든 파티션을 검색하여야 한다. 그 후 레코드의 값을 변경하여야 하는데 UPDATE 할 컬럼의 값이 파티션 키 이외의 값일 경우 해당 컬럼의 값만 변경되고, 파티션키에 해당하는 컬럼의 값이 변경될 경우 해당 레코드를 삭제 후, 다시 파티션키 값에 따라 해당 파티션에 값이 저장된다.

MySQL 에서 Partition 을 사용할 경우 아래의 효과를 볼 수 있다.

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

MySQL Partition 사용시 제약 사항

-. 모든 파티션은 동일한 스토리지 엔진 사용

파티션별 다른 엔진을 지정하여도 에러가 발생하지는 않지만 적용되는것은 아니다.

-. 테이블과 인덱스를 별도로 파티션 할수는 없다. 테이블과 인덱스를 같이 Partitioning 하여야 한다.

-. Partition 된 테이블은 foreign Key를 지원하지 않는다

-. Partition 된 테이블은 FullText Index 를 지원하지 않는다.

-. Partition 된 테이블은 Geometry(point, geometry...) 컬럼 타입을 지원하지 않는다.

-. 한 테이블당 파티션의 갯수는 최대 1,024개이다.

-. Temp Table 은 파티션 사용 불가.

-. Partition 값은 정수형이어야 한다.

-. 테이블이 Unique 또는 Primary Key를 가지고 있다면, 파티션키는 모든 Unique 또는 Primary Key의 일부 또는 모든 컬럼을 포함해야 한다.

MySQL Partition 적용

먼저 해당 MySQL 서버에서 Partition이 사용 가능한지 확인해 보아야 한다.

mysql> show variables like '%partition%';

+-------------------+-------+

| Variable_name     | Value |

+-------------------+-------+

| have_partitioning | YES   |

+-------------------+-------+

1 row in set (0.00 sec)

mysql> show plugins;

+--------------------------+----------+--------------------+---------+---------+

| Name                     | Status   | Type               | Library | License |

+--------------------------+----------+--------------------+---------+---------+

......

......

| partition                | ACTIVE   | STORAGE ENGINE     | NULL    | GPL     |

+--------------------------+----------+--------------------+---------+---------+

variables 와 plugins의 Partition정보를 확인한다.

MySQL Partition 종류

MySQL에서 지원되는 Partition은 Range, List, Hash, Key 의 4가지 이다

-. Range

-. 파티션 키의 연속된 범위로 파티션을 정의.

-. 날짜 기반 데이터가 누적되고 년도, 월,일 단위로 분석, 삭제 할 경우

-. 범위 기반으로 데이터를 여러 파티션에 균등하게 나눌 수 있을 경우

-. 파티션 키 위조로 검색이 자주 실행 될 경우

-. 대량의 과거 데이터 삭제시

CREATE TABLE employees (
    id INT NOT NULL,
    fname VARCHAR(30),
    lname VARCHAR(30),
    hired DATE NOT NULL DEFAULT '1970-01-01',
    separated DATE NOT NULL DEFAULT '9999-12-31',
    job_code INT NOT NULL,
    store_id INT NOT NULL
)
PARTITION BY RANGE (YEAR(hired)) (
    PARTITION p0 VALUES LESS THAN (2010),
    PARTITION p1 VALUES LESS THAN (2011),
    PARTITION p2 VALUES LESS THAN (2012),
    PARTITION p3 VALUES LESS THAN MAXVALUE
);

파티션 추가

ALTER TABLE employees ADD PARTITION(PARTITION p4 VALUES LESS THAN (2009));

파티션 삭제

ALTER TABLE employees DROP PARTITION p4;

기존 파티션의 분리

ALTER TABLE employees 

REORGIANIZE PARTITION p3 INTO (

PARTITION p3 VALUES LESS THAN (2013),

PARTITION p4 VALUES LESS THAN MAXVALUE

);

기존 파티션의 병합

ALTER TABLE employees 

REORGANIZE PARTITION p2,p3 INTO (

PARTITION p23 VALUES LESS THAN (2012)

);

-. Range 파티션에서 Null 은 어떤 값보다 작은 값으로 취급된다. hired 컬럼이 Null 인 데이터가 insert 된다면 가장 작은 값을 저장하는 p0에 저장된다. 하지만 파티션 지정시 PARTITION p0 VALUES LESS THAN (NULL) 과 같이 지정할 수는 없다.

-. 날짜 컬럼에 대한 Range 파티션 적용시 YEAR(), TO_DAYS()  함수만 사용하길 권장한다. 이 두 함수는 MySQL 서버 내부적으로 파티션 프루닝처리가 되어 성능상의 문제가 발생하지 않지만 그 외의 함수는 파티션 프루닝이 제대로 작동하지 않을 수도 있다.(UNIX_TIMESTAMP()를 이용한 변환식, 날짜를 문자열로 포매팅한 형태('2012-02-12')등의 형태는 사용하지 않길 바란다)

-. List

-. Range 파티션과 비슷

-. 파티션 키 값이 코드 값이나 카테고리와 같이 고정 값일 경우

-. 키 값이 연속적이지 않고 정렬순서와 관계없이 파티션을 해야 할 경우

-. 파티션 키 값을 기준으로 레코드 건수가 균일하고 검색 조건에 파티션 키가 자주 사용 될 때

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY List (job_code) ( PARTITION p0 VALUES IN (3), PARTITION p1 VALUES IN (1,9), PARTITION p2 VALUES IN (2,6,7), PARTITION p3 VALUES IN (4,5,8,NULL)

);

-. Range 와 달리 Null 을 명시할 수 있으며, MaxValue 를 지정할 수는 없다.

-. V5.5 부터는 파티션 키 값에 정수형 값  이외에 문자열 타입도 사용 가능하다.

PARTITION BY List (job_code) ( PARTITION p0 VALUES IN ('sports'),

PARTITION p1 VALUES IN ('teacher'), PARTITION p2 VALUES IN ('student'), PARTITION p3 VALUES IN ('banker',null)

);

Range 파티션과 동일(VALUES LESS THAN ==> VALUES IN 사용)

파티션 추가

ALTER TABLE employees ADD PARTITION(PARTITION p4 VALUES IN (3,10));

파티션 삭제

ALTER TABLE employees DROP PARTITION p4;

기존 파티션의 분리

ALTER TABLE employees 

REORGIANIZE PARTITION p3 INTO (

PARTITION p3  VALUES IN (2,6),

PARTITION p4  VALUES IN (7)

);

기존 파티션의 병합

ALTER TABLE employees 

REORGANIZE PARTITION p2,p3 INTO (

PARTITION p23  VALUES IN (2,6,7,4,5,8,NULL)

);

-. Hash
-. HASH 함수에 의해 레코드가 저장될 파티션을 결정하는 방식

-. Range, List 로 데이터를 균등하게 나누는 것이 어려울 때.

-. 테이블의 모든 레코드가 비슷한 사용빈도를 보이지만 너무 커서 파티션 적용이 필요한 경우

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY HASH (id) 

PARTITIONS 4;

또는

CREATE TABLE employees ( id INT NOT NULL, fname VARCHAR(30), lname VARCHAR(30), hired DATE NOT NULL DEFAULT '1970-01-01', separated DATE NOT NULL DEFAULT '9999-12-31', job_code INT NOT NULL, store_id INT NOT NULL ) PARTITION BY HASH (id) 

PARTITIONS 4

(

PARTITION p0 ENGINE = INNODB, PARTITION p1 ENGINE = INNODB PARTITION p2 ENGINE = INNODB PARTITION p3 ENGINE = INNODB

);

-. 파티션 추가

-. 파티션의 갯수로 MOD 연산한 결과에 따라 각 레코드를 저장할 파티션을 결정하므로 새로이 파티션이 추가될 경우 파티션에 저장된 모든 레코드는 재배치 되어야 하므로 많은 부하가 발생한다.

-. ALTER TABLE employees ADD PARTITION(PARTITION p5 ENGINE = INNODB); (파티션 이름을 부여하는 경우)

-. ALTER TABLE employees ADD PARTITION PARTITIONS 3; (별도의 이름 없이 3개의 파티션을 추가하는 경우)

-. 파티션 삭제

-. 파티션 키 값을 이용하여 데이터를 각 파티션으로 분산한 것이므로 각 파티션에 저장된 레코드의 부류를 사용자가 예측할 수 없기에 해시나 키를 이용한 파티션에서는 파티션단위의 삭제는 불가하다.

-. 파티션 분할

-. 특정 파티션을 분할하는 것은 불가하면, 파티션 추가만 가능하다.

-. 파티션 병합

-. 2개이상의 파티션을 하나로 합치는 기능은 제공하지 않는다. 다만 파티션의 갯수를 줄이는 것은 가능하다.

-. ALTER TABLE employees COALESCE PARTITION 1; (기존 파티션의 갯수에서 1개를 뺀 수 만큼 파티션을 재배치 한다.(4개에서 3개로 재구성)

-. Key
-. 해시 파티션과 거의 동일. 해시값을 계산하는 방법을 사용자가 지정 가능
-. 키 파티션은 선정된 파티션 키값에 대하여 내부적오르 MD5() 함수를 이용하여 해시값을 계산하고, 그 값에 MOD를 적용하여 저장할 파티션을 결정한다. 


MySQL 의 파티션기능은 SELECT 성능에는 그다지 큰 도움이 되지 않을 수 있지만, INSERT, UPDATE, DELETE 와 같은 쓰기 성능에는 어느정도 도움이 될것으로 보인다. 
인덱스가 많아지고, 데이터가 커지면 추가 비용은 더 많아질 것 이다. 테이블의 건수가 어느정도 이상이 되면 INSERT, UPDATE, DELETE 의 성능은 급격히 떨어질 수 있으며, 이럴때 각 상황에 맞는 파티션을 결정하여 적용을 고려해 볼 만 할 것이다.




Reference
-. http://www.rcy.co.kr/xeb/index.php?mid=study&page=7&listStyle=webzine&document_srl=2369&sort_index=readed_count&order_type=desc
-. http://dev.mysql.com/doc/refman/5.1/en/partitioning.html
-. 위키북스 real MySQL