우당탕탕 Cloud DB 이전기

새로운 도약을 위한 모험은 항상 과제와 함께합니다.
저에게 Aurora MySQL/RDS for MySQL 전문가로서의 경험이 GCP라는 새로운 cloud platform 운영에 대한 도전으로 다시 다가왔습니다. 이번 글에서는 public cloud 간의 이전 과정을 “우당탕탕 Cloud DB 이전기”로 공유하여 public cloud 간 이전 혹은 on-premise와 public cloud 간 이전을 고민하는 개발자들에게 조금이나마 도움을 주고자 합니다.

1. 목표

AWS에서 GCP로의 크게 4가지 주요 DB 작업을 목표로 가지고 진행하게 했습니다.

Aurora MySQL/RDS for MySQL/DynamoDB → GCP Cloud SQL for MySQL 이전
{1 DB : n Application} Architecture→ {1 DB: 1 Application} Architecture전환(DEV 환경)
service 안정성 향상과 Cost 절감을 위한 query, schema 및 index 최적화
MySQL version upgrade

이 모든 작업은 service downtime(10분 이내) 및 이전 비용 최소화라는 기본 목표 위에 설정했습니다. 실제로는 2분 내외의 service downtime이 예상되었으나 초보 GCP 운영을 생각하여 속도 조절을 위한 buffer 시간을 추가했습니다.

특히 3번의 경우는 AWS에 선 적용 후 migration을 할 것인지 아니면 migration target(GCP)에만 적용할 것인지를 두고, 내부 논의를 하였고, 큰 작업에 대한 Aurora MySQL/RDS for MySQL의 temporary disk size 이슈와 현재의 query 를 검토하고 query, index 및 schema 변경에도 문제가 발생하지 않을 것이라는 자신감(?) 으로 후자를 선택하여 진행했습니다. 이 부분은 개발사마다의 환경(query 형태, DB spec, DB 구성 등)이 다를 수 있어 각 회사에 맞게 판단하시면 좋겠습니다.

2. 사전 검토

우리의 궁극적인 목표는 service downtime 및 이전 비용을 최소화하고, 이전 후에 장애가 없도록 하는 것이었습니다. 여기에, instance spec을 down scale하고, 개발자들의 투입 리소스를 최소화하는 것도 필요했습니다. 이를 위해 크게 5가지의 내용으로 검토했습니다.

검토 전에 먼저 DB의 현재 상황을 파악하기 위해 제일 먼저 DB 실시간 모니터링 환경(grafana+victoriaMetrics+mysql/rds exporter)을 구축한 후, 아래의 검토 사항을 진행했습니다.

Step 1: 실행 query검토

service 실행 query를 분석하기 위해 general.log 를 이용해 실행 query를 추출하고 pt-query-digest 등등의 query generator를 통해 실행 query및 query 수행량 등을 파악합니다.

Step 2: schema 및 DB parameter 검토

주요 parameter 설정의 미설정 또는 오설정 여부 판단 및 schema type의 과설정 또는 오설정 판단 등등의 부하를 감소 시킬 수 있는 요소를 파악합니다.

Step 3: replica 구축 소요 시간 검토

6일 이내에 replication 구축이 완료되고 sync가 시작되어야 한다는 전제 조건을 만족해야 합니다.
사용 비용이 발생하는 solution(GCP Cloud Datastream / AWS DMS)을 사용하지 않기 위해 검토해야 합니다.

mysql export 시간 측정
dump file 전송시간 측정
mysql import 시간 측정
Sync complete 시간 측정

Step 4: version 차이에 따른 영향 검토

Aurora MySQL 2.x는 MySQL 5.7.x 기반이기 때문에 MySQL 8.0.2x 와의 parameter및 optimizer 차이에 따른 영향도를 검토해야 합니다.

Step 5: 사용 DB 종류의 단일화

Aurora MySQL/RDS for MySQL/DynamoDB → Cloud SQL for MySQL

3. 검토 결과

Step 1: 실행 query검토 결과

query 실행량이 너무 많음. 예를 들어 SELECT 1 과 같은 query는 단일 query로는 부하가 되지 않으나, 모이면 부하가 됩니다. 또, join은 부하가 크다라는 오해가 있어 join을 사용하지 않고 각각 실행하는 경우 오히려 더 부하가 됩니다.
single index가 많음. 이로 인해 scan량이 많고, 정렬이 늘어나게 됩니다.

e.g)
SELECT * FROM my_item 
WHERE user_id='test' 
AND expired_time > now() 
ORDER BY expired_time;
의 index가ALTER TABLE my_item ADD KEY ix_userid_expiredtime(user_id, expired_time);
가 아닌
ALTER TABLE my_item ADD KEY ix_userid(user_id);
ALTER TABLE my_item ADD KEY ix_expiredtime(expired_time);
로 생성되어 있다.

특히 admin site의 filter 조건 대응을 위해 column별 data 분포도와는 상관없이 모든 filter 항목별로 각각 single index를 생성한 경우도 있어 이 부분에 대한 검토가 필요했습니다.

3. or query가 많음. 아래 예제 query에서는 expired_time에 대해 NULL 대신 default값(ex.9999–12–31)을 주어지면 or를 제거할 수 있습니다. 제거하지 않는다면 optimizer에 따라 expired_time index를 활용할 수 없게 됩니다.

e.g)
SELECT * 
FROM my_item 
WHERE id = 1 
AND (expired_time > now() OR expired_time IS NULL);

4. order by single-index-column desc limit xx query로 인한 오동작 수행 query가 다수 존재함.(MySQL Optimizer: Naughty Aberrations on Queries Combining WHERE, ORDER BY and LIMIT). 잘못된 index 활용으로 인해 full scan 급의 data scan이 실행되어 큰 부하가 발생합니다.

5. transaction 이 많음. transaction이 필요한 DML(insert,update,delete) query가 아닌 select query만 실행되는 부분에도 begin 또는 auto_commit=0을 실행합니다. 이로 인해 commit이 많습니다. commit이 많으면 부하가 올라갑니다.

6. 건수가 적은 table에 대해서는 index 없이 전체 full scan query가 많음.작은 고추가 맵다고 건수가 적어도 실행량이 많으면 문제가 됩니다.

7. admin page들의 검색 query에 like ‘%명칭%’ 검색이 많음. 건수가 많지 않고 잦은 실행이 아니라면 무시할 수 있지만, 그 반대라면 full text search engine을 사용하는 것이 좋습니다.

8. join query 형태로 구현할 수 있는 query가 subquery 형태로 구현된 경우가 많음. optimizer 판단에 따라 subquery부터 실행되지 않을 수 있어 의도와는 다르게 부하를 유발하는 query가 될 수 있습니다.

9. in 절의 값들이 너무나 과도하게 들어 있음. in 값의 개수가 너무 과도한 경우 full scan으로 실행되는 경우가 있어 주의가 필요합니다. 이유를 여기서 설명하면 너무 길어지기 때문에 이유가 궁금하신 분들은 Index Dive 를 참고하면 좋습니다.

10. 현재 version의 Cloud SQL for MySQL은 이슈가 해소되었지만, 콴다팀의 migration시에는 lower_case_table_names 옵션이 제공되지 않아 query 에서 table명 대소문자 사용과 table 생성의 table명 대소문자 여부를 비교 확인했어야 했습니다.

Step 2: schema 및 DB parameter 검토 결과

과도한 longtext type 사용. text type과 varchar의 큰 size는 모두 longtext type으로 사용했습니다. 심지어 flag성 column의 경우도 longtext를 사용했습니다.
auto increment id 발행 수 초과 이슈 존재. auto increment 한계 수치에 도달했거나, 거의 도달하고 있는 int pk column 이 존재했습니다.
varchar size의 과도 설정. 특히 flag성 column size가 255로 설정되어 있는 경우가 많았습니다.
sql_mode 미적용 상태. sql_mode 가 null로 적용되어 있어, DB 입장에서는 warning 이지만, biz. 입장에서는 오류인 상황이 발생했습니다.
replication 설정이 되어 있지 않음. replication 설정을 위해서는 binlog_formet의 off 값을 바꿔야 했습니다.
table당 index가 과다 생성됨. performance schema 가 사용 가능한 spec이라면 아래의 query를 이용하여 미사용 index를 찾았습니다.(performance schema data는 휘발성이기 때문에 오랜시간 restart등을 하지 않은 상태일수록 정확할 것입니다.) performance schema를 사용할 수 없는 경우는 general log를 이용해 sql을 남겨 index 사용여부를 개발자와 논의하여 결정했습니다.

SELECT  OBJECT_SCHEMA, OBJECT_NAME, INDEX_NAME     
FROM performance_schema.table_io_waits_summary_by_index_usage 
WHERE INDEX_NAME IS NOT NULL
AND OBJECT_SCHEMA not in ('performance_schema','mysql','information_schema','sys') 
AND OBJECT_TYPE='TABLE'
AND COUNT_STAR = 0
ORDER BY 1,2;

Step 3: replica 구축 소요 시간 검토 결과

6일 이내에 replication 구축이 완료되고 sync가 시작되어야 한다는 전제 조건을 만족해야 합니다.

mysql export 시간 측정

table이 많은 경우 table들을 parallel로 import하면 많은 시간을 줄일 수 있기 때문에 아래의 query를 이용하여 table별 file size를 추출, 전체 평균 size에 맞추며 parallel로 export를 실행했습니다.

SELECT file_name, ROUND(SUM(total_extents * extent_size)/1024/1024/1024,2) AS "TableSizeinGB" 
FROM information_schema.files 
WHERE file_name like '%table명%' group by total_extents * extent_size;

mysqldump -uwriter -p -htest.com --databases test --tables t1 t2 --skip-lock-tables --complete-insert --opt --hex-blob --routines --triggers --skip-add-locks --default-character-set=utf8mb4 --no-create-info --no-create-db --skip-column-statistics --set-gtid-purged=OFF > schema.sql

2. dump file 전송시간 측정

server : 
nc -l 9999 | tar xvfz -
client :
tar cvfz - filename | nc mysql.test.ai 9999

물론 mysqldump와 netcat을 pipeline을 이용하여 아래와 같이 1,2번을 한 번에 실행 할 수도 있습니다.

client :
mysqldump -uwriter -p -htest.com --databases test --skip-lock-tables --complete-insert --opt --hex-blob --routines --triggers --skip-add-locks --default-character-set=utf8mb4 --no-create-info --no-create-db --skip-column-statistics --set-gtid-purged=OFF | tar cvzf - | nc mysql.test.ai 9999

3. mysql import 시간 측정

table size별로 export 받은 File을 disk IO를 고려하여 parallel로 import하면 많은 시간을 줄일 수 있습니다.
이를 위해 table별 size를 이용하여 큰 것과 작은 것 짝을 맞춰 parallel 하게 실행합니다.

만일, 단일 table의 data size가 매우 큰 경우는 아래와 같이 line number를 이용하여 마치 oracle의 pump와 비슷한 형태로 parallel import를 할 수도 있겠습니다.

 grep '^INSERT INTO' data.sql | awk '(NR % parallel수)==1' |./mysql -h'target-mysql.test.ai' --init-command="SET FOREIGN_KEY_CHECKS=0;" --database=target_db -utest -p'test'> /dev/null 2> error1.log

4. Sync complete 시간 측정

innodb_flush_log_at_trx_commit이 public cloud DB의 기본 설정인 1인 경우 DML traffic이 많은 service spec을 service 기준보다 높이지 않는 경우 결코 Sync가 완료되지 못했습니다. spec높이고 sync 완료시키고 service open전에 spec down을 하는 방식으로 하면 spec down 시간만큼 소요 시간이 증가하게 됩니다. 따라서 migration 시간을 줄이기 위해서는 꼭 innodb_flush_log_at_trx_commit을 0,2로 바꿀 수 있어야 하겠습니다.

Step 4: version 차이에 따른 영향 검토 결과

다양한 default 설정값이 변하였으나, 회사 내부 기준에 따라 변경 대상이 되는 아래의 variables 에는 critical한 영향이 없는 것으로 파악됐습니다.

default character
sql_mode
default_authentication_plugin
optimizer_switch
time_zone

Step 5: 사용 DB 종류의 단일화 검토 결과

Aurora MySQL/RDS for MySQL → Cloud SQL for MySQL
variables 설정 차이(version 설정 차이)로부터의 issue 가 예상되는 것은 없었습니다.
DynamoDB → Cloud SQL for MySQL
복제 구성을 위한 data 이전은 data copy application 개발로 대응하였습니다. DMS 사용을 고려할 수도 있었으나, 비용이 발생하고 내부적으로 데이터 압축을 통한 이전으로 결정되어 data copy application으로 data 이전을 하기로 했습니다.
DynamoDB → Cloud SQL for MySQL 의 이전은 Test 결과 response 성능이 보다 안정적으로 제공되고, 특히 비용이 약 1/8 정도로 절감되는 매우 큰 장점이 있습니다.

이러한 5step의 검토로 부터 구체적 작업시간을 공개할 수는 없지만, 
AWS의 최대 binlog 보관 기간인 7일이내에 migration 작업이 충분히 가능한 것으로 결과가 도출되어
예상 senario대로 migration비용을 최소화하며 migration을 진행했습니다.

4. migration 선행 작업

일괄 작업 script 혹은 App. 개발

DB cluster가 많은 환경에서 migration 시간을 단축하려는 방법으로 모든 cluster가 같은 작업에 대해 동시에 작업될 수 있도록 하는 것도 중요합니다.

binlog format 변경
binlog retention 기간 변경
instance demote
writer read_only = on 으로 변경
instance promote
session kill
service domain IP 변경 — writer,reader,admin
reader 추가

Cloud SQL for MySQL migration Senario 작성

migration Senario를 작성하여 타부서와 작업 내용을 공유함으로써 역할을 분배하고 migration 작업의 허점은 없는 지 Cross Check하는 것도 중요합니다.

DB 접속용 service domain을 생성하여 이전 전에 service domain으로 service가 될 수 있도록 app. server 배포
service 점검 시작
Aurora MySQL/RDS for MySQL writer instance를 read_only로 변경
Cloud Sql for MySQL Reader instance promote
모든 service domain(writer/reader/admin)에 promote된 instance ip 추가 후, 구 writer instance ip 제거
구 writer instance 의 session 정리(미리 작성해 둔 script를 이용하여 일괄 정리)
구 writer instance stop
service open
Cloud Sql for MySQL reader 생성
admin domain에 reader instance ip 추가 후, writer instance ip 제거

migration process

5. migration 실행 준비 작업

기존 DEV은 {1 DB: n Application} Architecture 형태로 되어 있고, PROD 환경은 {1 DB: 1 Application} Architecture 형태로 되어 있는 등 상이하여 같은 {1 DB: 1 Application}로의 이전을 하기 위해서는 DEV 환경의 replication이 조금은 특이하게 구성해야만 합니다.

왜냐하면 public cloud 환경에서는 replication 구성시 replication filter(replicate-do-db,replicate-ignore-db) 옵션 사용이 불가이기 때문입니다.

물론 전 DB를 모두 export 후 import하여 replication을 구성하고 향후 관련없는 database를 Drop하는 방법도 있겠지만, 이렇게 되면 {1 DB: 1 Application Architecture} 구성 이후 작은 규모의 DB instance들의 Disk Size들도 기존 통합DB size만큼 커지게 되어 불필요한 비용이 지속적으로 청구되기 때문에 이 방법은 사용할 수 없습니다.

RDS와 마찬가지로 Cloud SQL의 Disk Size도 증가는 online 중에 가능하지만, 감소는 불가능합니다.

Step 1: writer binlog format 적용

[DEV] binlog_format = STATEMEMT
empty table에 대해 delete,update 구문 실행시 error를 발생시키지 않게 하기 위함입니다. error가 발생하는 경우는 replication이 멈추게 되기 때문입니다.
[PROD] binlog_format = ROW

Step 2: Aurora MySQL/RDS for MySQL의 binlog retention 기간 변경

Aurora MySQL/RDS for MySQL의 binlog retention 기간을 7일로 변경하고, mysql replication 을 이용하여 failover 방식으로 이전을 진행합니다.

-- 7 * 24 = 168
call mysql.rds_set_configuration('binlog retention hours', 168);
show binary logs;

Step 3: DB Connection URI를 service domain으로 변경

Graceful migration을 위해 DB Connection 정보 변경을 중앙에서 보다 쉽게 할 수 있도록 해 점검시간 중의 app. server 재배포로 인한 소요시간을 없앱니다.

Step 4: Data export 대상 instance 구축

Aurora MySQL
snapshot backup 이후 복구하고, Recent events로부터 crash recovery position을 기록해 둡니다.
복구한 instance로부터 export를 진행합니다. 이 때 반드시 해당 database/table의 characterset을 맞추어(mysqldump 옵션에 — default-character-set=xxxxx 추가 ) export를 해야 character가 깨지는 상황을 방지할 수 있습니다.

RDS for MySQL
RDS for MySQL은 Aurora MySQL과 달리 snapshot backup events에 binlog position 정보가 남지 않습니다. 따라서, on-premise와 같이 reader를 추가한 이후 추가 reader를 replication stop하고 show replica status(구 show slave status)를 통해 binlog position을 기록해 둡니다.
신규로 추가한 instance로부터 export를 진행합니다.

CALL mysql.rds_stop_replication;

mysql> SHOW REPLICA STATUS\G
*************************** 1. row ***************************
             Replica_IO_State: Waiting for source to send event
                  Source_Host: mysql.test.ai
                  Source_User: writer
                  Source_Port: 13000
                Connect_Retry: 1
              Source_Log_File: master-bin.000001
          Read_Source_Log_Pos: 927
               Relay_Log_File: slave-relay-bin.000002
                Relay_Log_Pos: 1145
        Relay_Source_Log_File: master-bin.000001
           Replica_IO_Running: No
          Replica_SQL_Running: No
              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_Source_Log_Pos: 927
              Relay_Log_Space: 1355
              Until_Condition: None
               Until_Log_File:
                Until_Log_Pos: 0
           Source_SSL_Allowed: No
           Source_SSL_CA_File:
           Source_SSL_CA_Path:
              Source_SSL_Cert:
            Source_SSL_Cipher:
               Source_SSL_Key:
        Seconds_Behind_Source: 0
Source_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error:
               Last_SQL_Errno: 0
               Last_SQL_Error:
  Replicate_Ignore_Server_Ids:
             Source_Server_Id: 1
                  Source_UUID: 73f86016-978b-11ee-ade5-8d2a2a562feb
             Source_Info_File: mysql.slave_master_info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
    Replica_SQL_Running_State: Replica has read all relay log; waiting for more updates
           Source_Retry_Count: 10
                  Source_Bind:
      Last_IO_Error_Timestamp:
     Last_SQL_Error_Timestamp:
               Source_SSL_Crl:
           Source_SSL_Crlpath:
           Retrieved_Gtid_Set: 73f86016-978b-11ee-ade5-8d2a2a562feb:1-3
            Executed_Gtid_Set: 73f86016-978b-11ee-ade5-8d2a2a562feb:1-3
                Auto_Position: 1
         Replicate_Rewrite_DB:
                 Channel_Name:
           Source_TLS_Version:
       Source_public_key_path:
        Get_Source_public_key: 0
            Network_Namespace:

Step 5: Aurora MySQL/RDS for MySQL→ Cloud SQL for MySQL 간 replica 구축

export는 두 가지 형태로 합니다. 하나는 전체 schema export, 또 다른 하나는 복제 대상 database의 data만 export를 합니다.

schema export 의 내용을 수정:

적절치 않은 longtext type 제거
A.I. pk int->bigint
varchar size 감소
enum type 제거
불필요 index 제거
index 생성 구문 분리(schema_table.sql, schema_index.sql)
→ data적재시 index가 없는 상태에서 적재하고, 추후 alter 구문하나로 생성(특히 index개수가 3개/table 이상의 경우는 더욱) 하는 것이 data scan을 한번만 하므로 속도가 빠릅니다.
(e.g. ALTER TABLE T ADD KEY idx_1(1),ADD KEY idx_2(2),ADD KEY idx_3(3),ADD KEY idx_4(4));)
schema 내의 characterSet, collection 제거
등등

2. 데이터 초기 적재:

remote replica 생성을 위한 Cloud SQL for MySQL 을 생성합니다.
기본 characterSet은 utf8mb4 생성했으며, 이 set은 super Set이기때문에 import로 인한 깨짐 현상은 발생하지 않습니다.
schema_table.sql 을 실행합니다.
replica 구축 소요시간 검토 의 1. mysql export 시간 측정, 2. dump file 전송시간 측정에서 언급한 방식으로 Bastion instance까지 export_data.sql을 전송합니다.
data 이전과 동시에 Bastion instance에서 전송 받을 export_data.sql에 대해 import를 병렬로 실행하고, import data가 완료되면 schema_index.sql을 실행합니다.
replication 구성이후(demote이후)에는 target instance는 super_read_only mode로 변경되어 schema 및 data 작업이 불가하므로 반드시 replication 구성 전에 DB 계정 생성까지 모두 완료하고 demote를 해야 합니다.

3. replication 구성:

GCP의 경우 AWS나 on-premise와 같이 source writer에 직접적인 replication 연결이 불가능합니다.
External meta virtual instance를 생성하여 replication writer instance 역할을 하도록 설정합니다.

gcloud auth login
ACCESS_TOKEN="$(gcloud auth print-access-token)”
curl --header "Authorization: Bearer ${ACCESS_TOKEN}" --header 'Content-Type: application/json' --data @./source.json -X POST https://sqladmin.googleapis.com/sql/v1beta4/projects/project명/instances

source.json
{
      "name": "SOURCE_NAME",
      "region": "REGION",
      "databaseVersion": "DATABASE_VERSION",
      "onPremisesConfiguration": {
        "hostPort": "SOURCE_HOST",
        "username": "USERNAME",
        "password": "PASSWORD",
        "caCertificate": "SOURCE_CERT",
        "clientCertificate": "CLIENT_CERT",
        "clientKey": "CLIENT_KEY"
      }
}

Step 6: instance demote

Cloud SQL 의 demote는 console 에서 지원되지 않기 때문에 sdk 또는 API를 이용하여 대응해야 합니다. 아래는 demote 를 위한 golang 예제입니다.

masterName 은 representation instance의 NAME 입니다.
SkipReplicationSetup를 true 하지 않으면 demote와 동시에 replication 연결을 시도하므로, 반드시 false로 설정해야 합니다.

func SetDemoteMaster(ctx context.Context, sqladminService *sqladmin.Service, gcpProjectId, masterName, instanceName string) error {
 var demoteMasterContext = new(sqladmin.DemoteMasterContext) demoteMasterContext = &sqladmin.DemoteMasterContext{
  // # Database instance demote master context. # Contains details about the demoteMaster operation.
  //  Kind:                  "sql#demoteMasterContext",      //# This is always sql#demoteMasterContext.
  MasterInstanceName:    masterName, //# The name of the instance which will act as on-premises master in the replication setup.
  VerifyGtidConsistency: false,          //# Verify GTID consistency for demote operation. Default value: True. Second Generation instances only. Setting this flag to false enables you to bypass GTID consistency check between on-premises master and Cloud SQL instance during the demotion operation but also exposes you to the risk of future replication failures. Change the value only if you know the reason for the GTID divergence and are confident that doing so will not cause any replication issues.
  SkipReplicationSetup:  true,
  ReplicaConfiguration: &sqladmin.DemoteMasterConfiguration{
   // # Read-replica configuration for connecting to the on-premises master.
   // # Configuration specific to read-replicas replicating from the on-premises master.
   //   Kind: "sql#demoteMasterConfiguration", //# This is always sql#demoteMasterConfiguration.
   MysqlReplicaConfiguration: &sqladmin.DemoteMasterMySqlReplicaConfiguration{
    //# Read-replica configuration specific to MySQL databases.
    //# MySQL specific configuration when replicating from a MySQL on-premises master.
    //  Replication configuration information such as the username, password, certificates, and keys are not stored in the instance metadata.
    //  The configuration information is used only to set up the replication connection and is stored by MySQL in a file named master.info in the data directory.
    Username: replication계정명, //# The username for the replication connection.
    //    Kind:     "sql#demoteMasterMysqlReplicaConfiguration", //# This is always sql#demoteMasterMysqlReplicaConfiguration.
    //    ClientKey:         "A String",                                  //# PEM representation of the slave's private key. The corresponsing public key is encoded in the client's certificate. The format of the slave's private key can be either PKCS #1 or PKCS #8.
    //    CaCertificate:     "A String",                                  //# PEM representation of the trusted CA's x509 certificate.
    //    ClientCertificate: "A String",                                  //# PEM representation of the slave's x509 certificate.
    Password: replication계정 비밀번호, //# The password for the replication connection.
   },
  },
  ForceSendFields: []string{"VerifyGtidConsistency", "SkipReplicationSetup"},
 }
 rb := &sqladmin.InstancesDemoteMasterRequest{
  DemoteMasterContext: demoteMasterContext,
 }
 resp, err := sqladminService.Instances.DemoteMaster(gcpProjectId, instanceName, rb).Context(ctx).Do()
 if err != nil {
  log.ERROR().Errorf("Failed to Demote Master  (%s, %s) : %+v", gcpProjectId, instanceName, err)
  return err
 }
 err = InstanceOperationCheck(ctx, sqladminService, gcpProjectId, resp, 1, nil)
 if err != nil {
  log.ERROR().Errorf("Failed to Check for Demote Master : %+v", err)
  return err
 }
 return nil
}

Step 7: replication 성능 및 column type 변환 대응을 위한 옵션 적용

innodb_flush_log_at_trx_commit의 설정값을 2로 변경해야 합니다.
replica_type_conversions 의 설정값을 ALL_LOSSY,ALL_NON_LOSSY 로 변경합니다. 설정값을 변경하지 않으면 binlog_format=ROW에서 writer와 reader의 column type이 다른 경우 replication error가 발생합니다.

Step 8: replication Start

call mysql.resetMaster();
call mysql.setupExternalSource('Represetation_Instance_Name', 3306, 'rep', 'rep', 'mysql-bin-changelog.000002', 2170, false, false);
call mysql.startReplication();
show slave status\G;

6. migration 실행

Aurora MySQL/RDS for MySQL→ GCP Cloud SQL for MySQL 데이터 이전

선행 작업 단계에서 정리한 내용으로 진행합니다.

service 점검 시작
Aurora MySQL/RDS for MySQL writer instance를 read_only로 변경
Cloud SQL for MySQL reader instance promote
모든 service domain(writer/reader/admin)에 promote된 instance ip 추가 후, 구 writer instance ip 제거
구 writer instance 의 session 정리
instance가 promote되면 innodb_flush_log_at_trx_commit=1 로 변경됩니다. innodb_flush_log_at_trx_commit는 replica에 한해서 Console상에서 설정이 2로의 변경이 지원됩니다. 다만, 이 옵션을 변경하여 지속적으로 운영하는 경우 SLA를 보장 받을 수 없게 됩니다.
구 writer instance stop
service open
Cloud SQL for MySQL reader 생성
admin domain에 reader instance ip 추가 후, writer instance ip 제거
구 writer instance terminate
representation instance terminate

7. 장애

sql_mode = STRICT_TRANS_TABLES 적용으로
varchar size를 넘는 데이터를 저장할 때 warning이 발생하던 것이 error로 발생한 것 뿐이였습니다. 이것은 error가 발생되어 error 수정을 하는 것이 맞는 것으로 정상적인 error입니다.
MySQL Optimizer: Naughty Aberrations on Queries Combining WHERE, ORDER BY and LIMIT 이슈를 해결하고자 일부 DB에 아래의 옵션에 대해 값을 변경 적용했지만,

set global optimizer_switch=`prefer_ordering_index=off`;

다음과 같은 query가 full scan으로 실행됨에 따라 부하가 상승하여
(벼룩 잡으려다 초가삼간 다 태웠습니다. 😭)

SELECT * FROM my_items ORDER BY id DESC limit 10;

on 으로 Rollback하고 다음과 같은 형태로 문제의 query에 hint로 적용하여 문제대응을 하였습니다.

SELECT /*+ SET_VAR(optimizer_switch = 'prefer_ordering_index=off') */ `test`.`id`,...

마치며

이렇게 구체적인 검토/준비/실행 단계를 거쳐, 콴다팀에서는 Aurora MySQL/RDS for MySQL에서 GCP Cloud SQL for MySQL로의 DB이전을 목표시간내에 성공적으로 완료하였고, service를 더욱더 안정적으로 운영할 수 있게 되었습니다.
위의 process는 migration 뿐만 아니라 MySQL version upgrade시에도 같은 방법으로 진행할 수 있습니다. 개발자분들께 많은 도움이 되는 내용이길 바랍니다.

추가로

migration이후의 Cloud SQL for MySQL 사용에서 느낀 장단점을 간단히 언급해 보면

장점 :
1) query insight 가 있어 지정된 기간동안 실행된 거의 모든 query를 확인할 수 있어 부하 발생시 query분석을 general.log를 통한 query generation 을 하지 않고도 어렵지 않게 살펴볼 수 있어 개발자들에게 많은 도움이 됩니다. AWS performance insights가 있습니다만, 제한적으로 보여주고 있는데, CPU 부하기준으로 top 25만을 뽑기때문에 부하는 크지 않지만, 실행이 많아 부하가 되는 query등등 전체 instance 관점에서의 확인하기 어렵습니다.
2) AWS의 spec이 static 한 것에 비해 Core 수에 따라 memory size를 특정 범위내에서 자유롭게 지정이 가능합니다. memory size를 꼭 2^n으로 하지 않아도 된다네요. 물론 Core수도 다양하게 지정이 가능합니다.
단점 :
1) on-premise/RDS와 마찬가지로 대용량 table의 DDL작업으로 인한 replication delay 가 발생합니다.
물론 최근 MySql 8.0 version의 add column의 instant algorithm이 도입되어 replication delay issue에서 어느 정도 벗어났지만, 여전히 이외의 DDL문에서는 replication delay 현상이 존재하기 때문에 이를 방지를 위해서는 3rd party 솔루션으로 작업해야 합니다.
하지만, 널리 알려진 pt-online-schema-change 는 id를 명시적 A.I. pk로 사용하고, 논리 pk를 unique key를 사용하는 콴다팀 구성의 경우 trigger 복제를 이용한 작업의 경우 dead lock이 발생할 가능성이 높아 콴다팀에서는 gh-ost 를 사용하고 있습니다.
2) RDS와 같이 HA 구성을 위해서는 reader와는 별개의 추가 instance 비용이 발생하며, 이 instance는 active로의 활용이 불가합니다.
3) Cluster 간 replication 구성이 직관적이지 못 합니다.

양 public cloud DB의 성능 차이는 구체적 system spec 및 BMT 방법, traffic의 성격에 따라 상이할 수 있기 때문에 구체적으로 BMT를 진행하지 않았으며, 결과적으로는 AWS 에 비해 비용절감이 되었습니다.

🌏글로벌 Top 교육 앱 QANDA(콴다)를 함께 만들어 갈 엔지니어들을 기다리고 있습니다! ➡️ 공고 확인하기

Aurora/RDS 전문가의 GCP Cloud SQL이전기

댓글