gunosy-beer-2016-07-27

RDS for MySQLからDMSを使ってAmazon Auroraにノーメンテで移行し（ようとし）た話

Transcript

1. RDS for MySQL͔ΒDMSΛ࢖ͬͯ Amazon AuroraʹϊʔϝϯςͰ Ҡߦ͠ʢΑ͏ͱ͠ʣͨ࿩ גࣜձࣾGunosy ඿஍྄ี
2. ͓·͑ͩΕ • @aibou (͸·͡ Γΐ͏͚͢) • ࠷ۙຊ໊Ͱݺ͹Εͯͳ͍ • GunosyαʔϏεͷΠϯϑϥશൠ୲౰ • Java(Spring Boot), Ruby, Chef, Goͱ͔ • AWSྺ1೥ • ޷͖ͳAWSαʔϏεɿElasticTranscoder • Ԍ্ྺ͋Γ
3. GunosyͷΠϯϑϥ • AWS Opsworks + Scheduled Lambda + EMR • RDS + ElastiCache(Redis) + Redshift(ϩάอଘ) • աڈʹ Docker ΍ ElasticBeanstalk ΛҰ෦࠾༻͕ͯͨ͠
 ݁ہOpsworksʹམͪண͘ • EC2Πϯελϯεͷ͏ͪ99%͕Opsworks؅ཧԼ • EMR & ͱ͋ΔϨΨγʔαʔό͕؅ཧ֎ • ৄ͘͠͸ https://speakerdeck.com/koid/yokuwakaru-aws-opsworks
4. ࠓճͷ͖͔͚ͬ
5. AWS DMSͱ͸ AWS Database Migration Service (Ҏ߱DMS) • DB to DBͷσʔλϚΠάϨʔγϣϯΛαϙʔτ • ΦϯϓϨͷDBΛAWSʹɺతͳ • ϚΠάϨʔγϣϯޙͷϨϓϦέʔγϣϯ΋
6. DMSͷ࢓૊Έ
7. SHOW PROCESSLIST Source DataBase SELECT `id`,`client_name`,`action_name`, ~~ FROM `hoge`.`action_logs` SELECT `creative_id`,`media_id`, ~~~~~~~~~~ FROM
 `hoge`.`creatives_approval_medias` SELECT `id`,`campaign_id`,`date`, ~~~~~~~~~ FROM `hoge`.`campaign_stats` SELECT `id`,`campaign_id`,`os`, ~~~~~~~~~~~ FROM `hoge`.`os_stats` SELECT `id`,`campaign_id`,`creative_id`, ~~ FROM
 `hoge`.`campaign_creative_stats`
8. SHOW PROCESSLIST Target DataBase load data local infile "/rdsdbdata/data/tasks/XXXXX/data_files/6/LOAD00000003.csv" into table `hoge`.`action_logs` CHARACTER SET UTF8 fields terminated by ',' enclosed by '"' lines terminated by '\n'( `id`,`client_name`,`action_name` ~~~~~ ) load data local infile "/rdsdbdata/data/tasks/XXXXX/data_files/50/ LOAD00000007.csv" into table `hoge`.`creatives_approval_medias` CHARACTER SET UTF8 fields terminated by ',' enclosed by '"' lines terminated by '\n'( `creative_id`,`media_id`, ~~~~~ )
9. ޿ࠂ഑৴γεςϜͷRDSࣄ৘
10. ϚΠάϨʔγϣϯ࣌ͷߏ੒
11. ͕ɺ͔͠͠
12. ϨϓϦ௥͍͔ͭͳ͍໰୊ • ݕূޙͷਖ਼౰ੑ֬ೝͨ͠Βɺ਺࣌ؒલʙ൒೔લͷσʔλ͕ͳ͍ • ݪҼɿఆظతʹ૸ΔَUPDATEʹΑΓɺ
 ɹɹɹϚΠάϨʔτޙͷϨϓϦέʔγϣϯ͕௥͍͍͍ͭͯͳ͍ • ͲΜͲΜ޿͕Δ஗Ԇ • ΞϓϦέʔγϣϯࣄ৘ʹΑΔ΋ͷͰվमඞਢ • Ͱ΋ϝϯςφϯεࠂ஌͸ࡁʢ͋ͱ਺೔ʣ
13. ࢒೦ͳ͕Β ※MySQLͷεφοϓγϣοτΛAuroraԽͰ͖Δ௒ઈศརϘλϯͰ͢
14. DMSͷϋϚΓͲ͜Ζ ͋͘·Ͱ΋ݱ࣌఺Ͱͷ΋ͷͳͷͰ ΞοϓσʔτͰมΘΔ΋ͷ΋͋Δͱࢥ͍·͢
15. ύϥϝʔλ(SourceDB) ม͑ͳ͍ͱϚΠάϨʔτͰ͖ͳ͍ DMS༻ϨϓϦΧ͚ͩผPG෇༩͠·͠ΐ͏ QBSBNFUFS EFGBVMU DIBOHFE CJOMPH@GPSNBU .*9&% 308 CJOMPH@DIFDLTVN FOHJOFEFGBVMU /0/&
16. ϚΠάϨʔτ͞Εͳ͍΋ͷ • INDEX౳͸DMSͰ࡞੒͞Εͳ͍ • Ҋᶃ ϚΠάϨʔτલʹINDEXੜ੒ʢ͓͢͢Ίʣ • Ҋᶄ ϚΠάϨʔτޙʹALTER TABLE • MySQLϢʔβʢMySQLεΩʔϚʣ͸ϚΠάϨʔτ͞Εͳ͍ • ϚΠάϨʔτલޙͰखಈ࡞੒
17. ϨϓϦέʔγϣϯΠϯελϯε • t2 vs c4 ʢຊ൪ͳΒc4ͷํ͕҆શʣ • Source DBͷετϨʔδαΠζͱಉ౳͔ͦΕҎ্ • ద੾ͳωοτϫʔΫઃఆΛ • Security Group, Subnet • ϓϥΠϕʔτNWͰ௨৴Ͱ͖ΔͳΒ΍Δ
18. id = 0໰୊ • auto incrͳϑΟʔϧυʹ0͕ೖͬͯͨ৔߹ɺ࿈൪͕શͯͣΕΔ • ݕূ࣌͸ id = 0ͷΧϥϜ͚ͩεΩοϓ͞ΕͯҠߦ • sql_mode = NO_AUTO_VALUE_ON_ZERO Ͱ΋ղܾͰ͖ͣ • DMSܾߦ࣌͸id = 0ͷߦΛҰ୴࡟আͯ͠ҠߦޙखಈINSERTܭը • ϨϓϦΧ͸ read_only = true ʹͳͬͯΔͷͰ஫ҙ
19. ͦͷଞ஫ҙࣄ߲ • ϩά͸ඞͣ༗ޮʹʢΤϥͬͨͱ͖ͷௐࠪʣ • ϨϓϦΠϯελϯε͸EC2ͷ؅ཧ֎ • ׬ྃޙͷετοϓ͠๨Εʹ஫ҙ • λεΫΛ࡟আ͠ͳ͍ͱ࡟আͰ͖ͳ͍…
20. ·ͱΊ • DMSͰϚΠάϨʔγϣϯ͔ͨͬͨ͠ͳʔ • ϨϓϦέʔγϣϯͪΌΜͱ௥͍͍ͭͯͨΒܾߦͯͨ͠ • ࣄલݕূ͸େࣄɺࠂ஌લϦϋʔαϧ΋େࣄ • ΞϓϦέʔγϣϯࣄ৘Λ೺Ѳ • ϨϓϦΧ૿͑࢝ΊͨΒAuroraԽݕ౼ • ૣΊʹಈ͍ͨ΄͏͕