MySQL) Table Partitioning(파티셔닝)

전 직장에 하나의 테이블이 600GB 이상인 어느 업데이트 서버가 있었다.

업데이트는 거의 매일 신규 데이터를 추가하는 형태로 이루어지는데, 30대가 넘는 slave 서버가 rsync로 DB 파일을 동기화한다.

CDN 트래픽 비용도 엄청나고 동기화 시간도 꽤 걸리기 때문에 파티셔닝을 하기로 했었다.

 

우선 서버에 설치된 MySQL이 partitioning을 지원해야 한다.

확인 방법 및 서버 세팅은 여기 참고.

 

테이블을 파티셔닝하는 방법(기준)

• Range-Partition
• List-Partition
• Composite-Partition

 

주로 '기간'을 기준으로 하여 Range로 나누게 된다.

 

레코드의 등록일을 기준으로 나눌 경우 : 등록일 칼럼이 regdt이고 형식은 datetime이라면

• 연별 - PARTITION BY RANGE (YEAR(`regdt`))
• 일(월)별 - PARTITION BY RANGE (TO_DAYS(`regdt`))

테이블 생성 쿼리

내가 파티셔닝하려고 했던 테이블의 생성 쿼리이다.

본문에도 적었지만 아래 테이블을 파티셔닝하면 오류가 발생한다.

※ 파티셔닝의 기준이 되는 칼럼은 반드시 PRIMARY KEY로 지정이 되어 있어야 한다.

CREATE TABLE `pattern` (
  `spcode` VARCHAR(128) NOT NULL,
  `category` VARCHAR(64) NOT NULL,
  ...(중략)...
  `regdt` DATETIME NOT NULL,
  `treatindex` VARCHAR(128) DEFAULT NULL,
  PRIMARY KEY (`spcode`),
  KEY `treatindex` (`treatindex`) USING BTREE
) ENGINE=MYISAM DEFAULT CHARSET=utf8

파티셔닝 쿼리

기존 테이블을 등록일(regdt) 기준 월별로 파티셔닝하는 쿼리를 실행.

mysql> ALTER TABLE `pattern`
  PARTITION BY RANGE (TO_DAYS(`regdt`)) (
  PARTITION p_2006_01 VALUES LESS THAN (TO_DAYS('2006-02-01')) ENGINE = MYISAM,
  PARTITION p_2006_02 VALUES LESS THAN (TO_DAYS('2006-03-01')) ENGINE = MYISAM,
  PARTITION p_2006_03 VALUES LESS THAN (TO_DAYS('2006-04-01')) ENGINE = MYISAM,
  PARTITION p_2006_04 VALUES LESS THAN (TO_DAYS('2006-05-01')) ENGINE = MYISAM,
  ...(중략)... )
;
ERROR 1503 (HY000): A PRIMARY KEY must include ALL COLUMNS IN the TABLE's partitioning function

위에 적었다시피 오류가 발생하였다(regdt 칼럼이 PRIMARY KEY가 아니라서).

오류 피드백 - PRIMARY KEY로 지정

mysql> ALTER TABLE `pattern` DROP PRIMARY KEY;
mysql> ALTER TABLE `pattern` ADD CONSTRAINT 인덱스명 PRIMARY KEY (`칼럼1`, `칼럼2`, ...);

ALTER 해서 추가로 지정할 수도 있지만 이미 존재하고 있는 레코드에 따라 불가능할 수도 있다.

ALTER가 오류 메시지와 함께 불가능한 경우에는 테이블을 dump 뜨고 CREATE 구문에 추가하여 복원하면 된다.

결과

PRIMARY KEY 지정 후 파티셔닝 쿼리를 다시 실행하면 파티셔닝이 진행 된다.

아래처럼 나뉘어졌다.

 

파티션은 1024개라는 제한이 있다.

I/O가 얼마 없는 아주 예전 데이터는 연도별로 묶는 등 유동적인 관리가 필요하다.

 

참고 사이트.

1. http://yaruki0318.tistory.com/45
2. http://wyseburn.tistory.com/336
3. http://trendofit.tistory.com/739