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

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. εϞʔϧϏδωεΛɺ ੈքͷओ໾ʹɻ