Mysql InnoDB 성능 개선(innodb_flush_log_at_trx_commit)

서비스 성능테스트를 진행하다보면 Mysql 성능이 전체 시스템 성능을 결정하는 경우가 있다.

mysql 때문에 성능이 나오지 않는다면 성능을 위해 redis와 같은 다른 솔루션을 선택할 수 있지만 

운영중인 서비스에서 쉽게 솔루션을 변경하기는 쉽지 않다.

mysql 성능을 향상시키는 방법은 여러개가 있지만. 간단하게 설정을 변경하여 성능을 개선할 방법이 있다.

innodb_flush_log_at_trx_commit 는 요약하자면 커밋 로그에 대하여 디스크 플러시가 언제 발생할지 설정할 수 있다.

기본으로 1로 설정이 되어있고 1은 트랜젝션이 발생할 때마다 디스크에 플러시가 발생한다. 

2로 설정하면 1초마다 디스크로 플러시가 발생하게되고, 그만큼 io가 줄어들기때문에 성능 향상을 기대할 수 있다.

(단 장애시 1초 정도의 트랜젝션은 유실될 수 있다.)

• 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=1, sync_binlog=1, innodb_safe_binlog (MySQL 5.0.3 이전 버전의 경우)를 사용해야 한다. (innodb_safe_binlog는 5.0.3 이후에는 필요가 없다.)

Reference

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

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