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

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

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

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

$ZCFS"HFOU *OD

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

6 CyberAgent, Inc. Ϡό͍ʂʂʂ

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

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

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

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

$ZCFS"HFOU *OD ةػճආʂ

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

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

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

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

16 CyberAgent, Inc. ஗͍ʂʂʂ

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

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

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