MySQL Replication

공부한 이유

최근 이직한 회사의 데이터를 확인을 해보니 DB가 Master-Slave 구조로 되어있었다. 처음에 이러한 구조를 몰랐을 때 DB들을 확인해보니 같은 테이블명을 가졌으며, 같은컬럼을 공유하는 것이 매우 많아서 당황했었다.(진심으로.. 이거때문에 반나절을 날렸던...)

 

그래서 앞으로 이렇게 당황하지 않기위해서 오늘 MySQL Replication을 공부를 할 것이다.

 

 

MySQL Replication이란?

리플리케이션(Replication)은 복제를 뜻하며 2대 이상의 DBMS를 나눠서 데이터를 저장하는 방식이며,

사용하기 위한 최소 구성은 Master / Slave 으로 되어있다.

Master

웹서버로 부터 데이터 등록/수정/삭제 요청시 바이너리로그(Binarylog)를 생성하여 Slave 서버로 전달

(웹서버로 부터 요청한 데이터 등록/수정/삭제 기능을 하는 DBMS로 많이 사용)

Slave

Master DBMS로 부터 전달받은 바이너리로그(Binarylog)를 데이터로 반영

(웹서버로 부터 요청을 통해 데이터를 불러오는 DBMS로 많이 사용

 

MySQL Replication사용 목적

데이터의 백업

• 예로 Master 서버를 데이터의 원본서버, Slave서버를 백업서버로 지칭한다.
• 먼저 Master 서버에 DBMS의 등록/수정/업데이터가 생기는 즉시 Slave 서버의 변경된 데이터를 전달
  • 이러한 과정으로 데이터의 백업을 할수 있으며, 또한 Master 서버의 장애가 생겼을 경우 Slave 서버로 변경하여 사용
• 아래의 그림으로 표현한다면 먼저 사용자가 사용하는데 발생하는 쿼리를 Master 서버에 요청하며, Master 서버의 발생된 쿼리를 Slave 서버로 전달하게되어 백업의 용도로 사용

 

DBMS의 부화분산

• 사용자의 폭주로 인해 1대의 DB서버로 감당할수 없을때, MySQL 리플리케이션(Replication)을 이용하여 같은 DB 데이터를 여러대를 만들수 있기에 부하를 분산
• 아래의 그림으로 표현한다면 Master 서버를 등록/수정/삭제를 사용하는 서버로 사용하고, Slave 서버를 데이터를 읽는용도로 사용하게 되면 DBMS의 부하를 분산하는 용도로 사용

 

 

주의사항

1. 호환성을 위해 Replication을 사용하는 MySQL의 동일하게 맞추는것이 좋다

2. Replication을 사용하기에 MySQL 버전이 다른 경우 Slave 서버가 상위 버전을 해야 한다

3. Replication을 가동시에 Master 서버, Slave 순으로 가동

 

 

복제지연

복제지연이란?

• 마스터에서는 여러 개의 thread 가 동시다발적으로 write 를 수행하고 있는 반면, 슬레이브에 replay 하는 thread 는 1개 밖에 되지 않기 때문
• Multi Threaded Replication 기능으로 thread 를 늘려서 상황이 조금 나아졌다고 하더라도 근본 원인은 동일

복제지연 원인

Long Query

• Statement Based Replication 으로 복제하고 있는 상황에서 쿼리를 슬레이브에서 replay 해야하는 경우
• 1시간 소요되는 쿼리라면 슬레이브에서 쿼리가 실행되는 1시간 동안 복제 지연이 발생​

Write 쿼리량 증가 (데이터 사이즈 증가)

• 순수하게 write 쿼리가 증가 했기 때문에 복제해야하는 양이 많아지면서 지연이 발생 이는 서비스 사용량 증가 때문일 수도 있고 특정 시간대에 배치 작업으로 인한 영향일 수도 있음
• 서비스 사용량 증가 때문이라면 복제 지연은 계속 벌어지기만 하고 앞으로 영영 마스터를 따라잡지 못할 수도 있다. 하지만 보통은 새벽시간대에 한가해지기 때문에 낮에는 벌어지고 밤에는 따라가는 상황이 반복될 수 있다

락 이슈

• 슬레이브에서 락(metadata lock 등)이 걸려서 쿼리가 제대로 수행되지 않는 경우
  • 이 경우 락의 원인을 찾아서 해결

하드웨어 이슈

• BBU 충방전으로 인해 캐시를 사용하지 못하게 되거나,는 THP 확보를 위한 IO증가가 서버의 write 성능에 영향을 미쳐서 결과적으로 DB 성능도 저하
  • OS 파라미터도 같이 튜닝
• 간혹 하드웨어 성능 자체가 느려서 발생
  • 최근에도 21세기에 7200 rpm 하드디스크를 구매해서 사용하는 사례도 있다.

슬레이브 로드 증가

• 슬레이브에서 실행되는 서비스 read 트래픽, 배치 쿼리 또는 백업으로 인해 슬레이브 사용량이 증가하면서 평소보다 처리 성능이 지연되면서 결과적으로 복제 지연이 발생

데이터 불일치 (복제 중단)

• 지연이 아니라 중단 케이스 이지만, 중단이 길어지면 복제 지연을 유발
• 마스터와 슬레이브는 기본적으로 shared nothing 아키텍처 이므로 데이터가 불일치 되는 경우가 생긴다 쿼리의 문제일 수도 있고 누군가 슬레이브에 write 를 했기 때문
• delete 하려는 row 가 없거나 insert 하려는 row 가 있을 수 있다. 이런 경우는 슬레이브는 적용여부를 판단할 수 없기 때문에 복제를 중단하고 에러를 발생

해결방안

쿼리 성능 향상

• Long Query 때문이라면 해당 쿼리의 실행 속도를 향상시킴으로써 해결
  • 쿼리 자체를 변경할 수도 있고, 간단하게 인덱스 추가

Multi-Threaded Replication 설정

• 복제를 적용하는 worker thread 개수를 늘려서 처리 속도를 향상
• 개수가 무작정 많다고 좋지는 않다. 테스트 결과에서 16개 이상은 무의미 했던 경우가 많음
  • 개수를 적절히 조정해 보시면서 work load 에 맞는 설정

mysql> STOP SLAVE;
mysql> SET GLOBAL slave_parallel_type='LOGICAL_CLOCK';
mysql> SET GLOBAL slave_parallel_worker=8;
mysql> START SLAVE;

SBR or RBR 확인

• 쿼리 결과에 따라서 SBR (statement based replication) 이 좋을 수도 있고, RBR (row based replication) 이 좋을 수도 있다. 1시간 동안 수행된 쿼리가 결국 1건만 변경하는 쿼리였다면 세션에서 RBR 로 설정

mysql> SET SESSION binlog_format='STATEMENT';
or 
mysql> SET SESSION binlog_format='ROW';

데이터 삭제 및 로그 분리

• 전체적으로 데이터 양이 많을 수록 성능에 안좋은 영향을 미칠 가능성이 커진다.
• 대용량 데이터일 경우 대고객 서비스에 꼭 필요한 데이터만을 남기고 나머지 로그성 테이블들은 다른 DB로 분리하거나 삭제하여 경량화하는 것도 고려
• 사용량이 계속 증가하는 서비스라면 sharding 을 고민.
  • sharding 은 같은 스키마의 MySQL Cluster 를 여러 개 두어서 특정 ID 값을 기준으로 각 Cluster 로 분산해서 저장하는 방식입니다.

DB 파라미터 튜닝

• write 에 영향을 미치는 다양한 파라미터를 튜닝
• 아래 언급만 파라미터들의 sync 주기를 느슨하게 하시거나 off 하시는 방향으로 설정
• 당연히 비정상적인 crash 상황에서 안정성을 해칠 수 있다는 것을 염두해 두시고 사용

log_slave_updates
innodb_flush_log_at_try_commit
sync_binlog
binlog_group_commit_sync_delay
sync_relay_log
sync_relay_log_info
sync_master_info
binlog_row_image : 이미지를 얼마나 상세히 남길지..
relay_log_info_repository : 테이블로 설정할 경우 복제 성능에 영향을 미칠 수 있습니다.

OS 파라미터 튜닝

• 기본적으로 DB서버 성능에 영향을 미칠만한 OS 파라미터를 튜닝
• DB서버는 항상 같은 성능을 낼 수 있도록 튜닝하는 것이 좋으며, 불필요한 IO 등을 일이킬 수 있는 설정들을 배제하는 방식으로 튜닝

THP(transparent_hugepage) disable
swappiness=1 : swap 발생을 최소화
smart path disable : SSD의 경우 설정
BBU 관련 : https://blog.naver.com/sory1008/220708000897
CPU c state, p state off : cpu power 가 동일하게 유지되도록 설정
NUMA off (항상은 아니지만 메모리가 대용량일 경우 메모리 불균형 할당으로 swap 사용의 위험이 높아집니다.)

DB 엔진 변경

• TokuDB 로 엔진을 변경 및 마이그레이션 해서 복제 지연 문제를 해결
  • 이 경우에는 사전에 많은 테스트를 거쳐서 이슈가 없는지 검증

하드웨어 속도 향상

• RAID 1+0 구성이 되어 있는지, SSD 로 교체가 가능한지, CPU, Memory 등 업그레이드가 가능한지 검토
• Public Cloud 에서는 인스턴스 타입 변경이 비교적 자유롭기 때문에, 예전보다는 쉽게 접근해볼 수 있는 방법
  • 원인이 어느정도 확인이 되어야 설득력이 있다.

​

 

 

 

'

 

 

 

 

 

참고

m.blog.naver.com/PostView.nhn?blogId=sory1008&logNo=221600878238&proxyReferer=https:%2F%2Fwww.google.com%2F

tommypagy.tistory.com/210

nirsa.tistory.com/141

server-talk.tistory.com/240