10장 파티션

파티션을 사용하는 이유

한 테이블이 너무 커서 인덱스의 크기가 물리적인 메모리보다 훨씬 크거나, 데이터 특성상 주기적인 삭제 작업이 필요한 경우 등이 파티션이 필요한 대표적인 예이다.

단일 INSERT와 단일 또는 범위 SELECT의 빠른 처리

• update, delete 처리를 위해 대상 레코드를 검색하려면 인덱스는 필수적
• 인덱스의 워킹 셋이 실질적인 물리 메모리보다 크다면 쿼리 처리가 느려짐
  • 파티셔닝을 안하면 인덱스가 커지고 물리적인 메모리 공간도 많이 필요해짐 → 효율적 관리를 위해 필요

데이터의 물리적인 저장소를 분리

• 파티션을 통해 파일의 크기를 조절하거나 각 파티션별 파일들이 저장될 위치나 디스크를 구분해서 지정해서 해결 가능
• 하지만 mysql에서는 인덱스를 순차적으로 생성하는 방법은 아직 허용되지 않음

이력 데이터의 효율적인 관리

• 로그 테이블을 파티션 테이블로 관리한다면 불필요한 데이터 삭제 작업은 단순히 파티션을 추가하거나 삭제하는 방식으로 간단하고 빠르게 해결할 수 있다.

파티션 테이블의 레코드 insert

• MySQL 서버는 INSERT 되는 칼럼의 값 중 파티션 키를 이용해서 파티션 표현식을 평가하고, 그 결과를 이용해 레코드가 저장될 적절한 파티션을 결정한다.

파티션 테이블의 레코드 update

• UPDATE 쿼리의 WHERE 조건에 파티션 키 칼럼이 조건으로 존재한다면 그 값을 이용해 레코드가 저장된 파티션에서 빠르게 대상 레코드를 검색할 수 있다.
• 하지만 WHERE 조건에 파티션 키 칼럼의 조건이 명시되지 않았다면 MySQL 서버는 변경 대상 레코드를 찾기 위해 테이블의 모든 파티션을 검색해야 한다.

파티션 테이블의 레코드 select

성능에 크게 영향을 미치는 조건

• WHERE 절의 조건으로 검색해야 할 파티션을 선택할 수 있는가?
• WHERE 절의 조건이 인덱스를 효율적으로 사용할 수 있는가?

주요 선택 사항

• 파티션 선택 가능 + 인덱스 효율적 사용 가능
  • 가장 효율적으로 처리
• 파티션 선택 불가 + 인덱스 효율적 사용 가능
  • 파티션 개수만큼의 테이블에 대해 인덱스 레인지 스캔을 한 다음 결과를 병합해서 가져오는 것과 같다
• 파티션 선택 가능 + 인덱스 효율적 사용 가능
  • 파티션에 대해 풀 테이블 스캔을 한다.
• 파티션 선택 불가 + 인덱스 효율적 사용 불가
  • 모든 파티션에 대해 풀 테이블 스캔 (최악..)

파티션 테이블의 인덱스 스캔과 정렬

• 인덱스는 전부 로컬 인덱스
• 인덱스는 파티션 단위로 생성
• 파티션에 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다.
• 실제 MySQL 서버는 여러 파티션에 대해 인덱스 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위 큐(Priroity Queue)에 임시로 저장한다. 그리고 Priority Queue에서 다시 필요한 순서대로 데이터를 가져간다.

파티션 프루닝

• 최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제
• 실행 계획을 확인할 때는 explain partitions 명령어 사용

주의사항

• 제약 사항 조심할것(타입, 유니크 값 ...)
• MySQL에서는 일반적으로 테이블을 파일 단위로 관리하기 때문에 MySQL 서버에서 동시에 오픈된 파일의 개수가 상당히 많아질 수 있다. 이를 제한하기 위해 open-files-limit 시스템 변수가 존재한다. 파티션을 많이 사용하는 경우에는 open-files-limit를 적절히 높은 값으로 다시 설정해 줄 필요가 있다.

파티션 테이블 종류

• 레인지 파티션
  • 연속된 범위로 파티션을 정의 - 일반적으로 사용함
  • 보통 날짜를 파티션 키로 잡는다. 날짜를 키로 잡을 때 YEAR 또는 TO_DAYS 함수의 사용을 추천한다.
  • 레인지 파티션에서 NULL은 어떤 값보다 작은 값으로 간주된다.
• 리스트 파티션
  • 레인지 파티션과 흡사하지만 키 값 하나하나를 리스트로 나열
  • 레인지 파티션과 달리, 나머지 모든 값을 저장하는 MAXVALUE 파티션은 정의할 수 없다.
• 해시 파티션
  • 해시 함수에 의해 레코드가 저장될 파티션을 경정하는 방법
  • 특정 테이블만 DROP하는 것이 불가능하다.
  • 새로운 파티션을 추가하는 작업은 단순히 파티션만 추가하는 것이 아니라 기존의 모든 데이터의 재배치 작업이 필요하다.
  • 해시 파티션은 레인지 파티션이나 리스트 파티션과는 상당히 다른 방식으로 관리하기 때문에 해시 파티션이 용도에 적합한 해결책인지 확인이 필요하다.
• 키 파티션
  • 해시 파티션과 비슷하나, 선정된 파티션 키의 값을 MD5( ) 함수를 이용해 해시 값을 계산하고, 그 값을 MOD 연산해서 데이터를 각 파티션에 분배한다. 이것이 해시 파티션과의 차이점이다.
• 서브파티션
  • 서비스의 요건에 따라 기간 단위로 레인지 파티션을 생성하고, 각 레인지 파티션 내에서 다시 지역별로 리스트 서브 파티션을 구성하는 형태의 파티션이 가능하다. 하지만 MySQL에서는 최대로 사용 가능한 파티션의 개수가 다른 DBMS보다 상당히 제한적이라 서브 파티션으로 얻을 수 있는 이점은 별로 없다.

결론

• 파티션과 관련된 성능테스를 해본 결과, SELECT 쿼리 성능에는 그다지 큰 도움을 주지 못했고, 쓰기 성능에는 어느 정도 도움되는 것으로 보인다.
• MySQL에서는 레코드의 평균 크기나 하드웨어의 성능에 따라 차이는 있겠지만 경험상 1~3억건 수준이 임계치라고 생각한다. 어떤 파티션 종류를 사용하든 모든 파티션을 골고루 읽고 써야 하는 테이블이라면 절대 파티션을 이용해 SELECT 성능을 향상시키는 어렵다. 하지만 레코드의 건수가 너무 많아져서 INSERT나 DELETE와 같은 쓰기 작업 심각하게 느려지고 있다면 파티션 적용을 고려해 보는 것이 좋다.
• 만약 날짜 칼럼을 이용해 레인지 파티션을 사용할 수 있고, 읽기나 쓰기 작업을 일부 파티션으로 모을 수 있다면 테이블의 크기에 관계없이 항상 파티션을 적용하는 것이 쓰기 및 읽기, 그리고 관리 작업에까지 상당히 도움될 것이다.