'쇠똥굴리기(BOOK)/MySQL 퍼포먼스 최적화'에 해당되는 글 4건

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 안쓰는 것과 더불어 상용서비스에 바로 적용검토해봐야할 거 같다.

#트랜잭션 isolation 레벨에서 테이블 잠금이 발생할 수 있음을 기억하자
Mysql의 스토리지 엔진으로 InnoDB를 사용할 떄, 전체 데이터를 스캔하는 쿼리를 실행할 경우 서비스에 처리하는 데이터에 잠금현상이 발생할 수 있다.
쿼리 실행이 종료될 때까지 다른 세션에서는 테이블 안에 있는 데이터를 변경하지 못하기 때문에 서비스에 직접적인 영향이 있다.
이것은 InnoDB의 기본 Isolation 레벨이 REPEATABLE-READ 이기 때문에 발생하는 현상으로, 세션변수 일부를 변경하여 사전에 문제를 해결할 수 있다.

#현상
Mysql 에서 트랜잭션 Isolation 레벨은 기본적으로 REPEATABLE-READ 이다.
이 상태에서 insert into select .. 혹은 create table as select ..로 데이터를 처리하면 참조하는 테이블에 테이블단위 잠금이 발생할 수 있다.
select 만 발생함에도 테이블의 데이터를 변결할 수 없는 기이한 현상이 발생하는 것이다.

mysql> show variables like 'tx_isolation';
+---------------+-----------------+
| Variable_name | Value           |
+---------------+-----------------+
| tx_isolation  | REPEATABLE-READ |
+---------------+-----------------+
1 row in set (0.01 sec)

#발생원인
Mysql InnoDB 스토리지 엔진의 기본 Isolation 레벨이 REPEATABLE-READ로 설정되어 있기 때문에 발생한다. REPEATABLE-READ에서는 트랜잭션을 처리할 때
SELECT 결과의 일관성을 보장하기 위해 스냅샷을 이용한다.

위와 같이 insert 와 select 가 동시에 있는 경우에는 select 결과를 보장하기 위해 암묵적으로 select 임에도 불구하고 데이터 잠금과 비슷한 효과가 나타난다.
즉, select 작업이 종료될 때까지 해당데이터 변경작업을 할 수 없다.

#트랜젝션 isolation 설명

1) READ-UNCOMMITTED
다른 트랜잭션이 commit전의 상태를 볼수 있음 --> 응 안써요
바이너리 로그가 자동으로 row based 로 기록됨 (statement 설정불가, Mix 설정시 자동변환)

2) READ-COMMITED
commit된 내역을 읽을 수 있는 상태로, 트랜잭션이 다르더라도 다른 특정 트랜잭션이 commit을 수행하면 해당 데이터를 read 할 수 있음
바이너리 로그가 자동으로 row based로 기록됨(statement 설정불가, Mixed 설정시 자동변환)

REPEATABLE-READ와 달리 트랜잭션 발생할 때 마다 스냅샷을 뜨는 것으로 알고 있다.

만약 반영한다면 DB의 시스템 부하관련해서 좀 더 확인해봐야한다.

3) REPEATABLE-READ
Mysql InnoDB 스토리지 엔진의 기본 Isolation 레벨 --> default 옵션이다.
select 시 현재 데이터 버전의 스냅샷을 만들고 만든 스냅샷에서 데이터를 조회
동일 트랜젝션 내에서 데이터 일관성을 보장 데이터를 다시 읽으려면 트랜잭션을 다시 시작해야함

4) SERIALIZABLE
가장 높은 isolation 레벨로 트랜잭션이 완료될 때까지 select 문장이 사용하는 모든 데이터에 공유 락(shared lock)이 걸림 --> 응 안써요
다른 트랜잭션에서는 해당 영역에 관한 데이터 변경뿐만 아니라 입력도 불가

#해결방법
Isolation 레벨을 read-commited 로 변경하면 해결할 수 있다.
다음과 같이 세션 설정을 변경한 후에 create table as select, insert into select 를 수행하면 테이블 잠금 없이 데이터가 잘 처리되는 것을 확인할 수 있다.

SET GLOBAL transaction isolation level READ COMMITED;

vi /ect/my.cnf
-----------------------------------
transaction-isolation = READ-COMMITTED

단 isolation 레벨이 READ-COMMITED 인 경우에는 바이너리 로그 포맷을 MIXED로 설정해도 바이너리 로그가 ROW 기반으로 기록된다는 점을 반드시 기억해야한다.
만약 Mysql 리플리케이션 환경에서 시스템을 운영하고 있다면 설정을 변경한 후에 반드시 데이터 도익화가 정상적으로 잘 이루어지고 있는지를 주기적으로 체크해야한다.

--> replication 사용중이라면 DB 중단하고 cnf와 binlog_format=ROW 반영후 binlog reset하고

DB 재시작하는 것이 맘이 편할꺼 같은데?

#스키마 레벨에서의 접근법

#Mysql InnoDB에서 primary key는 성능에 직접적인 영향을 준다.
Mysql의 InnoDB에서는 primary key 순서로 데이터가 저장된다. primary key는 데이터에 접근하는 물리적인 주소로 사용된다고 봐도 된다.
MS-SQL의 클러스터 인덱스, 오라클의 IOT(index Organized table)라는 개념이 Mysql에서는 Primary key로 구현된 것이다.
(오라클의 클러스터 테이블 Cluster table 과는 다른 개념이다.) 즉, 인덱스 순서로 데이터가 정렬되어 디스크에 저장된다.

#B+TREE 알고리즘에 따른 pk 저장순서
pk 데이터 (4개가 있다고 하면 아래와 같이 순차적으로 생성되어있다.)
10->20-> 30-> 40

#insert into ~ pk데이터 15 가 들어간다면
pk 데이터
10->15-> 20-> 30-> 40
10과 20 사이로 들어간다. 이건 다시말하면 pk를 다시 정렬해야한다는 얘기로 부하를 야기할 수 있다.
데이터 크기가 비대해지면 UUID와 같이 무작위로 pk를 생성하여 테이블에 데이터를 넣으면 데이터가 누적됨에 따라 점차적으로 성능이 저하될 것이고
극심한 disk I/O wait에 빠져서 서비스품질이 크게 떨어질 수 있다. 데이터를 테이블에 넣을 때마다 위와 같이 순서에 맞게 물리적으로 이동시켜야하기 때문이다.

#테이블에 pk가 없다면?
1) 인덱스 중 unique 속성이 있는 key를 pk로 대체 사용
2) pk 선언도 없고 unique key도 없으면 내부적으로 6Byte 크기의 대체 pk를 생성하여 사용
결과적으로 모든 InnoDB는 pk를 선언하지 않아도 내부적으로는 pk를 가지고 있다는 것을 반드시 기억해야 한다

# 불필요한 인덱스 삭제
인덱스가 늘어날수록 insert 성능은 크게 떨어진다.
인덱스는 데이터를 접근하기 위한 필수요소지만 많은 것이 무조건 좋은 것은 아니다.
인덱스도 cpu와 메모리 자원을 소비하는 데이터라는 점을 반드시 기억하기 바란다.
pk = pk
key (보조인덱스) = pk + 인덱스

#테스트

읽으면서 한번 더 짚고 넘어갈 부분은 메모해서 정리한다.

#InnoDB 스토리지엔진
1) 트랜젝션 지원
2) MVCC (multiversion Concurrency Control - 다중 버전 동시성 제어 메커니즘)
- 행단위 잠금으로 데이터 변경 작업을 수행하기 때문에 연관이 없는 데이터를 다른 사용자가 변경할 수 있다.
3) MyISAM은 인덱스만 메모리에 올리지만 InnoDB는 인덱스와 데이터를 모두 메모리에 올린다는 것이 가장큰 차이점이다. 메모리에 인덱스와 데이터가 적재되어 있기 때문에 메모리버퍼 크기(Innodb_buffer_pool_size)가 DB 성능에 큰 영향을 미친다.

#테이블 조인방식
단일코어(1 cpu)에 Nested Loop Join 알고리즘
- 선행테이블 A의 조건 검색 결과값 하나하나를 테이블 B와 비교하여 조인하는 방식이다.
프로그램적으로 풀자면 for문안에 for문과 유사하다. 결국 처리할 데이터가 적으면 수행속도가 빠르지만 테이블 A와 테이블 B 중 하나라도 연산해야할 데이터가 많아지면 쿼리 효율이 기하급수적으로 떨어진다.

#LIKE 검색
LIKE 조건이 '검색어%'와 같이 검색어가 앞단에 있다면 데이터 분포도를 따져서 수행한다.
LIKE 조건이 '%검색어'와 같은 형태로 반드시 수행해야 한다면 LIKE 조건 이외의 조건절을 적극 활용하여 LIKE 처리가 필요한 데이터 범위를 최대한 줄인다.

#성능 저하를 유발하는 불필요한 서브쿼리 (rownum 생성 제거)
- 오라클에는 rownum 개념이 있기 때문에 데이터 결과 행에 번호를 쉽게 붙일 수 있다. 아쉽게도 Mysql에는 rownum 개념이 없지만
(select @RNUM:=0) 같은 방식으로 행 번호를 매길 수 있다. 하지만 이를 잘못 사용하면 성능이 저하된다.
결과값에 불필요한 Temporary table을 만들어 저장한다.
결과적으로 엄청난 횟수의 조인 연산이 내부적으로 발생하는 것이다. 다음과 같이 불필요한 서브쿼리를 제거해서 쿼리를 다시 작성한다.
페이지 번호는 DB에서 무리하게 생성하는 것보다 애플리케이션 서버에서 생성하는 것이 Mysql의 성능을 높이는 데 유리하다.

-> 그동안 튜닝포인트 중 하나였는데 푸시해도 잘 안들어줬는데 성동찬님책에서 있네 ㅎㅎ

개발쪽에 얘기해서 쪼는데 제대로인 설명인 듯

현재는 카카오페이에 있는 성동찬님이 쓴 책으로

이번에 mysql 튜닝관련 도움을 받으려고 구매했다.

근데.. 설치를 mysql 5.1로 하라네.. 2013년 출판이네...

MyISAM 이면 어쩌지;;

페이지는 112 쪽으로 가볍게 읽을 수 있을 거 같다.