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

MySQL Casual Talks vol.7

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. ةػճආʂʂʂ