スマホアプリ「モンスターストライク」のサーバー負荷は、年末年始に1年のピークを迎えます。2018年元旦のサーバー負荷に立ち向かうために実施した対策の一例として、データベースサーバー（MySQL）を安全に水平分割した事例を紹介します。見積もりから計画、実施に至るまでを時系列で振り返ります。
Ϟϯετͷαʔόʔෛՙͱͷઓ͍ʙ͚͓͋Ί2018ฤʙ2018/04/21ୈ2ճ Battle Conference U30XFLAG։ൃຊ෦ XFLAGࣄۀਪਐࣨ SREάϧʔϓా ګฏ @haman29גࣜձࣾϛΫγΟ
View Slide
About me•ా ګฏ @haman29•https://twitter.com/haman29•XFLAG ελδΦ SREάϧʔϓ•ओʹࠃ൛ϞϯελʔετϥΠΫʢ௨শɺϞϯετʣͷ։ൃɾӡ༻ɾෛՙରࡦΛ୲ɻ•هࣄ•ʮhbstudyˌ76 SREେશ: XFLAG ελδΦฤʯͰൃද͠·ͨ͠ɻ (2017.8)•ΤϯδχΞͬͯڅྉԿʹͬͯΔͷʁۚમࣄʹ͍͍ͭͯΖ͍Ζฉ͍͖ͯͨʛCodeIQ MAGAZINE 3ਓ (2016.3)•झຯϘϧμϦϯά 2
ϞϯελʔετϥΠΫ• ௨শɺϞϯετ• εϚϗͰ༡ΔɺͻͬͺΓϋϯςΟϯάRPG• ੈքྦྷܭར༻ऀ͕4,500ສਓಥഁʢ2018.3ʣ• ࠃ൛ɾւ֎൛ͱ͔Ε͍ͯΔ3
ࠓͷ͓4• ࠃ൛Ϟϯετ• ຖ૿͑ଓ͚Δαʔόෛՙͱͷઓ͍• DBαʔόͷਫฏׂʢShardingʣ
5ϞϯετͷDBαʔό
ϞϯετͷDBαʔό6• MySQLʢMariaDBʣ• શͯΦϯϓϨϛε• 2ڌͰDC• શମͰ300΄ͲʢόοΫΞοϓؚΉʣ• ਨׂ• ਫฏׂʢShardingʣ
7࢝ͷαʔόෛՙ
࢝ͷαʔόෛՙ• ຖɺϐʔΫΛܴ͑Δ• ಛʹɺݩ୴0͔࣌Β࢝·ΔΨνϟΠϕϯτʢڧڧΩϟϥʣ• 2017ݩ୴0࣌ɺαʔόෛՙʹΑΓۓٸϝϯςφϯε• ΞϓϦ௨՟༻DBʢҎ߱ɺΦʔϒDBʣͰεϩʔΫΤϦ͕ଟൃ• ଞͷϘτϧωοΫ• 2018উ͍ͪͨʂʂ• νʔϜͰ࿈ܞɾ୲ͯ͠ෛՙରࡦΛਐΊΔ8
9ΦʔϒDB
ΦʔϒDB• Φʔϒɹɹɹ ʹ Ϣʔβ༷ͷେࣄͳ͓ۚ• ԯϨίʔυ• ϐʔΫ࣌ͷUpdateΫΤϦ͕ສqpsʢΦʔϒͷফඅʹؔΘΔશͯʣ• ෛՙରࡦ• ͢ͰʹɺIndexΫΤϦ࠷దԽࡁΈ• ͢ͰʹɺਨׂࡁΈʢ1ͷཧϚγϯΛઐ༗ʣ• ͢Ͱʹɺߴ͍IOੑೳΛ࣋ͭioMemoryΛ͍ͬͯΔ10
ΦʔϒDBɹෛՙରࡦ• ෆ߹͕ڐ͞Εͳ͍• ReadΛSlaveΩϟογϡʹಀ͢͜ͱͰ͖Εආ͚͍ͨɻ• ͞ΕͨҰखɺਫฏׂʢShardingʣ͔͠ͳ͍• 2018ݩ୴0࣌ͷαʔόෛՙݟੵΓ• DAUɺAPIϦΫΤετɺϐʔΫ࣌ select/update (qps) • ׂɺϚγϯߏΛܾΊΔ11
12DBͷਫฏׂʢShardingʣͱ
DBͷਫฏׂʢShardingʣͱ13user_id orb10001 10010002 20010003 30010004 40010005 50010006 60010007 700user_id orb10001 10010005 500user_id orb10002 20010006 600user_id orb10003 30010007 700user_id orb10004 400• 4ׂͷྫ• shard_key Λݩʹׂ• modulo(༨)• auto_increment id ͕ར༻Ͱ͖ͳ͍ͷͰࣗલͰ࠾൪orbs_id_seq10007࠾൪
14ࣄྫʣΦʔϒDBͷਫฏׂʢShardingʣ
15QɽαʔϏεՔಇதɹ͔ͭɹԯϨίʔυنͷDBΛɺ҆શʹ࣮֬ʹɺαʔϏεఀࢭΛ࠷খݶʹ͑ɺਫฏׂʢShardingʣΛ͢Δʹʁ
161. ৽چDBͷDouble Write2. εΫϦϓτʹΑΔίϐʔͱݕূ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ4. چDBͷWriteΛࢭΊΔਫฏׂʢShardingʣͷखॱσʔλͷಉظ
171. ৽چDBͷDouble Write2. εΫϦϓτʹΑΔίϐʔͱݕূ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ4. چDBͷWriteΛࢭΊΔσʔλͷಉظਫฏׂʢShardingʣͷखॱ
1. ৽چDBͷDouble Write18appچDBReadWriteappappshard_1 shard_2 shard_NɾɾɾچDBͷWriteΛϑοΫͯ͠৽DBʹWrite͢Δ৽DBINSERT or UPDATE
191. ৽چDBͷDouble Write2. εΫϦϓτʹΑΔίϐʔͱݕূ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ4. چDBͷWriteΛࢭΊΔσʔλͷಉظਫฏׂʢShardingʣͷखॱ
2. εΫϦϓτʹΑΔίϐʔͱݕূ20appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBcopyscriptINSERT or UPDATEDouble Writeܧଓͨ͠··ίϐʔʢԯϨίʔυʣINSERT IGNORE INTO …※ϢχʔΫΩʔ੍ҧͷ࣌ʹԿ͠ͳ͍
21appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBcopyscriptverifyscriptVerify৽چDBͷσʔλ͕શʹҰக͍ͯ͠Δ͜ͱΛݕূ͢ΔINSERT or UPDATE2. εΫϦϓτʹΑΔίϐʔͱݕূ
22appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBcopyscriptverifyscriptVerify͜͜Ͱෆ߹͕͋ͬͨ߹࠶ίϐʔͱݕূΛ࣮ࢪINSERT or UPDATE2. εΫϦϓτʹΑΔίϐʔͱݕূ
23appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBcopyscriptverifyscriptVerify8ฒྻͰ࣮ߦ͓ͯ͠Αͦ1ϲ݄Ͱʢcopy1पɺverify3पʣINSERT or UPDATE2. εΫϦϓτʹΑΔίϐʔͱݕূʹ શʹಉظʂ
241. ৽چDBͷDouble Write2. εΫϦϓτʹΑΔίϐʔͱݕূ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ4. چDBͷWriteΛࢭΊΔσʔλͷಉظਫฏׂʢShardingʣͷखॱ
25appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBseqʢ࠾൪ʣશʹRead/Write͍ͯ͠ͳ͍ঢ়ଶʹ͢Δʢϝϯςφϯεʣ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ
26appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBscriptseqʢ࠾൪ʣinsertauto_increment idچDBͷ auto_increment id ͷΛɺ৽DBͷ࠾൪ςʔϒϧʹө͢Δ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ
27appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBseqʢ࠾൪ʣΓସ͑ɻΓ͠Λߟྀͯ͠چDBͷWriteܧଓ͢Δ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ
281. ৽چDBͷDouble Write2. εΫϦϓτʹΑΔίϐʔͱݕূ3. ৽چDBͷRead/WriteΓସ͑ʢϝϯς࣌ʣ4. چDBͷWriteΛࢭΊΔσʔλͷಉظਫฏׂʢShardingʣͷखॱ
4. چDBͷWriteΛࢭΊΔ29appچDBReadWriteappappshard_1 shard_2 shard_Nɾɾɾ৽DBseqʢ࠾൪ʣΤϥʔ0݅ɺෆ߹ͳ͘େޭ
302018ݩ୴0࣌ɹαʔόෛՙ
2018ݩ୴0࣌ɹαʔόෛՙ31• ΦʔϒDBͷεϩʔΫΤϦ0݅ → উͪʂʂʂ• ۓٸϝϯςφϯεແ͠ → উͪʂʂʂΦʔϒDB 2018ݩ୴ qps
32·ͱΊ• Ϟϯετͷαʔόෛՙ͕࢝ϐʔΫɻ• ΦʔϒDBͷਫฏׂʢShardingʣΛ࣮ࢪɻ• πʔϧ͕උ͞Ε͍ͯΔɻ• 2018ݩ୴0࣌ɺࠓ৽ͨͳϘτϧωοΫ͕ݟ͔ͭͬͨɻ• ϨεϙϯεԆ͕ൃੜ͠ଓ͚͍ͯͨɻ
33શʹউͭ·ͰɺԶͨͪͱϞϯεταʔόෛՙͷઓ͍ଓ͘…ʂʂTO BE CONTINUED
͓͠·͍
haman29
jpt_corp_official
pospome
shimabox
clown0082
ogiwarat
hazumirr
hschwentner
minma_curama
mrasu
maepon
loic425
mitchan
revolveconf
colly
brad_frost
afnizarnur
kastner
palkan
dougneiner
edds
carmenintech
mza
reverentgeek
hatefulcrawdad