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

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

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

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

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

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

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

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

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 ● ରԠ ○ ඞཁͳΧϥϜʹ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 ࢓༁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 ● ஫ҙ͍ͯ͠Δࣄ ○ indexΛషΔ࣌ؒ(௨ৗσϓϩΠͰ࣮ࢪ͢Δ͔ɺϝϯςͰ΍Δ͔) ○ ۀ຿ɺσʔλΛߟ͑ແବʹindexΛషΒͳ͍ ○ मਖ਼Λݕূ͢Δࡍ͸ຊ൪૬౰ͷσʔλͰݕূ͢Δ DBपΓͷύϑΥʔϚϯεվળ 

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

03 Section ·ͱΊ 

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

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