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

データの構造から再検討するパフォーマンスチューニング

freee
January 21, 2020

 データの構造から再検討するパフォーマンスチューニング

freee

January 21, 2020
Tweet

More Decks by freee

Other Decks in Technology

Transcript

  1. freee גࣜձࣾ
    σʔλͷߏ଄͔Β࠶ݕ౼͢ΔύϑΥʔϚϯενϡʔχϯά
    2019.01.21

    View full-size slide

  2. takuya kubo
    @tkuboma
    ॴଐνʔϜʢࡶձܭ ਓؒνʔϜʣ
    2
    ܦྺ
    ● Sierͱͯ͠ෳ਺اۀΛసʑ
    ○ ओʹ௨৴ܥͷόοΫΤϯυ
    ● ࣄۀձࣾͰհޢ੥ٻιϑτͷ։ൃʹैࣄ
    ○ ৽نɾอक։ൃɺࢢ৔ௐࠪɺίʔϧηϯλʔͳͲ
    ● 2017೥7݄ freee ʹ join
    ○ ձܭνʔϜॴଐ
    ■ ৽نɾอक։ൃ
    ○ νʔϜ஍ࠈ ͔Β νʔϜਓؒ ΁
    ■ ਃ੥υϝΠϯͷϚΠΫϩαʔϏεԽ
    ■ ܾࢉυϝΠϯपลͷอक։ൃ
    झຯ
    ● ͓ञ
    ● ࢠڙ

    View full-size slide

  3. ɹ02ɹDBपΓͷύϑΥʔϚϯεվળ
    ɹ01ɹfreeeͷ؂ࢹपΓʹ͍ͭͯ؆୯ʹ
    Agenda
    03ɹ·ͱΊ

    View full-size slide

  4. 01
    Section
    freeeͷ؂ࢹपΓʹ͍ͭͯ؆୯ʹ

    View full-size slide

  5. 5
    ● Mackerel
    ○ ֎ܗ؂ࢹɾαʔόʔϨϕϧͰ؂ࢹ
    ○ Slackʹ௨஌
    ● NewRelic
    ○ ΞϓϦέʔγϣϯͷੑೳ؂ࢹ
    ○ ϘτϧωοΫͱͳ͍ͬͯΔ End-point ֬ೝ
    ● Monyog
    ○ queryϨϕϧͰ؂ࢹ
    ○ ϝʔϧ௨஌
    ● Datadog (࠷ۙಋೖ࢝͠Ίͨ)
    ○ Kubernetes؀ڥͷ؂ࢹʹɺΑΓద੾ͳSaaSͱͯ͠ಋೖ
    freeeͷ؂ࢹपΓʹ͍ͭͯ؆୯ʹ

    View full-size slide

  6. 02
    Section
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  7. 7
    DBपΓͷύϑΥʔϚϯεվળ
    1. indexͰνϡʔχϯά
    2. ΫΤϦͷมߋͰνϡʔχϯά
    3. σʔλͷ࣋ͪํͷݟ௚͠
    4. ςʔϒϧߏ੒ͷݟ௚͠
    5. ػೳͷݟ௚͠

    View full-size slide

  8. 8
    DBपΓͷύϑΥʔϚϯεվળ
    1. indexͰνϡʔχϯά
    2. ΫΤϦͷมߋͰνϡʔχϯά
    3. σʔλͷ࣋ͪํͷݟ௚͠
    4. ςʔϒϧߏ੒ͷݟ௚͠
    5. ػೳͷݟ௚͠

    View full-size slide

  9. 9
    ● ಺༰
    ○ ୯७ʹindexΛషΔඞཁ͕͋Δ
    ○ indexΛར༻Ͱ͖͍ͯͳ͍
    DBपΓͷύϑΥʔϚϯεվળ
    explain
    SELECT * FROM औҾઌ
    WHERE ໊લ = 'גࣜձࣾA'
    ************* 1. row *************
    id: 1
    select_type: SIMPLE
    table: औҾઌ
    partitions: NULL
    type: ALL
    explain
    SELECT औҾ.*
    FROM औҾ
    INNER JOIN ࢓༁ ON ࢓༁.id = ࢓༁.औҾ_id
    WHERE औҾ.ࣄۀॴ_id = 111
    AND ࢓༁.࢓༁೔ >= '2019-10-10';
    *************** 1. row ****************
    key: index=औҾ_ࣄۀॴ_id

    View full-size slide

  10. 10
    ● ରԠ
    ○ ඞཁͳΧϥϜʹindexΛషΔ
    ○ ࣮ߦܭըΛݟͯద੾ͳindexΛར༻Ͱ͖͍ͯͳ͍ΫΤϦͷमਖ਼
    SELECT औҾ.*
    FROM औҾ
    INNER JOIN ࢓༁ ON ࢓༁.id = ࢓༁.औҾ_id
    WHERE औҾ.ࣄۀॴ_id = 111
    AND ࢓༁.ࣄۀॴ_id = 111 AND ࢓༁.࢓༁೔ >= '2019-10-10';
    mysql> SHOW INDEX FROM औҾ;
    +-------+-----------------------------+------------------+---------------------+----------------+
    | Table | Key_name | Seq_in_index | Column_name | Cardinality |
    +-------+-----------------------------+------------------+---------------------+----------------+
    | ࢓༁ | PRIMARY | 1 | id | 936 |
    | ࢓༁ | ࣄۀॴ_ID | 1 | ࣄۀॴ_id | 29 |
    +-------+-----------------------------+------------------+---------------------+----------------+
    mysql> SHOW INDEX FROM ࢓༁;
    +-------+-----------------------------+------------------+---------------------+----------------+
    | Table | Key_name | Seq_in_index | Column_name | Cardinality |
    +-------+-----------------------------+------------------+---------------------+----------------+
    | ࢓༁ | PRIMARY | 1 | id | 2627 |
    | ࢓༁ | ࣄۀॴ_ID_࢓༁೔ | 1 | ࣄۀॴ_id | 29 |
    | ࢓༁ | ࣄۀॴ_ID_࢓༁೔ | 2 | ࢓༁೔ | 467 |
    +-------+-----------------------------+------------------+---------------------+----------------+
    SELECT औҾ.*
    FROM औҾ
    INNER JOIN ࢓༁ ON ࢓༁.id = ࢓༁.औҾ_id
    WHERE औҾ.ࣄۀॴ_id = 111
    AND ࢓༁.࢓༁೔ >= '2019-10-10';
    ○ Cardinality͕ߴ͍indexΛར༻Ͱ͖ΔΑ͏ʹ͢Δ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  11. 11
    ࢓༁model
    scope :࢓༁೔Ҏ্, ->(೔෇) do
    where('࢓༁೔ΧϥϜ >= ?', ೔෇)
    end
    ● ͳͥ࿙ΕΔʁ
    ○ ActiveRecord ͷ scope Λར༻͍ͯ͠Δ
    ○ ࢓༁model͸ࣄۀॴmodelͱͷؔ࿈͕͋Γɺʮࣄۀॴ.࢓༁.scopeʯͱݺͼग़͢ҝ index͸࢖ΘΕΔ
    ○ joinͷ࣌͸ࣄۀॴ৘ใ͕ೖΒͳ͍ҝɺվળલͷΫΤϦͱͳΔ
    > ࣄۀॴ.࢓༁.࢓༁೔Ҏ্('2019-10-10')
    SELECT * FROM ࢓༁
    WHERE ࢓༁.ࣄۀॴ_id = '111' AND ࢓༁.࢓༁೔ >= '2020-01-01'
    > ࣄۀॴ.औҾ.joins(࢓༁).merge(࢓༁.࢓༁೔Ҏ্('2019-10-10'))
    SELECT * FROM औҾ
    ɹINNER JOIN ࢓༁ ON ࢓༁.औҾ_id = औҾ.id
    WHERE औҾ.ࣄۀॴ_id = '111' AND ࢓༁.࢓༁೔ >= '2020-01-01'
    ࢓༁model
    scope :࢓༁೔Ҏ্, ->(ࣄۀॴ_id, ೔෇) do
    where('ࣄۀॴ_id = ? AND ࢓༁೔ΧϥϜ >= ?', ࣄۀॴ_id, ೔෇)
    end
    > ࣄۀॴ.औҾ.joins(࢓༁).merge(࢓༁.࢓༁೔Ҏ্(111, '2019-10-10'))
    SELECT * FROM औҾ
    ɹINNER JOIN ࢓༁ ON ࢓༁.औҾ_id = औҾ.id
    WHERE
    ɹऔҾ.ࣄۀॴ_id = '111' AND
    ࢓༁.ࣄۀॴ_id = '111' AND ࢓༁.࢓༁೔ >= '2020-01-01'
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  12. 12
    ● ஫ҙ͍ͯ͠Δࣄ
    ○ indexΛషΔ࣌ؒ(௨ৗσϓϩΠͰ࣮ࢪ͢Δ͔ɺϝϯςͰ΍Δ͔)
    ○ ۀ຿ɺσʔλΛߟ͑ແବʹindexΛషΒͳ͍
    ○ मਖ਼Λݕূ͢Δࡍ͸ຊ൪૬౰ͷσʔλͰݕূ͢Δ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  13. 13
    DBपΓͷύϑΥʔϚϯεվળ
    1. indexͰνϡʔχϯά
    2. ΫΤϦͷมߋͰνϡʔχϯά
    3. σʔλͷ࣋ͪํͷݟ௚͠
    4. ςʔϒϧߏ੒ͷݟ௚͠
    5. ػೳͷݟ௚͠

    View full-size slide

  14. 14
    ● ಺༰
    ○ ର৅σʔλͷrow͕େ͖͗͢Δ
    mysql> SELECT DISTINCT ࢓༁.औҾઌ_id
    FROM ࢓༁
    WHERE ࢓༁.Ϣʔβʔ_id = 111;
    -----------
    4001 rows in set (12.37 sec)
    explain
    SELECT DISTINCT ࢓༁.औҾઌ_id
    FROM ࢓༁
    WHERE ࢓༁.ࣄۀॴ_id = 111;
    ***** 1. row *****
    rows: 20000000
    ○ ࢓༁͸Ϣʔβ಺Ͱ࠷େڃͷϨίʔυΛތΔςʔϒϧ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  15. 15
    ● ରԠ
    ○ row͕ߜΒΕΔςʔϒϧΛΫΤϦʹՃ͑Δ
    mysql> SELECT DISTINCT ࢓༁.औҾઌ_id
    FROM ࢓༁
    WHERE ࢓༁.ࣄۀॴ_id = 111;
    -----------
    4001 rows in set (12.37 sec)
    mysql> SELECT DISTINCT औҾઌ.id
    FROM औҾઌ
    INNER JOIN ࢓༁ ON partners.id = ࢓༁.औҾઌ_id
    WHERE औҾઌ.ࣄۀॴ_id = 111;
    -----------
    4000 rows in set (0.10 sec)
    ***** 1. row *****
    rows: 20000000
    ***** 1. row *****
    rows: 3000
    ***** 2. row *****
    rows: 40
    ○ ΑΓগͳ͍औҾઌͷςʔϒϧΛ৚݅ʹೖΕΔ͜ͱʹΑΓrow͕΁Γܶతʹૣ͘ͳΔ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  16. 16
    ● ஫ҙ͍ͯ͠Δࣄ
    ○ ςʔϒϧͷσʔλͷ૿Ճͷ֯౓
    ○ ΫΤϦͷ݁Ռ͕มΘΒͳ͍͔൱͔
    ○ ΞϓϦέʔγϣϯଆʹॲཧΛҕ೚͢ΔܗʹͳΔҝɺΞϓϦέʔγϣϯଆ΁ͷෛՙ૿
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  17. 17
    DBपΓͷύϑΥʔϚϯεվળ
    1. indexͰνϡʔχϯά
    2. ΫΤϦͷมߋͰνϡʔχϯά
    3. σʔλͷ࣋ͪํͷݟ௚͠
    4. ςʔϒϧߏ੒ͷݟ௚͠
    5. ػೳͷݟ௚͠

    View full-size slide

  18. 18
    ● ಺༰
    ○ idͰ͸ͳ͘StringͰjoin͍ͯ͠Δ
    ○ Ϛελʔσʔλͱڞଘ͢Δ৘ใ͕ଘࡏ͢Δ
    SELECT
    צఆՊ໨.*
    FROM
    צఆՊ໨
    WHERE
    צఆՊ໨.Ϣʔβʔ_id = γεςϜϢʔβʔ.id
    UNION
    SELECT
    צఆՊ໨.*
    FROM
    צఆՊ໨
    WHERE
    צఆՊ໨.Ϣʔβʔ_id = Ϣʔβʔ.id;
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  19. 19
    ● ରԠ
    ○ ؔ࿈idͷΧϥϜΛ௥Ճ͠idͰjoin͢Δ
    ○ ϚελʔσʔλΛϢʔβଆʹίϐʔ͠read͸ϢʔβʔσʔλͷΈʹ͢Δ
    SELECT
    צఆՊ໨.*
    FROM
    צఆՊ໨
    WHERE
    צఆՊ໨.Ϣʔβʔ_id = Ϣʔβʔ.id;
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  20. 20
    ● ஫ҙ͍ͯ͠Δࣄ
    ○ ϚελʔσʔλΛίϐʔ͢ΔλΠϛϯά
    ○ ͋Δఔ౓αʔϏε͕େ͖͘ͳ͔ͬͯΒͩͱमਖ਼ίετ͕ߴ͍ҝɺઃܭஈ֊Ͱݕ౼͍ͨ͠
    ○ සൟʹϚελʔσʔλ͕มߋ͞ΕΔςʔϒϧͷ৔߹ɺϝϯςίετ͕ߴ͘ͳΔҝผͷରॲΛݕ౼
    ■ ࠷ॳ͔ΒΫΤϦΛ෼͚ΔͳͲ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  21. 21
    DBपΓͷύϑΥʔϚϯεվળ
    1. indexͰνϡʔχϯά
    2. ΫΤϦͷมߋͰνϡʔχϯά
    3. σʔλͷ࣋ͪํͷݟ௚͠
    4. ςʔϒϧߏ੒ͷݟ௚͠
    5. ػೳͷݟ௚͠

    View full-size slide

  22. 22
    ● ಺༰
    ○ େྔσʔλΛ౎౓ूܭͯ͠ද͍ࣔͯ͠Δ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  23. 23
    ● ରԠ
    ○ ूܭςʔϒϧΛ࡞੒ʢѹॖ཰ͷߴ͍ςʔϒϧΛ࡞੒ʣ
    ○ ࢓༁σʔλొ࿥ɺߋ৽࣌ʹ1೥෼ͷσʔλΛूܭͯ͠อଘ͢Δ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  24. 24
    ● ৽ͨͳ໰୊΋ൃੜ
    ○ ొ࿥࣌ͷύϑΥʔϚϯε
    ○ ָ؍ϩοΫΤϥʔ
    ○ ΪϟοϓϩοΫʹΑΔϩοΫ଴ͪ
    ○ ϑΝϯτϜϦʔυʹΑΔσʔλෆ੔߹
    ● ରࡦ
    ○ ूܭσʔλͷอଘΛผτϥϯβΫγϣϯͰඇಉظʹ
    ○ ࣄۀॴɺूܭ୯ҐͱͳΔଐੑΛΩʔͱͯ͠ॲཧΛγϟʔσΟϯάͯ͠ɺಉ͡΍ͭ͸௚ྻʹ·ͱΊ࣮ͯߦ͢Δ
    ࢓༁σʔλ
    ͷอଘ
    ूܭσʔλ

    ͷอଘ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  25. KinesisΛར༻ͨ͠ूܭςʔϒϧͷߋ৽
    Kinesis
    Stream
    RDS
    ूܭ
    Server
    ձܭfreee
    Server
    1. ݩσʔλUpdate
    2. ूܭϦΫΤετQueuing
    3. Polling
    4. Data Fetch
    5. ूܭσʔλUpdate
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  26. 26
    ● ஫ҙ͍ͯ͠Δࣄ
    ○ ूܭςʔϒϧͷσʔλͷ੾Γํ
    ○ ొ࿥࣌ͷίετ૿
    ○ ूܭσʔλදࣔͷϦΞϧλΠϜੑΛଛͳ͏ͨΊϢʔβʔ΁ͷࠂ஌ͳͲ
    ○ վળʹ͸͔ͳΓͷ͕͔͔࣌ؒΔ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  27. 27
    DBपΓͷύϑΥʔϚϯεվળ
    1. indexͰνϡʔχϯά
    2. ΫΤϦͷมߋͰνϡʔχϯά
    3. ςʔϒϧߏ੒ͷݟ௚͠
    4. σʔλͷ࣋ͪํͷݟ௚͠
    5. ػೳͷݟ௚͠

    View full-size slide

  28. 28
    ● ಺༰
    ○ ඞཁҎ্ʹ࣮ߦ͞Ε͍ͯΔՄೳੑΛٙ͏
    ■ Homeը໘ΞΫηεͰݺ͹ΕΔ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  29. 29
    ● ରԠ
    ○ ϢʔβʔΞΫγϣϯϕʔεͰͷऔಘʹมߋ
    ○ ඇಉظԽ
    ● ஫ҙ͍ͯ͠Δࣄ
    ○ ϏδωεαΠυ(Ϣʔβʔ)ͱͷίϛϡχέʔγϣϯ
    ○ ϢʔβʔϝϦοτͱӡ༻ίετʹ͍ͭͯ
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  30. 30
    ● slave΁
    ○ Өڹͳ͍selectܥ͸slave΁
    ○ write͕ଟ͍αʔϏε͸ replication latency ʹ஫ҙ
    ● N + 1ͷղফ
    ○ bullet ͷಋೖ
    ● ݕࡧ෦෼ͷ Elasticsearch Խ
    ○ େྔσʔλͷ৚݅ݕࡧ࣌
    ○ ᐆດݕࡧ࣌
    ● γϟʔσΟϯάͷݕ౼
    DBपΓͷύϑΥʔϚϯεվળ

    View full-size slide

  31. 03
    Section
    ·ͱΊ

    View full-size slide

  32. 32
    ● νϡʔχϯά͸ద࣮ٓࢪ͠·͠ΐ͏
    ● Ϛελʔσʔλͱͷڞଘ͸ՄೳͳݶΓආ͚·͠ΐ͏
    ● ߏ੒ͷݟ௚͠͸ૣΊʹ͠·͠ΐ͏
    ·ͱΊ

    View full-size slide

  33. εϞʔϧϏδωεΛɺ
    ੈքͷओ໾ʹɻ

    View full-size slide