내맘대로 정리하는 Real MySQL #10장) 파티션

<내맘대로 정리하는> 시리즈는, 책을 읽으며 몰랐던 내용을위주로 정리한 내용 그대로 포스팅하는 시리즈입니다 ^^

원문의 문맥이 궁금하면 (좋은 책이니) 이 참에 하나 장만하는 것은 어떤가요??

파티션이란 MySQL 서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션이다. 일반적으로 DBMS의 파티션은 하나의 서버에서 테이블을 분산하는 것이며, 원격 서버 간에 분산을 지원하는 것은 아니다.

파티션을 사용하는 이유

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

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

레코드를 변경하는 쿼리를 실행하면 인덱스의 변경을 위한 부가적인 작업이 발생한다. 이때 인덱스의 크기가 너무 크다면 쿼리 수행 속도가 느려지게 된다.

파티션을 적용하면, 기존 테이블 하나에 대한 INDEX가 파티션 테이블에 대한 INDEX 여러개로 쪼개지기 때문에 INDEX 크기가 줄어들게 된다.

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

파티션을 통해 파일의 크기를 조절하거나 각 파티션별 파일들이 저장될 위치나 디스크를 구분해서 지정할 수 있다.

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

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

파티션 테이블의 쿼리

INSERT

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

UPDATE

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

SELECT

두 가지 조건이 중요하다.

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

위 두 조건에 따라 아래와 같은 조합이 가능하다.

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

MySQL에서 모든 인덱스는 파티션 단위로 생성되며, 파티션에 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다.

실제 MySQL 서버는 여러 파티션에 대해 인덱스 스캔을 수행할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 Priroity Queue에 임시로 저장한다. 그리고 Priority Queue에서 다시 필요한 순서대로 데이터를 가져간다.

파티션 프루닝 : 최적화 단계에서 필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는 것 (EXPLAIN PARTITIONS로 확인할 수 있다)

파티션 테이블 주의사항

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

MySQL에서는 파티션 테이블이 가지는 파티션의 개수가 늘어날 수록 성능이 더 떨어질 수 있다.

파티션 테이블의 종류와 특징

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

결론

파티션과 관련된 성능테스를 해본 결과, SELECT 쿼리 성능에는 그다지 큰 도움을 주지 못했고, 쓰기 성능에는 어느 정도 도움되는 것으로 보인다.

MySQL에서는 레코드의 평균 크기나 하드웨어의 성능에 따라 차이는 있겠지만 경험상 1~3억건 수준이 임계치라고 생각한다. 어떤 파티션 종류를 사용하든 모든 파티션을 골고루 읽고 써야 하는 테이블이라면 절대 파티션을 이용해 SELECT 성능을 향상시키는 어렵다. 하지만 레코드의 건수가 너무 많아져서 INSERT나 DELETE와 같은 쓰기 작업 심각하게 느려지고 있다면 파티션 적용을 고려해 보는 것이 좋다.

만약 날짜 칼럼을 이용해 레인지 파티션을 사용할 수 있고, 읽기나 쓰기 작업을 일부 파티션으로 모을 수 있다면 테이블의 크기에 관계없이 항상 파티션을 적용하는 것이 쓰기 및 읽기, 그리고 관리 작업에까지 상당히 도움될 것이다.