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

Transcript

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

2. takuya kubo @tkuboma ॴଐνʔϜʢࡶձܭ ਓؒνʔϜʣ 2 ܦྺ • Sierͱͯ͠ෳ਺اۀΛసʑ ◦

   ओʹ௨৴ܥͷόοΫΤϯυ • ࣄۀձࣾͰհޢ੥ٻιϑτͷ։ൃʹैࣄ ◦ ৽نɾอक։ൃɺࢢ৔ௐࠪɺίʔϧηϯλʔͳͲ • 2017೥7݄ freee ʹ join ◦ ձܭνʔϜॴଐ ▪ ৽نɾอक։ൃ ◦ νʔϜ஍ࠈ ͔Β νʔϜਓؒ ΁ ▪ ਃ੥υϝΠϯͷϚΠΫϩαʔϏεԽ ▪ ܾࢉυϝΠϯपลͷอक։ൃ झຯ • ͓ञ • ࢠڙ

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

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

5. 5 • Mackerel ◦ ֎ܗ؂ࢹɾαʔόʔϨϕϧͰ؂ࢹ ◦ Slackʹ௨஌ • NewRelic ◦

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

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

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

   5. ػೳͷݟ௚͠

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

   5. ػೳͷݟ௚͠

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

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पΓͷύϑΥʔϚϯεվળ

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पΓͷύϑΥʔϚϯεվળ

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

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

    5. ػೳͷݟ௚͠

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पΓͷύϑΥʔϚϯεվળ

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पΓͷύϑΥʔϚϯεվળ

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

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

    5. ػೳͷݟ௚͠

18. 18 • ಺༰ ◦ idͰ͸ͳ͘StringͰjoin͍ͯ͠Δ ◦ Ϛελʔσʔλͱڞଘ͢Δ৘ใ͕ଘࡏ͢Δ SELECT צఆՊ໨.* FROM

    צఆՊ໨ WHERE צఆՊ໨.Ϣʔβʔ_id = γεςϜϢʔβʔ.id UNION SELECT צఆՊ໨.* FROM צఆՊ໨ WHERE צఆՊ໨.Ϣʔβʔ_id = Ϣʔβʔ.id; DBपΓͷύϑΥʔϚϯεվળ

19. 19 • ରԠ ◦ ؔ࿈idͷΧϥϜΛ௥Ճ͠idͰjoin͢Δ ◦ ϚελʔσʔλΛϢʔβଆʹίϐʔ͠read͸ϢʔβʔσʔλͷΈʹ͢Δ SELECT צఆՊ໨.* FROM

    צఆՊ໨ WHERE צఆՊ໨.Ϣʔβʔ_id = Ϣʔβʔ.id; DBपΓͷύϑΥʔϚϯεվળ

20. 20 • ஫ҙ͍ͯ͠Δࣄ ◦ ϚελʔσʔλΛίϐʔ͢ΔλΠϛϯά ◦ ͋Δఔ౓αʔϏε͕େ͖͘ͳ͔ͬͯΒͩͱमਖ਼ίετ͕ߴ͍ҝɺઃܭஈ֊Ͱݕ౼͍ͨ͠ ◦ සൟʹϚελʔσʔλ͕มߋ͞ΕΔςʔϒϧͷ৔߹ɺϝϯςίετ͕ߴ͘ͳΔҝผͷରॲΛݕ౼ ▪

    ࠷ॳ͔ΒΫΤϦΛ෼͚ΔͳͲ DBपΓͷύϑΥʔϚϯεվળ

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

    5. ػೳͷݟ௚͠

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

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

24. 24 • ৽ͨͳ໰୊΋ൃੜ ◦ ొ࿥࣌ͷύϑΥʔϚϯε ◦ ָ؍ϩοΫΤϥʔ ◦ ΪϟοϓϩοΫʹΑΔϩοΫ଴ͪ ◦

    ϑΝϯτϜϦʔυʹΑΔσʔλෆ੔߹ • ରࡦ ◦ ूܭσʔλͷอଘΛผτϥϯβΫγϣϯͰඇಉظʹ ◦ ࣄۀॴɺूܭ୯ҐͱͳΔଐੑΛΩʔͱͯ͠ॲཧΛγϟʔσΟϯάͯ͠ɺಉ͡΍ͭ͸௚ྻʹ·ͱΊ࣮ͯߦ͢Δ ࢓༁σʔλ ͷอଘ ूܭσʔλ
 ͷอଘ DBपΓͷύϑΥʔϚϯεվળ

25. KinesisΛར༻ͨ͠ूܭςʔϒϧͷߋ৽ Kinesis Stream RDS ूܭ Server ձܭfreee Server 1. ݩσʔλUpdate

    2. ूܭϦΫΤετQueuing 3. Polling 4. Data Fetch 5. ूܭσʔλUpdate DBपΓͷύϑΥʔϚϯεվળ

26. 26 • ஫ҙ͍ͯ͠Δࣄ ◦ ूܭςʔϒϧͷσʔλͷ੾Γํ ◦ ొ࿥࣌ͷίετ૿ ◦ ूܭσʔλදࣔͷϦΞϧλΠϜੑΛଛͳ͏ͨΊϢʔβʔ΁ͷࠂ஌ͳͲ ◦

    վળʹ͸͔ͳΓͷ͕͔͔࣌ؒΔ DBपΓͷύϑΥʔϚϯεվળ

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

    5. ػೳͷݟ௚͠

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

29. 29 • ରԠ ◦ ϢʔβʔΞΫγϣϯϕʔεͰͷऔಘʹมߋ ◦ ඇಉظԽ • ஫ҙ͍ͯ͠Δࣄ ◦

    ϏδωεαΠυ(Ϣʔβʔ)ͱͷίϛϡχέʔγϣϯ ◦ ϢʔβʔϝϦοτͱӡ༻ίετʹ͍ͭͯ DBपΓͷύϑΥʔϚϯεվળ

30. 30 • slave΁ ◦ Өڹͳ͍selectܥ͸slave΁ ◦ write͕ଟ͍αʔϏε͸ replication latency ʹ஫ҙ

    • N + 1ͷղফ ◦ bullet ͷಋೖ • ݕࡧ෦෼ͷ Elasticsearch Խ ◦ େྔσʔλͷ৚݅ݕࡧ࣌ ◦ ᐆດݕࡧ࣌ • γϟʔσΟϯάͷݕ౼ DBपΓͷύϑΥʔϚϯεվળ

31. 03 Section ·ͱΊ

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

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