MySQL InnoDB History란?

 

Introduction

(시작 전에 이 글은 MySQL 5.6 기준의 글입니다.)

왜 Innodb History에 대해서 알아야 할까요?

1. InnoDB의 MVCC의 동작 원리에 대해 이해하려면 History에 대한 이해가 필요합니다.

2. History List Length가 길어지면 많은 문제들이 발생합니다. (CPU Peak, DML delay)

3. 왜 DBA들이 hung transaction이나 long query를 모니터링하고, Kill 하는지 알 수 있습니다. (커뮤니케이션)

 

 

MVCC (Multi Version Concurrency Control)

본격적으로 InnoDB History를 살펴보기 앞서 MVCC에 대해서 먼저 간략하게 설명하고 넘어가겠습니다. MVCC를 먼저 설명하는 이유는 MVCC의 구현을 위해서 History를 사용하기 때문입니다. InnoDB Storage Engine은 MVCC를 지원하는 Storage Engine이고 MVCC는 Multi Version Concurrency Control의 약자입니다. 한글로 해석해 보면 동시에 여러 버전을 컨트롤한다는 이야기입니다.

 

MVCC에 대한 이해를 위해서 Isolation level이 REPEATBLE_READ인 MySQL 서버에서  InnoDB Storage Engine이 어떻게 처리되는지 한 편 살펴보겠습니다.

 

transaction id가 10인 트랜잭션이(먼저 시작) 있고 transaction id가 11인 트랜잭션이(나중에 시작) 예를 들어 phonebook이라는 테이블에 id=10인 row의 name을 다음과 같이 업데이트했다고 하겠습니다.

 

transaction id=12 > Update phonebook set name='jordan' where id = 10;

transaction id=12 > Commit;

 

이때 InnoDB의 상태는 아래와 같습니다.

(Disk의 경우 일정 주기에 따라서 disk에 쓰기 때문에 알 수 없습니다. 물론, Innodb_flush_log_at_trx_commit 파라미터에 따라서도 다릅니다.)

여기서 transaction id가 10인 transction에서 해당 row를 select하면 undo log에 있는 log를 통해서 해당 트랜잭션이 시작했을 때 당시의  데이터를 반환합니다. 여기서 말은 undo로그 영역에서 바로 데이터를 읽어서 원래 데이터를 만들어서 가져오는 것처럼 말했지만 이 과정은 transaction이 길어지는 경우 굉장히 cost가 큽니다 . 밑에서 따로 이야기할 것이기 때문에 여기서는 자세한 내용은 다루지 않겠습니다.

(여기서 isolation level이 READ_UNCOMMITED 이나 READ_COMMITED 이었다면 bufferpool의 데이터파일에서 바로 읽게 됩니다.)

 

지금 본 것처럼 id가 12인 phonebook의 row는 동시에 2개의 버전이 존재합니다. 실제 production환경에서는 긴 transaction 혹은 오랜 시간 수행되는 states가 있으면 수 천개 이상의 history가 존재하게 됩니다. 

 

 

그래서 History 그리고 History List가 뭔가요?

History는 Undo log에 저장되는 오래된 version들의 snaphot 데이터들을 위한 데이터를 의미합니다.

History List는 변경된 것들을 되돌릴 수 있는 내용을 저장하고 있는 undo logs를 의미합니다.

 

 

저희는 아래와 같은 방법은 History List length 를 체크할 수 있습니다.

# 1
SHOW ENGINE INNODB STATUS

# 2
select name, count from information_schema.INNODB_METRICS where name like '%hist%';

# for aws aurora checking past history
SELECT
	*
FROM
	mysql.rds_global_status_history
WHERE
variable_name = "AURORA_ROLLBACK_SEGMENT_HISTORY_LENGTH"

예시)

 

 

History List length가 4128이라는 것을 무엇을 의미할까요?

 

이는 가장 오래된 transaction의 repeatable read를 위해서 4128개의 transaction의 이한 변경된 집합이 있다는 것들을 의미합니다. 저는 이를 GIT으로 생각하면 조금 쉽다고 생각하는데 4128개의 Commit을 보관하고 있다고 생각하면 쉽게 이해할 수 있습니다.

 

 

 

어떻게 과거의(해당 트랜잭션이 시작 전의) snapshot을 가져올까요?

1.행을 보고 사용하기에 적합한 버전인지 확인합니다.

 

2.그렇지 않은 경우 실행 취소 공간으로 이동하여 이전 버전을 찾습니다.

 

3..실행 취소 공간의 해당 페이지가 버퍼 풀에 없으면 디스크에서 로드합니다.

 

4.공간이 없는 경우 페이지를 제거하여 버퍼 풀의 공간을 확보합니다.

 

5.제거할 수 있는 페이지가 없는 경우, 페이지를 디스크로 플러시하여 공간을 확보하기 위해 페이지를 삭제합니다.

 

6.flush 스케쥴(write thread가 바쁘면) 된게 너무 많다면 대기를 해야합니다....

 

7.버퍼 풀에 올려서 redo log를 통해서 해당 트랜잭션 시작 전의 데이터를 만들어서 client thread에 전달합니다.

 

대략적으로만 봐도 과거 시점의 snapshot 데이터를 만드는 것은 특정 상황에서 매우 비싼 작업이 될 것으로 보입니다.

 

 

History List Length가 길어지면 어떤 문제가 생길까요?

Introduction에서 언급한 것 처럼 아주 긴 History를 Purge할 때 CPU가 순간적으로 튀게 됩니다. 아주 많은 양의 데이터를 purge하기 때문에 CPU에 영향을 줍니다. 또, 위에서 살펴본 것 처럼 Snapshot 데이터를 만들기 위해서 비싼 작업을 해야 합니다. 또한, 장기간 유지되면 disk에 flush를 해야 하기 때문에 write 부하가 증가하게 됩니다.

 

 

History List Length를 어떻게 모니터링하고 길어지는 것을 막을 수 있는가?

모니터링

- long query, long transaction을 모니터링 하고 일정 시간이 지난 connection은 connection을 kill해서 DB를 보호할 수 있습니다. 나중에 모니터링 할 수 있는 방법에 대해서는 따로 글을 쓰도록 하겠습니다.

- history length 를 모니터링 합니다.

 

결론

InnoDB Engine에서는 MVCC를 지원하기 위해서 redo log에 history를 저장합니다. 이는 강력한 기능을 제공함과 동시에 성능에 영향을 줄 수 있는 부분입니다. InnoDB History를 이해하기 위해서는 InnoDB에 대해서 기본적인 이해를 필요로 합니다. 개인적으로는 undo log length를 썼으면 조금 덜 헷갈리지 않았을까 하는 생각도 있는데 Naming은 참 어려운 것 같습니다.

끝으로, Long query와 long transaction은 MySQL에 해롭습니다.

 

 

+혹시, 잘못된 내용이 있다면 댓글로 편하게 알려주세요.

 

 

 

 

 

 

 

 

Reference

dev.mysql.com/doc/refman/5.6/en/innodb-undo-logs.html

MySQL :: MySQL 5.6 Reference Manual :: 14.6.7 Undo Logs

orangematter.solarwinds.com/2015/07/20/what-is-innodb-history-list-length/

What Is InnoDB History List Length? - Orange Matter

www.percona.com/blog/2017/05/08/chasing-a-hung-transaction-in-mysql-innodb-history-length-strikes-back/

Chasing a Hung MySQL Transaction: InnoDB History Length Strikes Back - Percona Database Performance Blog