MySQL 파티션 종류 및 사이즈 설계 전략, 안하면 조회 안되는 테이블?

MySQL 데이터베이스가 많은 데이터를 컨트롤하기에는 한계가 있다는 말을 한적 있습니다. 그래서 페이징처리도 MySQL만의 방법으로 처리해야 합니다. 특히, MySQL의 경우 테이블 사이즈에 영향을 많이 받는 특징이 있습니다.

이 부분에 대해서는 “Why MySQL Could Be Slow With Large Tables”라는 링크에 글에서도 언급하고 있고 그 하나의 대안으로 파티션을 제안하고 있습니다. 이 글에서는 어떤 경우에 파티션 테이블로 만들어야 하는지 그 기준을 알아보도록 하겠습니다. 

 

MySQL 파티션 설계 전략

 

느려진 테이블 파티션이 답이다

어느 날 오후, MySQL에서 일부 서비스에서 데이터를 보여주는 게 점점 느려진다는 확인 요청이 들어왔습니다. 특히, 상품의 가입 내역을 저장하는 테이블이 100GB를 넘어서면서 쿼리 실행 시간이 눈에 띄게 길어졌죠. 점점 서비스 장애까지 우려되는 상황이더군요.

원인 파악을 위해 쿼리 실행 계획을 바로 분석해 보았죠. 그 결과 특정 컬럼에 대한 인덱싱이 부족하고, 테이블 Full Scan이 자주 발생하고 있더군요. 무엇보다 테이블의 크기가 커지면서 디스크 I/O 부하가 증가하여 전체 시스템 성능에 영향을 미치고 있었습니다.

여러 가지 해결 방안을 검토한 결과, 테이블 파티션이 가장 적합한 해결책이라고 판단했습니다. 그리고 아래와 같은 파티션 전략으로 접근했죠.

 

테이블 사이즈별 전략

MySQL의 경우 실제 사용해 보고, 필드에서 경험한 부분을 가지고 정리해 보았습니다. 대략적으로 구분해서 아래와 같은 사이즈 별로 가이드할 수 있습니다. 

 

테이블 사이즈	내용	전략 및 가이드
10GB 이하	대부분 성능 문제가 거의 없습니다. 대부분의 SQL의 경우 이 크기의 테이블은 효율적으로 처리할 수 있습니다.	단일 테이블 권장
10GB ~ 100GB	이 범위의 테이블은 특정 쿼리 패턴이나 인덱스 설계에 따라 성능 저하가 발생할 수 있습니다.	Case 별로 파티션 필요
100GB ~ 1TB	이 크기는 성능 최적화가 필수적입니다. 인덱싱, 파티션, 샤딩, 하드웨어 성능 등의 요소를 모두 고려해야 합니다. 대규모 데이터 처리에 맞는 서버 파리미터 설정도 최적화가 중요합니다.	파티션 필요
1TB 이상	이 수준은 대부분 MySQL db에서 성능 문제가 발생합니다. 데이터베이스 아키텍처를 재설계하거나, 분산 데이터베이스 시스템으로 전환이 필요합니다.	재설계 권장

그렇다면 사이즈 확인은 어떻게 할 수 있을까요? 통계정보를 이용하여 간단하게 size를 체크할 수 있습니다. 아래 SQL문은 파티션 테이블뿐 아니라 일반 테이블까지 모든 정보를 확인해 볼 수 있는 쿼리입니다. 

WITH PART_VIEW as        (SELECT TABLE_SCHEMA, TABLE_NAME, COUNT(*) PRT_CNT, MIN(partition_name) PRT_MIN , MAX(partition_name) PRT_MAX_1 , MAX(IF(partition_name='PR_99','PR_00',partition_name)) PRT_MAX_2 FROM INFORMATION_SCHEMA.PARTITIONS where partition_name is not null  GROUP BY TABLE_SCHEMA, TABLE_NAME ) select a.table_name as 'Table'      , b.PRT_CNT, b.PRT_MIN, b.PRT_MAX_1, b.PRT_MAX_2      , round((data_length + index_length) / 1024 / 1024, 2) as 'Size(MB)'  , round((data_length + index_length) / 1024 / 1024 / 1024, 2) as 'Size(GB)'  , round((data_length)  / 1024 / 1024 / 1024, 2) as 'Data Size(GB)'  , round((index_length) / 1024 / 1024 / 1024, 2) as 'Index Size(GB)' from information_schema.tables a left outer join       PART_VIEW b on ( a.TABLE_SCHEMA = b.TABLE_SCHEMA and   a.table_name = b.table_name) where a.table_schema = '데이터베이스명' order by  6 DESC  ;

MySQL 파티션 테이블 전략은 데이터 크기와 사용 패턴에 따라 달라질 수 있습니다. 적절한 파티셔닝 전략을 통해 데이터베이스의 성능과 관리 효율성을 향상할 수 있습니다. 우선 파티셔닝 종류 및 방법부터 알아보겠습니다. 

파티셔닝 종류

1. 레인지 파티셔닝 (Range Partitioning)

가장 많이 사용되는 range 파티션인데요. 날짜나 시간 기반 데이터에서 많이 사용됩니다. 각 파티션에 특정 범위의 값을 할당해서 분할합니다. 

PARTITION BY RANGE (year) ( PARTITION p0 VALUES LESS THAN (1991), PARTITION p1 VALUES LESS THAN (1995), PARTITION p2 VALUES LESS THAN (2000), PARTITION p3 VALUES LESS THAN (MAXVALUE) );

 

2. 리스트 파티셔닝 (List Partitioning)

특정 값 목록을 기준으로 데이터를 분할할 때 사용합니다. 각 파티션에 명시된 값 목록을 할당합니다.

PARTITION BY LIST (region) ( PARTITION p0 VALUES IN ('North', 'South'), PARTITION p1 VALUES IN ('East', 'West') );

 

3. 해시 파티셔닝 (Hash Partitioning)

내부적으로 균등하게 데이터를 분산시킬 때 사용합니다. 균등하게 분산시키는 방법이 해시 함수를 사용하는데요. 이 함수를 통해 각 파티션을 할당합니다.

PARTITION BY HASH (user_id) PARTITIONS 4;

 

4. 키 파티셔닝 (Key Partitioning)

해시 파티셔닝과 유사하지만 MySQL의 내부 해시 함수를 사용합니다. 하나 이상의 열에 기반하여 파티션을 할당할 때 사용합니다. 

PARTITION BY KEY(user_id) PARTITIONS 4;

 

테이블 사이즈 전략

1. 파티션 크기 관리

각 파티션의 크기를 적절히 유지하여 관리하는 것이 성능 최적화하는데 도움을 줍니다. 업무에 따라 마지막 파티션에 많은 데이터가 몰리는 경우도 있지만 일반적인 경우 골고루 분산되게 파티션을 설계해야 합니다. 

 

각 파티션의 크기가 적정 크기는 10-50GB 내가 되도록 설정하는 것을 추천합니다. 너무 큰 파티션은 성능 저하를 유발할 수 있습니다. 하지만 파티션을 너무 작게 만들어 많은 파티션을 생성하는 것도 성능에 그렇게 좋지는 않습니다. 

 

2. 자동 파티션 관리

주기적으로 새로운 데이터를 Insert되는 경우에는 자동 파티션 설정을 해 놓은 게 좋습니다. 새로운 데이터가 계속 들어오면 새 파티션을 생성해야 하는데, 가끔씩 생성시기를 놓치는 경우가 있기 때문입니다.

 

그럴 경우를 대비해 Range 파티션은 maxvalue를 설정할 수 있지만, MySQL의 List 파티션은 디폴트 파티션을 지정할 수 없습니다. 과거의 오래된 데이터를 아카이빙 하거나 삭제하여 전체 파티션 크기가 너무 늘어나지 않도록 관리하는 것이 좋습니다. 

ALTER TABLE logs REORGANIZE PARTITION p3 INTO ( PARTITION p3 VALUES LESS THAN (2023), PARTITION p4 VALUES LESS THAN (MAXVALUE) );

 

3. 인덱스와 파티션 키의 일치

인덱스가 파티션 키와 일치해야 쿼리 성능이 좋습니다. 자주 사용되는 쿼리에 대해 파티션 키와 일치하는 인덱스를 생성하는 것을 추천합니다.

 

4. 파티션 통계정보 관리

통계정보 테이블의 최신화하는 것도 하나의 방법입니다. 파티션 관련 메타데이터 테이블을 주기적으로 최적화해 주시기 바랍니다. 

ANALYZE TABLE partition_table_name;

 

5. 적합한 파티션 적용

테이블과 파티션의 구조가 적합해야 관리 효율성을 높일 수 있습니다. 데이터 분포와 사용 패턴을 분석하여 파티션의 수와 크기를 적절히 조정합니다.

 

예를 들어, 년 파티션을 할지, 월 파티션을 할지, 일 파티션을 할지 정의해야 합니다. 또 년 파티션을 했다가 너무 데이터가 많아지면 아래와 같이 월 파티션을 고려해보아야 합니다.  

ALTER TABLE table_name PARTITION BY RANGE (TO_DAYS(date_column)) ( PARTITION p0 VALUES LESS THAN (TO_DAYS('2023-01-01')), PARTITION p1 VALUES LESS THAN (TO_DAYS('2023-02-01')), PARTITION p2 VALUES LESS THAN (TO_DAYS('2023-03-01')), PARTITION p3 VALUES LESS THAN (TO_DAYS('2023-04-01')) );

 

위 전략들은 데이터의 크기와 사용 패턴에 따라 선택해서 적용할 수 있습니다. 각 전략을 테스트하고 모니터링하여 최적의 파티셔닝 구조를 찾는 것이 중요합니다.