[MySQL] Assigned, Completed, Written, Flushed / Redo Log 가 처리되는 과정 알아보기

- 목차

 

들어가며.

이번 들에서는 Redo Log 가 생성되는 과정을 알아보려고 합니다.

Redo Log 는 MySQL 내부적으로 메모리/디스크 영역에서 생성 및 저장되는 구조를 취하기 때문에 시각적으로 확인하긴 어렵지만,

show engine innodb status; 명령어를 통해서 확인하는 방법을 소개하려고 합니다.

 

Redo Log 란 ?

Redo Log 는 MySQL 에서 발생하는 DML 쿼리들을 기록합니다.

Insert, Update, Delete 쿼리를 Redo Log 파일 내부에 저장하게 됩니다.

Redo Log 의 용도는 Crash Recovery 를 목적으로 합니다.

만약 MySQL 이 급작스럽게 종료되었을 때에 Redo Log 에 기록된 변경사항을 토대로 이전 상태를 복구하게 됩니다.

 

Redo Log 가 필요한 이유는 Buffer Pool 과 같은 캐싱 구조와 관련이 깊습니다.

MySQL 은 사용자의 DML 요청을 즉각적으로 디스크에 영구적으로 저장하지 않습니다.

메모리에 존재하는 BufferPool 이라는 영역에 변경 사항을 임시적으로 기록해줍니다.

예를 들어, Student 테이블의 A Student 를 수정할 때에 변경된 A Student Record 는 메모리에 남아있을 가능성이 높습니다.

즉, 즉시 Disk 로 Flush 되지 않는다는 점입니다.

이러한 현상을 Dirty Page 라고 합니다.

 

이러한 구조에서 데이터 변경사항이 Memory 에만 남아있고, Disk 로 영구 저장되지 않은 상태에서 서버가 종료되면 데이터는 유실됩니다.

하지만 Redo Log 는 이러한 변경사항을 상시 기록해줍니다.

그리고 Redo Log 를 토대로 Crash Recovery 를 수행합니다.

 

show engine innodb status.

Redo Log 는 메모리와 디스크 레벨에서 존재하는 구성요소입니다.

따라서 Human Readable 한 형식으로 확인하기가 쉽지 않습니다.

따라서 show engine innodb status; 의 출력 값을 분석하여 Redo Log 의 상태를 확인하려고 합니다.

 

먼저 간단히 Table 을 하나 생성하겠습니다.

아래의 명령어를 통해서 MySQL Docker Container 와 SQL 명령어를 실행합니다.

docker run -d --rm --name mysql -e MYSQL_ALLOW_EMPTY_PASSWORD=1 mysql:8.0.30

create database test;

CREATE TABLE test.large_blob_test (
    id INT AUTO_INCREMENT PRIMARY KEY,
    data LONGBLOB
) ENGINE=InnoDB;

SET GLOBAL max_allowed_packet = 1073741824;

 

MySQL 의 실행과 SQL 명령어의 실행을 마쳤다며, innodb engine 의 상태를 확인합니다.

 

show engine innodb status\G;

 

( 참고로 \G 문자열을 첨가하면, 세로 형식으로 결과값이 출력됩니다. )

아래의 내용은 Redo Log 와 관련된 출력 결과입니다.

 

---
LOG
---
Log sequence number          31708922
Log buffer assigned up to    31708922
Log buffer completed up to   31708922
Log written up to            31708922
Log flushed up to            31708922
Added dirty pages up to      31708922
Pages flushed up to          31708922
Last checkpoint at           31708922
Log minimum file id is       9
Log maximum file id is       9
74 log i/o's done, 1.00 log i/o's/second

 

• Log buffer assigned up to
• Log buffer completed up to
• Log written up to
• Log flushed up to

이어지는 내용에서 위 네가지 상태에 대해서 자세히 알아보겠습니다.

 

Log Sequence Number.

먼저 Log Sequence Number 에 대해서 알아봅니다.

Log Sequence Number 는 LSN 이라고 줄여서 부르기도 합니다.

Log Sequence Number 는 byte 단위의 수치이며, MySQL 에서 변경된 데이터의 크기가 누적된 결과라고 생각하시면 됩니다. 

예를 들어, 처음의 LSN 은 0 이었습니다.

하지만 "create table test_Table (id int);" 을 실행하게 되었고, 이 DDL 쿼리의 크기가 1000byte 라면 LSN 은 1000 이 됩니다.

그리고 "insert into test_Table (id) values (1);" 의 Insert Query 가 수행되면 10Byte 의 LSN 이 늘어나 1010 이 됩니다.

 

LSN 은 이러한 방식으로 DDL, DML 쿼리의 수와 크기에 비례하여 단조증가하게 됩니다.

Redo Log 는 LSN 을 명시하는 방식으로 동작합니다.

"현재 Redo Log 에 기록된 LSN 이 몇 byte 입니다." 와 같은 방식으로 사용되며, LSN 은 MySQL 전역적으로 사용됩니다.

예를 들어, 각 Table Record 들을 저장하는 Page 에서도 현 Page 를 마지막으로 수정한 LSN 을 기록하여 시간적은 선후 관계를 파악할 수 있습니다.

 

Assigned.

show engine innodb status; 의 출력값은 Log buffer assigned up to 이라는 정보를 표시합니다.

여기서 Assigned 이라는 의미를 파악해보겠습니다.

Redo Log 는 캐싱 영역인 메모리와 영구적인 저장을 위한 Disk 영역으로 나뉩니다.

Memory 영역의 Redo Log 는 Log Buffer, Redo Log Buffer 라고 부릅니다.

그리고 Disk 영역에서는 "#ib_redo_XX" 와 같은 형식의 이름의 파일이 존재합니다.

 

Assigned 는 데이터 변경사항을 기록하기 위해서 MySQL 로부터 할당받은 Byte Array 라고 생각하시면 됩니다.

예를 들어, "Insert into test_table (id) values (1)" 라는 DML Query 를 실행하는 상황을 가정해봅니다. 

이 쿼리를 Redo Log Entry 로 변경하면,  아래와 같이 표현된다고 가정하구요.

{
    table : 'test_table', 
    column: 'id', 
    value : '1'
}

 

이를 직렬화하면 20바이트가 필요하다고 가정하겠습니다.

그럼 MySQL 내부의 Query Executor 는 20바이트를 위한 Byte Array 나 Byte Buffer 를 MySQL 에게 요청합니다.

그리고 이러한 동작 과정에서 할당되거나 생성되는 과정을 Assigned 이라고 표현합니다.

 

그래서 show engine innodb status 의 Log buffer assigned up to 의 값은 Redo Log Buffer 로 데이터 변경사항을 기록하기 위해서 할당받은 메모리 크기라고 생각하시면 됩니다.

 

Completed.

Completed 는 Assigned 된 Byte Array 또는 Byte Buffer 에 데이터 변경사항이 기록되고,

이러한 Byte Array 또는 Byte Buffer 가 Redo Log Buffer 에 추가된 상태입니다.

데이터 변경사항이 Redo Log Buffer 에 추가된다면 이는 Disk 영역의 Redo Log File 로 Flush 되어 영구저장이 가능한 상태입니다.

 

사실상 Assigned 과 Completed 는 거의 동시에 일어나는 과정입니다.

하지만 이 차이를 확인하기 위해서 한가지 테스트를 수행하겠습니다.

아래의 쿼리는 대략 1GB 크기의 데이터를 추가하는 Insert Query 이며,

의도적으로 Heavy 한 쿼리를 수행하여 Assigned 과 Completed 사이의 시간적인 갭을 두려고 해보았습니다.

실행 결과는 아래와 같이 한 70kb 정보의 Gap 을 보이는 순간을 포착하였습니다.

 

INSERT INTO large_blob_test (data) SELECT REPEAT(CHAR(FLOOR(65 + (RAND() * 26))), 50000000) FROM DUAL;

---
LOG
---
Log sequence number          439275169
Log buffer assigned up to    439275169
Log buffer completed up to   439206432
Log written up to            422451200
Log flushed up to            422451200
Added dirty pages up to      439206432
Pages flushed up to          388369719
Last checkpoint at           388163138
Log minimum file id is       118
Log maximum file id is       128
47928 log i/o's done, 1908.00 log i/o's/second

 

 

Written 과 Flushed.

Redo Log 는 Written 과 Flushed 상태가 존재합니다.

이들 모두 Redo Log Buffer 인 메모리에서 Redo Log File 인 디스크 영역으로 Flush 된 상태를 의미합니다.

하지만 written 과 flushed 는 약간의 차이가 존재합니다.

 

OS 레벨에서 File Write 에 해당하는 시스템 콜을 사용하여 데이터를 파일로 추가할 수 있습니다.

MySQL 또한 Redo Log File 로 Write System Call 을 통해서 데이터 변경사항을 Redo Log File 에 추가하게 됩니다.

이 과정에서 OS Kernel 의 Cache 영역에 데이터가 잠시 머물 수 있습니다.

즉, Write System Call 이 즉시 파일 내부로 Flush 하는 것이 아니라 OS Kernel memory 에 임시적으로 머물 수 있습니다.

따라서 Written 상태는 Write System Call 이 호출되어서 Redo Log Buffer 의 기록이 메모리를 벗어난 상태입니다.

하지만 OS Kernel 의 Memory 에 머물 수도 있는 상태입니다.

이는 아직까지 영구적으로 저장된 상태라고 볼 수 없습니다.

 

하지만 fsync 와 같은 시스템콜을 주기적으로 시도함으로써 OS Kernel 의 캐싱된 데이터들은 Disk 로 최종적으로 Flush 됩니다.

이것이 "Log written up to" 와 "Log flushed up to" 의 차이점입니다.

 

Written 과 Flushed 또한 거의 동일한 수치를 가지지만 아래와 같이 약간의 시간적인 갭을 가질 수도 있습니다.

 

---
LOG
---
Log sequence number          1422630550
Log buffer assigned up to    1422630550
Log buffer completed up to   1422630550
Log written up to            1417981952
Log flushed up to            1417975808
Added dirty pages up to      1422630550
Pages flushed up to          1354256452
Last checkpoint at           1354256452
Log minimum file id is       405
Log maximum file id is       433
169689 log i/o's done, 7238.00 log i/o's/second