Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Amazon Auroraを活用したソーシャルゲームの複数ワールドデータ統合 / AWS De...

Amazon Auroraを活用したソーシャルゲームの複数ワールドデータ統合 / AWS Dev Day Online Japan

Avatar for FUJIWARA Shunichiro

FUJIWARA Shunichiro

September 29, 2021
Tweet

More Decks by FUJIWARA Shunichiro

Other Decks in Technology

Transcript

  1. ΅͘ΒͷʂߕࢠԂϙέοτ αʔόʔ؀ڥ 2014೥9݄ϦϦʔε࣌఺͔Β͢΂ͯ Amazon Web Services(AWS) Amazon Elastic Compute Cloud

    (EC2) Amazon RDS for MySQL Amazon ElastiCache for Redis Amazon Redshift 2016೥10݄ RDS for MySQL → Aurora MySQL 2021೥3݄ EC2 → Amazon Elastic Container Service(ECS)
  2. ҰൠతͳεϚʔτϑΥϯ޲͚ήʔϜͷ Ϣʔβʔ਺ਪҠ (֓೦) 1. ϦϦʔεޙҰఆظؒ·Ͱٸܹʹ૿Ճ 2. ؇΍͔ʹݮগ 3. ޿ࠂ΍ίϥϘࢪࡦͰҰ࣌తʹ૿Ճ 2→3→2→3

    Λ܁Γฦ͢ ଛӹ෼ذ఺ΛԼճΓ ҡ࣋Ͱ͖ͳ͘ͳΔͱαʔϏεऴྃ΁ ͲΕ͚ͩݮগΛ؇΍͔ʹͰ͖Δ͔Ͱ ण໋͕มΘͬͯ͘Δ
  3. GvG (guild versus guild) ήʔϜͷಛੑ ϦϦʔε࣌఺Ͱ͸ͻͱͭͷϫʔϧυ͔͠ͳ͔ͬͨ GvG ήʔϜ = ରਓؒ(νʔϜ)ઓ

    ࢒͍ͬͯΔϢʔβʔ͸೥݄ͱͱ΋ʹͲΜͲΜڧ͘ͳ͍ͬͯ͘ • ৽نϢʔβʔ͕ೖͬͯ΋ͳ͔ͳ͔উͯͳ͍ • ͔ͤͬ͘ೖ͖ͬͯͯ͘Εͨ৽نϢʔβʔ͕ఆண͠ͳ͍ ݮগ͕Ճ଎͢ΔͱαʔϏε͕ҡ࣋Ͱ͖ͳ͍ 㱺 ·ͬ͞Βͳ৽نϫʔϧυΛ௥Ճ͠Α͏ʂ ৽نϢʔβʔ͕ઓ͑ΔੈքΛ༻ҙͯ͠ɺͦ͜Ͱఆணͯ͠΋Β͏
  4. σʔλͷID͕ॏෳɺͱ͸ ͭ·Γ౷߹લ͸͜͏ • ϫʔϧυA/Bͷ player.id = 100͸ શ͘ͷผਓ • ϫʔϧυA/Bͷ

    player_item.id = 1000 ͸׬શʹผ෺ • player_item.player_id = 100 ͕ࢀর͢Δઌ(player.id)΋౰વผਓ Ͳ͏ͨ͠Β…
  5. ID ରԠදΛ࣋ͭ? ͨͱ͑͹id_mapςʔϒϧʹ৽چIDͷରԠදΛ࡞Δ? world old_id new_id A 1 1 B

    1 2 A 2 3 ... ... ... A 9999 19999 B 9999 20000 A 10000 20001 B 10000 20002 ... ... ...
  6. world old_id new_id A 9999 19999 B 9999 20000 UPDATE

    player SET id=(SELECT new_id FROM id_map WHERE old_id=player.id AND world='A'); UPDATE player_item SET id=(SELECT new_id FROM id_map WHERE old_id=player_item.id AND world='A'), player_id=(SELECT new_id FROM id_map WHERE old_id=player_item.player_id AND world='A'); ͜ͷαϒΫΤϦͰ਺ेԯߦΛUPDATE……ͭΒͦ͏
  7. ҰൠతͳγϟʔσΟϯά γϟʔσΟϯά(sharding) = σʔλΛ෼ׂͯ͠ෳ਺σʔλϕʔεʹ֨ೲ id data 2 AAA 3 BBB

    4 CCC 5 DDD Ұ൪୯७ͳͷ͸IDͷ৒༨(mod)ͰৼΓ෼͚ ϫʔϧυA: id data 2 AAA 4 CCC ϫʔϧυB: id data 3 BBB 5 DDD
  8. ࠓճ͸෼ׂͰ͸ͳ͘౷߹ͳͷͰٯʹ͢Δ ϫʔϧυA: ৽͍͠IDΛ ʹ͢Δ data id new_id AAA 1 →

    2 CCC 2 → 4 ϫʔϧυB: ৽͍͠IDΛ ʹ͢Δ data id new_id BBB 1 → 3 DDD 2 → 5 ͷIDͱ ͷID͸ ౷߹ͯ͠΋িಥ͠ͳ͍ʂ data id AAA 2 BBB 3 CCC 4 DDD 5
  9. SQLͰ΋αϒΫΤϦͳ͠Ͱ؆୯ܭࢉ UPDATE player SET id=(id * 2 + 1); UPDATE

    player_item SET id=(id * 2 + 1), player_id=(player_id * 2 + 1); ͢΂ͯͷม׵ޙͷIDΛ͋Β͔͡ΊܾఆతʹٻΊΒΕΔ IDৼΓ௚͠໰୊͸ղܾʂ (ͨͩ͠ʮٕज़తʹ͸Մೳʯঢ়ଶ)
  10. σʔλͷexportͱimportํ๏Λݕ౼ ୯७ͳํ๏: Aurora MySQL ͔Β mysqldump 1. UPDATE player SET

    id=(id * 2 + shard_id)... 2. mysqldump > dump.sql 3. mysql < dump.sql িಥͤ͞ͳ͍ͨΊʹ͸ɺઌʹIDΛॻ͖׵͔͑ͯΒdump͢Δඞཁ͕͋Δ • ࠷ॳͷUPDATEͰֻ͕͔࣌ؒΓͦ͏ • ਺ඦGBͷdumpσʔλ(SQL)ΛऔΓ͜Ήͱ1೔ʙ਺೔Ϩϕϧ(ܦݧଇ) ϝϯςφϯε͸Ͱ͖Δ͚ͩ୹͍ͨ͘͠
  11. mysqldump Ҏ֎ͷํ๏Λߟ͑Δ • ڊେςʔϒϧͷUPDATEʹ͸ֻ͕͔࣌ؒΔ 㱺 import͢ΔσʔλࣗମΛIDॻ͖׵͑ޙͷঢ়ଶʹ͍ͨ͠ • ߴ଎ԽͷͨΊɺฒྻॲཧΛ͍ͨ͠ 㱺 ςʔϒϧ୯ҐͰ෼ׂͯ͠ॲཧ

    (͜Ε͸mysqldumpͰ΋Մೳ) ʮAmazon Aurora MySQL ͔Β Amazon S3 ʹσʔλΛ ΤΫεϙʔτ͓ΑͼΠϯϙʔτ͢ΔͨΊͷϕετϓϥΫςΟεʯ1 1 https://aws.amazon.com/jp/blogs/news/best-practices-for-exporting-and-importing-data-from-amazon-aurora-mysql-to- amazon-s3/
  12. SELECT INTO OUTFILE S3 ͱ LOAD DATA FROM S3 1.

    ΫΤϦ݁ՌΛ Amazon S3 ʹ CSV ͱͯ͠ export 2. CSV Λ S3 ͔ΒಡΈࠐΜͰςʔϒϧʹ import ςʔϒϧ୯ҐͰฒྻॲཧ͕Մೳ Aurora 㱻 S3 ؒͰॲཧ͕׬݁
  13. SELECT INTO OUTFILE S3 mysqldumpͱ͸ҧ͍ɺ೚ҙͷΫΤϦ݁ՌΛS3ʹ௚઀ग़ྗͰ͖Δ IDॻ͖׵͑ࡁΈͷCSV͕ग़ྗͰ͖ΔͷͰɺͦͷ··औΓ͜ΊΔ 㱺 ڊେςʔϒϧͷ UPDATE ͕ෆཁʹ

    SELECT id*2+1 AS id, foo, bar, ... -- IDΛॻ͖׵͑ͯ͠·͏ FROM player INTO OUTFILE S3 's3://...' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' MANIFEST ON; MANIFEST ON : ڊେͳςʔϒϧͷ৔߹ɺෳ਺S3 objectʹ෼ׂ͞ΕΔ objectҰཡ͕هࡌ͞ΕΔϚχϑΣετϑΝΠϧΛग़ྗ͢Δ
  14. LOAD DATA FROM S3 S3ʹଘࡏ͍ͯ͠ΔCSVΛςʔϒϧʹऔΓ͜Ή LOAD DATA FROM S3 MANIFEST

    's3://...' INTO TABLE player FIELDS TERMINATED BY ',' LINES TERMINATED BY '\\n' (id, foo, bar, ...) SELECT INTO OUTFILE S3 Ͱग़ྗͨ͠ MANIFEST Λ ࢦఆͯ͠औΓࠐΈ
  15. ฒྻॲཧ͕Մೳ SELECT INTO OUTFILE S3 ͱ LOAD DATA FROM S3

    ͸ ෳ਺ηογϣϯͰಉ࣌ʹൃߦՄೳ σʔλ͸ Aurora 㱻 S3 ؒͰ௚઀సૹ͞ΕΔ ΫΤϦൃߦݩʹ͸ෛՙֻ͕͔Βͳ͍ AuroraͷϚϧνίΞͱڧྗͳετϨʔδ S3 ͷྗΛଘ෼ʹ׆༻Ͱ͖Δʂ
  16. ਺ඦςʔϒϧʹରͯ͠ SQL Λੜ੒͢Δ खͰ͸ແཧͳͷͰ Perl ͷεΫϦϓτͰ…… 1. show tables ͰςʔϒϧҰཡΛऔಘ

    2. ࣄલʹચ͍ग़ͨ͠ςʔϒϧ໊ɺΧϥϜ໊Λݩʹ IDม׵͕ඞཁͳςʔϒϧʹ͍ͭͯ SELECT INTO OUTFILE S3 LOAD DATA FROM S3 ͷSQLΛੜ੒
  17. IDม׵ର৅ͷચ͍ग़͠ͱϧʔϧԽ xxx_id ͱ͍͏໊લ͕෇͍͍ͯΕ ͹େ఍͸ม׵ର৅ ͨͩ͠Ϛελʔσʔλ͸ม׵ର৅ Ͱ͸ͳ͍ͷͰআ֎ ஍ಓͳચ͍ग़͠࡞ۀͰϧʔϧԽ # ඞͣIDม׵ͷର৅ʹ͢ΔΧϥϜ໊ #

    ͜͜Ͱఆٛ͞Ε͍ͯΔ΋ͷ + # ***_idͱͭ͘΋ͷͰϚελʔσʔλςʔϒϧ͕ # ଘࡏ͠ͳ͍΋ͷ͕IDม׵ͷର৅ my $REQUIRED_CONVERT_COLUMN = { id => 1, team_id => 1, team_name_id => 1 }; # IDม׵ͷର৅ʹؚΊͳ͍ΧϥϜ໊ my $IGNORE_CONVERT_COLUMN = { synced_league_id => 1, growth_lock_league_id => 1, post_id => 1, farm_league_id => 1, character_id => 1, # ...
  18. Կ౓΋ຊ൪ͷσʔλͰϦϋʔαϧ͢ΔͨΊʹ TerraformͰsnapshot͔ΒͷAuroraΫϥελ෮ݩΛࣗಈԽ ࠷৽ͷεφοϓγϣοτIDͰ terraform apply ͢Δ͚ͩ ਺ඦGBͷΫϥελͰ΋1࣌ؒఔ౓ ࣮֬ʹಉ͡ઃఆͰ෮ݩͰ͖Δ resource "aws_rds_cluster"

    "restore" { cluster_identifier = "restore-cluster" engine = "aurora" engine_version = "5.6.mysql_aurora.1.19.5" snapshot_identifier = "arn:aws:rds:ap-northeast-1:..." // ←͚ͩ͜͜ॻ͖׵͑Δ // ...
  19. ͦͷଞɺࡉ͔͍޻෉ͳͲ े਺ຊɺܭ3000ߦ΄ͲͷPerl script͕ੜΈग़͞Εͨ • ηΧϯμϦΠϯσοΫεΛ DROP, ADD ͢Δ SQLจ΋ੜ੒ •

    import࣌͸ηΧϯμϦΠϯσοΫε͕ͳ͍ํ͕ߴ଎ • importޙʹ·ͱΊͯ ADD INDEX ͢Δ • SELECT ࣌ʹ count(*) ͰҠߦର৅ͷߦ਺ΛϑΝΠϧʹه࿥ • LOAD ޙʹ count(*) ͨ݁͠Ռͱಥ͖߹ΘͤΔ • Ͳ͏ͯ͠΋ॏෳͯ͠͠·͏ϨίʔυΛͳΜͱ͔͢Δ • ֎෦αʔϏεͷID͕ه࿥͞Ε͍ͯΔςʔϒϧͰॏෳͳͲ • ΞϓϦέʔγϣϯཁ݅ʹैͬͯ৽͍͠΄͏Λ࢒͢ͳͲରॲ
  20. Ϧϋʔαϧͷ݁Ռ export, import ͸ෳ਺ϓϩηεͰฒྻ࣮ߦ͢Δ Aurora(౷߹લ r5.4xlarge 16ίΞ, ౷߹ޙ r5.8xlarge 32ίΞ)

    ͱS3ͷੑೳΛҾ͖ग़ͨ͢Ί export 16ฒྻɺimport 32ฒྻͰ࣮ߦ ਺ඦGBͷIDॻ͖׵͑ͱσʔλҠߦ͕2࣌ؒͰ׬ྃʂ
  21. ϦϋʔαϧͰൃ֮ͨ͠໰୊ importޙɺADD INDEX Λ32ฒྻͰ࣮ߦ͍ͯͨ͠ͱ͜Ζ… Aurora ͷۭ͖ϝϞϦ͕ރׇͯ͠ΫϥογϡˠϑΣΠϧΦʔόʔ ଴ͬͯ΋ճ෮͠ͳ͍ ฒྻ౓Λམͱͯ͠΋ރׇ͕؇΍͔ʹͳΔ͚ͩ ADD INDEX

    Λ൒෼΄Ͳ࣮ߦޙɺAuroraΠϯελϯεΛ࠶ىಈ ࠶ىಈޙʹ͸ϝϞϦ໰୊ͳ͠ ͕࣌ؒͳ͔ͬͨͨΊݪҼ௥ٴ͸ఘΊͯ࠶ىಈͰ৐Γ੾Δ͜ͱʹ