MySQL innodb_flush_log_at_trx_commit 옵션

서론

 

실무에 있어서 MySQL 설정 중 하나인 innodb_flush_log_at_trx_commit 옵션이 무엇인지 알아두면 좋다. 

 

공식문서 내용을 참고하는 것이 가장 정확하겠지만 영어문서를 읽는 것이 익숙하지 않다면 아래 정리 내용을 봐도 좋을 것 같다. 

 

요약

innodb_flush_log_at_trx_commit 옵션은 InnoDB 스토리지 엔진에서 트랜잭션의 커밋(commit) 작업 시 로그 파일을 언제 디스크에 쓸 것인지를 결정하는 옵션이다. DB 운영 중 고민 해야 할 부분이 성능(performance)와 데이터 트랜잭션 커밋 신뢰성(ACID) 사이의 균형일텐데 이 둘 사이의 균형을 조절하는 옵션이라 보면 될 것 같다. 성능과 트랜잭션 커밋된 데이터의 신뢰성은 양립할 수 없고 경우에 따라 이 둘의 중요도에 따라 옵션을 잘 선택하는 것이 중요하다. 

 

innodb_flush_log_at_trx_commit 은 0, 1, 2 값을 가질 수 있는데 기본값은 1 이다.

 

각 경우에 대해 설명하면 다음과 같다. 

1.  innodb_flush_log_at_trx_commit = 0 인 경우

트랜잭션의 커밋이 발생할 때마다 로그를 즉시 기록하지 않고, 일정 시간(기본적으로 대략 1초이지만 항상 1초를 보장하지는 않음)마다 로그가 기록되고 디스크에 flush 된다. 따라서 flush 되지 않은 트랜잭션은 DBMS가 다운되면 해당 데이터를 잃어버리게 된다. 또한 데이터 영속도 시간차가 발생해 트랜잭션에서 데이터는 삽입, 변경 후 직후 트랜잭션에서 쿼리하면 데이터가 제대로 쿼리되지 않을 수 있다.

 

이 경우 DBMS의 안정성은 낮겠지만 가장 높은 성능을 낼 수 있다.

2.  innodb_flush_log_at_trx_commit = 1 인 경우 (default, 기본값)

트랜잭션의 커밋이 발생할 때마다 로그가 기록되고 디스크에 flush 된다. 즉, 모든 트랜잭션에 대해 full ACID compliance를 준수한다.

 

이 경우 데이터베이스 시스템의 안정성이 가장 높겠지만 매우 빈번한 디스크 I/O가 발생하므로 성능 저하가 발생할 수 있다.

 

3.  innodb_flush_log_at_trx_commit = 2 인 경우

트랜잭션의 커밋이 발생할 때마다 로그를 기록하고, 일정 시간(기본적으로 대략 1초이지만 항상 1초를 보장하지는 않음)마다 디스크에 flush 한다.  따라서 0으로 설정했을 때와 마찬가지로 flush 되지 않은 트랜잭션은 DBMS가 다운되면 해당 데이터를 잃어버리게 된다. 또한 데이터 영속도 시간차가 발생해 트랜잭션에서 데이터는 삽입, 변경 후 직후 트랜잭션에서 쿼리하면 데이터가 제대로 쿼리되지 않을 수 있다.

 

이 경우 데이터베이스 시스템의 안정성과 성능 모두를 어느 정도 만족시키는 중간정도의 선택이라 볼 수 있다.

 

Log Flushing 에 대해

innodb_flush_log_at_timeout 설정에 의해 Log Flushing 주기가 설정되는데 기본값은 1초이다. 이 설정에 대해서는 우선 공식 문서 링크로 대체하고 다음에 다시 포스팅하면 좋을 것 같다. 

 

결론

각 설정에 대해 성능과 DMBS의 안정성에 대한 관계를 표로 정리하면 다음과 같다. 

innodb_flush_log_at_trx_commit 값	성능(Performance)	안정성(ACID compliance)
0	상	하
1(기본값)	하	상
2	중	중

성능과 안정성은 양립할 수 없는 관계이기 때문에 innodb_flush_log_at_trx_commit 옵션을 어떻게 설정하는지는 정답 없으며 운영하는 서비스 성격에 맞게 DBMS의 안정성과 성능 사이에서 균형있게 설정하면 된다.