파티션 : 주의사항

파티션 : 주의사항

MySQL의 파티션은 5.1 버전부터 도입됐지만 아직은 많은 제약을 지니고 있다. 물론 MySQL 5.5 버전에서 해결된 문제도 있지만 아직 MySQL의 파티션은 모두에게 익숙하지 않으므로 자세한 제약 사항을 한번 살펴보겠다.

1. 파티션의 제한 사항

• 숫자 값(INTEGER 타입 칼럼 또는 INTEGER 타입을 반환하는 함수 및 표현식)에 의해서만 파티션이 가능함(MySQL 5.5부터는 숫자 타입뿐 아니라 문자열이나 날짜 타입 모두 사용할 수 있도록 개선됨)
• 키 파티션은 해시 함수를 MySQL이 직접 선택하기 때문에 칼럼 타입 제한이 없음
• 최대 1024개의 파티션을 가질 수 있음(서브 파티션까지 포함해서)
• 스토어드 루틴이나 UDF 그리고 사용자 변수 등을 파티션 함수나 식에 사용할 수 없음
• 파티션 생성 이후 MySQL 서버의 sql_mode 파라미터 변경은 추천하지 않음
• 파티션 테이블에서는 외래키 사용 불가
• 파티션 테이블은 전문 검색 인덱스 생성 불가
• 공간 확장 기능에서 제공되는 칼럼 타입(POINT, GEOMETRY, ..)은 파티션 테이블에서 사용 불가
• 임시 테이블(Temporary table)은 파티션 기능 사용 불가
• MyISAM 파티션 테이블의 경우 키 캐시를 사용할 수 없음(MySQL 5.5부터 이 버그는 보완됨)
• 파티션 키의 표현식은 일반적으로 칼럼 그 자체 또는 MySQL 내장 함수를 사용할 수 있는데, 여기서 MySQL 내장 함수를 모두 사용할 수 있는 것이 아니라 일부만 사용할 수 있다. 하지만 이 함수 중에서도 정상적으로 파티션 프루닝(Pruning)을 지원하는 함수는 YEAR()와 TO_DAYS(), 그리고 TO_SECONDS() 밖에 없으므로 제대로 파티션의 기능을 이용하려고 한다면 INTEGER 타입의 칼럼 그 자체 또는 이 3가지 내장 함수를 사용한 표현식을 파티션 키로 사용할 것을 권장한다(TO_SECONDS 함수는 MySQL 5.5부터 지원됨).

MySQL의 파티션에서 인덱스는 로컬이나 글로벌의 의미가 없이 모두 로컬 인덱스이며, 같은 테이블에 소속돼 있는 모든 파티션은 같은 구조의 인덱스만 가질 수 있다. 즉 파티션 단위로 인덱스를 변경하거나 추가할 수 없다. 또한 하나의 테이블에 소속된 파티션은 다른 종류의 스토리지 엔진으로 구성하는 것을 추천하지 않는다. 위의 제약사항을 고려해 보면 MySQL 5.1부터는 INTEGER 칼럼과 DATE(또는 DATETIME) 타입의 칼럼으로 파티션된 테이블만 제대로 된 기능(파티션 생성 및 파티션 프루닝)을 활용할 수 있을 것을로 보인다.

매뉴얼을 살펴보면 파티션 키의 표현식에 사용 가능한 MySQL 내장 함수 목록을 각 버전별로 확인할 수 있다. 현재 MySQL 5.1 이상의 버전에서 파티션 표현식에 사용할 수 있는 함수는 아래와 같다.
ABS(), CELING(), DAY(), DAYOFMONTH(), DAYOFWEEK(), DAYOFYEAR(), DAYDIFF(), EXTRACT(), FLOOR(), HOUR(), MICROSECOND(), MINUTE(), MOD(), MONTH(), QUARTER(), SECOND(), TIME_TO_SEC(), TO_DAYS(), WEEKDAY(), YEAR(), YEARWEEK()
하지만 SUBSTRING()이나 ASCII() 등과 같은 대표적인 문자열 함수는 파티션 표현식에 사용할 수 없다.

2. 파티션 사용 시 주의사항

파티션 테이블의 경우 프라이머리 키를 포함한 유니크 키에 대해서는 상당히 머리를 아프게 하는 제약 사항이 있다. 파티션의 목적이 작업의 범위를 좁히기 위함인데, 유니크 인덱스는 중복 레코드에 대한 체크 작업 때문에 범위가 좁혀지지 않기 때문이다. 또한 MySQL의 파티션 또한 테이블과 같이 별도의 파일로 관리되기 때문에 MySQL 서버가 조작할 수 있는 파일의 개수와도 연관된 제약이 있다.

파티션과 유니크 키(프라이머리 키 포함)

종류에 관계없이 테이블에 유니크 인덱스(프라이머리 키 포함)가 있으면 파티션 키는 모든 유니크 인덱스의 일부 또는 모든 칼럼을 포함해야 한다. 다음의 파티션 테이블 생성 스크립트로 이를 자세히 살펴보자.

CREATE TABLE tb_partition (
  fd1 INT NOT NULL, fd2 INT NOT NULL, fd3 INT NOT NULL,
  UNIQUE KEY (fd1, fd2)
) PARTITION BY HASH (fd3)
PARTITIONS 4;

CREATE TABLE tb_partition (
  fd1 INT NOT NULL, fd2 INT NOT NULL, fd3 INT NOT NULL,
  UNIQUE KEY (fd1),
  UNIQUE KEY (fd2)
) PARTITION BY HASH (fd1 + fd2)
PARTITIONS 4;

CREATE TABLE tb_partition (
  fd1 INT NOT NULL, fd2 INT NOT NULL, fd3 INT NOT NULL,
  PRIMARY KEY (fd1)
  UNIQUE KEY (fd2, fd3)
) PARTITION BY HASH (fd1 + fd2)
PARTITIONS 4;

위의 예제는 모두 잘못된 테이블 파티션을 생성하는 방법이다. 유니크 키에 대해 파티션 키가 제대로 설정됐는지 체크하는 간단한 방법은 각 유니크 키에 대해 값이 주어지면 해당 레코드가 어느 파티션에 저장돼 있는지 계산할 수 있어야 한다는 점을 기억하면 된다. 위의 3가지 생성 스크립트 모두 이 방법으로 체크해 보면 예제 3개 테이블 모두 조금씩 부족하다는 점을 알 수 있다.

• 첫 번째 쿼리는 유니크 키와 파티션 키가 전혀 연관이 없기 때문에 불가능하다.
• 두 번째 쿼리는 첫 번째 유니크 키 칼럼인 fd1만으로 파티션 결정이 되지 않는다(fd2 칼럼 값도 같이 있어야 파티션의 위치를 판단할 수 있다). 두 번째 유니크 키 또한 첫 번째와 같은 이유로 불가능하다.
• 세 번째 쿼리 또한 두 번째 쿼리와 같이 프라이머리 키 칼럼인 fd1 값만으로 파티션 판단이 되지 않으며, 유니크 키인 fd2와 fd3로도 파티션 위치를 결정할 수 없다.

이제 파티션 키로 사용할 수 있는 예제를 몇 개 살펴보자.

CREATE TABLE tb_partition (
  fd1 INT NOT NULL, fd2 INT NOT NULL, fd3 INT NOT NULL,
  UNIQUE KEY (fd1, fd2, fd3)
) PARTITION BY HASH (fd1)
PARTITIONS 4;

CREATE TABLE tb_partition (
  fd1 INT NOT NULL, fd2 INT NOT NULL, fd3 INT NOT NULL,
  UNIQUE KEY (fd1, fd2)
) PARTITION BY HASH (fd1 + fd2)
PARTITIONS 4;

CREATE TABLE tb_partiton (
  fd1 INT NOT NULL, fd2 INT NOT NULL, fd3 INT NOT NULL,
  UNIQUE KEY (fd1, fd2, fd3),
  UNIQUE KEY (fd3)
) PARTITION BY HASH (fd3)
PARTITIONS 4;

위의 예제 3개는 각 유니크 키를 구성하는 칼럼의 값이 결정되면 해당 레코드가 어느 파티션에 저장돼 있는지 계산할 수 있다는 사실을 알 수 있다. 모두 해시 파티션으로 예를 들었지만 이는 파티션 방식에 관계없이 모든 파티션 테이블에서 프라이머리 키나 유니크 키를 생성하기 위해 지켜야 할 요건이다.

테이블의 파티션 키가 유효한지는 위에서 설명한 방법으로 확인해 보면 된다. 그 반대로, 주어진 테이블에서 파티션 키를 직접 선택해야 할 때는 다음과 같이 진행하는 것이 좋다.
1. 테이블에서 중복을 허용하지 않는, 프라이머리 키와 유니크 인덱스만 선별한다.
2. 프라이머리 키와 유니크 인덱스에 공통적으로 포함돼 있는 칼럼만 수집한다. 테이블에 프라이머리 키만 있다면 프라이머리 키를 구성하는 칼럼만 수집한다.
3. 2번에서 수집한 칼럼 중에서 일부 또는 전체를 사용한 표현식은 파티션 표현식으로 사용할 수 있다. 물론 2번 단계에서 수집한 칼럼을 별도의 표현식 없이 그대로 파티션 키로 사용할 수도 있다.

파티션과 open_files_limit 파라미터

MySQL에서는 일반적으로 테이블을 파일 단위로 관리하기 때문에 MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다. 이를 제한하기 위해 open-files-limit 시스템 변수에 동시에 오픈할 수 있는 적절한 파일의 개수를 설정할 수 있다. 파티션되지 않은 일반 테이블은 테이블 1개당 오픈된 파일의 개수가 2~3개 수준이지만 파티션 테이블에서는 (파티션의 개수 * 2~3)개가 된다. 예를 들어, 파티션이 1,024개 포함된 테이블을 생각해보자. 쿼리가 적절히 파티션 프루닝으로 최적화되어 1,024개의 파티션 가운데 2개의 파티션만 접근해도 된다고 하더라도 일단 동시에 모든 파티션의 데이터 파일이 오픈돼야 한다. 그래서 파티션을 많이 사용하는 경우에는 open-files-limit를 적절히 높은 값으로 다시 설정해 줄 필요가 있다.

파티션 테이블과 잠금

MySQL에서는 파티션 테이블이 가지는 파티션의 개수가 늘어날수록 성능이 더 떨어질 수도 있다. 예를 들어, 파티션이 350개 정도인 테이블에 10000건의 레코드를 INSERT해 보면 오히려 파티션이 없는 테이블의 INSERT가 30% 정도 더 빠르게 처리된다. 이러한 성능 차이는 테이블의 파티션 개수에 따라 더 커질 수도 있다. 파티션 테이블의 INSERT 성능에 대해서는 나중에 다시 한번 자세히 살펴볼 것이므로 여기서는 파티션 테이블의 잠금이 어떤 형태로 처리되는지 주의해서 살펴보자.

MySQL에서 파티션 테이블에 쿼리가 수행되면 우선 테이블의 열고 잠금을 걸고 쿼리의 최적화를 수행한다. 쿼리의 처리에 필요한 파티션만 선별하는 파티션 프루닝 작업은 쿼리의 최적화 단계에서 수행되므로 테이블을 열고 잠금을 거는 시점에서는 어떤 파티션만 사용될지 MySQL 서버가 알아낼 방법이 없다. 그래서 파티션된 테이블을 열고 잠금을 거는 작업은 파티션 프루닝이 적용되지 않는다. 즉 파티션 테이블에 쿼리가 실행되면 MySQL 서버는 테이블의 파티션 개수에 관계없이 모든 파티션을 열고 잠금을 걸게 된다. 이는 테이블의 파티션 개수가 많아지면 많아질수록 더 느려지게 되므로 적정 수준의 파티션이 있는 테이블에서는 오히려 더 느려지는 현상이 발생하는 것이다. 이런 현상은 MySQl 5.1과 5.5에서 똑같이 발생하며, 오라클에서는 이 문제를 버그로 등록하고 현재 개선 방법을 찾고 있는 중이다.

여기서 언급한 잠금은 테이블 잠금을 이야기하는 것인데, InnoDB 테이블에서 테이블 잠금은 큰 역할을 수행하지는 않는다. 하지만 여전히 모든 파티션에 테이블 잠금을 거는 추가적인 부하는 피할 수 없다. 만약 파티션이 많이 포함된 테이블에 한 번에 많은 레코드를 INSERT하거나 UPDATE한다면 LOCK TABLES 명령으로 테이블을 잠그고 INSERT나 UPDATE를 수행하면 조금은 더 빠르게 처리할 수 있다.

참고

• Real MySQL