이 페이지에서는 Cloud SQL 읽기 복제본의 복제 지연 문제를 해결하는 방법을 설명합니다.
개요
Cloud SQL 읽기 복제본은 전역 트랜잭션 식별자(GTID)를 사용하여 MySQL 행 기반 복제를 사용합니다. 변경사항은 기본 인스턴스의 바이너리 로그에 기록되고 복제본으로 전송됩니다. 여기서 복제본은 수신된 다음 데이터베이스에 적용됩니다.복제 지연은 다음과 같은 몇 가지 시나리오에서 발생할 수 있습니다.
- 기본 인스턴스가 변경사항을 복제본에 빠르게 전송할 수 없습니다.
- 복제본에서 변경사항을 빠르게 수신할 수 없습니다.
- 복제본에서 변경사항을 빠르게 적용할 수 없습니다.
network_lag 측정항목을 사용하여 기본 인스턴스가 변경사항을 빠르게 전송할 수 없거나 복제본이 변경사항을 빠르게 수신할 수 없는 처음 두 시나리오를 모니터링합니다.
총 지연 시간은 replica_lag 측정항목으로 관찰됩니다.
replica_lag와 network_lag의 차이는 복제본이 복제 변경사항을 충분히 빠르게 적용할 수 없는 세 번째 이유를 나타낼 수 있습니다.
이러한 측정항목은 다음 복제 지연 모니터링 섹션에 설명되어 있습니다.
신속한 복제본 구성
MySQL 복제본에 변경사항을 빠르게 적용하는 방법은 두 가지가 있습니다. 사용자는 다음 옵션을 사용하여 복제본을 구성할 수 있습니다.
- 병렬 복제
- 고성능 플러시
병렬 복제
병렬 복제는 복제본에 변경사항을 적용하기 위해 병렬로 작동하는 여러 스레드를 사용하도록 복제본을 구성함으로써 복제 지연에 도움이 될 수 있습니다. 병렬 복제 사용에 대한 자세한 내용은 병렬 복제 구성을 참조하세요.
고성능 플러시
기본적으로 MySQL용 Cloud SQL은 각 트랜잭션 후 재실행 로그를 디스크로 플러시합니다. 고성능 플러시가 수행되면 디스크에서 초당 플러시되는 재실행 로그 빈도가 줄어 쓰기 성능이 향상됩니다.
읽기 복제본의 innodb_flush_log_at_trx_commit 플래그를 2로 설정합니다. 또한 innodb_flush_log_at_trx_commit 플래그를 적용하려면 sync_binlog 플래그를 더 높은 값으로 설정해야 합니다.
이 플래그에 대한 자세한 내용은 플래그 사용 팁을 참조하세요.
innodb_flush_log_at_trx_commit 플래그가 읽기 복제본에 설정되고 Cloud SQL이 비정상 종료 발생을 감지하면 Cloud SQL이 자동으로 복제본을 다시 만듭니다.
쿼리 및 스키마 최적화
이 섹션에서는 복제 성능을 개선할 수 있게 해주는 일반적인 쿼리와 스키마 최적화 몇 가지를 제안합니다.
읽기 복제본의 쿼리 격리 수준
REPEATABLE READ 및 SERIALIZABLE 트랜잭션 격리 수준은 복제 변경을 차단할 수 있는 잠금을 획득합니다. 복제본에서 쿼리의 격리 수준을 줄이는 방법을 고려해 보세요. READ COMMITTED 트랜잭션 격리 수준으로 성능이 향상될 수 있습니다.
기본 데이터베이스의 장기 실행 트랜잭션
단일 트랜잭션으로 많은 수의 행이 업데이트되면 기본 인스턴스에 적용한 다음 복제본으로 보내야 하는 변경사항 수가 급증할 수 있습니다. 이는 한 번에 많은 행에 영향을 미치는 단일 문 업데이트 또는 삭제에도 적용되는 사항입니다. 변경사항은 커밋된 후 복제본으로 전송됩니다. 복제본에 적용하는 변경사항이 급증하는 경우 복제본의 쿼리 부하도 높아져 복제 지연이 발생하면 복제본에서 잠금 경합이 발생할 가능성이 높아질 수 있습니다.
대규모 트랜잭션은 여러 개의 작은 트랜잭션으로 나누는 것이 좋습니다.
기본 키 누락
Cloud SQL 읽기 복제본은 행 기반 복제를 사용하므로 복제되는 MySQL 테이블에 기본 키가 없으면 성능이 저하됩니다. 모든 복제된 테이블에 기본 키를 사용하는 것이 좋습니다.
MySQL 8 이상에서는 데이터베이스의 테이블에 기본 키가 있어야 하도록 sql_require_primary_key 플래그를 ON로 설정하는 것이 좋습니다.
DDL로 인한 배타적 잠금
ALTER TABLE 및 CREATE INDEX와 같은 데이터 정의 언어(DDL) 명령어를 사용하면 배타적 잠금으로 인해 복제본에서 복제 지연이 발생할 수 있습니다. 잠금 경합을 방지하려면 복제본에서 쿼리 부하가 낮은 시간에 DDL 실행을 예약하는 것이 좋습니다.
과부하된 복제본
읽기 복제본이 너무 많은 쿼리를 수신하면 복제가 차단될 수 있습니다. 여러 복제본 간에 읽기를 분할하여 각 복제본의 부하를 줄이는 것이 좋습니다.
쿼리 급증이 방지되도록 애플리케이션 로직이나 프록시 레이어 중 하나를 사용하는 경우 여기에서 복제본 읽기 쿼리를 제한하는 것이 좋습니다.
기본 인스턴스에서 활동이 급증하면 업데이트를 분산하는 것이 좋습니다.
모놀리식 기본 데이터베이스
지연 테이블 하나 이상이 다른 모든 테이블을 방해하지 않도록 기본 데이터베이스를 수직이나 수평으로 샤딩하는 것이 좋습니다.
복제 지연 모니터링
replica_lag 및 network_lag 측정항목을 사용하여 복제 지연을 모니터링하고 지연 원인이 기본 데이터베이스, 네트워크 또는 복제본에 있는지 확인할 수 있습니다.
| 측정항목 | 설명 |
|---|---|
| 복제 지연 ( cloudsql.googleapis.com) |
복제본 상태가 기본 인스턴스 상태보다 지연되는 시간(초)입니다. 이는 현재 시간과 현재 복제본에 적용 중인 트랜잭션을 커밋하는 기본 데이터베이스의 원래 타임스탬프 간의 차이입니다. 특히 복제본에서 쓰기를 수신했더라도 아직 데이터베이스에 적용되지 않았으면 쓰기가 지연된 것으로 간주될 수 있습니다. 이 측정항목은 복제본에서 |
| 마지막 I/O 스레드 오류 번호 ( cloudsql.googleapis.com) |
I/O 스레드가 실패하도록 만든 마지막 오류를 나타냅니다. 값이 0이 아니면 복제가 중단됩니다. 이러한 상황은 드물지만 발생할 수 있습니다. MySQL 문서에서 오류 코드의 의미를 알아보세요. 예를 들어 기본 인스턴스의 바이너리 로그 파일은 복제본이 수신되기 전에 삭제되었을 수 있습니다.
복제가 중단되면 일반적으로 Cloud SQL은 복제본을 자동으로 다시 만듭니다.
|
| 마지막 SQL 스레드 오류 번호 ( cloudsql.googleapis.com) |
SQL 스레드가 실패하도록 만든 마지막 오류를 나타냅니다. 값이 0이 아니면 복제가 중단됩니다. 이러한 상황은 드물지만 발생할 수 있습니다. MySQL 문서에서 오류 코드의 의미를 알아보세요.
복제가 중단되면 일반적으로 Cloud SQL은 복제본을 자동으로 다시 만듭니다.
|
| 네트워크 지연 ( cloudsql.googleapis.com) |
복제본의 IO 스레드에 도달하도록 기본 데이터베이스에 binlog를 작성하는 데 걸리는 시간(초)입니다.
|
복제 확인
복제본이 작동하는지 확인하려면 복제본에 다음 문을 실행합니다.
mysql> SHOW SLAVE STATUS\G;
*************************** 1. row ***************************
Slave_IO_State: Queueing master event to the relay log
Master_Host: xx.xxx.xxx.xxx
Master_User: cloudsqlreplica
Master_Port: 3306
Connect_Retry: 60
Master_Log_File: mysql-bin.199927
Read_Master_Log_Pos: 83711956
Relay_Log_File: relay-log.000025
Relay_Log_Pos: 24214376
Relay_Master_Log_File: mysql-bin.199898
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
Exec_Master_Log_Pos: 24214163
Relay_Log_Space: 3128686571
Until_Condition: None
Until_Log_File:
Until_Log_Pos: 0
Master_SSL_Allowed: Yes
Master_SSL_CA_File: master_server_ca.pem
Master_SSL_CA_Path: /mysql/datadir
Master_SSL_Cert: replica_cert.pem
Master_SSL_Cipher:
Master_SSL_Key: replica_pkey.pem
Seconds_Behind_Master: 2627
Master_SSL_Verify_Server_Cert: No
Last_IO_Errno: 0
Last_IO_Error:
Last_SQL_Errno: 0
Last_SQL_Error:
Replicate_Ignore_Server_Ids:
Master_Server_Id: 321071839
Master_UUID: 437d04e9-8456-11e8-b13d-42010a80027b
Master_Info_File: mysql.slave_master_info
SQL_Delay: 0
SQL_Remaining_Delay: NULL
Slave_SQL_Running_State: System lock
Master_Retry_Count: 86400
Master_Bind:
Last_IO_Error_Timestamp:
Last_SQL_Error_Timestamp:
Master_SSL_Crl:
Master_SSL_Crlpath:
Retrieved_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:52111095710-52120776390
Executed_Gtid_Set: 437d04e9-8456-11e8-b13d-42010a80027b:1-52113039508
Auto_Position: 1
Replicate_Rewrite_DB:
Channel_Name:
Master_TLS_Version:
1 row in set (0.00 sec)
복제가 발생하면 Slave_IO_State(첫 번째 열)에 Waiting
for master to send event 또는 유사한 메시지가 표시됩니다. 또한 Last_IO_Error 필드가 비어 있습니다.
복제가 수행되지 않으면 Slave_IO_State 열에는 Connecting to master 상태가 표시되고 Last_IO_Error 열에는 error connecting to master cloudsqlreplica@x.x.x.x:3306 상태가 표시됩니다.
MySQL 문서에 따르면 복제 지연과 관련된 몇 가지 흥미로운 필드는 다음과 같습니다.
| 필드 | 설명 |
|---|---|
Master_Log_File |
현재 I/O 스레드가 읽고 있는 소스 바이너리 로그 파일의 이름입니다. |
Read_Master_Log_Pos |
I/O 스레드가 읽은 현재 소스 바이너리 로그 파일의 위치입니다. |
Relay_Log_File |
SQL 스레드가 현재 읽고 실행하는 릴레이 로그 파일의 이름입니다. |
Relay_Log_Pos |
SQL 스레드가 읽고 실행한 현재 릴레이 로그 파일의 위치입니다. |
Relay_Master_Log_File |
SQL 스레드에서 실행된 최신 이벤트가 포함된 소스 바이너리 로그 파일의 이름입니다. |
위의 예시에서 Relay_Master_Log_File은 mysql-bin.199898 값을 가집니다.
Master_Log_File의 값은 mysql-bin.199927입니다. 숫자 서픽스 199898은 199927보다 작습니다. 즉, 복제본이 최신 mysql-bin.199927 로그 파일을 받았더라도 이전 mysql-bin.199898이 계속 적용됩니다.
이 경우 SQL 스레드는 복제본에서 지연됩니다.
기본 데이터베이스에 연결하고 다음을 실행할 수도 있습니다.
SHOW MASTER STATUS;
이 명령어는 기본 데이터베이스에 기록되는 바이너리 로그 파일을 보여줍니다.
기본 데이터베이스 바이너리 로그 파일이 복제본의 Master_Log_File보다 최신이면 I/O 스레드가 지연되고 있음을 의미합니다. 복제본은 기본 데이터베이스에서 여전히 이전 바이너리 로그 파일을 읽고 있습니다.
I/O 스레드가 지연되면 network_lag 측정항목도 높습니다. SQL 스레드가 지연되고 있지만 I/O 스레드는 그렇지 않은 경우 network_lag 측정항목은 높지 않지만 replica_lag는 높습니다.
이전 명령어를 사용하면 지연이 발생하는 동안 지연 세부정보를 확인할 수 있지만 network_lag 및 replica_lag 측정항목을 통해 과거에 발생한 지연 빈도를 확인할 수 있습니다.