안녕하세요.
복제 구성을 하고 나서 동기화 지연이 발생하는데 왜 Second 타임이 안잡이지 할때가 있는데요.
특히 AWS나 리전간 동기화 타임에서 안잡힐때 설정하면 확인이 가능합니다.
innodb_flush_log_at_trx_commit
- 위의 그림에서 알 수 있듯이, 해당 값에 따라 순간적인 장애시 트랜잭션을 잃을 수 있다.
- 0 인 경우, MySQL 이나 OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있다.
- 1 인 경우, 안전하다.
- 2 인 경우, OS가 갑자기 crash 된다면 최대 1초동안의 트랜잭션을 잃을 수 있다. 하지만 MySQL 장애시에는 이미 OS 영역으로 데이터는 넘어갔기 때문에 안전할 수 있다.
- 각 값에 따라, 엄청난 성능을 보일 수 있다.
- 지난번에 엄청난 양의 log를 위하여 MySQL을 사용하는 팀이 있었는데, 해당값을 1에서 0으로 수정함에 따라 성능이 7배 빨라지기도 했다.
- 단순 select용의 slave나, 최대 1초정도의 트랜잭션은 무시할 수 있는 서비스 혹은 log를 저장할 서버라면 해당값을 1에서 0으로 변경할 수 있다.
- MySQL을 가장 빠르고 쉽게 튜닝할 수 있는 parameter 중의 하나이다
Slave_IO_Running: Yes
Slave_SQL_Running: Yes
Replicate_Do_DB:
Replicate_Ignore_DB:
Replicate_Do_Table:
Replicate_Ignore_Table:
Replicate_Wild_Do_Table:
Replicate_Wild_Ignore_Table:
Last_Errno: 0
Last_Error:
Skip_Counter: 0
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: No
Master_SSL_CA_File:
Master_SSL_CA_Path:
Master_SSL_Cert:
Master_SSL_Cipher:
Master_SSL_Key:
Seconds_Behind_Master: 118
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
실행 순서 설명
1. Commit 이 되면
2. InnoDB의 log buffer에 데이터를 쓴다(메모리영역)
3. 그런 다음 OS buffer 를 거쳐서(메모리영역)
4. redo 로그라 불리는 InooDB log file 에 쓰게 된다. (디스크영역) (이 부분을 flush라 표현한다.)
innodb_flush_log_at_trx_commit 옵션이 위 흐름을 어떻게 컨트롤 할지 선택할 수 있게 한다.
innodb_flush_log_at_trx_commit=1
기본값이다. 굳이 설정하지 않아도 해당 값으로 셋팅된다.
트랜잭션이 커밋되면 1~4 의 과정을 건건이 처리하게 되는데 ACID의 지속성을 보장받을수 있지만
IO 부하가 상당하다. 쓰기 속도보다 데이터의 중요도가 훨씬 더 크다면 기본값을 사용하는게 좋다.
innodb_flush_log_at_trx_commit=2
기본값과 다른점은 커밋됐을때 1~3 까지의 과정만 처리한다는 것이다. (한마디로 메모리 영역만)
그리고 실제 디스크에 쓰는 flush 는 약 1초에 한번씩 자동 수행된다.
트랜젝션의 양은 상관없이 flush 가 1초에 한번씩만 수행되기 때문에 매번 flush 되는 기본값보다 IO 성능이
월등히 좋아지지만 단점은 데이터를 유실할 가능성이 있다는 점이다.
OS buffer 까지는 데이터가 넘어가기 때문에 DBMS가 크래시되는건 별 문제 없지만 1초마다 실행되는 flush가
실행되고 있는 와중에 OS가 셧다운되버린다면 해당 트랜젝션은 커밋되었지만 유실될수 밖에 없다.
innodb_flush_log_at_trx_commit=0
이 옵션은 1~2 까지의 과정만 처리한다. 1초에 한번씩 3~4 과정을 자동으로 수행하게 되는데 쓰기 속도가
그만큼 더 빨라지지만 역시나 리스크는 더 커진다. 커밋해도 최종적으로 log buffer 에 쓰여지는 것 까지만
보장하므로 flush 되는 과정에서 DBMS가 크래시 되면 해당 트랜잭션은 유실된다.
서버가 갑작스럽게 죽었을때 1~2초 정도의 데이터를 버릴수 있을 정도의 상황이라면 옵션2 가 가장 좋은 선택이다.
서버가 죽었을때는 당연하고 MySQL/MariaDB 가 갑자기 죽어도 1~2초 정도의 데이터를 버릴수 있다면 옵션0 이 가장 좋다.
커밋된 데이터는 무조건 살려야만 한다면 반드시 옵션1 을 선택해야 한다.
결론.
Data의 유실을 막고 안정적인 서비스를 위해서는 기본값 1 사용을 추천합니다. Database 는 Data의 유실이 있어서는 안되겠죠?
감사합니다.