[MySQL] InnoDB - Redo Log

■ Redo Log란

많은 데이터베이스 관리 시스템과 마찬가지로 MySQL은 데이터 내구성을 달성하기 위해 로그를 사용합니다(기본 InnoDB 스토리지 엔진을 사용할 때). 만약 DB 에 장애가 발생하여서 메모리 영역에만 남아있는 데이터를 디스크 영역으로 옮겨지지 못한채 서버가 다운되는 현상이 발생했을때 복구할수 있는방법이 바로 Redo Log, Undo log를 이용하는 것입니다. 이 로그들을 이용하면 트랜잭션이 커밋될 때 충돌이나 정전이 발생해도 데이터가 손실되지 않습니다. 리두로그에 대해선 따로 정리해서 포스팅하겠습니다.

 

■ Redo log 동작원리

MySQL의 InnoDB 스토리지 엔진은 고정 크기(순환방식)의 Redo 로그 공간을 사용합니다. 크기는 innodb_log_file_size 및 innodb_log_files_in_group(기본값 2개)에 의해 제어됩니다. 이러한 값을 곱하서 사용할 수 있는 Redo 로그 공간을 확보합니다.

 

InnoDB의 Redo log 크기 구성은 쓰기 집약적 워크로드를 위한 가장 중요한 구성 옵션 중 하나입니다. 그러나 트레이드 오프가 있습니다. 구성한 Redo 공간이 많을수록 InnoDB가 쓰기 I/O를 최적화할 수 있습니다. 그러나 Redo log 크기를 늘리면 시스템의 전원이 꺼지거나 다른 이유로 인해 충돌이 발생할 때 복구 시간이 길어집니다.

 

특정 Redo log크기에 대해 시스템 충돌 복구에 걸리는 시간을 예측하는 것은 쉽지 않거나 간단하지 않습니다. 이는 하드웨어, MySQL 버전 및 워크로드에 따라 매우 다양할 수 있습니다.(상황에 따라 10배 이상 차이). 그러나 Redo log 1GB당 복구시간은 평균 5분 정도로 시간이 소요된다고 하지만 시스템 성능, 그리고 리두로그에 쌓여있는 트랜잭션의 크기에 따라 달라집니다. 반드시 전체 로드(데이터베이스가 완전히 워밍업된 후)에서 시스템 충돌을 시뮬레이션하여 테스트하는 것을 추천드립니다. 리두로그가 클수록 성능에 좋은건 맞지만 그렇다고 무작정 크게 설정해 놓는다면 장애시 복구 시간이 그만큼 오래 걸린다는것을 뜻하기 때문입니다.

 

Redo log 문서와 별개로 Crash Recovery, Data File, ib_logfile(Redo log), Log Buffer, LSN, Shutdown, Transaction도 관련이 있으므로 관련 문서를 보는것을 추천드립니다.

 

 

▶︎ 동작 이론

 

 

그림 : 전체 아키텍쳐중 버퍼풀에서 변경된 데이터가 리두 로그로 옮겨가는 과정

 

그림 : 언두 로그, 리두 로그가 시스템 테이블 스페이스 및 언두 로그로 쓰이는 과정

 

 

리두 로그 동작 순서

1) 버퍼풀에서 데이터 변경시 해당 되는 페이지를 수정 후 Dirty 마크를 표시합니다.

2) 관련 Redo log record 를 메모리 상의 내부적인 log 관련 memory(Double Write Buffer)에 저장 합니다.

3) Redo log record 를 Log Buffer(Innodb_log_buffer) 로 이동합니다.

4) redo log record 를 Redo Log File 에 Flush 합니다.

5) 버퍼풀 내의 변경된 Dirty 페이지에 대해 checkpoint를 수행하여 System Tablespae에 저장합니다.

 

Database 에서 Commit 이 발생하면 곧바로 디스크 영역(Table Space)으로 들어가지 않고 버퍼풀에 있는 내용을 먼저 Log Buffer에 들어가게 됩니다. 그리고 로그버퍼에 작성된 내용을 한꺼번에 모아서 리두 로그로 내리게 됩니다. 만약 버퍼풀에서 바로 디스크로 작성하게 되면 매번 Disk I/O를 발생시키기 때문에 성능에 심각한 문제를 발생시킬 수 있게 됩니다. 그래서 중간에 Log Buffer를 둠으로써 I/O의 성능을 높일 수 있습니다.

 

만약 Database에 문제가 생겨 복구가 필요하다고 할때 메모리에 있는 데이터는 보통 전부 잃어버리게 됩니다. 이렇게 되면 복구할수 있는 방법은 디스크에 기록된 Redo Log파일을 이용해서 복구를 해야합니다.(물론 예상하신것처럼 Redo log, Undo log모두 필요합니다. 이곳에서는 Redo Log만 다루기 때문에 Redo log만 말씀드립니다.)

그런데 여기서 문제는 언제 메모리에 저장되어있는 데이터를 디스크로 이동할까요??

바로 innodb_flush_log_at_trx_commit 파라미터를 확인하는 것입니다. 이 파라미터의 설정값에 따라 언제 메모리에서 디스크로 Redo Log 가 기록 되는지 설정할 수 있습니다.

또한 해당 값에 따라 순간적인 장애시 1초간격의 트랜잭션을 잃을 수 있습니다.

 

그림 : innodb_flush_log_at_trx_commit의 파라미터 값에 따라 반영되는 방법

 

 

innodb_flush_log_at_trx_commit 파라미터 값에 따른 기능

value	설명	장점	단점
0	1초마다 log buffer의 내용들을 Redo log(log file)로 내려씀 . MySQL 이나 OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있다.	디스크 I/O 요청이 줄어듬	장애 발생시 디스크 에 플러시 되지 않은 Redo Log 는손실된다
1(default)	Client 에서 트랜잭션이 실행되면 메모리에 기록하고Commit 시에 메모리에 있는 Redo log내용을 디스크에 기록한다. 커밋발생시마다 Redo log로 내려쓰기 때문에 안전하긴 하지만 성능이 느림	데이터의 손실이 발생하지 않음	디스크 I/O 요청이 많아짐
2	커밋 발생 시 OS buffer/cache 로 내려쓰고 1초마다 log file 로 내려씀 OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있으나 MySQL 장애시에는 이미 OS 영역으로 데이터는 넘어갔기 때문에 안전할 수 있음	O/S 장애가 아니고 DB 만의 장애인 경우 데이터 손실없음디스크 I/O 요청이 줄어듬	O/S 장애 발생시 디스크에 기록되지 않은 Redo Log 는 손실된다

참고로 0 과 2 옵션의 경우 DDL 변경 및 기타 내부 InnoDB 활동으로 인해 설정과 무관하게 로그가 플러시 되는 경우가 많으며 스케쥴링 문제로 인해 초당 1 회 플러시가 100% 보장 되지않습니다.

sync_binlog =1 + innodb_flush_log_at_trx_commit=1 이 가장 안전한 설정방법이라고 할 수 있습니다. 대신 성능은 많이 저하됩니다. 기본값입니다.

 

▶︎ 성능 및 일관성에 대한 플러싱 효과

innodb_flush_log_at_trx_commit 시스템 변수는 트랜잭션이 redo log로 플러시되는 빈도를 결정하게 됩니다. 또한 속도와 안정성 사이의 균형을 잘 맞추는 것이 무엇보다 중요합니다.

 

• 바이너리 로그 그룹 커밋 및 리두 로그 플러싱

MariaDB 10.0 이상에서 innodb_flush_log_at_trx_commit=1(기본값)이 모두 설정되고 바이너리 로그가 활성화되면 커밋하는 동안 InnoDB 내부 디스크에 대한 동기화가 하나 줄어듭니다(3개가 아닌 트랜잭션 그룹 간에 2개의 동기화가 공유됨).

 

 

■ Redo Log 설정방법

▶︎ 설정시 참고 사항

• MySQL 5.7 이상

1. 공식 문서에는 리두 로그 파일을 버퍼 풀만큼 만들라고 나와 있지만 이건 테스트를 해보아야 합니다. 리두로그 크기가 크면 클수록 DB 크래쉬 복구 발생시 그만큼 시간이 오래 걸립니다. 그래서 반드시 크래쉬 복구 테스트를 해보면서 크기를 결정해야 합니다. 반대로 작은 redo 로그 파일은 불필요한 디스크 쓰기를 많이 발생시킵니다. 그러므로 테스트를 통해 알맞은 크기를 결정해야 합니다. 리두로그는 낮은 버전에서 많은 문제를 일으켰지만 최근에는 성능개선이 많이 되었다고 합니다. 하지만 그래도 테스트를 통해 결정해야 합니다. 또한 InnoDB는 리두 로그 파일을 가득 채웠을 때 버퍼 풀의 수정된 내용을 체크포인트의 디스크에 기록해야 합니다.

 

2. 로그 버퍼의 크기를 늘리는 것이 좋습니다. 큰 로그 버퍼를 사용하면 트랜잭션이 커밋되기 전에 디스크에 로그를 쓸 필요 없이 큰 트랜잭션을 실행할 수 있습니다. 따라서 많은 행을 업데이트, 삽입 또는 삭제하는 트랜잭션이 있는 경우 로그 버퍼를 더 크게 만들면 디스크 I/O가 절약됩니다. 로그 버퍼 크기는 innodb_log_buffer_size 파라미터를 사용하여 구성됩니다.

 

3. "read-on-write"를 방지하기 위해 innodb_log_write_ahead_size 파라미터를 설정합니다. 이 파라미터는 리두 로그에 대한 write-ahead 블럭 크기를 정의합니다. 운영 체제 또는 파일 시스템 캐시 블록 크기와 일치하도록 innodb_log_write_ahead_size를 설정합니다. Read-on-write는 Redo log에 대한 write-ahead 블록 크기와 운영 체제 또는 파일 시스템 캐시 블록 크기 간의 불일치로 인해 Redo log 블록이 운영 체제 또는 파일 시스템에 완전히 캐시되지 않을 때 발생합니다. innodb_log_write_ahead_size의 유효한 값은 InnoDB 로그 파일 블록 크기(2n)의 배수입니다. 최소값은 InnoDB 로그 파일 블록 크기(512)입니다. 최소값을 지정하면 Write-ahead가 발생하지 않습니다. 최대값은 innodb_page_size 값과 같습니다. innodb_log_write_ahead_size 값을 innodb_page_size 값보다 크게 지정하면 innodb_log_write_ahead_size 설정이 innodb_page_size 값으로 잘립니다. 운영 체제 또는 파일 시스템 캐시 블록 크기와 관련하여 innodb_log_write_ahead_size 값을 너무 낮게 설정하면 read-on-write가 발생합니다. 값을 너무 높게 설정하면 한 번에 여러 블록이 작성되기 때문에 로그 파일 쓰기에 대한 fsync 성능에 약간의 영향을 미칠 수 있습니다.

 

• MySQL 8.0에서 추가 사항

1. MySQL 8.0.11은 로그 버퍼에서 시스템 버퍼로 리두 로그 레코드를 기록하고 리두 로그 파일에 시스템 버퍼를 플러시하기 위한 전용 로그 쓰기 스레드(dedicated log writer threads)를 도입했습니다. 이전에는 개별 사용자 스레드가 이러한 작업을 담당했습니다. MySQL 8.0.22부터 innodb_log_writer_threads 변수를 사용하여 로그 기록기 스레드를 활성화 또는 비활성화할 수 있습니다. 전용 로그 작성기 스레드는 동시성이 높은 시스템에서 성능을 향상시킬 수 있지만 동시성이 낮은 시스템의 경우 전용 로그 작성기 스레드를 비활성화해야 성능이 향상됩니다. 즉 동시성이 높은 시스템에서 사용하시는게 좋습니다.

2. 플러시된 redo를 기다리는 사용자 스레드의 스핀 지연 사용을 최적화합니다. 스핀 지연은 대기 시간을 줄이는데 도움이 됩니다. 동시성이 낮은 기간에는 대기 시간을 줄이는 것이 우선 순위가 낮을 수 있으며 이러한 기간 동안 스핀 지연을 사용하지 않는 것이 에너지 소비를 줄일 수 있습니다. 동시성이 높은 기간 동안에는 다른 작업에 사용할 수 있도록 스핀 지연에 대한 처리 능력을 낭비하는 것을 피할 수 있습니다. 다음 시스템 변수를 사용하면 스핀 지연 사용에 대한 경계를 정의하는 높고 낮은 워터마크 값을 설정할 수 있습니다.

- innodb_log_wait_for_flush_spin_hwm: 사용자 스레드가 플러시된 다시 실행을 기다리는 동안 더 이상 회전하지 않는 최대 평균 로그 플러시 시간을 정의합니다. 기본값은 400마이크로초입니다.

- innodb_log_spin_cpu_abs_lwm: 사용자 스레드가 플러시된 다시 실행을 기다리는 동안 더 이상 회전하지 않는 최소 CPU 사용량을 정의합니다. 값은 CPU 코어 사용량의 합계로 표시됩니다. 예를 들어 기본값 80은 단일 CPU 코어의 80%입니다. 다중 코어 프로세서가 있는 시스템에서 값 150은 한 CPU 코어의 100% 사용량과 두 번째 CPU 코어의 50% 사용량을 나타냅니다.

- innodb_log_spin_cpu_pct_hwm: 사용자 스레드가 플러시된 Redo을 기다리는 동안 더 이상 회전하지 않는 최대 CPU 사용량을 정의합니다. 값은 모든 CPU 코어의 결합된 총 처리 능력에 대한 백분율로 표시됩니다. 기본값은 50%입니다. 예를 들어 2개의 CPU 코어를 100% 사용하는 것은 4개의 CPU 코어가 있는 서버에서 결합된 CPU 처리 능력의 50%입니다. innodb_log_spin_cpu_pct_hwm 구성 옵션은 프로세서 선호도를 존중합니다. 예를 들어, 서버에 48개의 코어가 있지만 mysqld 프로세스가 4개의 CPU 코어에만 고정되어 있는 경우 다른 44개의 CPU 코어는 무시됩니다.

 

▶︎ 리두 로그 크기 설정에 대해서

• 리두로그 의 파일사이즈를 크게 하면 좋을까요?

리두 로그의 공간을 늘리면 InnoDB가 DISK I/O를 최적화할 수 있습니다. 대량의 DML이 들어올 시 리두로그를 이용하여 디스크에 바로 작성하지 않고 유연하게 대처를 할 수 있습니다. 그러나 만약 시스템 장애가 발생하여 크래쉬 리커버리가 발생한다면 복구할때 시간이 그만큼 길어집니다.
Redo Log 파일 별로 복구시간을 예측하는건 하드웨어, DB버전, 워크로드에 따라 달라서 달라집니다. 단순 DML이 많다면 금방 복구 되지만 조인이 들어간 대량의 트랜잭션이라면 복구시간이 틀려집니다. 예전엔 대략적으로 1GB 당 5분 정도가 걸린다고 했는데 시스템 사양에 따라 달라지기도 합니다. SSD 스토리지에 몇십코어의 DB라면 그만큼 빨리 복구가 됩니다. 최근엔 리두로그의 아키텍쳐가 많이 개선되어 빠르다고는 하지만 위에서 얘기했듯이 반드시 크래쉬 리커버리 테스트가 필요합니다.

 

• 그렇다면 RedoLog 파일의 크기를 작게하는게 좋을까요??

사용자가 DML 문장을 실행하면 변경된 데이터는 먼저 로그버퍼에 쌓고 이 로그버퍼에서 일정부분 쌓이게 되면 Redo Log에 기록되면서 디스크에 영구적으로 남게됩니다. InnoDB 스토리지 엔진은 실제 테이블의 데이터를 버퍼풀 메모리 (InnoDB Buffer Pool) 상에서만 변경하고 DB 서버는 클라이언트의 사용자에게 쿼리가 실행 완료 되었다고 리턴하게 됩니다.

InnoDB 버퍼 풀에 변경된 데이터는 더티 페이지라고 표현하는데 더티페이지를 디스크로 영구히 기록하는 작업을 Flush 라고 합니다.
이런 Fulsh 작업은 크게 두가지로 나누어지는데 Redo Log 에서 다루는 영역은 Flush_list 입니다.

 

- 버퍼 풀에서 자주 사용되지 않는 페이지들 (LRU_list)

- 변경된 페이지들의 목록을 시간 순서대로 관리 (Flush_list)

 

InnoDB의 Redo Log 는 몇개의 파일로 구성되지만 InnoDB 의 스토리지 엔진은 내부적으로 이 파일들을 모두 모아서 하나의 연속된 공간으로 인식하게 됩니다. 이런 더티 페이지들은 Redo Log와 연결되어 있는데 Redo Log 페이지가 가득차게 되면 오래된 Redo Log 슬롯을 적절한 시점에 가장 오래된 리두 로그슬롯을 비워주어야 하는데 항상 Redo Log 가 먼저 디스크로 Flush 되고 버퍼풀에서 더티 페이지가 디스크로 완전히 플러시 되어야만 더티페이지와 연관을 가지고 있던 로그 슬롯이 재사용 될 수 있습니다. 따라서 Redo Log 가 작을 경우 InnoDB 의 버퍼풀은 Flush 작업이 빈번하게 일어날 것이고 Flush 과정에서 Random Write 작업(테이블 스페이스로 영구저장) 이 발생하여 DISK I/O 부하로 이어집니다.

 

 

▶︎ 현재 Redo Log 확인 방법

리두 로그 파일에는 ib_logfileN과 같은 이름으로 되어 있습니다. 여기서 N은 숫자입니다. MySQL에서는 기본으로 2개이고 MariaDB 10.5부터는 redo 로그가 하나만 있으므로 파일 이름은 항상 ib_logfile0입니다. innodb_log_group_home_dir 시스템 변수가 구성되어 있으면 해당 디렉토리에 리두 로그 파일이 생성됩니다. 그렇지 않으면 datadir 시스템 변수에 의해 정의된 디렉토리에 생성됩니다.

[root@gdb01 data]# cd (MySQL 데이타 디렉토리)
[root@gdb01 data]# ls -l
[root@gdb01 data]# ls -al
total 1624756
drwxr-xr-x.  6 mysql mysql      4096 Feb 11 08:12 .
drwxr-xr-x. 13 root  root        201 Sep 24 10:48 ..
-rw-r-----.  1 mysql mysql        56 Sep 24 10:51 auto.cnf
-rw-------.  1 mysql mysql      1676 Sep 24 10:51 ca-key.pem
-rw-r--r--.  1 mysql mysql      1112 Sep 24 10:51 ca.pem
-rw-r--r--.  1 mysql mysql      1112 Sep 24 10:51 client-cert.pem
-rw-------.  1 mysql mysql      1680 Sep 24 10:51 client-key.pem
-rw-r-----.  1 mysql mysql    633721 Feb  9 10:17 ib_buffer_pool   <- Buffer Pool 상태 저장 파일
-rw-r-----.  1 mysql mysql  77594624 Feb 11 08:12 ibdata1          <- System Table Space(Undo Log 포함)
-rw-r-----.  1 mysql mysql 524288000 Feb 11 08:12 ib_logfile0      <- Redo Log FIle 0
-rw-r-----.  1 mysql mysql 524288000 Feb  9 09:55 ib_logfile1      <- Redo Log FIle 1
-rw-r-----.  1 mysql mysql 524288000 Feb  9 09:56 ib_logfile2      <- Redo Log FIle 2
-rw-r-----.  1 mysql mysql  12582912 Feb 11 08:26 ibtmp1
drwxr-x---.  2 mysql mysql      4096 Sep 24 10:51 mysql
drwxr-x---.  2 mysql mysql      8192 Sep 24 10:51 performance_schema
-rw-------.  1 mysql mysql      1676 Sep 24 10:51 private_key.pem
-rw-r--r--.  1 mysql mysql       452 Sep 24 10:51 public_key.pem
drwxr-x---.  2 mysql mysql      4096 Feb  9 10:15 sbtest
-rw-r--r--.  1 mysql mysql      1112 Sep 24 10:51 server-cert.pem
-rw-------.  1 mysql mysql      1680 Sep 24 10:51 server-key.pem
drwxr-x---.  2 mysql mysql      8192 Sep 24 10:51 sys

 

▶︎ Redo Log 그룹 용량 설정방법 및 계산방법

리두 로그 그룹 용량은 모든 InnoDB 리두 로그파일의 총 크기입니다. 관련 구성은 다음과 같습니다.

• 리두 록 파일 수는 innodb_log_files_in_group 시스템 변수에 의해 구성됩니다.

• 각 리두 로그 파일의 크기는 innodb_log_file_size 시스템 변수에 의해 설정됩니다. 따라서 redo log 그룹 용량은 다음 계산에 의해 결정됩니다.

innodb_log_group_capacity = innodb_log_file_size * innodb_log_files_in_group

 

예를 들어, innodb_log_file_size가 2G로 설정되고 innodb_log_files_in_group이 2로 설정되면 다음과 같이 됩니다.

innodb_log_group_capacity = innodb_log_file_size(2G) * innodb_log_files_in_group(2개) = 4G

 

• 변경 명령어

shell> vi /etc/my.cnf

[mysqld]
# 특정 디렉토리 지정(ex : /dr3/iblogs)
innodb_log_group_home_dir
# K,M,G 사용가능
innodb_log_file_size = 2G
# redo log file 갯수. 2는 기본값
innodb_log_files_in_group : 2

 

• 위의 환경설정으로 파일 시스템에 최종 생성되는 파일명

file system상의 redo log file : ib_logfile0,ib_logfile1

 

▶︎ 리두 로그 그룹 크기 변경 방법

다음 프로세스를 통해 redo log 파일의 수와 크기를 변경할 수 있습니다.

• 서버를 중지합니다.

• 로그 파일 크기를 변경하려면 innodb_log_file_size를 설정합니다. 로그 파일 수를 늘리려면 innodb_log_files_in_group을 구성합니다.

• 서버를 시작합니다.

• 리두 로그의 최대 크기는 512G입니다(로그파일을 나누었을시 모두 더한 크기).

• 최대 로그 크기 설정 및 계산법 : 512GB(리두로그 최대 허용 크기) / innodb_log_files_in_group

 

 

■ 모니터링 방법

▶︎ 체크포인트 수명(Age) 확인(1) - Show engine innodb status를 이용하는 방법

체크포인트 수명은 마지막 체크포인트 이후 InnoDB 리두 로그에 기록된 데이터의 양입니다. 이 크기를 확인하여 Redo log 크기를 얼마만큼 잡아야 하는지를 알 수 있습니다.

 

InnoDB 체크포인트 수명을 확인하려면 다음 명령어를 실행합니다.

mysql> show engine innodb status;

 

LOG 섹션을 찾습니다. 다음과 비슷하게 출력됩니다.

---
LOG
---
Log sequence number 252794398789379
Log flushed up to 252794398789379
Pages flushed up to 252792767756840
Last checkpoint at 252792767756840
0 pending log flushes, 0 pending chkp writes
23930412 log i/o's done, 2.03 log i/o's/second

계산 수식은 다음과 같습니다.

innodb_checkpoint_age = Log sequence number(위 로그 출력내용) - Last checkpoint at(위 로그 출력내용)

 

위의 예에서는 다음과 같습니다.

• Innodb Checkpoint Age = Log sequence number - Last checkpoint at
• 252794398789379 - 252792767756840 = 1631032539 bytes
• 1631032539 byes / (1024 * 1024 * 1024) = 1.5 GB/bytes
• 마지막 체크포인트 이후에 작성된 Check Point Age는 1.5GB.

즉 현제 Check Point Age로 1.5G를 사용중이며 최소한 1.5G이상의 Redo log크기가 확보되어야 함을 의미합니다.

참고로 MariaDb 10.1 이전 버전에서는 Innodb_checkpoint_age라는 상태 변수에서 확인이 가능했습니다.

 

▶︎ 체크포인트 수명(Age) 확인(2) - information_schema.INNODB_METRICS를 이용하는 방법

테이블을 이용해서 체크포인트 수명을 확인하려면 innodb_metrics 테이블안의 특정 메트릭을 이용해서 조회하는 방법도 있습니다.

이것으로 조회하면 위의 계산식을 통하지 않고도 한번에 조회가 가능합니다. log_lsn_checkpoint_age메트릭이 위의 계산식에 대한 값입니다.

파라미터 이름	설명
log_lsn_checkpoint_age	Current LSN value minus LSN at last checkpoint
log_lsn_current	Current LSN value
log_lsn_last_checkpoint	LSN at last checkpoint

 

참고로 트랜잭션 로그 사용량 계산식입니다. 이 메트릭도 INNODB_METRICS안에 있습니다.

(log_lsn_checkpoint_age / log_max_modified_age_async) * 100

 

 

▶︎ 리두 로그에서 Checkpoint Age 점유율(사용량) 확인

리두 로그 점유(사용량)는 체크포인트에서 물리적 InnoDB 테이블스페이스 파일로 아직 플러시되지 않은 더티 페이지가 차지하는 InnoDB 리두 로그 용량의 백분율입니다. 따라서 다음 계산에 의해 결정됩니다.

innodb_log_occupancy = innodb_checkpoint_age(위의 Chceck Point Age 사용량) / innodb_log_group_capacity(리두 로그 총 크기)

예를 들어 innodb_checkpoint_age가 1.5G이고 innodb_log_group_capacity가 4G인 경우 다음과 같이 됩니다.

innodb_log_occupancy = innodb_checkpoint_age / innodb_log_group_capacity

1.5G / 4G = 0.375

리두 로그 점유에 대해 계산된 값이 1.0에 너무 가까우면 InnoDB 리두 로그 용량이 현재 워크로드에 비해 너무 작다는것을 의미합니다. 즉 리두 로그 크기를 줄이거나 갯수를 늘려야 합니다. 1에 너무 가까울때는 Redo Log 크기를 늘려서 성능을 높일 수 있지만 만약 1에 한참 미치지 않는다면 크기를 늘려도 성능에 크게 도움이 되지는 않습니다. 0.3~0.4 사이를 유지하는것이 좋을것으로 판단됩니다. 배치잡이나 대량의 작업이 수행될때의 여유분도 고려해야 하기 때문입니다.

 

▶︎ 리두 로그 사용량 확인방법(메트릭을 이용한 모니터링)

Global Status의 Innodb_os_log_written : 리두 로그 파일에 기록된 바이트 수입니다. 이것을 확인하여 현재 리두로그가 얼마나 사용되고 있는지 확인할 수 있습니다. 이것을 참고로 리두 로그 크기를 얼마나 설정해야 할지 가능해 볼 수 있습니다. 참고로 LSN과 같이 크기가 증가하지는 않습니다. LSN에서 100이 증가되었다고 해서 Innodb_os_log_written이 100이 증가하지 않습니다. 로그 버퍼에서는 같을 수 있지만 Innodb_os_log_written 좀더 큰 값으로 작성되게 됩니다. 트랜잭션의 크기로 작성되기 때문입니다. LSN은 10이 증가될 수 있지만 트랜잭션은 트랜잭션안에 여러 SQL이 있을 수 있고 그 크기가 다를 수 있기 때문입니다.

Global Status의 Innodb_log_writes : 리두 로그파일에 작성되고 있는 실제 쓰기 수 입니다. 만약 쓰기 횟수가 높다면 로그파일일 갯수를 늘려 로그 로테이트가 원활하게 되도록 해주는게 필요할 수 있습니다.

 

■ 리두 로그 크기에 따른 복구 실험

Percona에서 Redo Log 용량에 따른 복구 실험을 한 포스팅이 있습니다. 다음 포스팅을 참고하시면 도움이 될듯 합니다.

https://www.percona.com/blog/2017/10/18/chose-mysql-innodb_log_file_size/

 

■ 용어 및 파라미터

▶︎ 용어

• Log Sequence Number (LSN)

기본적으로 리두 로그는 물리적으로 ib_logfile0 및 ib_logfile1이라는 두 개의 파일로 디스크에 표시됩니다. MySQL은 리두 로그 파일에 순환 방식으로 씁니다. 리두 로그의 데이터는 영향을받는 레코드로 인코딩됩니다. 이 데이터를 통틀어 리두라고합니다. 리두 로그를 통한 데이터 통과는 점점 증가하는 LSN 값으로 표시됩니다.

 

• 체크포인트(Checkpoint)

InnoDB는 체크포인트를 수행할 때 InnoDB 버퍼 풀에서 가장 오래된 더티 페이지의 LSN을 InnoDB 리두 로그에 기록합니다. 페이지가 InnoDB 버퍼 풀에서 가장 오래된 더티 페이지인 경우 LSN이 더 낮은 모든 페이지가 물리적 InnoDB 테이블스페이스 파일로 플러시되었음을 의미합니다. 서버가 충돌하면 InnoDB는 마지막 체크포인트에 기록된 가장 오래된 더티 페이지의 LSN보다 크거나 같은 LSN이 있는 로그 레코드만 적용하여 충돌 복구를 수행합니다.

체크포인트는 InnoDB 마스터 백그라운드 스레드가 수행하는 작업 중 하나입니다. 이 스레드는 서버가 매우 활성화되어 있을 때 7초 간격으로 체크포인트를 예약하지만 서버가 덜 활성화되면 체크포인트가 더 자주 발생할 수 있습니다.

더티 페이지는 실제로 체크포인트 동안 버퍼 풀에서 물리적 InnoDB 테이블스페이스 파일로 플러시되지 않습니다. 이 프로세스는 innodb_write_io_threads 시스템 변수에 의해 구성된 InnoDB의 쓰기 I/O 백그라운드 스레드에 의해 지속적으로 비동기적으로 발생합니다. 이 프로세스를 보다 적극적으로 만들고 싶다면 innodb_max_dirty_pages_pct 시스템 변수의 값을 줄일 수 있습니다. 또한 innodb_io_capacity 시스템 변수를 설정하여 시스템에서 InnoDB의 I/O 용량을 더 잘 조정해야 할 수도 있습니다.

 

• LRU

Least Recently Used List라고 합니다. 직역은 최근에 잘 사용되지 않은 목록입니다. DBMS의 메모리 관리기법중에 하나로 버퍼풀을 관리할 때 사용되는 알고리즘입니다.

 

• WAL

WAL 이란 용어도 자주 등장하는데 Write Ahead Log의 약자입니다.  InnoDB 에서는 Redo Log 로 불린다.

 

• Doublewrite Buffer

InnoDB 에서 데이터 파일의 적절한 위치에 페이지를 쓰기전에 버퍼풀에서 Flush 된 페이지를 쓰는 저장 영역입니다. 페이지 쓰기 도중에 스토리지/서브시스템/mysqld 프로세스 충돌이 있는 경우에는 Redo Log만을 이용하여 복구시도를 할때는 복구가 불가능하지만 Doublewrite Buffer를 이용해서 복구 할수 있습니다.

 

• Log Buffer

로그 버퍼는는 디스크의 리두 로그파일에 기록될 데이터를 보유하는 메모리 영역입니다. 로그 버퍼의 크기는 innodb_log_buffer_size 파라미터로 설정할 수 있습니다. (일반적으로 4MB ~ 16MB 가 좋은 크기라고 합니다.)
로그 버퍼크기를 크게 조정하면 트랜잭션을 커밋 하기 전에 디스크에 로그를 쓰지않아도 큰 트랜잭션을 실행할수 있게 됩니다. 또한 많은 행을 업데이트, 삽입, 삭제하는 트랜잭션이 있는 경우 로그버퍼를 크게조정하면 DISK I/O 가 절약됩니다.

 

▶︎ 파라미터

• innodb_max_dirty_pages_pct

설정값 : 0~99.999 ( default : 75)

버퍼 풀에서 dirgy page 를 몇 %까지 허용할 수 있을지 결정합니다.

dirty page가 비율을 초과하면 innodb_io_capacity 파라미터로 정한 값 만큼 한번에 페이지를 flush시킵니다. 이 값을 낮추면 좀 더 자주 dirty page 를 flush해주겠지만 버퍼 풀의 효율성이 떨어지게 되어 Disk I/O가 높아질 수도 있으나 너무 높으면 redo file이 full 나서 더 이상 로그를 기록할 수 없는 현상이 발생할 수  있습니다.

 

• innodb_io_capacity

설정값 : 100 ~ 2^64-1 (default : 200)

innodb_max_dirty_pages_pct 파라미터로 명시한 값 만큼 버퍼 풀 내 dirty page가 늘어나면 이 파라미터 값 만큼 한번에 페이지를 flush 시킴

 

• innodb_log_buffer_size

설정값 : 1048576 ~ 4294967295 (Default : 16777216)

리두 로그를 파일에 직접 기록하기 전, 메모리상에서 버퍼링을 하는데 이를 위한 버퍼 사이즈입니다.

 

• innodb_log_files_in_group (default :2 )

설정값: 2 ~ 100(default : 2)

redo log 의 갯수 입니다.

 

• innodb_log_file_size

설정값: (≥ 5.7.11) - 4194304 ~ (≤ 5.7.10) - 1048576 (default : 50331648)

최대 512GByte크기. 리두 로그 파일 크기로 일반적으로  innodb_buffer_pool_size/innodb_log_files_in_group 를 적정 값으로 봄. 

 

■ 참고 URL

https://mariadb.com/kb/en/innodb-redo-log/

https://dev.mysql.com/doc/refman/5.7/en/innodb-redo-log.html

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-logging.html

https://dus815.tistory.com/entry/Mysql-Redo-Log-%EB%9E%80

https://www.percona.com/blog/2017/10/18/chose-mysql-innodb_log_file_size/

https://www.percona.com/blog/2012/10/08/measuring-the-amount-of-writes-in-innodb-redo-logs/

https://dev.mysql.com/doc/refman/5.7/en/optimizing-innodb-logging.html

https://dus815.tistory.com/entry/Mysql-Redo-Log-%EB%9E%80

https://sarc.io/index.php/mariadb/1146-innodb-redo-logging-process