ソーシャルゲーム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
   */50
   4&-&$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. ةػճආʂʂʂ