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

Transcript

1. CyberAgent, Inc. Ryosuke Suto ιʔγϟϧήʔϜ%#ͷ ةػճආ MySQL Casual

   Talks vol.7 2014/12/07

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

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

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

5. $ZCFS"HFOU *OD

6. $ZCFS"HFOU *OD ΪϑτϘοΫεͱ ΧʔυҰཡͷ্ঢ཰͕Ϡό͍ •  OͷσʔλͰ৽௠୅ँ͕ܹ͍͠ •  ཤྺදࣔ΍ௐࠪͷͨΊɺҰఆظؒͷσʔλ͸ඞཁ • 

   ΠϕϯτͷϐʔΫ࣌͸਺े(#%BZ૿͑Δ͜ͱ΋ •  ࿦ཧ࡟আͨ͠··Ͱ෺࡟ͯ͠ͳ͔ͬͨΓʜ

7. 6 CyberAgent, Inc. Ϡό͍ʂʂʂ

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

   ͓͢ •  σʔλ࡟আόονΛͭͬͯ͘ఆظతʹ࡟আ

9. $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;

10. $ZCFS"HFOU *OD %&-&5&ͯ͠΋ ۭ͖༰ྔ͕૿͑ͳ͍ •  IUUQOJQQPOEBOKJCMPHTQPUKQ JOOPECIUNM •  %&-&5&ͯ͠΋࡟আ͞ΕͨϚʔΫ͕͞ΕΔ͚ͩͰ༰

    ྔ͸ݮΒͳ͍ •  015*.*;&͢ΔͨΊʹQUPTDΛ͔ͭ͏

11. $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ʜεϨου਺͕͜ΕΛ௒͑ΔͱҰ࣌ఀࢭ

12. $ZCFS"HFOU *OD ةػճආʂ

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

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

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

    •  %&-&5&͢ΔΑΓ΋஗͍

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

    %&-&5&TFD

17. 16 CyberAgent, Inc. ஗͍ʂʂʂ

18. $ZCFS"HFOU *OD όάͩͬͨ •  IUUQXXXQFSDPOBDPNCMPH QFSGPSNBODFQSPCMFNXJUIJOOPECBOEESPQ UBCMF •  536/$"5&は%301$3&"5&

    •  lJOOPEC@pMF@QFS@UBCMFzͰ%301࣌ͷόά •  όοϑΝϓʔϧͷར༻ྔ͕େ͖͍΄Ͳ஗͘ͳΔ

19. $ZCFS"HFOU *OD %&-&5&ʹͯ͠஗Ԇճආ •  σʔλྔ͕ଟ͍ͱ%&-&5&Ͱ΋஗͘ͳΔ •  εϨʔϒˠϚελʔͷॱͰ࣮ߦͯ͠ճආ Slave> DELETE

    FROM masterdata; Master> DELETE FROM masterdata; Master> INSET INTO masterdata (hoge,fuga,…) VALUES (xxx,yyy,…); ʢུʣ

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