[MySQL] Delete 쿼리 하나로 수십시간의 복제 지연 발생 원인 분석

MySQL DB를 3대 HA 구성으로 사용하고 있다.

(Primary 1대와 Replica 2대로 구성되어 있다. Write은 Primary에서만 일어난다.)

 

DB 작업을 하다가 Primary에서 1~2 시간 내로 끝나던 Delete 쿼리가 Replica에서는 수십시간이 지나도 쿼리가 완료되지 않고 Replication Lag만 계속 늘어나는 현상을 발견하여 이에 대해 정리해 보고자 한다.

---

내 상식으로는 Primary에서 걸린 시간만큼 Replica에서도 같은 쿼리를 적용할 것 같았다.

그런데 이 경우에는 Primary보다 Replica에서 쿼리를 수행하는 데 훨씬 더 시간이 오래 걸렸다.

 

Primary에서 날린 쿼리는 50GB 정도 되는 테이블에  날짜 기반으로 검색하여 데이터를 삭제하는 쿼리(delete ~ where date <= '' and date >= '';)였다. 해당 테이블에는 날짜 컬럼에 대한 인덱스가 없었다.

 

인덱스가 없기 때문에, Primary에서 해당 쿼리를 수행할 때는 full table scan이 일어난다. (왜 쿼리 하나가 1~2시간이나 걸렸는지 알 수 있다...)

---

그러면 Replica에서 이 쿼리를 복제할 때는 어떻게 복제해 갈까?

 

MySQL에서 어떤 binary log format을 사용하는지에 따라 달라진다.

binary log format에는 ROW와 STATEMENT 두가지 방식이 있다.

 

STATEMENT는 쿼리 자체를 기록하여 Replica에서 binlog에서 쿼리를 읽어 동일 쿼리를 수행하게 된다.

ROW는 Primary에서 쿼리를 수행하면서 일어난 row의 변화를 기록한다. Replica에서는 row별 변경 내용을 그대로 적용한다.

 

두 방식의 장단점은 명확하다. Statement는 binlog 양을 줄여준다. 이에 반해 ROW는 binlog 양이 많으나 Primary에서 일어난 데이터 변경을 그대로 replica에도 전달한다.

 

Statement 방식은 binlog가 적게 쌓인다는 장점이 있으나, 때에 따라서는 사용이 불가하다. 그 예가 READ COMMITTED일 때이다.

---

READ COMMITTED는 MySQL Isolation Level 중 한 단계이다.

READ_COMMITTED는 커밋된 데이터만 보이는 정책으로, 현재 트랜잭션이 종료되지 않은 상태에서 다른 트랜잭션이 commit되면 다른 트랜잭션이 변경한 값이 보이는 정책이다. 이런 경우에 SQL statement 단위로 binary log에 적용하는 STATEMENT 방식은 이 현상을 재현하지 못하게 된다. 

(Transaction 중간에 다른 transaction에서 데이터를 바꿔버리는 현상이 있을 때, SQL 쿼리 단위로 binary log에 쓰면 레플리카에서 binlog 기반으로 데이터를 복제할때 이 현상이 보이지 않는다. 이 현상을 보려면 row 레벨로 데이터 변경을 다 로깅하는 ROW-level이여야 한다.)

즉, Isolation level이 READ_COMMITTED일 경우에 binlog는 ROW로 쓰이게 된다.

---

그래서 다시 원점으로 와서 왜 delete 쿼리 하나가 source에서 수행된 시간보다 replica에서 수행된 시간이 훨씬 길었냐 하면…

우리 팀에서는 READ_COMMITTED Isolation Level + MIXED Row Format을 사용하고 있었다.

( MIXED format의 경우에 MySQL 이 알아서 STATEMENT와 ROW 중 골라 binlog를 적는다.)

 

source에서는 full table scan 한번으로 수행된 쿼리가,

Bin log에는 row 기반으로 기록되어 delete된 row수만큼 bin log가 생겨났고,

replica에서는 이 binlog를 적용하는데 (index가 없었으므로) full table scan*row수만큼 실행해야 해서 수행시간이 훨씬훨씬 길었던 것이다…

 

정리해 보면, 인덱스가 없는 컬럼에 대해 데이터 변경을 많이 일으키는 쿼리는 애초에 날리면 안 되었던 것이다...!!

---

그러면 어떻게 이 상황을 해결할 수 있을까?

1. 쿼리 튜닝
2. 인덱스 생성

 

쿼리 튜닝의 경우 limit을 걸어 한 쿼리에서 변경하는 데이터의 수를 줄이는 것이다. 이렇게 하면 Replica로 복제할 때도 데이터 수만큼 적용하면 되니까 적용 시간을 그만큼 줄일 수 있다.

인덱스 생성의 경우에는 1건의 데이터 변경마다 full table scan하던 것에서 index만 보면 되니까 시간이 많이 줄어들게 된다.

---

Lesson Learned: 쿼리 하나 날릴때도 신중하자, DB 동작 방식을 알아야 쿼리 튜닝도 가능하다.

---

참고 사이트

https://stackoverflow.com/questions/68905310/mysql-replication-lag-in-slave-due-to-delete-query-row-based-replication

MySQL Replication lag in slave due to Delete query - Row Based Replication

https://www.percona.com/blog/heres-a-quick-way-to-foresee-if-replication-slave-is-ever-going-to-catch-up-and-when/

Is Replication Slave ever going to catch up? When?!

https://www.percona.com/blog/fighting-mysql-replication-lag/

Fighting MySQL Replication Lag

https://dev.mysql.com/doc/refman/5.7/en/binary-log-setting.html

MySQL :: MySQL 5.7 Reference Manual :: 5.4.4.2 Setting The Binary Log Format

https://dev.mysql.com/doc/refman/8.0/en/replication-formats.html

MySQL :: MySQL 8.0 Reference Manual :: 17.2.1 Replication Formats