MySQL - 아키텍처 정리 (3) - Real MySQL 8.0

Share

Last Updated on 5월 6, 2024 by Jade(정현호)

안녕하세요 
이번 포스팅은 MySQL 의 아키텍처 중에서 언두 로그(Undo Log), 체인지 버퍼, Redo Log 에 대해서 내용을 정리하였습니다.

해당 내용은 Real MySQL 8.0 책에 대해서 정리한 내용이며 아래 포스팅에서 이어지는 글입니다. 

언두 로그

InnoDB 스토리지 엔진은 트랜잭션과 격리 수준을 보장하기 위해 DML(insert,update,delete) 로 변경되기 이전 버전의 데이터를 별도로 백업 합니다.

이렇게 백업된 데이터를 언두 로그(Undo Log) 라고 하며 아래와 같은 방식으로 사용됩니다.

• 트랜잭션 보장: 트랜잭션이 롤백 되면 언두 로그에 백업 해둔 이전 버전의 데이터를 이용해 복구를 합니다.

• 격리 수준 보장: 특정 커넥션에서 데이터를 변경하는 도중에 다른 커넥션에서 데이터를 조회하면 트랜잭션 격리 수준에 맞게 변경 중인 레코드를 읽지 않고 언두 로그에 백업해둔 데이터를 읽어서 반환하기도 합니다

언두 로그는 InnoDB 스토리지 엔진에서 매우 중요한 역할을 담당하지만 그 만큼 관리 비용도 많이 필요하게 됩니다.
        

언두 로그 레코드 모니터링

언두 영역은 DML 문장으로 데이터 변경시 변경 되기전의 데이터(이전 데이터)를 보관하는 영역으로 예를 들어 다음과 같은 업데이트 문을 실행했다고 가정해 봅시다.

mysql> update user set username='홍길동' where userid=1;

위의 문장이 실행되면 트랜잭션을 커밋하지 않아도 실제 데이터 파일(데이터/인덱스 버퍼) 내용은 '홍길동' 으로 변경됩니다.
이전 데이터 값이 '이순신' 이었다면 언두 영역에는 '이순신' 이라는 이전 데이터 레코드가 백업 되어있게 됩니다.

이 단계에서 커밋이 수행되면 그대로 상태가 유지되며, 롤백을 하면 언두 영역에서 이전데이터를 통해서 다시 데이터 파일을 복구합니다.

언두 로그의 데이터는 크게 두가지 용도로 사용이 되며, 트랜잭션 롤백을 위해서와 트랜잭션 격리 수준을 구현 및 높은 동시성 제공하는데 있습니다.

트랜잭션 격리 수준은 여러 트랜잭션이 데이터를 변경하거나 조회할 때 한 트랜잭션의 작업 내용이 다른 트랜잭션에 어떻게 보일지를 결정하는 기준입니다.


언두 로그의 유지 기간 관련

대용량의 데이터를 처리하는 트랜잭션뿐만 아니라 트랜잭션이 오랜 시간 동안 실행될 때도 언두 로그의 양은 급격히 증가할 수 있습니다. 

왜냐하면 트랜잭션이 종료되었다고 해서 해당 트랜잭션이 생성한 언두 로그를 즉시 삭제할 수 있는 것이 아니기 때문입니다.

데이터 변경 작업을 한 세션의 트랜잭션이 시작 이전에 다른 세션에서 트랜잭션이 시작하고 조회를 하였고 아직 활성화 인 상태라면 데이터 변경을 만들어낸 세션의 트랜잭션 완료 여부와 관계없이 계속 읽혀야 하기 때문에 언두 로그는 계속 유지되게 됩니다.

예를 들어 일반 사용자가 트랜잭션을 시작한 상태로 완료하지 않고(commit 또는 rollback 또는 접속 종료) 하루 정도 방치했다고 가정해보면 해당 세션의 트랜잭션 시점 부터 생성된 언두 로그를 계속 보존해야 할 것입니다.
그에 따른 디스크의 언두 로그 저장공간은 계속 증가할 수 있게 됩니다.

증가된 용량 보다 문제가 되는 것은 빈번하게 변경된 레코드를 조회하는 쿼리가 실행되면 언두 로그의 이력을 필요한 만큼 스캔을 해야만 필요한 레코드를 찾을 수 있기 때문에 쿼리의 성능이 전반적으로 떨어질 수 있게 됩니다.

MySQ 5.5 버전까지는 늘어난 언두 로그의 공간이 한번 늘어나면 줄일 수가 없었으며, 5.7 과 8.0 버전에서는 언두 로그 공간에 대한 문제점은 해결된 상태입니다.

MySQL 8.0 에서는 언두 로그를 순차적으로 돌아가면서 사용하도록 해서 디스크 공간을 줄이는 것도 가능하며 MySQL 서버가 필요한 시점에 사용 공간을 줄여 주기도 합니다.

관련해서는 아래 기존 포스팅을 확인 해보시면 됩니다.


어느 버전에서나 활성 트랜잭션이 장시간 유지되는 것은 성능상 좋지 않으며 현재 MySQL 서버의 언두 로그 레코드가 얼마나 되는지는 항상 모니터링 하는 것이 좋을 것이라 생각됩니다.

아래 명령어를 통해서 MySQL 서버의 언두 로그 레코드를 확인할 수 있습니다.

mysql> show engine innodb status\G
*************************** 1. row ***************************
  Type: InnoDB
  Name:
...
------------
TRANSACTIONS
------------
Trx id counter 47130570
Purge done for trx's n:o < 47130570 undo n:o < 0 state: running but idle
History list length 32  <--!!!!
....


또는 information_schema 에서도 조회할 수 있습니다.

mysql> select count
from information_schema.innodb_metrics
where subsystem='transaction'
and name ='trx_rseg_history_len';

      

언두 테이블스페이스 관리

언두 로그가 저장되는 공간을 언두 테이블 스페이스(Undo Tablespace) 라고 합니다. 언두 테이블 스페이스는 MySQL 버전에 따라서 많은 변화를 하였습니다.

MySQL 5.6 이전 버전에서는 언두 로그가 모두 시스템 테이블스페이스에 저장되었으며 그에 따라서 서버가 초기화 될 때 생성되기 때문에 확장의 한계가 있었습니다.

My5.6 버전 부터 innodb_undo_tablespaces 시스템 변수가 도입이 되면서 시스템 변수를 2 보다 큰 값으로 설정하면 언두 로그를 시스템 테이블 스페이스가 아닌 별도의 언두 로그 파일을 이용해 저장을 합니다.
(5.6 버전 이후에도 innodb_undo_tablespaces 시스템 변수를 0으로 설정하면 여전히 언두 로그는 시스템 테이블스페이스 저장됨)

MySQL 8.0 부터 innodb_undo_tablespace의 기본값은 2가 되었으며 8.0.14 버전 부터 innodb_undo_tablespace 시스템 변수가 Deprecated 되면서 8.0.14 버전 이후로 부터는 무조건(항상) 시스템 테이블스페이스가 아닌 별도의 테이블 스페이스을 사용되도록 개선되었습니다.

하나의 언두 테이블스페이스는 1개 이상 128개 이하의 롤백 세그먼트를 가지게 되며, 롤백 세그먼트는 1개 이상의 언두 슬롯(Undo Slot) 을 가지게 됩니다.

하나의 롤백 세그먼트는 InnoDB의 페이지 크기를 16바이트로(16으로 나누는) 나눈 값의 개수만큼의 언두 슬롯(Undo Slot)을 가지게 됩니다.

InnoDB 페이지 크기 롤백 세그먼트의 언두 슬롯 수(InnoDB 페이지 크기 / 16)
4096 (4KB) 256
8192 (8KB) 512
16384 (16KB) 1024
32768 (32KB) 2048
65536 (64KB) 4096


최대 동시 트랜잭션 수는 
= (InnoDB 페이지 크기) / 16 * (롤백 세그먼트 개수) * (언두 테이블스페이스 개수) 

일반적인 서비스에서는 이정도까지 동시 트랜잭션이 필요하지는 않겠지만 기본적으로 수치가 크다고 해서 문제될 것은 없으므로 기본값을 사용을 하면 됩니다.

언두 로그 공간이 남는 것은 크게 문제가 되지 않지만 언두 로그 슬롯이 부족한 경우에 트랜잭션이 시작할 수 없는 심각한 문제가 발생되게 됩니다.

MySQL 8.0 버전 부터는 CREATE UNDO TABLESPACE 나 DROP TABLESPACE 같은 명령으로 새로운 언두 테이블스페이스를 동적 추가/삭제할 수 있도록 기능 개선이 되었으며 언두 테이블스페이스 공간을 반납하는 truncate 기능도 추가되었습니다.

관련 이전 포스팅

       

체인지 버퍼

레코드가 INSERT 되거나 UPDATE 될 때는 데이터 파일을 변경하는 작업뿐만 아니라 해당 테이블에 포함된 인덱스를 갱신하는 작업도 필요하게 됩니다.

그런데 인덱스를 업데이트하는 작업은 랜덤하게 디스크를 읽는 작업이 필요하므로 테이블에 인덱스가 많다면 이 작업은 상당히 많은 자원을 소모하게 됩니다.

InnoDB는 변경해야 할 인덱스 페이지가 버퍼 풀에 있으면 바로 업데이트를 수행을 하게 됩니다.
그렇지 않고 디스크로 부터 읽어 와서 업데이트 해야 한다면 이를 즉시 실행하지 않고 임시 공간에 저장해 두고 바로 사용자에게 결과를 반환하는 형태로 성능을 향상시키게 되는데, 이때 사용하는 임시 메모리 공간을 체인지 버퍼(Change Buffer) 라고 합니다.

중복 여부를 체크해야 하는 유니크 인덱스는 체인지 버퍼를 사용할 수 없습니다.

체인지 버퍼에 임시로 저장된 인덱스 레코드 조각은 이후 백그라운드 스레드에 의해 병합되는데, 이 스레드를 체인지 버퍼 머지 스레드(Merge thread) 라고 합니다.

MySQL 5.5 이전 버전 까지는 INSERT 작업에 대해서만 이러한 버퍼링이 가능 하였으며, 이 버퍼를 인서트 버퍼라고 하였습니다.

MySQL 5.5 버전 부터 조금씩 개선되면서 MySQL 8.0 에서는 INSERT, DELETE, UPDATE 로 인해 키를 추가하거나 삭제하는 작업에 대해서도 버퍼링이 될 수 있게 개선되었습니다.

[Change Buffer]


MySQL 5.5 버전 부터 innodb_change_buffering 시스템 변수가 새로 도입되어 작업의 종류 별로 체인지 버퍼를 활성화할 수 있으며, 체인지 버퍼가 비효율적일 때는 체인지 버퍼를 사용하지 않게 설정할 수 있게 개선되었습니다.

innodb_change_buffering 시스템 변수의 default 는 all 로 모든 인덱스 관련 작업(inserts + deletes + purges)을 버퍼링 하게 됩니다.

Value Numeric Value Description
none 0 Do not buffer any operations.
inserts 1 Buffer insert operations.
deletes 2 Buffer delete marking operations; strictly speaking, the writes that mark index records for later deletion during a purge operation.
changes 3 Buffer inserts and delete-marking operations.
purges 4 Buffer the physical deletion operations that happen in the background.
all 5 The default. Buffer inserts, delete-marking operations, and purges.


체인지 버퍼는 기본적으로 InnoDB 버퍼 풀로 설정된 메모리 공간의 25%까지 사용할 수 있게 설정되어 있으며, 필요하다면 InnoDB 버퍼 풀의 50%까지 사용할 수 있습니다.

체인지 버퍼가 너무 많은 버퍼 풀 공간을 사용하여 제한을 하고 자 하거나 DML 이 빈번하여 체인지 버퍼가 더 많은 버퍼 풀을 사용할 수 있게 하고자 한다면 innodb_change_buffer_max_size 시스템 변수에 비율을 설정하면 됩니다.

innodb_change_buffer_max_size : 기본값 25, Max 50
       

리두 로그 및 로그 버퍼

리두 로그(Redo Log) 는 트랜잭션의 4가지 요소인 ACID 중에서 D(Durable) 에 해당하는 영속성과 가장 밀접하게 연관되어 있습니다.

리두 로그는 하드웨어나 소프트웨어 등 여러 가지 문제점으로 인해 MySQL 서버가 비정상적으로 종료되었을 때 데이터 파일에 기록되지 못한 데이터를 잃지 않게 해주는 "안정장치" 입니다.

MySQL 서버를 포함한 대부분 데이터베이스 서버는 데이터 변경 내용을 로그에 먼저 기록하는 매커니즘을 구현되어 있고 Oracle Database 나 기타 DB에서는 WAL(Write-Ahead Logging) 로그 라고 합니다.

대부분의 모든 DBMS에서 데이터 파일은 쓰기보다 읽기 성능을 고려한 자료 구조를 가지고 있고 데이터 파일 쓰기는 디스크의 랜덤 액세스를 통해 진행되게 됩니다. 그래서 변경된 데이터를 데이터 파일에 기록하려면 상대적으로 큰 비용이 필요 하게 됩니다.

데이터베이스 ACID 도 중요하지만 성능도 중요하기 때문에 리두 로그를 버퍼링 할 수 있는 리두 로그를 버퍼링 할 수 있는 로그 버퍼와 같은 자료 구조도 가지고 있습니다.


MySQL 서버가 비정상 종료가 되었을 경우 두가지 종류의 일관되지 않은 상황이 발생될 수 있습니다.

1. 커밋되었지만 데이터 파일에 기록되지 않은 데이터
2. 롤백되었지만 데이터 파일에 이미 기록된 데이터


데이터베이스 서버에서 리두 로그는 트랙잭션이 커밋되면 즉시 디스크로 기록되도록 시스템 변수를 설정하는 것을 권장하고 있습니다. 그리고 그렇게 되어야만 서버가 비정상적으로 종료되었을 때 직전까지의 트랜잭션 커밋 내용이 리두 로그에 기록될 수 있고, 그 리두 로그를 이용해 장애 직전 시점까지의 복구가 가능 하게 됩니다.

그래서 InnoDB 가 스토리지는 인스턴스가 비정상 종료가 발생하여 재시작 시 리두 로그를 이용해 Instance crash recovery 를 하게 됩니다.


innodb_flush_log_at_trx_commit

이와 관련해서 innodb_flush_log_at_trx_commit 시스템 변수는 리두 로그를 어느 주기로 디스크에 동기화 할지를 결정하는 역할을 하게 됩니다.

[blog.naver.com/parkjy76]

0 : 매 1초마다 메모리의 Log Buffer 내용을 디스크로(Redo Log  파일)에 기록하게 됩니다.
디스크 I/O 가 줄어 들게 되면서 성능 적으로 튜닝 요소가 파라미터 값이지만 MySQL 이나 OS가 갑자기 문제가 생기게 된다면 최대 1초동안의 트랜잭션을 잃을 수 있습니다.

1 : 기본값 으로 commit 수행시 변경 내용을 Redo Log 파일에 기록하게 됩니다
디스크의 I/O 요청이 파라미터 값 0에 비해 상대적으로 많지만 트랜잭션의 4가지 요소인 ACID 중에서 D(Durable) 에 해당하는 영속성에 부합되는 데이터 손실이 되지 않는 시스템 변수 값입니다.

2 : 트랜잭션이 commit 하면 Log Buffer 를 OS File Cache(OS Buffe) 에 기록을 하고 1초에 한 번씩 디스크에 플러시 하게 됩니다.
MySQL 이 문제가 생기면 OS Cache 까지는 기록이 되었기 때문에 복구가 가능하지만, OS 가 비정상 종료시에는 최대 1초 동안의 내용을 손실 될수도 있습니다.

innodb_flush_log_at_trx_commit 시스템 변수가 0이나 2로 설정할 경우 디스크 동기화 작업은 항상 1초 간격으로 실행되는 것은 아니며, 스키마 변경을 위한 DDL 이 실행되면 리두 로그가 디스크로 동기화 되기 때문에 DDL이 실행되었다면 1초 보다 간격이 작을 수도 있습니다

다만 스키마 변경 작업이 자주 실행되는 것이 아니므로 문제가 발생되는 형태에 따라서 리두 로그는 최대 1초 정도 손실이 발생할 수는 있습니다.


Redo Log 크기

리두 로그 파일들의 전체 크기는 InnoDB 버퍼 풀의 효율성과 연관이 있기 때문에 신중히 결정해야 합니다.

리두 로그 파일의 크기는 innodb_log_file_size 시스템 변수로 결정되며, innodb_log_files_in_group 시스템 변수는 리두 로그 파일의 개수를 결정하게 됩니다.(8.0.29 버전까지)

MySQL 8.0.30 버전 부터 Dynamic InnoDB Redo Log 기능의 도입에 따라서 innodb_redo_log_capacity 시스템 변수도 새로 추가되었습니다.



리두  로그 파일의 전체 크기는 InnoDB 버퍼 풀의 크기에 맞게 적절히 선택되어야 변경된 내용을 버퍼 풀에 모았다가 한 번에 모아서 디스크에 기록할 수 있게 됩니다.

리두 로그 파일의 사이즈가 너무 작게 되면 되면 잦은 체크포인트 발생에 따라서 버퍼 풀의 내용을 자주 쓰게 되는, 즉 쓰기가 빈번하게 발생되게 됩니다.

로그 버퍼 기본 값이 16MB 수준에서 설정하는 것이 적합하며, BLOB 이나 TEXT 와 같이 큰 데이터를 자주 변경하는 경우에는 더 크게 설정하는 것도 고려할 수 있습니다.
        

리두 로그 아카이빙

MySQL 8.0 버전부터 리두 로그를 아카이빙 할 수 있는 기능이 추가되었습니다.

MySQL 엔터프라이즈 백업이나 Xtrabackup 툴은 데이터 파일을 복사하는 동안 리두 로그에 쌓인 내용을 계속 추적하면서 새로 추가된 리두 로그 엔트리를 복사하게 됩니다.

데이터 파일을 복사하는 동안 추가된 리두 로그 엔트리가 같이 백업되지 않는다면 복사된 데이터 백업 파일은 일관된 상태를 유지하지 못하게 됩니다. 그런데 MySQL 서버에 발생되는 데이터 변경량이 많다면 리두 로그가 매우 빠르게 증가하고 백업 툴이 새로 추가되는 리두 로그 내용을 복사하기 전에 덮어 쓰일 수 있습니다.

이렇게 아직 복사하지 못한 리두 로그 파일이 덮어 쓰이면 백업 툴은 리두 로그 엔트리를 복사할 수 없어서 백업은 실패하게 됩니다.

MySQL 8.0 버전에서 추가된 리두 아카이빙 기능은 변경량이 많아서 리두 로그 덮어 쓰인다고 해도 백업이 실패 되지 않게 해줍니다.

리두 로그 아카이빙을 사용하려면 innodb_redo_log_archive_dirs 시스템 변수를 설정해야 하며, 디렉토리는 MySQL 서버가 실행되는 OS 유저가 접근 및 사용이 가능한 소유권과 퍼미션이 필요 합니다.

설정예시)

# mkdir -p /var/lib/mysql_archive/20220101
# chown -R mysql:mysql /var/lib/mysql_archive/


mysql> set global innodb_redo_log_archive_dirs='backup:/var/lib/mysql_archive';


위와 같이 디렉토리 준비 및 파라미터를 설정하였다면 리두 로그 아키이빙을 사용하기 위해서는 UDF를 실행하면 됩니다.

mysql> DO innodb_redo_log_archive_start('backup','20220101');

UDF 를 통해서 아카이빙을 시작하였다면 테스트로 데이터 변경을 해보시면 됩니다.

데이터 변경 작업 후에 디렉토리를 확인 해보시면 리두 로그 아카이브 파일이 생성된 것을 확인할 수 있으며, 아카이빙 파일은 리두 로그 파일이 로테이션(로그 스위치)때 복사되는 것이 아닌 리두 로그 파일에 리두 엔트리가 추가될 때 함께 기록되는 방식을 사용하게 됩니다 그래서 파일을 계속 모니터링 해보면 데이터의 변경이 발생함에 따라서 파일의 용량이 증가하는 것을 확인할 수 있습니다.

# ls -alrt
drwx------. 3 mysql mysql        22 ..
drwx------. 2 mysql mysql        69 .
-r--r-----. 1 mysql mysql 253562880 archive.ab4257eb-2768-11ec-bf9a-02001700c50b.000001.log


아카이빙을 종료할 때는 innodb_redo_log_archive_stop UDF 를 사용하여 멈출 수 있습니다.
다만 아카이빙 파일은 삭제되지 않음으로 별도로 삭제를 해야 합니다.

다만 여기서 주의할 점은 innodb_redo_log_archive_start UDF 를 통해서 아카이빙을 시작한 후에 innodb_redo_log_archive_stop UDF 실행을 하기 전에 세션 연결이 끊기거나 exit 로 접속을 종료하면 아카이빙을 멈추고 생성된 아카이브 파일도 자동으로 삭제하게 됩니다.

아카이빙을 정상적으로 사용하려면 사용하는 동안 커넥션 유지 및 작업 완료 후 innodb_redo_log_archive_stop UDF 를 실행하여 정상 종료를 해야 합니다.

또한 파일 이름이 겹치면 안 되며 아카이빙 파일을 생성하려는 디렉토리에 파일이 있다면 아래와 같은 에러가 발생하면서 시작되지 않습니다.

ERROR 3847 (HY000): Cannot create redo log archive file 
'../20220101/archive.ab4257eb-2768-11ec-bf9a-02001700c50b.000001.log' (OS errno: 17 - File exists)


* 가로 길이가 길어서 개행이 내용은 개행이 되어 있습니다

파일명은 archive.server_uuid.번호.log 포맷으로 생성됨으로 아카이빙 파일이 있는 디렉토리를 지정해서 시작하게 되면 파일이름이 겹치게 됩니다.

이전에 사용한 디렉토리를 계속(또는 다시) 사용하려고 할 때는 명시적으로 파일을 정리(삭제) 후 진행이 필요 합니다.
또는 다른 디렉토리를 이용하는 것도 방법이 될 것입니다.
          

리두 로그 활성화 및 비활성화

MySQL 8.0.21 버전 부터는 수동으로 리두 로그를 활성화/비활성화 할 수 있는 기능이 추가되었습니다.

그래서 데이터를 복구하거나 인스턴스 생성 후 초기에 대량의 데이터를 적재(마이그레이션) 등의 작업을 할 경우 아래와 같이 REDO 를 비활성화 후 작업을 수행할 수 있으며, 작업 수행 시간이 단축되게 됩니다.

30만건 데이터를 WHILE Loop 로 데이터를 적재 테스트 시 REDO 로그 활성화시 약 11분, 비활성화시 약 4분 정도로 차이가 나는 것을 확인해보았습니다.
(시간 등은 시스템이나 데이터에 따라 달라짐)

비활성화는 아래와 같은 명령어로 수행하게 됩니다.

mysql> alter instance disable innodb redo_log;


작업을 모두 완료하였다면 다시 활성화를 합니다

mysql> alter instance enable innodb redo_log;


리두 로그 활성화 / 비활성화는 아래와 같이 status 상태 변수를 통해서 확인할 수 있습니다.

mysql> show global status like '%innodb_redo_log_enabled%';
+-------------------------+-------+
| Variable_name           | Value |
+-------------------------+-------+
| Innodb_redo_log_enabled | ON    |
+-------------------------+-------+


리두 로그 비활성화 하여 작업을 완료한 이후에는 반드시 리두 로그를 활성화를 해야 합니다.

리두 로그는 인스턴스의 비정상 종료 이후 Crash Recovery 시에 필요함으로 리두 로그 비활성화시에 마지막 체크포인트 이후의 데이터는 복구가 안될 수도 있습니다.

그래서 수동으로 비활성화 후 작업이 완료되었다면 꼭 활성화를 해야함을 기억해야 합니다.

또한 Redo 로그를 활성화하지 않으면 xtrabackup 유틸리티로 백업을 받을 때도 아래의 에러 메세지와 함께 실패하게 됩니다.

[Note] [MY-011825] [Xtrabackup] Redo Log Archiving is not set up.
[ERROR] [MY-013598] [InnoDB] Redo logging is disabled, cannot take consistent backup
[ERROR] [MY-011825] [Xtrabackup] recv_find_max_checkpoint() failed.



해당 포스팅은 Real MySQL 8.0 책의 많은 내용 중에서 일부분의 내용만 함축적으로 정리한 것으로 모든 내용 확인 및 이해를 위해서 직접 책을 통해 모든 내용을 확인하시는 것을 권해 드립니다.
     

Reference

Reference Book
 • Real MySQL 8.0

Reference Link
 • mysql.com/8.0/innodb-undo-logs


관련된 다른 글

 

 

      

0
글에 대한 당신의 생각을 기다립니다. 댓글 의견 주세요!x