RDB - 파티션을 사용할 때 고려할 부분! (feat. 공식문서)

파티션을 사용할 때 고려할 부분

합류한 새로운 팀에서는 파티셔닝을 적극적으로 사용하고 있었다.

Partitioning(파티셔닝)은 논리적으로 하나의 테이블이지만, 물리적으로는 여러 개의 파일 시스템에 분산하는 방법이다.

파티셔닝은 테이블과 인덱스 모두 적용된다. 파티셔닝을 사용하는 이유는 다음과 같다.

• 더 많은 데이터를 하나의 테이블에 저장할 수 있다.
• 유용성을 잃은 데이터에 대해 특정 파티션을 제거하는 등 관리가 가능하다.
• 검색에 대한 DB 부하를 감소하는 기능이다. (WHERE 절로 필요하지 않은 파티션은 자동으로 제외)

아래 공식 문서를 기반으로 파티셔닝을 사용할 때 고려해야할 부분에 대해 알아보자.

1. 인덱스(Index)

파티셔닝을 사용할 때 고려해야 할 것이 있다. 바로 Index이다.

파티셔닝을 사용할 때 인덱스를 활용할 수 없다면 효율이 떨어질 것이다. 반면, 주로 사용하는 인덱스가 파티션 풀 스캔을 하는 경우에도 효율이 떨어진다.

파티션 O 인덱스 O

가장 이상적인 케이스는 파티션 스캔을 할 수 있으면서 원하는 인덱스를 타는 경우이다.

이 경우 주어진 파티션 내에서 인덱스로 스캔하므로 최상의 조건이라고 할 수 있다.

파티션 X 인덱스 O

가장 대표적인 예시로 PK 스캔을 하는 경우 예가 된다.

거래일자를 기반으로 파티션을 생성한 경우 PK 스캔을 하면 전체 파티션을 찾아야 하지만, PK를 사용해서 데이터를 빠르게 찾을 수 있다.

파티션의 개수가 많지 않다면 크게 문제되지는 않을 것이다. 다만, 파티션 수가 지나치게 많을 경우 가랑비에 옷이 젖는 것처럼 자원에 대한 문제가 생길 우려가 있다. 파티션을 사용하도록 유도하는 것이 더 좋을 수 있다.

파티션을 사용하려면 WHERE 조건에서 파티션 키에 있는 모든 컬럼을 모두 사용해야 한다.

파티션 O 인덱스 X

이 경우 특정 파티션만 읽으면 되지만 인덱스를 사용하지 않는 경우에 해당한다.

즉, 특정 파티션에 대해 Full Table Scan을 하기에 상당히 느린 쿼리가 발생한다.

파티션 X 인덱스 X

테이블의 모든 파티션을 검색하고 각 파티션을 Full Table Scan 해야 하기에 최악의 쿼리이다.

좋은 설계..?

파티션 스캔도 하고 인덱스도 타면 더할 나위 없이 좋다. 하지만 파티션과 인덱스의 기준은 요구사항에 따라 매우 다를 수 있다. 특정 서비스에서는 일자가 파티션 키일 수 있고, 어떤 서비스에서는 지역일 수 있다.

어떤 서비스에서는 요구사항이 목록 조회만 있어서 파티션을 모든 경우에서 활용할 수 있을 수도 있고, 특정 서비스에서는 PK 조회, 파티션 키가 아닌 다른 키로 조회해야 하는 등 요구사항이 있을 수 있다.

즉, 모든 서비스에서 Partition Key로 모든 요구사항에서 조회할 수 있기를 기대하는 것은 현실적으로 어렵다.

일반적인 Case에서는 파티션을 사용하지 않더라도 인덱스를 사용한다면 크게 무리가 있지는 않을 것이다. 즉, 인덱스를 사용하지 않는 것을 더 지양하기를 권한다.

튜닝이 가능할 수도 있다.

배민시스템에서 주문일시, 배달일시 컬럼이 있고 배달일시를 조건으로 검색을 해야 한다. 하지만 주문일시 컬럼이 파티션 키라고 가정하자. 이때는 배달일시를 조건으로 조회하더라도 주문일시까지 넉넉하게 이틀 정도를 조건으로 잡아주면 파티션 풀 스캔을 막을 수 있다.

2. 락(Lock)

MySQL을 예시로 5.6.6 이전버전에서는 SELECT를 시도했을 때 전체 테이블에 Lock이 걸린다. 아래 설명은 MySQL 5.6.6 이상의 버전을 기준으로 한다.

SELECT의 경우 기본적으로 실제로 읽어야 하는 파티션을 대상으로 잠금을 수행한다. 만약 1개의 파티션을 대상으로 조회한다면 해당 파티션만 LOCK이 발생하고, 모든 파티션을 대상으로 조회한다면 모든 파티션에 LOCK이 발생한다.

UPDATE의 경우에는 파티셔닝 키 컬럼이 변경되지 않았을 경우 해당 파티션만 잠근다. 하지만, 파티셔닝 키 컬럼이 변경될 경우 MySQL에서는 어떤 파티션이 영향을 받을지 예측할 수 없으므로 모든 파티션을 잠궈 무결성을 보장한다.

INSERT는 삽입된 데이터의 파티셔닝 키에 따라 특정 파티션만 잠근다. 단, 파티셔닝 키가 AUTO_INCREMENT에 의해 생성된다면 모든 파티션을 잠근다.

정리하면 파티셔닝된 테이블은 1개의 테이블로 관리될 때에 비해 의도치 않게 많은 영역에 대해 Lock을 걸게될 수 있다. 그래서 데드락을 유발하기도 한다.

그래서 조회 조건이 특정 파티션만을 조회하도록 하고, 파티셔닝 키 컬럼을 변경할 때는 각별히 주의해야 한다.

3. 파일 시스템 환경 변수

기본적으로 MySQL 에서는 1개 테이블을 열 때 2~3개의 파일을 조회한다.

만약 파티션이 1024개라면 1024 x 2~3 개의 파일을 조회할 수도 있다는 말이다.

MySQL에서는 관련해서 아래의 환경 변수를 지원한다. 해당과 같은 환경 변수를 적절하게 조절해야 한다.

• large_files_support: 대용량 파일 지원 여부 (여러 파티션을 조회한다면 활성화가 되어 있어야 한다.)
• open_files_limit: MySQL이 오픈할 수 있는 최대 파일 개수 (default = OS에 따라 조정됨)

4. etc

파티셔닝된 테이블에서는 아래의 제약이 있다.

• FK를 사용할 수 없다.
• Full-Text Index를 사용할 수 없다.
• Geometry(point, geometry, ...) 컬럼 타입을 사용할 수 없다.

참고

• https://dev.mysql.com/doc/refman/8.4/en/partitioning.html
• https://dev.mysql.com/doc/refman/5.7/en/partitioning-limitations-locking.html