RecoPick



안녕하세요. RecoPick 팀의 김성민 입니다.


MySQL을 사용하다 보면, 항상 골치가 아픈 문제 중의 하나가 MySQL을 Master-Slave 구조로 이중화한 경우 replication lag가 크게 발생하는 문제일 것입니다.

저희 팀 역시 AWS RDS 서비스에서 MySQL 5.5 (InnoDB)을 사용하면서, 간헐적으로 발생하는 replication lag 를 고민하던 중에 MySQL InnoDB 설정으로 문제를 해결할 수 있었습니다.

아래 그래프 처럼, replication lag가 1,000~3,500초 사이로 발생하고 있었습니다.




MySQL InnoDB 설정을 다음과 같이 변경한 이후에는 replication-lag가 500초 이하로 줄었습니다

innodb_flush_log_at_trx_commit = 2

이 마법 같은 시스템 설정에 대해서 좀 더 알아보면,

innodb_flush_log_at_trx_commit


innodb_flush_log_at_trx_commit이 0으로 설정되면, 로그 버퍼는 초 당 한번씩 로그 파일이 기록이 되고 디스크 연산에 대한 플러시는 로그 파일에서 실행되지만, 트랜젝션 실행 시점에는 아무것도 실행되지 않게 된다. 이 값이 1 (디폴트)로 설정되면, 로그 버퍼는 각 트랜젝션이 실행될 때마다 로그 파일에 기록되고 로그 파일에서 디스크 연산에 대한 플러시가 실행된다. 2로 설정되면, 로그 버퍼는 각 실행 시점마다 파일로 기록되지만, 디스크 연산에 대한 플러시는 실행되지 않는다. 하지만, 로그 파일에 대한 플러시는 값이 2일 때에도 초당 한번씩 실행된다. 초당1회의 플러시는 모든 초당 이루어진다고는 장담할 수가 없는데, 그 이유는 프로세스 스케쥴링 문제 때문이라는 점을 알아두자.

이 변수의 디폴트 값은 1이며, 이 값은 ACID와의 호환성을 위해 요구되는 값이다. 여러분은 이 값을 1 이외의 값으로 설정해서 보다 좋은 성능을 얻을 수는 있겠지만, 크래시가 나게 되면 한 순간에 모든 것을 잃어 버릴 수도 있다. 만일 이 값을 0으로 설정한다면, 어떠한 mysqld 프로세스 크래시라도. 만일 이 값을 2로 설정한다면, OS 크래시 또는 전원 불량을 통해서만 마지막 초 순간의 트랜젝션이 지워지게 된다. 하지만, InnoDB의 크래시 복구는 영향을 받지 않으며 따라서 크래시 복구는 변수 값에 상관없이 실행된다. 대다수의 OS와 몇몇 디스크들은 디스크에 대한 플러시 연산을 제대로 실행하지 못한다는 점을 알아두자.

 
Note: 트랜젝션을 사용하는 InnoDB을 이용한 리플리케이션 설정에서 내구성과 일관성을 최대로 확보하기 위해서는, 여러분이 사용하는 마스터 서버의my.cnf 파일에서innodb_flush_log_at_trx_commit=1sync_binlog=1innodb_safe_binlog (MySQL 5.0.3 이전 버전의 경우)를 사용해야 한다. (innodb_safe_binlog는 5.0.3 이후에는 필요가 없다.)


(출처: MySQL 5.0 한글 매뉴얼)


요약하자면,
innodb_flush_log_at_trx_commit = 2 로 설정하면,
- commit 할 때마다 log 파일에 쓰지 않고, 1초 간격으로 log 파일에 기록함 

- 장애 발생 시, 1초간의 transaction 유실이 있을 수 있음


즉, log 파일을 1초 간격으로 기록하기 때문에, 파일 쓰기 횟수가 줄어들어서 replication lag를 크게 줄일 수 있었습니다.

MySQL replication lag 때문에 고민하시는 분들은 innodb_flush_log_at_trx_commit 설정을 변경해 보시는 것은 어떨까요?

[참고 자료]

(1) MySQL 5.0 한글 매뉴얼, 14.2.4. InnoDB 스타트업 옵션 및 시스템 변수

- http://www.mysqlkorea.co.kr/sub.html?mcode=manual&scode=01&m_no=21862&cat1=14&cat2=422&cat3=438&lang=k


(2) MySQL 5.5 Reference Manual, 14.3.16 InnoDB Startup Options and System Variables
http://dev.mysql.com/doc/refman/5.5/en/innodb-parameters.html


(3) Reasons for MySQL Replication Lag

- http://www.mysqlperformanceblog.com/2011/07/29/reasons-for-mysql-replication-lag/


(4) Fighting MySQL Replication Lag

- http://www.mysqlperformanceblog.com/2008/09/22/fighting-mysql-replication-lag/


(5) Managing Slave Lag with MySQL Replication

- http://www.mysqlperformanceblog.com/2007/10/12/managing-slave-lag-with-mysql-replication/


(6) 5 Tricks to Avoid MySQL Replication Delays

- http://mechanics.flite.com/blog/2012/06/12/5-tricks-to-avoid-mysql-replication-delays/


Posted by recopick