MySQL replication : Statement-Based vs. Row-Based replication

* Reference : http://www.qijoo.com/FAPM/mysql/5.1/MySQL_51_en/ch06s12.html

[Statement-based]

<Up>

 - MySQL 3.23 부터 존재해 온 검증된 기술이다

 - 상대적으로 log 파일 크기가 작다. 특히 다량의 update/delete의 경우에 효과가 크다

    log 크기가 작으면 저장공간에 부담이 적고 빠른 백업이 가능하다

 - log 파일에 모든 변경 내용이 저장되어 DB audit 에서 사용 가능하다

 - log 파일은 replication 용도 뿐 아니라, 적절한 시간과 지점에서의 recovery에 사용될 수 있다

 - Slave는 Mater와 다른 더 새로운 버전의, row 구조가 다른 MySQL로도 적용될 수도 있다

<Down>

 - UPDATE문에서의 random함수 사용시에 replication이 보장되지 않을 수 있다

 - statement 내에서 found_rows(), uuid() 또는 non-deterministic UDF(User-defined function) 등의

   함수 사용시에 정확한 replication을 기대할 수 없다

   ex) load_file(), uuid(), user(), found_rows(), sysdate() (--sysdate-is-now 옵션으로 수행하지 않은 경우)

   cf) rand(), now()등의 대부분의 함수는 가능

 - INSERT...SELECT 문의 경우, 상대적으로 많은 수의 row-level lock이 발생할 수 있다 <1>

 - index를 사용하지 않아서 table scan이 발생하는  UPDATE문의 경우, 상대적으로 많은 수의 Master 내에서의 row-level lock이 발생할 수 있다 <2>

 - innodb의 경우, auto_increment 가 적용된 테이블에 INSERT시에, 실제로 충돌이 일어나지 않는 다른 INSERT 문들이 제약을 받을 수 있다 <3>

 - 내장함수들이 동일한 now() 함수를 호출하게 되는데, 이것이 원하는 결과일 수도, 아닐 수도 있다

 - Slave에는 Deterministric UDF만 존재하고, 사용되어야 한다

 - Slave의 동기화에 문제가 생기는 경우, 시간이 지날수록 Master와의 차이가 커질 가능성이 높다

 - Master와 Slave의 테이블들의 구조는 (거의) 동일해야 한다

[Row-based]

<Up>

 - 모든 것들이 동기화 된다. 가장 안전한 Replication 방법이다.

    * Mixed mode(--binlog-format=MIXED) 일 경우, CREATE 같은 DDL은 statement-based로, DML들(GRAND, REVOKE를 포함하여)은 row-based 로동작한다

 - 대부분의 다른 DBMS들과 동일한 복제 방법이다

 - 일반적인 경우, PK를 가지는 테이블에 대해 빠른 동기화가 이루어 진다

 - Statement-based의 <1>, <2>, <3> 경우에 대해, Master에서 상대적으로 적은 수의 lock이 발생한다 

 - INSERT, UPDATE, DELETE 문들에 대해, Slave에서 상대적으로 적은 수의 lock이 발생한다

 - SMP 머신일 경우 Slave에서 여러개의 thread를 통해 data를 병렬적으로 반영할 수 있게 된다

<Down>

 - Log 파일의 크기가 커질 가능성이 많다

 - 대량의 데이터가 롤백될 경우 binlog에 해당 데이터가 다량 쌓이게 된다

 - Statement-based의 경우 단순 statement(UPDATE, DELETE 문 등)만 log에 기록되지만, row-based 는

   모든 UPDATE 또는 DELETE된 row의 데이터가 기록되어 binlog가 상대적으로 긴 시간동안 lock 될 것이고

   Concurreny 문제가 발생될 가능성이 커진다

 - 크기가 큰 BLOB 값을 생성하는 Deterministic UDF의 경우 replicate에 상당히 많은 시간이 소요될 수 있다

 - 어떤 statement가 실행되었는지 log를 분석할 수 없다

 - Slave에서 어떤 statement가 Master로부터 전송되고 실행되었는지 알 수 없다

 - Non-transactional bulk operation(SELECT * FROM table_name)에 대해서 Masterdhk Slave에서의 결과가 달라질 가능성이 있다

* 위의 대부분의 내용은 reference의 내용을 번역하고, 본인이 정리하기 쉽게 각색한 것이다.

[Bottom Line]

- 모든 경우에 해당하는 정답은 없으나, 개발자가 어떤 Stored function이나 UDF를 쓸지 100% 통제가 불가능하다면

  row-based가 더 권장된다.

- Trigger, stored procedure를 주로 사용하는 환경이라면 row-based가 더 권장된다

  (실제로 Trigger를 다수 사용하는 Schema에서 Statement-based replication을 설정하면 상당히 자주 Sync가

   깨지는 경우는 발견할 수 있다. 즉, 이 경우에는 MUST 라고 할 수 있다)

- DDL, DML에서 uuid(), rand() 와 같은 non-deterministic 함수를 사용하거나, cascading foreign key relation

  등을 통해 indirect update를 자주 수행하는 경우 row-based가 더 권장된다.

- Storage가 넉넉하고 하드웨어 성능이 좋다면 row-based가 더 권장된다.

- 상대적으로 단순한 Schema를 사용하고, 대량의 데이터가 한꺼번에 UPDATE, DELETE, INSERT...SELECT 되는

  경우가 빈번하다면 Statement-based가 더 권장된다.