[SQL Server, MySQL] 데이터 플러시와 커밋

데이터, 로그 플러시와 커밋에 대해 헷갈리는 부분이 있어 정리하고자 한다.

 

SQL SERVER

SQL SERVER는 버퍼캐시라는 메모리 공간을 사용한다.

위 아키텍처에서 확인할 수 있듯 버퍼캐시의 내용은 Lazy Write와 Cechk Point에 의해 Datafile로 동기화 된다.

1. 데이터 파일은 버퍼캐시의 더티 캐시가 특정 주기 (CHECKPOINT)에 맞춰서 Datafiles로 Flush 된다.

2. 로그는 데이터보다 먼저 쓰기 및 커밋이 되는데 이를 WAL 이라고 칭한다.

WAL을 통하여 서버 장애로 인한 데이터 파일에 변경 내용이 저장이 되지 않은 커밋은 복구가 가능하다.

데이터 변경 -> 로그 캐시 기록 -> 트랜잭션 로그 플러시 및 커밋 -> 더티 캐시 검사점에 의한 데이터 파일 플러시

 

공식 문서에 따르면 더티 페이지는 다음 세 가지 방법 중 하나로 디스크 플러시 된다.

1. 지연 쓰기 (Lazy Writer)
버퍼 캐시에서 자주 사용되지 않는 페이지를 제거하여 사용 가능한 버퍼를 유지하는 시스템 프로세스입니다. 더티 페이지는 먼저 디스크에 기록.

 

2. 열성적인 글쓰기
즉시 쓰기 프로세스는 대량 삽입 및 선택과 같이 최소 로깅된 작업과 연결된 더티 데이터 페이지를 씁니다. 이 프로세스를 통해 새 페이지를 만들고 작성하여 병렬로 수행할 수 있습니다. 즉, 호출 작업은 디스크에 페이지를 쓰기 전에 전체 작업이 완료될 때까지 기다릴 필요가 없습니다.

 

3. 검사점(CHECKPOINT)
검사점 프로세스는 지정된 데이터베이스의 페이지가 있는 버퍼에 대한 버퍼 캐시를 주기적으로 검사하고 모든 더티 페이지를 디스크에 씁니다. CHECKPOINT 명령을 사용하여 직접 호출 또는 데이터베이스 엔진은 사용된 로그 공간의 양과 마지막 검사점 이후 경과된 시간에 따라 자동 검사점을 생성. 데이터 또는 로그 파일이 데이터베이스에서 추가 또는 제거되거나 SQL Server 인스턴스가 중지된 경우 등으로 발생한다. 

3-1 자동 체크포인트

시스템에서 자동으로 체크포인트를 발생사키며 주기는 configure의 recovery interval (min) 항목으로 사용된다

이 경우 database의 target_recovery_time_seocnds는 0

3-2 간접 체크포인트

database의 target_recovery_time_seocnds 값이 0이 아닌 경우 해당 시간 값으로 체크포인트가 발생한다.

SQL SERVER 2016 버전부터 기본 값은 60이다.

TARGET_RECOVERY_TIME	recovery interval	사용되는 검사점 유형
0	0	대상 복구 간격이 1분인 자동 검사점입니다.
0	> 0	sp_configure 'recovery interval' 옵션의 사용자 정의 설정에 의해 대상 복구 간격이 지정된 자동 검사점입니다.
> 0	해당 없음	대상 복구 시간이 TARGET_RECOVERY_TIME 설정에 의해 결정되는 간접 검사점(초)입니다.

 

지연 쓰기, 즉시 쓰기 및 검사점 프로세스는 I/O 작업이 완료되기를 기다리지 않습니다. 항상 비동기(또는 겹치는) I/O를 사용하고 나중에 I/O 성공을 확인하면서 다른 작업을 계속합니다. 이렇게 하면 SQL Server가 적절한 작업에 대한 CPU 및 I/O 리소스를 모두 최대화할 수 있습니다.

 

 

MySQL

 

MySQL도 마찬가지이다.

메모리의 버퍼풀 캐시에 더티 페이지는 검사점에 의해 데이터 파일로 플러시된다.

1. Buffer Pool 내에서 Page를 수정하여 더티 페이지 생성.

2. Log Buffer에 Redo Log 생성

3. Log Buffer를 Commit이 발생하거나 Log Buffer가 가득 차는 시점에 디스크로 Flush.

4. Dirty Page는 체크포인트 과정을 통해 디스크로 Flush.

Doublewrite Buffer (디스크 영역)에 Buffer Pool에 변경된 Page를 모아서 한 번에 Write 를 하는 과정을 먼저 거침

Data 파일에 Page를 쓰는 도중 문제가 발생하면 Doublewrite Buffer 를 통하여 복구 가능하다.

 

CHECKPOINT 는 다음과 같이 2가지가 있다.

Sharp Checkpoint

Commit 된 트랜잭션들의 모든 Dirty Page를 디스크에 Flush 하고, 가장 최근에 Commit 된 트랜잭션의 LSN을 기록. Database가 정상 종료될 때나 Redo Log 파일의 순환하여 재사용해야 하는 경우에 InnoDB는 Sharp Checkpoint를 수행.

 

Fuzzy Checkpoint

Fuzzy Checkpoint는 일반적으로 사용되는 체크포인트로, 모든 Dirty Page를 디스크로 Flush하지 않고, Dirty Page를 조금씩 디스크로 Flush 하고 그 위치를 관리하는 방식. 두 개의 LSN(시작 시점, 종료 시점)을 기록함.

특정주기, FreePage가 부족한 경우, Redo Log File이 거의 가득 찬 경우, Dirty Page가 너무 늘어난 경우에 발생한다.

 

로그 파일의 경우 옵션에 따라 플러시하는 방식이 다르다

로그는 다음과 같이 처리된다.

Redo Log(WAL)을 먼저 기록.

복제 구성 또는 특정 시점으로의 Data 복구 등에 사용하기 위해 Binlog를 작성.

 

1. Buffer Pool의 Data가 변경되면 Redo Log를 생성하여 Log Buffer에 추가.

2. Redo Log 작성 후에 트랜잭션을 언제든지 Commit을 할 수 있는 prepare 상태가 되고, Log Buffer의 Redo Log를 플러시.

3. Data 변경사항 관련하여 Binlog Cache에 Binary Log를 작성.

4. 작성된 Binlog를 플러시하고 Binlog Cache를 비움.

5. Binlog 관련 정보를 Redo Log File에 기록하고 ACK와 Commit을 수행.

 

설정 옵션은 다음과 같다

Redo Log : innodb_flush_log_at_trx_commit

Binlog : sync_binlog

 

 

INNODB_FLUSH_LOG_AT_TRX_COMMIT : 0

Commit 시에 로그 버퍼를 로그 파일에 바로 기록하지 않고 1초 간격으로 Disk에 Flush
Commit이 최종적으로 Log Buffer에 쓰여지는 것 까지만 보장하므로 Flush 되는 과정에서 DBMS 가 Crash 되면 해당 트랜잭션은 유실됨
INNODB_FLUSH_LOG_AT_TRX_COMMIT : 1
Commit 시에 로그 버퍼를 로그 파일에 기록하고 바로 Flush (기본값, ACID 준수를 위해 필요)
트랜잭션이 Commit 되면 일련의 과정을 건건이 처리하게 되는데 ACID 지속성을 보장할 수는 있지만 I/O 부하가 있음
INNODB_FLUSH_LOG_AT_TRX_COMMIT : 2
Commit 시에 로그 버퍼를 로그 파일에 기록함. 디스크 플러시 작업은 초당 1회. 그러나 스케줄링 상황에 따라 반드시 초당 1회 동작한다는 보장은 없음 트랜잭션의 양은 상관없이 Flush 가 약 1초에 한번씩 자동 수행되기 때문에 매번 Flush 되는 기본값보다 I/O 성능이 좋아지지만 단점은 데이터를 유실할 가능성은 있음
OS Buffer 까지는 데이터가 넘어가기 때문에 DBMS가 Crash 되는 건 별 문제가 없지만 1초 마다 실행되는 Flush가 실행되고 있는 와중에 OS 셧다운이 발생되버린다면 해당 트랜잭션은 Commit 되었지만 유실될 수 있음

 

sync_binlog=0
Binary log의 Disk 동기화는 MySQL이 아닌 운영체제에 의해서 특정 주기별로 처리되며, Filesystem Cache Layer 까지만 쓰게됨. 이 설정은 최고의 성능을 제공하지만 정전 또는 운영 체제 문제가 발생한 경우 MySQL 서버는 트랜잭션을 커밋을 하였지만 바이너리 로그에는 동기화가 되지 않을 수 있다.

 

sync_binlog=1 (default)
트랜잭션이 커밋되기 전에 바이너리 로그를 디스크에 동기화. 
바이너리 로그에서 트랜잭션이 손실되지 않음을 보장하지만 그 만큼 성능은 저하.

 

sync_binlog=N
1보다 큰 값 n 으로 설정되면 n개의 바이너리 로그 커밋 그룹 마다 동기화가 수행됩니다.
여기서 N는 0 또는 1이 아닌 값이며 N 바이너리 로그 그룹 커밋이 수집된 후 바이너리 로그가 디스크에 동기화 됩니다.
정전이나 운영 체제 문제(Crash)이 발생한 경우 MySQL서버가 바이너리 로그에 동기화(플러시)되지 않은 트랜잭션을 커밋했을 수 있습니다.
파라미터 설정 값이 높을수록 성능이 향상되지만 데이터 손실 위험이 높아짐.

 

innodb_flush_method
InnoDB가 Data File 과 Log File 로 데이터를 Flush 하는 방법

fsync : 데이터와 파일의 메타데이터를 한꺼번에 변경하는 방식
fdatasync : 파일의 메타 정보는 무시하고 순수하게 사용자의 데이터만 변경하는 방식

Direct IO : 디스크 쓰기의 두 단계 작업 중에서 ‘운영체제의 버퍼 기록하는 작업’ 단계를 생략하고 바로 사용자의 데이터를 디스크로 쓰는 방식

FDATASYNC

 

파일의 메타 데이터는 변경하지 않게 하는 설정

InnoDB는 fsync()를 사용해서 데이터 파일과 로그 파일을 모두 플러시하며 Double Buffering 유발 (I/O 증가)

 

O_DSYNC

 

사용자 데이터와 메타 데이터까지 동기(Sync) IO 방식으로 기록하도록 설정

InnoDB는 O_SYNC를 사용해서 로그 파일을 열고 플러시하지만 데이터 파일을 플러시하기 위해서는 fsync()를 사용

 

O_DIRECT

 

사용자 데이터와 메타데이터까지 동기(Sync) IO 방식으로 버퍼를 거치지 않고 설정

InnoDB는 O_DIRECT를 사용해서 데이터 파일을 열고 데이터 파일과 로그 파일을 플러시하기 위해 fsync()를 사용

O_DIRECT 사용하는 이유는 IO 성능 저하를 감안하고 더블버퍼링을 막아 메모리를 효율적으로 쓰기 위함

InnoDB는 이미 버퍼풀(Buffer Pool)이라는 훨씬 고도화된 메모리 관리 영역을 가지고 있기 때문에 동일한 데이터를 버퍼풀과 OS캐시에 중복으로 저장해 메모리를 낭비할 필요가 없음

디스크로부터 읽혀지는 데이터는 운영체제의 캐시에 담아두고 다시 읽기 요청이 발생하면 디스크로부터 읽지 않고 캐시의 내용을 반환하는 형태로 성능을 개선하게 되지만, InnoDB는 운영체제의 캐시보다 더 체계적이고 효율적인 캐시 (InnoDB Buffer Pool)을 가지고 있기 때문에 운영체제의 캐시가 별로 도움이 되지 않음

 

 

참조

https://nomadlee.com/mysql-database-commit-architecture/

https://learn.microsoft.com/ko-kr/sql/relational-databases/writing-pages?view=sql-server-ver16

https://blog.ex-em.com/1700

https://dataonair.or.kr/db-tech-reference/d-guide/sql/?mod=document&uid=357