[3day] 퍼포먼스 최적화 정리

5.2 테이블 파티셔닝을 활용해 대용량 데이터를 관리하자

#파티셔닝 간단히 이해하기

파티셔닝은 하나의 커다란 데이터를 여러 개의 데이터로 분할 저장함으로써 데이터 선택 효율을 높이는 기법이다.

특히 InnoDB에서는 PK순으로 데이터가 저장되기 때문에 무작위로 PK를 생성하여 insert를 수행하면 성능이 급격히 저하될 수 있다. 이 경우 테이블에 파티셔닝을 적용하면 비효율을 어느 정도 극복할 수 있다. 하지만 파티셔닝 사용에는 다음과 같은 몇 가지 제약 사항이 있다.

 

1) 파티셔닝 키는 PK와 연관된 컬럼이어야 한다.

2) 파티셔닝 사용시 Unique Key혹은 Forgien Key와 같은 제약조건을 추가할 수 없다.

(단 Unique Key가 PK로 사용되는 경우는 제외)

3) 파티셔닝 적용 시 풀텍스트 인덱싱 혹은 스페셜 인덱싱 기능을 사용할 수 없다.

4) 데이터 조회시 반드시 파티셔닝 키가 포함되어야하며 그렇지 않은 경우 전체 데이터 파일을 스캔한다.

--> 반드시 파티셔닝 키가 포함되어야하며 그렇지 않을경우 Full scan 한다는 얘기.. ㅎㄷㄷ

 

#파티셔닝 테이블 스키마

CREATE TABLE partition_test (
ID varchar(50) NOT NULL,
NAME varchar(40) NOT NULL,
TYPE varchar(20) NOT NULL,
DETAIL varchar(255) NOT NULL,
REG_TIME datetime NOT NULL,
KEY(ID)
) ENGINE=InnoDB
PARTITION BY RANGE (TO_DAYS(REG_TIME)) (
    PARTITION P_MIN VALUES LESS THAN (to_days('19000101')),
    PARTITION P_20110516 VALUES LESS THAN (to_days('20120517')),
    PARTITION P_20110517 VALUES LESS THAN (to_days('20120518')),
    ....
    PARTITION P_MAX VALUES LESS THAN MAXVALUE
);

쿼리에서 P_MIN을 넣은 이유는 불필요한 파티셔닝 데이터 스탠을 방지하기 위해서다.

날짜 데이터를 파티셔닝한 상태에서 RANGE 파티셔닝 옵션으로 범위 검색(예를 들어 BETWEEN과 같은)을 하면 가장 앞단의 파티셔닝 파일을 스캔한다. 그런데 가장 앞단의 파티셔닝에 데이터가 수천만 건 저장되어 있다면 범위 검색을 할 때마다 대용량 데이터를 매번 스캔해야 하는 비효율이 발생한다. 즉, 불필요한 스캔을 방지하기 위해 생성한 깡통 파티셔닝이라고 보면 된다(Mysql 5.5 이상에서는 Range Column 옵션으로 날짜를 파티셔닝해도 이러한 문제가 발생하지 않는다) --> Mysql 5.5 이하버전에서는 MIN을 반드시 넣어야한다는 얘기 5.5이상에서는 굳이 Min 넣을 필요 없다.

 

#파티셔닝 플랜확인

mysql> explain partitions
select * from partition_test
where REG_TIME BETWEEN '20120517' and '20120518'

#파티셔닝 추가

파티셔닝을 하나 더 추가하기위해서 다음과 같이 P_MAX 파티셔닝 테이블을 재정의한다. 여기서 P_MAX 파티션에 데이터가 많으면 파티셔닝이 추가되는 동안 테이블 잠금이 발생하여 서비스에 영향을 줄 수 있으므로, 재정의 전에 반드시 확인하는 습관을 들이도록 하자.

-->P_MAX에 데이터가 많으면 재정의하는데 시간이 많이 걸리므로 count(*) 데이터가 얼마나되는지 확인하라는 얘기며

혹시 예상치 못한 락이 발생할 수도 있으므로 실행 전에 파티션테이블에 락이 있는지 확인한다.

select * from information_schema.metadata_lock_info;

ALTER TABLE partition_test REORGANIZE PARTITION P_MAX INTO (
	PARTITION P_20120523 VALUES LESS THAN (to_days('20120524')),
    PARTITION P_MAX VALUES LESS THAN MAXVALUE
);

--> 기존 스크립트는 MAXVALUE Drop 후에 재생성했었는데 REORANIZE 로 바꿔봐야겠다.

 

#파티셔닝 삭제

ALTER TALBE partition_test DROP partition P_20110516;

 

5.3.2 통계 테이블을 적절히 사용하자

서비스를 수행하다 보면 데이터 수를 세는 로직이 필요한 경우가 많다. 예를 들어 데이터 페이징을 위해 데이터 수를 카운트하는데, 페이징을 계산하려면 반드시 전체 데이터 수가 필요하다. 어느 경우나 마찬가지로 데이터 수가 크지 않다며 큰 문제가 없겠지만 데이터가 누적되어 대용량화되면 어느 시점부터는 성능에 커다란 저해요소가 된다.

 

이러한 경우에 통계 테이블을 적절하게 도입해서 관리하면 매번 데이터 건수를 확인하는 카운트 쿼리를 실행하지 않아도 현재 필요한 카운트 정보를 바로 가져올 수 있다.

통계테이블

위와같이 데이터를 처리하려면 로직을 일부 변경해야 한다. 데이터를 insert 하는 로직만 있었다면 데이터를 insert한 후 데이터 카운트를 변경하는 로직을 추가해야 한다.

 

특정 일의 데 통계를 구하는 서비스를 구현하려면 다음과 같이 간단한 쿼리를 사용하면 된다.

select count(*) from log where name = 'RED';

만약 하루에 한번씩 호출되는 쿼리라면 큰 문제가 없겠지만 애플리케이션에서 자주 위와같은 쿼리를 호출한다면 서비스 성능에 엄청난 타격을 준다.

 

이 문제를 해결하기 위해 통계성 테이블을 다음과 같이 추가한다.

CREATE TABLE log_stat(
name varchar('16') not null,
name_count int not null,
parimary key(name)
);

그리고 데이터를 insert할 때 log_stat에 관련 건수를 업데이트한다.

INSERT INTO log_stat (name, name_count) VALUES ('RED', 1)
ON DUPLICATE KEY UPDATE name_count = name_count + 1;

RED 데이터 카운트는 다음과 같이 수행한다.

select name_count FROM log_stat WHERE name ='RED';

이렇게 하면 RED의 데이터 수를 찾기 위해 매번 log 데이터 카운트를 수행하지 않아도 된다.

따라서 서비스 품질이 향상될 뿐만 아니라 DB 효율도 자연스럽게 높아진다.

-->상당히 유용한 정보이다. 테이블 정규화만 제대로 된다면 rownum 안쓰는 것과 더불어 상용서비스에 바로 적용검토해봐야할 거 같다.