Upgrade to Pro — share decks privately, control downloads, hide ads and more …

pixivにおけるMySQL運用の実際

konoiz
February 23, 2018

 pixivにおけるMySQL運用の実際

MySQL Trend Seminar 2018 @ 福岡

konoiz

February 23, 2018
Tweet

More Decks by konoiz

Other Decks in Technology

Transcript

  1. QJYJWʹ͓͚Δ.Z42-ӡ༻ͷ࣮ࡍ
    .Z42-5SFOE4FNJOBS!෱Ԭ
    ϐΫγϒגࣜձࣾΠϯϑϥ෦ࢁࠜ঵೭

    View full-size slide

  2. ࣗݾ঺հ
    w :"."/&:PTIJZVLJLPOPJ[
    w ೥ʹ৽ଔೖࣾɺΠϯϑϥνʔϜʹ഑ଐ
    w ීஈ͸౦ژΦϑΟεۈ຿

    View full-size slide

  3. BHFOEB
    w QJYJWʹ͍ͭͯ
    w QJYJWͷΠϯϑϥɺ.Z42-ߏ੒
    w QJYJWͰͷ.Z42-ͷӡ༻ʹ͍ͭͯ
    w .BTUFSঢ֨ɺ"-5&3

    View full-size slide

  4. QJYJWʹ͍ͭͯ

    View full-size slide

  5. QJYJWʹ͍ͭͯ
    w ΫϦΤΠλʔͷͨΊͷ4/4
    w ೥݄αʔϏε։࢝
    w ొ࿥Ϣʔβ਺ ສਓҎ্
    w ݄ؒͷ΂ར༻ऀ਺໿ ສਓ
    w ૯౤ߘ਺໿ ສ࡞඼

    View full-size slide

  6. QJYJWʹ͍ͭͯ

    View full-size slide

  7. QJYJWͷΠϯϑϥʹ͍ͭͯ

    View full-size slide

  8. QJYJWͷΠϯϑϥʹ͍ͭͯ

    View full-size slide

  9. QJYJWͷΠϯϑϥʹ͍ͭͯ

    View full-size slide

  10. QJYJWͷΠϯϑϥʹ͍ͭͯ
    w ݱࡏ͸σʔληϯλͰӡ༻
    w ڌ఺ϥοΫ
    w पลαʔϏεͱ߹Θͤͯαʔό໿୆
    w ໿(CQT

    View full-size slide

  11. QJYJWͷΠϯϑϥʹ͍ͭͯ
    w νʔϜϝϯόʔਓ
    w αʔϏεͷ਺ΠϯϑϥνʔϜ
    w .Z42-αʔόͷӡ༻΋ਓ

    View full-size slide

  12. QJYJWͷΠϯϑϥʹ͍ͭͯ
    'SPOU4FSWFS

    OHJOY

    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    TMBWF
    *NBHF

    4UPSBHF
    ,74

    3FEJT

    /"5(BUFXBZ
    -PH4UPSBHF
    XSJUF
    JNHVQMPBE
    SFQMJDBUJPO
    SFBE

    SFR

    SFBE SFT

    MPH
    DBDIF
    ˞/"5HBUFXBZ͸*OUFSOFUʹ௚͍݁ͯ͠ͳ͍
    αʔόʹରͯ͠ΠϯλʔωοτΞΫηεΛఏڙ
    *OUFSOFU
    $POWFSU
    $BDIF4FSWFS
    *NBHF'MVY

    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    4FBSDI4FSWFS

    4PMS

    TFBSDI
    JNQPSU
    *NBHF
    %JTQBUDIFS

    OHJOY

    View full-size slide

  13. QJYJWͷΠϯϑϥʹ͍ͭͯ
    'SPOU4FSWFS

    OHJOY

    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    TMBWF
    *NBHF

    4UPSBHF
    ,74

    3FEJT

    /"5(BUFXBZ
    -PH4UPSBHF
    XSJUF
    JNHVQMPBE
    SFQMJDBUJPO
    SFBE

    SFR

    SFBE SFT

    MPH
    DBDIF
    ˞/"5HBUFXBZ͸*OUFSOFUʹ௚͍݁ͯ͠ͳ͍
    αʔόʹରͯ͠ΠϯλʔωοτΞΫηεΛఏڙ
    *OUFSOFU
    $POWFSU
    $BDIF4FSWFS
    *NBHF'MVY

    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    4FBSDI4FSWFS

    4PMS

    TFBSDI
    JNQPSU
    *NBHF
    %JTQBUDIFS

    OHJOY

    View full-size slide

  14. QJYJWͷ.Z42-

    View full-size slide

  15. QJYJWͷ.Z42-
    w ༻్ผʹܥ౷ͷ.Z42-σʔλϕʔε
    w .Z42-ࣗମ͸͘͝ී௨ͷ.BTUFS4MBWFߏ੒
    w SFBE͸-JOVY7JSUVBM4FSWFS -74
    Ͱ෼ࢄ
    w ։ൃ΍όοΫΞοϓ༻ʹ.Z42-4BOECPYΛ࢖༻

    View full-size slide

  16. ܥ౷ͷ.Z42-
    w Ϣʔβؔ࿈σʔλɺΠϥετؔ࿈σʔλɺܾࡁؔ࿈σʔλʜͱ͍ͬ
    ͨܗͰਨ௚෼ׂ
    w ݪଇͱ֤ͯ͠ܥ౷୆͸4MBWF͕͍Δ
    w ߹ܭ.BTUFS୆ɺ4MBWF୆
    w ࠷΋େ͖ͳߏ੒ͷܥ౷͸.BTUFS୆ʹରͯ͠4MBWF୆

    View full-size slide

  17. -74
    w -JOVY7JSUVBM4FSWFS
    w ܥ౷ຖʹTMBWF༻ͷԾ૝*1Λ༻ҙ
    w BQQ͕Ծ૝*1ʹରͯ͠ࢀরϦΫΤετ
    w -74͕όϥϯγϯάͯ͠TMBWFʹ࠶ૹ
    w TMBWF͸BQQʹରͯ͠௚઀Ԡ౴ %43

    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    TMBWF
    XSJUF
    SFQMJDBUJPO
    SFBE

    SFR

    SFBE SFT

    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    View full-size slide

  18. .Z42-4BOECPY
    w αʔόͰෳ਺ͷ.Z42-Λ؆୯ʹىಈͰ͖Δ
    w ։ൃ؀ڥ΍όοΫΞοϓ༻ͷ.Z42-ʹ࢖༻
    w 4BOECPYͰ࡞ͬͨTMBWFͷEBUBσΟϨΫτϦΛίϐʔͯ͠όοΫ
    ΞοϓΛ࡞੒
    w ։ൃ؀ڥ΋ಉ༷ͷख๏Ͱຖ೔ຊ൪σʔλͱಉظ͠ͳ͓͢

    View full-size slide

  19. .Z42-ͷӡ༻ʹ͍ͭͯ

    View full-size slide

  20. લఏ
    w QJYJWͰ͸ݪଇϝϯςφϯεϞʔυʹ͠ͳ͍
    w ਺݅ͷΤϥʔ͸ڐ༰͠ɺαʔϏεແఀࢭͰϝϯςφϯε͢Δ
    w .Z42-4MBWFΛ.BTUFSʹঢ֨ͤ͞Δࡍ΋ಉ༷

    View full-size slide

  21. .BTUFSঢ֨
    w QJYJWͷ.Z42-.BTUFS͸ܥ౷͝ͱʹԾ૝*1Λ͍࣋ͬͯΔ
    w Ծ૝*1Λ4MBWFʹ෇͚ସ͑ΔࣄͰ.BTUFSঢ֨Λॠؒతʹ࣮ࢪ͢Δ
    w ྗٕӡ༻

    View full-size slide

  22. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    TMBWF
    XSJUF
    SFQMJDBUJPO
    SFBE
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    Ծ૝*1
    ࣮*1

    View full-size slide

  23. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    TMBWF
    XSJUF
    SFQMJDBUJPO
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    Ծ૝*1
    ࣮*1
    SFBE
    LJMMNZTRME

    View full-size slide

  24. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    TMBWF
    XSJUF
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    Ծ૝*1
    ࣮*1
    SFBE

    View full-size slide

  25. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    TMBWF
    XSJUF
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    Ծ૝*1
    ࣮*1
    SFBE

    View full-size slide

  26. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    /FX.BTUFS
    XSJUF
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    Ծ૝*1
    ࣮*1
    SFBE

    View full-size slide

  27. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    /FX.BTUFS
    XSJUF
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    Ծ૝*1
    ࣮*1
    SFBE

    View full-size slide

  28. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    /FX.BTUFS
    XSJUF
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    $)"/(&."45&350
    ."45&3@)045
    Ծ૝*1
    ࣮*1
    SFBE

    View full-size slide

  29. .BTUFSঢ֨
    -PBE#BMBODFS

    -74

    .Z42-
    NBTUFS
    .Z42-
    /FX.BTUFS
    XSJUF
    "QQMJDBUJPO4FSWFS
    BQBDIFQIQ

    .Z42-
    TMBWF



    SFQMJDBUJPO
    Ծ૝*1
    ࣮*1
    SFBE

    View full-size slide

  30. .BTUFSঢ֨
    w ܭըతʹ࣮ߦ͢Δ৔߹͸֓Ͷඵఔ౓Ͱ.BTUFSঢ֨Λ׬ྃͤ͞Δ
    w TMBWFಉ࢜ͰQPTJUJPO͕ҟͳΔ৔߹͸࠷΋৽͍͠΋ͷ·ͨ͸ଟ਺ܾͰ
    ଟ͍΋ͷΛਖ਼ͱͯ͠.BTUFSʹ͢Δ
    w ݩ.BTUFS͸ࢮΜͰ͍ΔͷͰ࠶ηοτΞοϓͯ͠4MBWFͱͯ͠໭͢
    w ো֐Ͱ.BTUFS͕ࢮ๢ͨ͠৔߹΋खॱ͸΄΅ಉ͡

    View full-size slide

  31. .BTUFSঢ֨ͷλΠϛϯά
    w %#ͷ࠶ىಈ͕ඞཁͳ࣌
    w .Z42-ͷόʔδϣϯΞοϓ
    w ΦϯϥΠϯมߋෆՄͳઃఆͷมߋ࣌
    w αʔόҠసɾ෺ཧαʔόͷϝϯςφϯε࣌
    w 8SJUFʹӨڹ͕͋Δέʔε
    w େن໛ͳςʔϒϧͷ"-5&3
    w .BTUFSো֐ൃੜ࣌

    View full-size slide

  32. CPPLNBSLUBCMF
    w QJYJWͷػೳͷҰͭͰ͋ΔΠϥετͷʮϒοΫϚʔΫʯΛอଘ͢Δ
    UBCMF
    w QJYJWͷ%#ͷதͰ΋ಛʹϨίʔυ਺ͷଟ͍ςʔϒϧ
    w ໿(#ͷσʔλΛΦϯϝϞϦͰॲཧ
    w ౥ࡌϝϞϦ(#ͷαʔό

    View full-size slide

  33. CPPLNBSLͷ"-5&3
    w ೥Πϯϑϥ഑ଐޙ࠷ॳͷ࢓ࣄ
    w ౰࣌ͷ#PPLNBSLUBCMF͸.Z*4".Ͱ.Z42-ɺ໿ԯϨίʔυ
    w ͜ΕΛ*OOP%#ɺ.Z42-ʹมߋ
    w ී௨ʹ"-5&35"#&-ͨ͠৔߹ɺҰिؒఔ౓͔͔Δ

    View full-size slide

  34. CPPLNBSLͷ"-5&3
    w "-5&3Λ࢖Θͳ͍
    w ฒྻ*/4&354&-&$5

    View full-size slide

  35. ฒྻ*/4&354&-&$5
    w ִ཭͞ΕͨTMBWF؀ڥΛ༻ҙ
    w "-5&3ޙͷঢ়ଶͷςʔϒϧΛ࡞੒
    w "-5&3લͷςʔϒϧΛ4&-&$5ͯ͠CVML*/4&35
    w ͨͩ͠*%ͰൣғΛ੾ͬͯɺฒྻ࣮ߦ͢Δ

    View full-size slide

  36. *%CJHJOU
    ฒྻ*/4&354&-&$5
    *%JOU

    ʜ


    ʜ


    ʜ


    ʜ


    ʜ
    HSPVQ"
    HSPVQ#
    PVQ$

    View full-size slide

  37. *%CJHJOU
    ฒྻ*/4&354&-&$5
    *%JOU

    ʜ


    ʜ


    ʜ


    ʜ


    ʜ
    HSPVQ"
    HSPVQ#
    PVQ$
    4&-&$5
    4&-&$5
    4&-&$5

    View full-size slide

  38. *%CJHJOU

    ʜ


    ʜ


    ʜ
    ฒྻ*/4&354&-&$5
    *%JOU

    ʜ


    ʜ


    ʜ


    ʜ


    ʜ
    HSPVQ"
    HSPVQ#
    PVQ$
    4&-&$5
    4&-&$5
    4&-&$5
    */4&35
    */4&35
    */4&35

    View full-size slide

  39. *%CJHJOU

    ʜ


    ʜ


    ʜ
    ฒྻ*/4&354&-&$5
    *%JOU

    ʜ


    ʜ


    ʜ


    ʜ


    ʜ
    HSPVQ"
    HSPVQ#
    PVQ$
    4&-&$5
    4&-&$5

    View full-size slide

  40. *%CJHJOU

    ʜ


    ʜ


    ʜ


    ʜ


    ʜ
    ฒྻ*/4&354&-&$5
    *%JOU

    ʜ


    ʜ


    ʜ


    ʜ


    ʜ
    HSPVQ"
    HSPVQ#
    PVQ$
    4&-&$5
    4&-&$5
    */4&35
    */4&35

    View full-size slide

  41. ฒྻ*/4&354&-&$5
    w ฒྻ࣮ߦ͢Δͱ΄΅׬શʹ*0ϘτϧωοΫʹͳΔ
    w ଎౓͸σΟεΫੑೳ࣍ୈ
    w ͜ͷ࣌͸໿೔൒Ͱ׬ྃ

    View full-size slide

  42. CPPLNBSLUBCMF

    View full-size slide

  43. w ΋͏௚͙*/5ܕͷݶքͷԯʹୡ͢Δ
    w ͜͜਺೥Ͱ૿Ճϖʔε͸Ճ଎܏޲
    w ૣٸʹ*/5Λ#*(*/5ʹ͢Δඞཁ͕͋Δ
    CPPLNBSLUBCMF

    View full-size slide

  44. w ೥લͱಉ༷ʹฒྻ*/4&54&-&$5Λ࣮ߦ
    */5UP#*(*/5

    View full-size slide

  45. */5UP#*(*/5
    TUBSU FOE

    View full-size slide

  46. w ೥લͱಉ༷ʹฒྻ*/4&54&-&$5Λ࣮ߦ
    w ೔͔Β։࢝ͯ͠ࠓே׬ྃ
    w ೔ؒ
    */5UP#*(*/5

    View full-size slide

  47. ฒྻ*/4&354&-&$5
    w "-5&3ͱൺֱͨ͠ϝϦοτ
    w ௨ৗͷ"-5&3ΑΓߴ଎ʹॲཧͰ͖ΔՄೳੑ͕͋Δ
    w ࿦ཧ࡟আͷϨίʔυΛ෺ཧ࡟আ͢Δͱ͍ͬͨࣄ͕ಉ࣌ʹͰ͖Δ

    View full-size slide

  48. ·ͱΊ
    w QJYJWͰͷ.Z42-ʹ͍ͭͯ঺հ͠·ͨ͠
    w QJYJWͷ৔߹ɺجຊతʹ͸γϯϓϧͳ.BTUFS4MBWFߏ੒Ͱɺ

    ͋Δఔ౓ͷن໛·Ͱ͸໰୊ͳ͘ӡ༻Ͱ͖͍ͯ·͢

    View full-size slide

  49. QJYJWͰ͸.Z42-αʔόͷӡ༻ɾվળ
    ͍ͨ͠ΠϯϑϥΤϯδχΞΛืू͍ͯ͠·͢ʂ
    XXXQJYJWDPKQ

    View full-size slide