モンストのサーバー負荷との戦い〜あけおめ2018編〜 / bcu_30_server_9

Slide 1

Slide 1 text

Ϟϯετͷαʔόʔෛՙͱͷઓ͍ ʙ͚͓͋Ί2018ฤʙ 2018/04/21 ୈ2ճ Battle Conference U30 XFLAG։ൃຊ෦ XFLAGࣄۀਪਐࣨ SREάϧʔϓ ඿ా ګฏ @haman29 גࣜձࣾϛΫγΟ

Slide 2

Slide 2 text

About me •඿ా ګฏ @haman29 •https://twitter.com/haman29 •XFLAG ελδΦ SREάϧʔϓ •ओʹࠃ಺൛ϞϯελʔετϥΠΫʢ௨শɺϞϯετʣͷ։ൃɾӡ༻ɾෛՙରࡦ Λ୲౰ɻ •هࣄ •ʮhbstudyˌ76 SREେશ: XFLAG ελδΦฤʯͰൃද͠·ͨ͠ɻ (2017.8) •ΤϯδχΞͬͯڅྉԿʹ࢖ͬͯΔͷʁۚમࣄ৘ʹ͍͍ͭͯΖ͍Ζฉ͍͖ͯͨʛ CodeIQ MAGAZINE 3ਓ໨ (2016.3) •झຯ͸ϘϧμϦϯά 2

Slide 3

Slide 3 text

ϞϯελʔετϥΠΫ • ௨শɺϞϯετ • εϚϗͰ༡΂ΔɺͻͬͺΓϋϯςΟϯάRPG • ੈքྦྷܭར༻ऀ਺͕4,500ສਓಥഁʢ2018.3ʣ • ࠃ಺൛ɾւ֎൛ͱ෼͔Ε͍ͯΔ 3

Slide 4

Slide 4 text

ࠓ೔ͷ͓࿩ 4 • ࠃ಺൛Ϟϯετ • ຖ೥૿͑ଓ͚Δαʔόෛՙͱͷઓ͍ • DBαʔόͷਫฏ෼ׂʢShardingʣ

Slide 5

Slide 5 text

5 ϞϯετͷDBαʔό

Slide 6

Slide 6 text

ϞϯετͷDBαʔό 6 • MySQLʢMariaDBʣ • શͯΦϯϓϨϛε • 2ڌ఺ͰDC৑௕ • શମͰ300୆΄ͲʢόοΫΞοϓؚΉʣ • ਨ௚෼ׂ • ਫฏ෼ׂʢShardingʣ

Slide 7

Slide 7 text

7 ೥຤೥࢝ͷαʔόෛՙ

Slide 8

Slide 8 text

೥຤೥࢝ͷαʔόෛՙ • ຖ೥ɺϐʔΫΛܴ͑Δ • ಛʹɺݩ୴0͔࣌Β࢝·ΔΨνϟΠϕϯτʢڧڧΩϟϥʣ • 2017೥ݩ୴0࣌ɺαʔόෛՙʹΑΓۓٸϝϯςφϯε • ΞϓϦ಺௨՟༻DBʢҎ߱ɺΦʔϒDBʣͰεϩʔΫΤϦ͕ଟൃ • ଞͷϘτϧωοΫ • 2018೥͸উ͍ͪͨʂʂ • νʔϜͰ࿈ܞɾ෼୲ͯ͠ෛՙରࡦΛਐΊΔ 8

Slide 9

Slide 9 text

9 ΦʔϒDB

Slide 10

Slide 10 text

ΦʔϒDB • Φʔϒɹɹɹ ʹ Ϣʔβ༷ͷେࣄͳ͓ۚ • ਺ԯϨίʔυ • ϐʔΫ࣌ͷUpdateΫΤϦ͕਺ສqpsʢΦʔϒͷফඅʹؔΘΔશͯʣ • ෛՙରࡦ • ͢ͰʹɺIndex΍ΫΤϦ͸࠷దԽࡁΈ • ͢Ͱʹɺਨ௚෼ׂࡁΈʢ1୆ͷ෺ཧϚγϯΛઐ༗ʣ • ͢Ͱʹɺߴ͍IOੑೳΛ࣋ͭioMemoryΛ࢖͍ͬͯΔ 10

Slide 11

Slide 11 text

ΦʔϒDBɹෛՙରࡦ • ෆ੔߹͕ڐ͞Εͳ͍ • ReadΛSlave΍Ωϟογϡʹಀ͢͜ͱ΋Ͱ͖Ε͹ආ͚͍ͨɻ • ࢒͞ΕͨҰख͸ɺਫฏ෼ׂʢShardingʣ͔͠ͳ͍ • 2018೥ݩ୴0࣌ͷαʔόෛՙݟੵ΋Γ • DAUɺAPIϦΫΤετ਺ɺϐʔΫ࣌ select/update (qps) ౳ • ෼ׂ਺ɺϚγϯߏ੒ΛܾΊΔ 11

Slide 12

Slide 12 text

12 DBͷਫฏ෼ׂʢShardingʣͱ͸

Slide 13

Slide 13 text

DBͷਫฏ෼ׂʢShardingʣͱ͸ 13 user_id orb 10001 100 10002 200 10003 300 10004 400 10005 500 10006 600 10007 700 user_id orb 10001 100 10005 500 user_id orb 10002 200 10006 600 user_id orb 10003 300 10007 700 user_id orb 10004 400 • 4෼ׂͷྫ • shard_key Λݩʹ෼ׂ • modulo(৒༨) • auto_increment id ͕ར༻Ͱ͖ ͳ͍ͷͰࣗલͰ࠾൪ orbs_id_seq 10007 ࠾൪

Slide 14

Slide 14 text

14 ࣄྫʣΦʔϒDBͷਫฏ෼ׂʢShardingʣ

Slide 15

Slide 15 text

15 QɽαʔϏεՔಇதɹ͔ͭɹ਺ԯϨίʔυن໛ͷDBΛɺ ҆શʹ࣮֬ʹɺαʔϏεఀࢭΛ࠷খݶʹ཈͑ɺਫฏ෼ׂ ʢShardingʣΛ͢Δʹ͸ʁ

Slide 16

Slide 16 text

16 1. ৽چDBͷDouble Write 2. εΫϦϓτʹΑΔίϐʔͱݕূ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ 4. چDB΁ͷWriteΛࢭΊΔ ਫฏ෼ׂʢShardingʣͷखॱ σʔλͷಉظ

Slide 17

Slide 17 text

17 1. ৽چDBͷDouble Write 2. εΫϦϓτʹΑΔίϐʔͱݕূ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ 4. چDB΁ͷWriteΛࢭΊΔ σʔλͷಉظ ਫฏ෼ׂʢShardingʣͷखॱ

Slide 18

Slide 18 text

1. ৽چDB΁ͷDouble Write 18 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ چDB΁ͷWriteΛϑοΫͯ͠৽DBʹ΋Write͢Δ ৽DB INSERT or UPDATE

Slide 19

Slide 19 text

19 1. ৽چDBͷDouble Write 2. εΫϦϓτʹΑΔίϐʔͱݕূ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ 4. چDB΁ͷWriteΛࢭΊΔ σʔλͷಉظ ਫฏ෼ׂʢShardingʣͷखॱ

Slide 20

Slide 20 text

2. εΫϦϓτʹΑΔίϐʔͱݕূ 20 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB copy script INSERT or UPDATE Double Write͸ܧଓͨ͠··ίϐʔʢ਺ԯϨίʔυʣ INSERT IGNORE INTO … ※ϢχʔΫΩʔ੍໿ҧ൓ͷ࣌ʹ͸Կ΋͠ͳ͍

Slide 21

Slide 21 text

21 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB copy script verify script Verify ৽چDBͷσʔλ͕׬શʹҰக͍ͯ͠Δ͜ͱΛݕূ͢Δ INSERT or UPDATE 2. εΫϦϓτʹΑΔίϐʔͱݕূ

Slide 22

Slide 22 text

22 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB copy script verify script Verify ͜͜Ͱෆ੔߹͕͋ͬͨ৔߹͸࠶౓ίϐʔͱݕূΛ࣮ࢪ INSERT or UPDATE 2. εΫϦϓτʹΑΔίϐʔͱݕূ

Slide 23

Slide 23 text

23 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB copy script verify script Verify 8ฒྻͰ࣮ߦ͓ͯ͠Αͦ1ϲ݄Ͱ׬૸ʢcopy1पɺverify3पʣ INSERT or UPDATE 2. εΫϦϓτʹΑΔίϐʔͱݕূ ʹ ׬શʹಉظʂ

Slide 24

Slide 24 text

24 1. ৽چDBͷDouble Write 2. εΫϦϓτʹΑΔίϐʔͱݕূ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ 4. چDB΁ͷWriteΛࢭΊΔ σʔλͷಉظ ਫฏ෼ׂʢShardingʣͷखॱ

Slide 25

Slide 25 text

25 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB seq ʢ࠾൪ʣ ׬શʹRead/Write͍ͯ͠ͳ͍ঢ়ଶʹ͢Δʢϝϯςφϯεʣ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ

Slide 26

Slide 26 text

26 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB script seq ʢ࠾൪ʣ insert auto_increment id چDBͷ auto_increment id ͷ஋Λɺ৽DBͷ࠾൪ςʔϒϧʹ൓ө͢Δ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ

Slide 27

Slide 27 text

27 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB seq ʢ࠾൪ʣ ੾Γସ͑ɻ੾Γ໭͠Λߟྀͯ͠چDB΁ͷWrite͸ܧଓ͢Δ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ

Slide 28

Slide 28 text

28 1. ৽چDBͷDouble Write 2. εΫϦϓτʹΑΔίϐʔͱݕূ 3. ৽چDBͷRead/Write੾Γସ͑ʢϝϯς࣌ʣ 4. چDB΁ͷWriteΛࢭΊΔ σʔλͷಉظ ਫฏ෼ׂʢShardingʣͷखॱ

Slide 29

Slide 29 text

4. چDB΁ͷWriteΛࢭΊΔ 29 app چDB Read Write app app shard_1 shard_2 shard_N ɾɾɾ ৽DB seq ʢ࠾൪ʣ Τϥʔ0݅ɺෆ੔߹ͳ͘େ੒ޭ

Slide 30

Slide 30 text

30 2018೥ݩ୴0࣌ɹαʔόෛՙ

Slide 31

Slide 31 text

2018೥ݩ୴0࣌ɹαʔόෛՙ 31 • ΦʔϒDBͷεϩʔΫΤϦ͸0݅ → উͪʂʂʂ • ۓٸϝϯςφϯεແ͠ → উͪʂʂʂ ΦʔϒDB 2018೥ݩ୴ qps

Slide 32

Slide 32 text

32 ·ͱΊ • Ϟϯετͷαʔόෛՙ͸೥຤೥͕࢝ϐʔΫɻ • ΦʔϒDBͷਫฏ෼ׂʢShardingʣΛ࣮ࢪɻ • πʔϧ͕੔උ͞Ε͍ͯΔɻ • 2018೥ݩ୴0࣌ɺࠓ೥΋৽ͨͳϘτϧωοΫ͕ݟ͔ͭͬͨɻ • Ϩεϙϯε஗Ԇ͕ൃੜ͠ଓ͚͍ͯͨɻ

Slide 33

Slide 33 text

33 ׬શʹউͭ·ͰɺԶͨͪͱϞϯετ αʔόෛՙͷઓ͍͸ଓ͘…ʂʂ TO BE CONTINUED

Slide 34

Slide 34 text

͓͠·͍