ソーシャルゲームDBの危機回避

by Ryosuke Suto

Slide 1

Slide 1 text

CyberAgent, Inc. Ryosuke Suto ιʔγϟϧήʔϜ%#ͷ ةػճආ MySQL Casual Talks vol.7 2014/12/07

Slide 2

Slide 2 text

$ZCFS"HFOU *OD ΞδΣϯμ   ࣗݾ঺հ   σΟεΫ༰ྔͷةػΛճආ͢Δ   ϨϓϦ஗ԆͷةػΛճආ͢Δ

Slide 3

Slide 3 text

ਢ౻ྋհʢ͢ͱ͏Γΐ͏͚͢ʣ @strsk גࣜձࣾαΠόʔΤʔδΣϯτ  ΠϯϑϥˍίΞςΫຊ෦ ISUCON4 ༧બഊୀ $ZCFS"HFOU *OD

Slide 4

Slide 4 text

$ZCFS"HFOU *OD σΟεΫ༰ྔͷةػΛճආ͢Δ

Slide 5

Slide 5 text

$ZCFS"HFOU *OD

Slide 6

Slide 6 text

$ZCFS"HFOU *OD ΪϑτϘοΫεͱ ΧʔυҰཡͷ্ঢ཰͕Ϡό͍ •  OͷσʔλͰ৽௠୅ँ͕ܹ͍͠ •  ཤྺදࣔ΍ௐࠪͷͨΊɺҰఆظؒͷσʔλ͸ඞཁ •  ΠϕϯτͷϐʔΫ࣌͸਺े(#%BZ૿͑Δ͜ͱ΋ •  ࿦ཧ࡟আͨ͠··Ͱ෺࡟ͯ͠ͳ͔ͬͨΓʜ

Slide 7

Slide 7 text

6 CyberAgent, Inc. Ϡό͍ʂʂʂ

Slide 8

Slide 8 text

$ZCFS"HFOU *OD ΍ͬͨ͜ͱ •  ࿦ཧ࡟আ͞Ε͍ͯΔɺҰఆظؒܦͬͨσʔλΛ࡟আ •  ࡟আྔ͕ଟ͗͢ΔͷͰϝϯςϯε࣮ࢪ •  */4&35*/504&-&$5ͱ3&/".&Ͱ࡞Γͳ ͓͢ •  σʔλ࡟আόονΛͭͬͯ͘ఆظతʹ࡟আ

Slide 9

Slide 9 text

$ZCFS"HFOU *OD CREATE TABLE giftbox_new LIKE giftbox; INSERT INTO giftbox_new SELECT giftbox WHERE (status = 1 AND updated > ‘2014-11-07 00:00:00’) OR (status = 0); RENAME giftbox giftbox_bk, giftbox_new giftbox; DROP TABLE giftbox_bk;

Slide 10

Slide 10 text

$ZCFS"HFOU *OD %&-&5&ͯ͠΋ ۭ͖༰ྔ͕૿͑ͳ͍ •  IUUQOJQQPOEBOKJCMPHTQPUKQ JOOPECIUNM •  %&-&5&ͯ͠΋࡟আ͞ΕͨϚʔΫ͕͞ΕΔ͚ͩͰ༰ ྔ͸ݮΒͳ͍ •  015*.*;&͢ΔͨΊʹQUPTDΛ͔ͭ͏

Slide 11

Slide 11 text

$ZCFS"HFOU *OD $ pt-online-schema-change –-execute --alter=“engine=InnoDB” h=localhost,D=db_name,t=giftbox,u=root Α͔ͭ͘͏Φϓγϣϯ TFUWBSTlTRM@MPH@CJOzʜϨϓϦͤͨ͘͞ͳ͍࣌ DSJUJDBMMPBEʜεϨου਺͕͜ΕΛ௒͑Δͱதஅ NBYMPBEʜεϨου਺͕͜ΕΛ௒͑ΔͱҰ࣌ఀࢭ

Slide 12

Slide 12 text

$ZCFS"HFOU *OD ةػճආʂ

Slide 13

Slide 13 text

12 CyberAgent, Inc. ةػճආʂʂʂ

Slide 14

Slide 14 text

$ZCFS"HFOU *OD ϨϓϦ஗ԆͷةػΛճආ͢Δ

Slide 15

Slide 15 text

$ZCFS"HFOU *OD 536/$"5&ͨ͠Β஗Ԇ •  ήʔϜͷϚελσʔλ͕ຖճσʔλΛΫϦΞͯ͠ ಡΈࠐΉελΠϧ •  ςʔϒϧΛ536/$"5&ͯ͠Δ෦෼Ͱ஗Ԇ͕ ൃੜ͍ͯ͠Δ •  %&-&5&͢ΔΑΓ΋஗͍

Slide 16

Slide 16 text

$ZCFS"HFOU *OD খ͍͞ςʔϒϧͰ΋஗͍ •  ༰ྔ.#ɺ ߦ •  536/$"5&TFD •  %&-&5&TFD

Slide 17

Slide 17 text

16 CyberAgent, Inc. ஗͍ʂʂʂ

Slide 18

Slide 18 text

$ZCFS"HFOU *OD όάͩͬͨ •  IUUQXXXQFSDPOBDPNCMPH QFSGPSNBODFQSPCMFNXJUIJOOPECBOEESPQ UBCMF •  536/$"5&は%301$3&"5& •  lJOOPEC@pMF@QFS@UBCMFzͰ%301࣌ͷόά •  όοϑΝϓʔϧͷར༻ྔ͕େ͖͍΄Ͳ஗͘ͳΔ

Slide 19

Slide 19 text

$ZCFS"HFOU *OD %&-&5&ʹͯ͠஗Ԇճආ •  σʔλྔ͕ଟ͍ͱ%&-&5&Ͱ΋஗͘ͳΔ •  εϨʔϒˠϚελʔͷॱͰ࣮ߦͯ͠ճආ Slave> DELETE FROM masterdata; Master> DELETE FROM masterdata; Master> INSET INTO masterdata (hoge,fuga,…) VALUES (xxx,yyy,…); ʢུʣ

Slide 20

Slide 20 text

19 CyberAgent, Inc. ةػճආʂʂʂ