파티셔닝5

서브 파티셔닝

RANGE와 LIST일 때 각 파티션을 더 분할할 수가 있다.  매뉴얼에서는 이것을 서브파티션이라고 부른다.

예1>
CREATE TABLE ptrange( a INT, b INT, c CHAR(10) )
    PARTITION BY RANGE(a)
      SUBPARTITION BY HASH(a)
     (
       PARTITION pt0 VALUES LESS THAN ( 1000)
      (
          SUBPARTITION s0,
          SUBPARTITION s1
      ),
      PARTITION pt1 VALUES LESS THAN MAXVALUE
     (
         SUBPARTITION s2,
         SUBPARTITION s3
      )
    );

예2>
CREATE TABLE ptrange( a INT, b INT, c CHAR(10) )
    PARTITION BY RANGE(a)
      SUBPARTITION BY HASH(a)
         SUBPARTITIONS 2
        (
          PARTITION pt0 VALUES LESS THAN (1000),
          PARTITION pt1 VALUES LESS  THAN MAXVALUE
        );

MySQL의 파티셔닝에 대해서  정리해보았다.  꽤 유연하게 파일을 분할할 수 있다는 것을 알 수 있다.   한개의 테이블이 큰 경우에는  파티셔닝의 채용을 검토 해 보자.

파티셔닝4

파티셔닝의 방법 

MySQL의 파티셔닝방법에는  RANGE, LIST, HASH, KEY의 4가지 방법이 제공된다.
이것은 PARTITION BY로 지정한다.

• RANGE: 지정된 컬럼 값에 의해서 파티셔닝을 한다.  컬럼명을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는  정수를 리턴하는 함수이어야 한다.  조건의 판정에는 VALUES LESS THAN를 사용한다. MAXVALUE는 최대값을 의미한다.

CREATE TABLE ptrange( a INT)

   PARTITION BY RANGE(a) (

     PARTITION pt0 VALUES LESS THAN ( 1000 ), 

     PARTITION pt1 VALUES LESS THAN MAXVALUE ); 

• LIST: 지정된 값에 의해서 파타셔닝을 한다.  컬럼명만을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는 정수를 리턴하는 함수이어야 한다.  조건을 지정할때에는 각 파티션정의에 VALUES IN(값 [값,...])을 사용한다. 

 CREATE TABLE ptlist( a INT UNSIGNED)

   PARTITION BY LIST(a) (

     PARTITION pt0 VALUES IN (1,4),

     PARTITION pt1 VALUES IN (2,5),

     PARTITION pt2 VALUES IN (3,6)  );

• HASH: 지정된 값에 따라서 MySQL이 행을 자동으로 나눈다.  컬럼명만을 지정하는 경우에는 정수형이어야하고 함수식을 지정하는 경우에는 정수를 리턴하는 함수이어야 한다.  

CREATE TABLE pthash(a INT, b INT, c CHAR(10) ) 

     PARTITION BY HASH(a) 

     PARTITION 2;

• KEY: 지정된 값에 따라서 MySQL이 행을 자동으로 나눈다.  0개이상의 컬럼명(함수)를 지정할 수 있다.  값을 지정하지 않은 경우 테이블의 PRIMARY KEY가 자동적으로 채용된다. 

CREATE TABLE ptkey(a INT, b INT, c CHAR(10) ) 

    PARTITION BY KEY(a, b, c)

    PARTITIONS 2;

 CREATE TABLE ptkey( a INT PRIMARY KEY, b INT, c CHAR(10) ) 

    PARTITION BY KEY()

    PARTITIONS 2;

파티셔닝3

파티셔닝의 제한

5.1.12-beta에서 다음과 같은 제한이 있다.

• 파티셔닝된 테이블은 스토리지엔진이 같아야 된다. 
• 외부키 제약은 사용할 수 없다. 
• FULLTEXT인덱스를 사용할 수 없다. 
• GIS컬럼을 사용할 수 없다. 
• 임시테이블, MERGE스토리지엔진, CSV스토리지엔진은 파티셔닝할 수 없다. 

파티셔닝의 지정 

파티셔닝의 지정은 다음과 같이 한다.

CREATE TABLE 테이블명 (컬럼 정의) 

 PARTITION BY { [LINEAR] HASH(expr) | [LINEAR] KEY(expr) }

[ PARTITIONS 파티션갯수]

CREATE TABLE 테이블명(컬럼정의)
 PARTITION BY { RANGE(expr) | LIST(expr) }
[ SUBPARTITION BY { [LINEAR] HASH() | [LINEAR] KEY() }
    [ SUBPARTITIONS 서브파티션갯수 ]
]
(파티션정의 [, 파티션정의] ... )

파티션정의:
   PARTITION 파티션명
     [ VALUES { LESS THAN (expr) | MAXVALUE } ] <--RANGE인 경우
     [ VALUES IN (값 리스트) ]   <--LIST인 경우
     [ 테이블 옵션]
     [ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
     [ NODEGROUP [=] MySQL Cluster의 node_group_id ]
     [(서브 파티션정의 [, 서브파티션정의 ]...) ]

서브파티션정의:
   SUBPARTITION 서브파티션명
     [ 테이블옵션 ]
     [ TABLESPACE [=] (NDB Cluster의 테이블영역명) ]
     [ NODEGROUP [=] MySQL Cluster의 node_group_id ]

테이블옵션:
  보통 CREATE TABLE문에서 지정할 수 있는 ENGINE= 키워드등의 테이블옵션. 파티션정의구문에서는 다음을 사용할 수 있다.
       [STORAGE] ENGINE [=] 스토리지엔진명
       COMMENT [=] 코멘트
       DATA DIRECTORY [=] '.MYD파일의 저장디렉토리'
       INDEX DIRECTORY [=] '.MYI파일의 저장디렉토리'
      MAX_ROWS [=] '테이블에 들어가는 최대 행수'
      MIN_ROWS [=] '테이블에 들어가는 최소 행수'
     

파티셔닝2

파티셔닝 개요

파티셔닝에서는 데이터를 여러개의 기록영역에 나누어서 기록하는 것을 의미한다.
MySQL의 파티셔닝은 수평 파티셔닝(horizontal partitioning)이라고 불리우는 방법으로 수행한다.

이것은 어떤 컬럼의 값에 따라서  레코드를 기록하는 장소를 바꾸는 방법이다.
이외에도 수직형 파티셔닝(vertical partitioning)처럼 나누는 방법도 있지만 MySQL은 서포트하고 있지 않다. (현재는 잘 모름.. )

다시말하면 수직형 파티셔닝에서는  "이 컬럼은  이쪽 파티션에 기록하고 저컬럼은 저쪽 파티션에 기록한다" 처럼 컬럼에 착목한 분할이 된다.


파티셔닝기본동작

파티셔닝이 어떻게 동작하는지  사용해보면서 알아보자.

 MyISAM테이블  mycom의 작성 

mysql> CREATE TABLE mycom( a INT) ENGINE=MyISAM
     PARTITION BY RANGE(a) (
         PARTITION pt0 VALUES LESS THAN (1000) ENGINE=MyISAM, 
         PARTITION pt1 VALUES LESS THAN (2000) ENGINE=MyISAM,
        PARTITION pt2 VALUES LESS THAN  MAXVALUE  ENGINE=MyISAM) ;


 이 때 데이터베이스의 디렉토리에는 다음과 같은 파일이 작성된다.

데이터베이스 디렉토리의 내용(MyISAM테이블)
mycom#P#pt0.MYD
mycom#P#pt0.MYI
mycom#P#pt1.MYD
mycom#P#pt1.MYI
mycom#P#pt2.MYD
mycom#P#pt2.MYI
mycom.frm
mycom.par

다시 말하면,  어떤 MyISAM 테이블을 파티셔닝하면  다음과 같은 파일이 생성된다.

테이블.frm
테이블.par
테이블#P#파티션이름.MYD(데이터)
테이블#P#파티션이름.MYI(인덱스)

.par파일은 바이너리 파일로  파티셔닝 정보를 기록한다.

.par파일의 내용 (일부)
shell# hexdump -C mycom.par
00000000 08 00 00 00 7e 7d 3a 00 03 00 00 00 09 09 09 00 
00000010 0c 00 00 00 70 74 30 00 70 74 31 00 70 74 32 00 
00000020

 그럼, InnoDB의 경우는 어떻게 될까?   아까전에 만든 mycom테이블을 ALTER TABLE문으로 InnoDB로 변경해 보자.

데이터베이스디렉토리는 다음과 같은 파일이 생성된다.

mycom.frm
mycom.par

InnoDB  형식에 따라서  테이블의  데이터와 인덱스부분은  InnoDB의  테이블 영역으로 이동되었다.   그럼 InnoDB의 --innodb_file_per_table옵션을 사용했을 때에는 파티셔닝은 어떻게 될까?  실행해보면  파일은 다음과 같이 되었다.

mycom#P#pt0.ibd
mycom#P#pt1.ibd
mycom#P#pt2.ibd
mycom.frm
mycom.par


이론 그대로이다.

파티셔닝1

파티셔닝은  MySQL 5.1에서 추가된 기능이다.  이것은  한개의 테이블을 여러개로 나누어 사용하는 기능으로 I/O분산을 위한 것이다.

I/O를 분산시킨다는 의미로는 MySQL에서 다음과 같은 방법이 제공되었다.

• 데이터베이스 디렉토리를 심볼릭 링크로 해서 분산 배치 
• InnoDB에서는 테이블스페이스 파일(innodb_data_file_path)를 여러개의 파일로 분할 
• InnoDB에서는 테이블단위로 파일을 작성(innodb_file_per_table)
• MyISAM에서는 MERGE 테이블 
• MyISAM에서는 RAID테이블( MySQL5.0에서 페지: 옛날 OS에서는 한개의 파일 사이즈가 4G제한이 있었지만 현재는 그런 제한이 없어졌으므로 자연스럽게 없어짐... )
• MyISAM에서 파일단위로 심볼릭 링크를 사용해서 분산배치 

그러나, 이것은  어떤 특정 스토리지엔진에서만 가능하다던지 또 사용방법도 각각 다르므로 범용적이라고 할 수 없었다.   그중에는 서버의 설정작업등의 변경작업도 필요하므로 관리자는 별도로 지식을 익혀두어야 했다.

MySQL5.1에서 도입된 파티셔닝기능은 MyISAM과 InnoDB둘다 동작하는 공통의 인터페이스(사용방법)이어서 통일감도 있고,  조작은 SQL문의 실행만으로 완료할 수 있으므로 관리자도 이용자도 편하게 되어졌다. 

파티셔닝을 이용하는 것으로 지금까지 보다 편하게  I/O분산배치를 시행할 수 있게 되었다.

다만,  파티셔닝이 지금까지의 수법을 완전히 대체할 수 있느냐라고는 할 수 없다.
예를 들어 innodb_data_file_path등은 DB셋업할 때 고려해야할 사항이다.

또 MERGE테이블에서는  myisampack으로 각각의 테이블을 작게 할 수 있으므로  이미 존재하는 테이블을 분할 할 때는 파이셔닝보다 편리할 때도 있다.

각각 때와 장소에 맞게 적절히 사용할 필요가 있다.

파티셔닝기능이 유효인지 확인하는 방법에는 SHOW VARIABLES로  have_partitioning의 값을 보면 알 수 있다.   이 값이 YES이면 파티셔닝기능이 들어있다라고 할 수 있다.

만약 값이 NO로 되어 있거나 원래 have_partitioning자체가 나타나지 않은 경우 사용하고 있는 mysqld 바이너리에 파티셔닝이 포함되어 있지 않는 것이다.

서버 소스를  configure할 때 --with-partition를 지정해서 컴파일하면 될 것이다.