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

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

    ΞϓϦέʔγϣϯͷੑೳ؂ࢹ ◦ ϘτϧωοΫͱͳ͍ͬͯΔ End-point ֬ೝ • Monyog ◦ queryϨϕϧͰ؂ࢹ ◦ ϝʔϧ௨஌ • Datadog (࠷ۙಋೖ࢝͠Ίͨ) ◦ Kubernetes؀ڥͷ؂ࢹʹɺΑΓద੾ͳSaaSͱͯ͠ಋೖ freeeͷ؂ࢹपΓʹ͍ͭͯ؆୯ʹ
  3. 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
  4. 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पΓͷύϑΥʔϚϯεվળ
  5. 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पΓͷύϑΥʔϚϯεվળ
  6. 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पΓͷύϑΥʔϚϯεվળ
  7. 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पΓͷύϑΥʔϚϯεվળ
  8. 18 • ಺༰ ◦ idͰ͸ͳ͘StringͰjoin͍ͯ͠Δ ◦ Ϛελʔσʔλͱڞଘ͢Δ৘ใ͕ଘࡏ͢Δ SELECT צఆՊ໨.* FROM

    צఆՊ໨ WHERE צఆՊ໨.Ϣʔβʔ_id = γεςϜϢʔβʔ.id UNION SELECT צఆՊ໨.* FROM צఆՊ໨ WHERE צఆՊ໨.Ϣʔβʔ_id = Ϣʔβʔ.id; DBपΓͷύϑΥʔϚϯεվળ
  9. 24 • ৽ͨͳ໰୊΋ൃੜ ◦ ొ࿥࣌ͷύϑΥʔϚϯε ◦ ָ؍ϩοΫΤϥʔ ◦ ΪϟοϓϩοΫʹΑΔϩοΫ଴ͪ ◦

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

    2. ूܭϦΫΤετQueuing 3. Polling 4. Data Fetch 5. ूܭσʔλUpdate DBपΓͷύϑΥʔϚϯεվળ
  11. 29 • ରԠ ◦ ϢʔβʔΞΫγϣϯϕʔεͰͷऔಘʹมߋ ◦ ඇಉظԽ • ஫ҙ͍ͯ͠Δࣄ ◦

    ϏδωεαΠυ(Ϣʔβʔ)ͱͷίϛϡχέʔγϣϯ ◦ ϢʔβʔϝϦοτͱӡ༻ίετʹ͍ͭͯ DBपΓͷύϑΥʔϚϯεվળ
  12. 30 • slave΁ ◦ Өڹͳ͍selectܥ͸slave΁ ◦ write͕ଟ͍αʔϏε͸ replication latency ʹ஫ҙ

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