(MySQL) 파티셔닝
*파티션이란 MySQL서버 입장에서는 데이터를 별도의 테이블로 분리해서 저장하지만 사용자 입장에서는 여전히 하나의 테이블로 읽기와 쓰기를 할 수 있게 해주는 솔루션이다.
<파티션을 사용하는 이유>
1. 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인메모리보다 훨씬 크거나
2. 데이터의 특성상 주기적인 삭제 작업이 필요할 경우 파티션이 필요하다.
*테이블의 데이터가 10GB이고 인덱스가 3GB라고 가정하자. 하지만 대부분의 테이블은 13GB전체를 항상 사용하는것이 아니라 그중에서 활발하게 사용하는 부분을 주로 다시 사용한다. 즉, 회원이 100만명이라더라도 그중에서 활발하게 사용하는 회원은 2~30%수준이라는 것이다. 거의 대부분의 테이블데이터가 이런형태로 사용된다고 볼 수 있는데 ,활발하게 사용되는 데이터를 워킹 셋이라고 표현한다.
테이블의 데이터를 워킹 셋과 그렇지 않은 부류로 파티셔닝 할 수 있다면 상당히 효과적으로 성능을 개선할 수 있을것이다.
<이력데이터의 효율적인 관리>
로그데이터는 단기간에 대량으로 누적됨과 동시에 일정기간이 지나면 쓸모없어진다.
<파티션 테이블의 인덱스 스캔과 정렬>
MySQL의 파티션 테이블에서 인덱스는 전부 로컬 인덱스에 해당한다. 즉 모든 인덱스는 파티션 단위로 생성되며, 파티션에 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다는 것을 의미한다.
실제 MySQL 서버는 여러 파티션에 대해 인덱스 스캔을 수행 할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위 큐에 임시로 저장한다.
그리고 우선순위 큐에서 다시 필요한 순서(인덱스의 정렬순서)대로 데이터를 가져가는 것이다.
결론적으로 파티션 테이블에서 인덱스 스캔을 통해 레코드를 읽을 때 MySQL서버가 별도의 정렬 작업을 수행하지는 않는다. 하지만 일반 테이블의 인덱스 스캔처럼 결과를 바로 반환하는 것이 아니라 내부적으로 큐처리가 한번 필요한 것이다.
<파티션 프루닝(partition pruning)>
필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는 것을 파티션 프루닝이라고 한다.
<파티션의 제한사항>
즉 파티션 단위로 인덱스를 변경하거나 추가 할 수 없다.
<파티션시 주의사항>
파티션 테이블의 경우 프라이머리 키를 포함한 유니크 키에 대해서는 상당히 머리를 아프게하는 제약 사항이있다. 파티션의 목적이 작업의 범위를 좁히기 위함인데, 유니크 인덱스는 중복 레코드에 대한 체크 작업 때문에 범위가 좁혀지지 않기 때문이다.또한 MySQL의 파티션 또한 테이블과 같이 별도의 파일로 관리 되기 때문에 MySQL서버가 조작 할 수 있는 파일의 개수와도 연관된 제약이있다.
<파티션 테이블과 잠금>
MySQL에서는 파티션 테이블이 가지는 파티션의 갯수가 늘어날 수록 성능이 더 떨어 질 수 있다.
예를들어, 파티션이 350개 정도인 테이블에 10000건의 레코드를 INSERT해 보면 오히려 파티션이 없는 테이블의 INSERT가 30%정도 더 빠르게 처리 된다.
MySQL에서 파티션 테이블에 쿼리가 수행되면 우선 테이블의 열고 잠금을 걸고 쿼리의 최적화를 수행한다. 쿼리의 처리에 필요한 파티션만 선별하는 파티션 프루닝 작업은 쿼리의 최적화 단계에서 수행되므로 테이블을 열고 잠금을 거는 시점에서는 어떤 파티션만 사용될지 MySQL서버가 알아낼 방법이 없다.
그래서 파티션된 테이블을 열고 잠금을 거는 작업은 파티션 프루닝이 적용되지 않는다.
즉 파티션 테이블에 쿼리가 실행되면 MySQL서버는 테이블의 파티션 개수에 관계없이 모든 파티션을 열고 잠금을 걸게 된다. 이는 테이블의 파티션개수가 많아질 수록 더 느려지게 되므로 적정 수준의 파티션이 있는 테이블에서 오히려 더 느려지는 현상이 발생하는 것이다.
<파티션을 사용하는 이유>
1. 하나의 테이블이 너무 커서 인덱스의 크기가 물리적인메모리보다 훨씬 크거나
2. 데이터의 특성상 주기적인 삭제 작업이 필요할 경우 파티션이 필요하다.
*테이블의 데이터가 10GB이고 인덱스가 3GB라고 가정하자. 하지만 대부분의 테이블은 13GB전체를 항상 사용하는것이 아니라 그중에서 활발하게 사용하는 부분을 주로 다시 사용한다. 즉, 회원이 100만명이라더라도 그중에서 활발하게 사용하는 회원은 2~30%수준이라는 것이다. 거의 대부분의 테이블데이터가 이런형태로 사용된다고 볼 수 있는데 ,활발하게 사용되는 데이터를 워킹 셋이라고 표현한다.
테이블의 데이터를 워킹 셋과 그렇지 않은 부류로 파티셔닝 할 수 있다면 상당히 효과적으로 성능을 개선할 수 있을것이다.
<이력데이터의 효율적인 관리>
로그데이터는 단기간에 대량으로 누적됨과 동시에 일정기간이 지나면 쓸모없어진다.
<파티션 테이블의 인덱스 스캔과 정렬>
MySQL의 파티션 테이블에서 인덱스는 전부 로컬 인덱스에 해당한다. 즉 모든 인덱스는 파티션 단위로 생성되며, 파티션에 관계없이 테이블 전체 단위로 글로벌하게 하나의 통합된 인덱스는 지원하지 않는다는 것을 의미한다.
실제 MySQL 서버는 여러 파티션에 대해 인덱스 스캔을 수행 할 때, 각 파티션으로부터 조건에 일치하는 레코드를 정렬된 순서대로 읽으면서 우선순위 큐에 임시로 저장한다.
그리고 우선순위 큐에서 다시 필요한 순서(인덱스의 정렬순서)대로 데이터를 가져가는 것이다.
결론적으로 파티션 테이블에서 인덱스 스캔을 통해 레코드를 읽을 때 MySQL서버가 별도의 정렬 작업을 수행하지는 않는다. 하지만 일반 테이블의 인덱스 스캔처럼 결과를 바로 반환하는 것이 아니라 내부적으로 큐처리가 한번 필요한 것이다.
<파티션 프루닝(partition pruning)>
필요한 파티션만 골라내고 불필요한 것들은 실행 계획에서 배제하는 것을 파티션 프루닝이라고 한다.
<파티션의 제한사항>
- 최대 1024개의 파티션을 가질 수 있음(서브 파티션까지 포함)
- 파티션 테이블에서는 외래키 사용불가
- 파티션 테이블은 전문 검색 인덱스 생성 불가
즉 파티션 단위로 인덱스를 변경하거나 추가 할 수 없다.
<파티션시 주의사항>
파티션 테이블의 경우 프라이머리 키를 포함한 유니크 키에 대해서는 상당히 머리를 아프게하는 제약 사항이있다. 파티션의 목적이 작업의 범위를 좁히기 위함인데, 유니크 인덱스는 중복 레코드에 대한 체크 작업 때문에 범위가 좁혀지지 않기 때문이다.또한 MySQL의 파티션 또한 테이블과 같이 별도의 파일로 관리 되기 때문에 MySQL서버가 조작 할 수 있는 파일의 개수와도 연관된 제약이있다.
<파티션 테이블과 잠금>
MySQL에서는 파티션 테이블이 가지는 파티션의 갯수가 늘어날 수록 성능이 더 떨어 질 수 있다.
예를들어, 파티션이 350개 정도인 테이블에 10000건의 레코드를 INSERT해 보면 오히려 파티션이 없는 테이블의 INSERT가 30%정도 더 빠르게 처리 된다.
MySQL에서 파티션 테이블에 쿼리가 수행되면 우선 테이블의 열고 잠금을 걸고 쿼리의 최적화를 수행한다. 쿼리의 처리에 필요한 파티션만 선별하는 파티션 프루닝 작업은 쿼리의 최적화 단계에서 수행되므로 테이블을 열고 잠금을 거는 시점에서는 어떤 파티션만 사용될지 MySQL서버가 알아낼 방법이 없다.
그래서 파티션된 테이블을 열고 잠금을 거는 작업은 파티션 프루닝이 적용되지 않는다.
즉 파티션 테이블에 쿼리가 실행되면 MySQL서버는 테이블의 파티션 개수에 관계없이 모든 파티션을 열고 잠금을 걸게 된다. 이는 테이블의 파티션개수가 많아질 수록 더 느려지게 되므로 적정 수준의 파티션이 있는 테이블에서 오히려 더 느려지는 현상이 발생하는 것이다.
댓글
댓글 쓰기