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

#트랜잭션 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 + 인덱스

 

#테스트

인덱스 수	0개	1개	2개	3개
시간	4.14초	7.18초	10.40초	12.61초