Tweet
Tweet

Slide 1

Slide 1 text

RDS for MySQL͔ΒDMSΛ࢖ͬͯ Amazon AuroraʹϊʔϝϯςͰ Ҡߦ͠ʢΑ͏ͱ͠ʣͨ࿩ גࣜձࣾGunosy ඿஍྄ี

Slide 2

Slide 2 text

͓·͑ͩΕ • @aibou (͸·͡ Γΐ͏͚͢) • ࠷ۙຊ໊Ͱݺ͹Εͯͳ͍ • GunosyαʔϏεͷΠϯϑϥશൠ୲౰ • Java(Spring Boot), Ruby, Chef, Goͱ͔ • AWSྺ1೥ • ޷͖ͳAWSαʔϏεɿElasticTranscoder • Ԍ্ྺ͋Γ

Slide 3

Slide 3 text

GunosyͷΠϯϑϥ • AWS Opsworks + Scheduled Lambda + EMR • RDS + ElastiCache(Redis) + Redshift(ϩάอଘ) • աڈʹ Docker ΍ ElasticBeanstalk ΛҰ෦࠾༻͕ͯͨ͠
 ݁ہOpsworksʹམͪண͘ • EC2Πϯελϯεͷ͏ͪ99%͕Opsworks؅ཧԼ • EMR & ͱ͋ΔϨΨγʔαʔό͕؅ཧ֎ • ৄ͘͠͸ https://speakerdeck.com/koid/yokuwakaru-aws-opsworks

Slide 4

Slide 4 text

ࠓճͷ͖͔͚ͬ

Slide 5

Slide 5 text

AWS DMSͱ͸ AWS Database Migration Service (Ҏ߱DMS) • DB to DBͷσʔλϚΠάϨʔγϣϯΛαϙʔτ • ΦϯϓϨͷDBΛAWSʹɺతͳ • ϚΠάϨʔγϣϯޙͷϨϓϦέʔγϣϯ΋

Slide 6

Slide 6 text

DMSͷ࢓૊Έ

Slide 7

Slide 7 text

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`

Slide 8

Slide 8 text

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`, ~~~~~ )

Slide 9

Slide 9 text

޿ࠂ഑৴γεςϜͷRDSࣄ৘

Slide 10

Slide 10 text

ϚΠάϨʔγϣϯ࣌ͷߏ੒

Slide 11

Slide 11 text

͕ɺ͔͠͠

Slide 12

Slide 12 text

ϨϓϦ௥͍͔ͭͳ͍໰୊ • ݕূޙͷਖ਼౰ੑ֬ೝͨ͠Βɺ਺࣌ؒલʙ൒೔લͷσʔλ͕ͳ͍ • ݪҼɿఆظతʹ૸ΔَUPDATEʹΑΓɺ
 ɹɹɹϚΠάϨʔτޙͷϨϓϦέʔγϣϯ͕௥͍͍͍ͭͯͳ͍ • ͲΜͲΜ޿͕Δ஗Ԇ • ΞϓϦέʔγϣϯࣄ৘ʹΑΔ΋ͷͰվमඞਢ • Ͱ΋ϝϯςφϯεࠂ஌͸ࡁʢ͋ͱ਺೔ʣ

Slide 13

Slide 13 text

࢒೦ͳ͕Β ※MySQLͷεφοϓγϣοτΛAuroraԽͰ͖Δ௒ઈศརϘλϯͰ͢

Slide 14

Slide 14 text

DMSͷϋϚΓͲ͜Ζ ͋͘·Ͱ΋ݱ࣌఺Ͱͷ΋ͷͳͷͰ ΞοϓσʔτͰมΘΔ΋ͷ΋͋Δͱࢥ͍·͢

Slide 15

Slide 15 text

ύϥϝʔλ(SourceDB) ม͑ͳ͍ͱϚΠάϨʔτͰ͖ͳ͍ DMS༻ϨϓϦΧ͚ͩผPG෇༩͠·͠ΐ͏ QBSBNFUFS EFGBVMU DIBOHFE CJOMPH@GPSNBU .*9&% 308 CJOMPH@DIFDLTVN FOHJOFEFGBVMU /0/&

Slide 16

Slide 16 text

ϚΠάϨʔτ͞Εͳ͍΋ͷ • INDEX౳͸DMSͰ࡞੒͞Εͳ͍ • Ҋᶃ ϚΠάϨʔτલʹINDEXੜ੒ʢ͓͢͢Ίʣ • Ҋᶄ ϚΠάϨʔτޙʹALTER TABLE • MySQLϢʔβʢMySQLεΩʔϚʣ͸ϚΠάϨʔτ͞Εͳ͍ • ϚΠάϨʔτલޙͰखಈ࡞੒

Slide 17

Slide 17 text

ϨϓϦέʔγϣϯΠϯελϯε • t2 vs c4 ʢຊ൪ͳΒc4ͷํ͕҆શʣ • Source DBͷετϨʔδαΠζͱಉ౳͔ͦΕҎ্ • ద੾ͳωοτϫʔΫઃఆΛ • Security Group, Subnet • ϓϥΠϕʔτNWͰ௨৴Ͱ͖ΔͳΒ΍Δ

Slide 18

Slide 18 text

id = 0໰୊ • auto incrͳϑΟʔϧυʹ0͕ೖͬͯͨ৔߹ɺ࿈൪͕શͯͣΕΔ • ݕূ࣌͸ id = 0ͷΧϥϜ͚ͩεΩοϓ͞ΕͯҠߦ • sql_mode = NO_AUTO_VALUE_ON_ZERO Ͱ΋ղܾͰ͖ͣ • DMSܾߦ࣌͸id = 0ͷߦΛҰ୴࡟আͯ͠ҠߦޙखಈINSERTܭը • ϨϓϦΧ͸ read_only = true ʹͳͬͯΔͷͰ஫ҙ

Slide 19

Slide 19 text

ͦͷଞ஫ҙࣄ߲ • ϩά͸ඞͣ༗ޮʹʢΤϥͬͨͱ͖ͷௐࠪʣ • ϨϓϦΠϯελϯε͸EC2ͷ؅ཧ֎ • ׬ྃޙͷετοϓ͠๨Εʹ஫ҙ • λεΫΛ࡟আ͠ͳ͍ͱ࡟আͰ͖ͳ͍…

Slide 20

Slide 20 text

·ͱΊ • DMSͰϚΠάϨʔγϣϯ͔ͨͬͨ͠ͳʔ • ϨϓϦέʔγϣϯͪΌΜͱ௥͍͍ͭͯͨΒܾߦͯͨ͠ • ࣄલݕূ͸େࣄɺࠂ஌લϦϋʔαϧ΋େࣄ • ΞϓϦέʔγϣϯࣄ৘Λ೺Ѳ • ϨϓϦΧ૿͑࢝ΊͨΒAuroraԽݕ౼ • ૣΊʹಈ͍ͨ΄͏͕