Tweet
Tweet

Slide 1

Slide 1 text

Splitting DB on "Rails" How to split DB efficiently with Ruby "magic" RejectKaigi 2018 12.May.2018 - @izumin5210

Slide 2

Slide 2 text

izumin5210 Engineer at Wantedly, Inc. Wantedly People ‣ Web Application Engineer - Profile Data Strategy Group ‣ Interests in developer productivity on microservices Gopher, Rubyist, JavaScripter and Androider

Slide 3

Slide 3 text

Background ͳͥ%#Λ෼ׂ͢Δඞཁ͕͔͋ͬͨ Splitting DB on Rails ͍͔ʹͯ͠%#෼ׂΛਐΊ͍͔ͯ͘ Agenda

Slide 4

Slide 4 text

©2018 Wantedly, Inc. Background WHY did we need to split the database?

Slide 5

Slide 5 text

©2018 Wantedly, Inc. Background History of our apps 2012 ~ 2016.01 ~ 2016.11 ~

Slide 6

Slide 6 text

©2018 Wantedly, Inc. Background History of our apps 2012 ~ 2016.01 ~ 2016.11 ~ Monolithic Rails app

Slide 7

Slide 7 text

©2018 Wantedly, Inc. Background History of our apps 2012 ~ 2016.01 ~ 2016.11 ~ Monolithic Rails app 2 Rails apps
 (Main, Accounts)

Slide 8

Slide 8 text

©2018 Wantedly, Inc. Background History of our apps The Go gopher © Renée French, The Ruby logo © Yukihiro Matsumoto 2012 ~ 2016.01 ~ 2016.11 ~ Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python)

Slide 9

Slide 9 text

©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write

Slide 10

Slide 10 text

©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write GET on Kubernetes cluster

Slide 11

Slide 11 text

©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write GET on Kubernetes cluster

Slide 12

Slide 12 text

©2018 Wantedly, Inc. Background Problems of our apps Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) profiles, working_histories, ... companies messages ... Main DB Read / Write GET on Kubernetes cluster ‣ ͦͦ͜͜େن໛ ࡀ൒͘Β͍ ىಈ΋$*΋ʢଞʹൺ΂Ε͹ʣ஗͘ͳ͖ͬͯͨ ‣ ଞͷαʔϏεͰ͔ͭ͏ʮϓϩϑΟʔϧ৘ใʯ΋͜͜ʹ௥Ճ͢Δͷ͔ʁ ‣ ͜ͷαʔϏεࣗମ͕ѻ͏Ϣʔβ਺ɾτϥϑΟοΫ͕ͦ΋ͦ΋ଟ͍ ͳΔ΂͘ͳΒ͜͜Λܦ༝ͤͣϓϩϑΟʔϧΛऔಘ͍ͨ͠

Slide 13

Slide 13 text

©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) Main DB Read / Write on Kubernetes cluster

Slide 14

Slide 14 text

©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write GET / UPDATE on Kubernetes cluster Profile DB Read / Write

Slide 15

Slide 15 text

©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write GET / UPDATE on Kubernetes cluster Profile DB Read / Write ‣ ͦͦ͜͜େن໛ DPNNJUT JTTVFTBOEQVMMSFRT SPVUJOHT

Slide 16

Slide 16 text

©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write on Kubernetes cluster Profile DB Read / Write GET / UPDATE

Slide 17

Slide 17 text

©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write GET / UPDATE on Kubernetes cluster Profile DB Read / Write "DUJWF3FDPSE͕࢖͑ͳ͘ͳΔ ͜͜ͷҠߦ͕Ұ൪Ωπ͍

Slide 18

Slide 18 text

©2018 Wantedly, Inc. Background Ideal architecture Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Read / Write on Kubernetes cluster Profile DB Read / Write GET / UPDATE ͍͖ͳΓཧ૝ΛٻΊΔͷ͸ݫ͍͠ʜ ஈ֊తʹҠߦ͸Ͱ͖ͳ͍͔ʁ

Slide 19

Slide 19 text

©2018 Wantedly, Inc. Background Ideal architecture - intermediate Monolithic Rails app Main DB Profile DB Read / Write Read / Write 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) GET on Kubernetes cluster

Slide 20

Slide 20 text

©2018 Wantedly, Inc. Background Ideal architecture - intermediate Monolithic Rails app Main DB Profile DB Read / Write Read / Write 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) GET on Kubernetes cluster ͱΓ͋͑ͣ%#Λ෼͚Δͱ͜Ζ͔Β "DUJWF3FDPSEΛ࢖͍ଓ͚ΒΕΔ

Slide 21

Slide 21 text

©2018 Wantedly, Inc. Background Ideal architecture - intermediate ͱΓ͋͑ͣ%#Λ෼͚Δͱ͜Ζ͔Β

Slide 22

Slide 22 text

©2018 Wantedly, Inc. Background Ideal architecture - intermediate ‣ ͦͦ͜͜େن໛ DPNNJUT JTTVFTBOEQVMMSFRT SPVUJOHT

Slide 23

Slide 23 text

©2018 Wantedly, Inc. Background Ideal architecture - intermediate

Slide 24

Slide 24 text

©2018 Wantedly, Inc. )PSJ[POUBM7FSUJDBM w *%˓˓˓˓Ҏ߱͸ͬͪ͜ͷ%#ʹʙ͜ͷลΓͷςʔϒϧΛผ%#ʹʙ w ࠓճ͸ޙऀ 'VODUJPO%PNBJO w 'VODUJPOFHಈըΞοϓϩʔυʹؔ࿈͢ΔςʔϒϧΛʙ w %PNBJOFHϒϩάαʔϏεʹؔ࿈͢ΔςʔϒϧΛʙ DB splitting

Slide 25

Slide 25 text

©2018 Wantedly, Inc. )PSJ[POUBM7FSUJDBM 'VODUJPO%PNBJO w 'VODUJPOFHಈըΞοϓϩʔυʹؔ࿈͢ΔςʔϒϧΛʙ w %PNBJOFHϒϩάαʔϏεʹؔ࿈͢ΔςʔϒϧΛʙ w $PSFEPNBJO αʔϏε಺ͷ͋ΒΏΔͱ͜ΖͰར༻͞ΕΔɼαʔϏεͷ֩ʹ͍ۙςʔϒϧΛʙ FH6TFS "DDPVOU 1SPpMF FUD DB splitting

Slide 26

Slide 26 text

©2018 Wantedly, Inc. ίΞΛ੾Γग़͢೉͠͞ ‣ Ͳ͜·Ͱ੾Γग़͔͢ Abelongs_to :userAͳϞσϧɼ͍ͭ͋͘Δʁ ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ αʔϏεͷ͍ͨΔͱ͜ΖͰར༻͞Ε͍ͯΔ मਖ਼ՕॴɾӨڹൣғͷ೺Ѳ΋ࠔ೉ ‣ ίΞΛ੾Γग़͍ͨ͠ͱߟ͑Δͷ͸ଟ͘ͷ৔߹ผͷαʔϏεΛ࡞͍ͬͯΔνʔϜ DB splitting

Slide 27

Slide 27 text

©2018 Wantedly, Inc. ίΞΛ੾Γग़͢೉͠͞ ‣ Ͳ͜·Ͱ੾Γग़͔͢ ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ ‣ ίΞΛ੾Γग़͍ͨ͠ͱߟ͑Δͷ͸ଟ͘ͷ৔߹ผͷαʔϏεΛ࡞͍ͬͯΔνʔϜ DB splitting ޮ཰తɾ࣮֬ʹίΞυϝΠϯ੾Γग़͠Λ΍Γ਱͛ΔͨΊͷϨʔϧ͕ඞཁ

Slide 28

Slide 28 text

©2018 Wantedly, Inc. Splitting DB on "Rails" How to split DB efficiently with Ruby "magic"

Slide 29

Slide 29 text

©2018 Wantedly, Inc. ‣ Ͳ͜·Ͱ੾Γग़͔͢ ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ Splitting DB on "Rails"

Slide 30

Slide 30 text

©2018 Wantedly, Inc. ‣ Ͳ͜·Ͱ੾Γग़͔͢ ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ Splitting DB on "Rails"

Slide 31

Slide 31 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 2੾Γग़͍ͨ͠υϝΠϯͷத৺͸ͳʹʁ

Slide 32

Slide 32 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ "ϢʔβϓϩϑΟʔϧ 1SPpMFNPEFM class Profile < ApplicationRecord # ... end

Slide 33

Slide 33 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 21SPpMFʹؔ࿈Λ࣋ͭNPEFM͸ͲΕʁ

Slide 34

Slide 34 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end

Slide 35

Slide 35 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end ย૝͍

Slide 36

Slide 36 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end ย૝ΘΕ

Slide 37

Slide 37 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ class Profile < ApplicationRecord has_many :working_histories end class WorkingHistory < ApplicationRecord belongs_to :company end class Company < ApplicationRecord end class Post < ApplicationRecord has_one :profile end ย૝͍ͷย૝͍

Slide 38

Slide 38 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 21SPpMFʹؔ࿈Λ࣋ͭNPEFM͸ͲΕʁ

Slide 39

Slide 39 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ # activerecord/lib/active_record/associations.rb in rails/rails def has_many(name, scope = nil, **options, &extension) reflection = Builder::HasMany.build(self, name, scope, options, &extension) Reflection.add_reflection self, name, reflection end

Slide 40

Slide 40 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ # activerecord/lib/active_record/associations.rb in rails/rails def has_many(name, scope = nil, **options, &extension) reflection = Builder::HasMany.build(self, name, scope, options, &extension) Reflection.add_reflection self, name, reflection end

Slide 41

Slide 41 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ [2] pry(main)> Profile.methods.grep(/reflect/) => [:_reflections, :aggregate_reflections, :aggregate_reflections=, :aggregate_reflections?, :_reflections=, :_reflections?, :reflect_on_aggregation, :reflect_on_all_aggregations, :_reflect_on_association, :reflect_on_all_associations, :clear_reflections_cache, :reflections, :reflect_on_association, :reflect_on_all_autosave_associations]

Slide 42

Slide 42 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ [1] pry(main)> Profile.methods.grep(/reflect/) => [:_reflections, :aggregate_reflections, :aggregate_reflections=, :aggregate_reflections?, :_reflections=, :_reflections?, :reflect_on_aggregation, :reflect_on_all_aggregations, :_reflect_on_association, :reflect_on_all_associations, :clear_reflections_cache, :reflections, :reflect_on_association, :reflect_on_all_autosave_associations]

Slide 43

Slide 43 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ [2] pry(main)> Profile.reflect_on_all_associations.map(&:table_name).uniq => ["users", # ... "academic_records", "working_histories", # ... "posts", #... ] ย૝͍Ұཡ͕औΕͨʂ

Slide 44

Slide 44 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) ย૝ΘΕҰཡ΋ಉ͡Α͏ʹͯ͠औΕΔʂ

Slide 45

Slide 45 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ͋ΔϞσϧʹؔ࿈Λ΋ͭϞσϧ ‣ Areflect_on_all_associationsAΛར༻ ͋ΔϞσϧ͕ؔ࿈Λ࣋ͭϞσϧΛऔಘͰ͖Δ ٯ޲͖΋΍Ε͹ย૝͍ɼย૝ΘΕؚΊ
 ͢΂ͯݟ͔ͭΔʂ from = ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } to = Profile.reflect_on_all_associations (from + to).map(&:table_name)

Slide 46

Slide 46 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) .uniq [ "users", "posts", "academic_records", # ... "working_histories", # ... ] # => 18 tables

Slide 47

Slide 47 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) .uniq [ "users", "posts", "academic_records", # ... "working_histories", # ... ] # => 18 tables ͜ͷ΁Μ͸ϓϩϑΟʔϧ ʢ6*తʹ΋ϓϩϑΟʔϧը໘ʹग़ͯ͘Δ৘ใʣ

Slide 48

Slide 48 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } .map(&:active_record) .map(&:table_name) .uniq [ "users", "posts", "academic_records", # ... "working_histories", # ... ] # => 18 tables ͜ΕΒ͸ϓϩϑΟʔϧʁ ʢQPTUT͸Ϣʔβ౤ߘςʔϒϧʣ

Slide 49

Slide 49 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ from = ApplicationRecord.descendants .reject(&:abstract_class?) .flat_map(&:reflect_on_all_associations) .select { |r| r.table_name == 'profiles' } to = Profile.reflect_on_all_associations (from + to) .map(&:table_name) .reject { |n| excluded_table_names.include? n } ผυϝΠϯͬΆ͍΋ͷ͸੾Γग़͠ର৅͔Β֎͢ ʢࠓճͩͱձࣾ৘ใ΍Ϣʔβ౤ߘ͕֘౰ʣ [ "academic_records", # ... "working_histories", # ... ]

Slide 50

Slide 50 text

©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 2 1SPpMFʹؔ࿈Λ࣋ͭNPEFM͸ͲΕʁ " Areflect_on_all_associationsAͰػցతʹ୳͢ ผυϝΠϯͬΆ͍΋ͷ͸আ֎ ࣍ɼ࣍ͷBTTPDJBUJPO͘Β͍·ͰݟͯɼαʔϏεʹৄ͍͠ਓͱ౴͑߹Θͤ͢Δ ˞࣮ࡍʹ࢖ͬͨεΫϦϓτIUUQTHJTUHJUIVCDPNJ[VNJOFCCGBEDCEDEFDDE

Slide 51

Slide 51 text

©2018 Wantedly, Inc. ‣ Ͳ͜·Ͱ੾Γग़͔͢ ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ Splitting DB on "Rails"

Slide 52

Slide 52 text

©2018 Wantedly, Inc. ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ มߋߦΛ࠷খʹ͢Δ मਖ਼ՕॴΛݟಀ͞ͳ͍

Slide 53

Slide 53 text

©2018 Wantedly, Inc. ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ มߋߦΛ࠷খʹ͢Δ मਖ਼ՕॴΛݟಀ͞ͳ͍

Slide 54

Slide 54 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ApplicationRecord < ActiveRecord::Base self.abstract_class = true end class Profile < ApplicationRecord # ... end class WorkingHistory < ApplicationRecord # ... end

Slide 55

Slide 55 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileDb::Base < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class ProfileDb::Profile < ProfileDb::Base # ... end class ProfileDb::WorkingHistory < ProfileDb::Base # ... end "DUJWF3FDPSEͰ઀ଓઌΛม͑ΔΑ͋͘Δύλʔϯ

Slide 56

Slide 56 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileDb::Base < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class ProfileDb::Profile < ProfileDb::Base # ... end class ProfileDb::WorkingHistory < ProfileDb::Base # ... end ৽͘͠ϕʔεΫϥεΛ࡞ͬͯɼܧঝͤ͞Δ

Slide 57

Slide 57 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileDb::Base < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class ProfileDb::Profile < ProfileDb::Base # ... end class ProfileDb::WorkingHistory < ProfileDb::Base # ... end ϞδϡʔϧΛ੾Δʁ

Slide 58

Slide 58 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ProfileRecord < ApplicationRecord self.abstract_class = true db_config = YAML::load(ERB.new(IO.read('config/profile_database.yml')).result) establish_connection(db_config[Rails.env]) end class Profile < ProfileRecord # ... end class WorkingHistory < ProfileRecord # ... end ϞδϡʔϧΛ੾Βͳ͍ʁ

Slide 59

Slide 59 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ .PEVMFΛ੾Δʁ ‣ ੾Δʁ ίʔυతʹ΋ʮҟͳΔυϝΠϯͰ͋Δ͜ͱͷද໌ʯ Ahas_manyAͷڍಈͱ͔ؾΛ͚ͭΔඞཁΞϦ ‣ ੾Βͳ͍ʁ ϕʔεΫϥεม͑Δ͚ͩͳͷͰɼมߋ͸͘͢ͳ͍ ίʔυ্Ͱ͸ҧ͏%#Λࢀর͍ͯ͠Δ͜ͱ͕ෆ໌ྎ class ProfileDb::WorkingHistory < ProfileDb::Base # ... end # or class WorkingHistory < ProfileRecord # ... end

Slide 60

Slide 60 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ .PEVMFΛ੾Δʁ ‣ ੾Δʁ ίʔυతʹ΋ʮҟͳΔυϝΠϯͰ͋Δ͜ͱͷද໌ʯ Ahas_manyAͷڍಈͱ͔ؾΛ͚ͭΔඞཁΞϦ ‣ ࠓճ͸੾Βͳ͍ʂ ϕʔεΫϥεม͑Δ͚ͩͳͷͰɼมߋ͸͘͢ͳ͍ ίʔυ্Ͱ͸ҧ͏%#Λࢀর͍ͯ͠Δ͜ͱ͕ෆ໌ྎ class ProfileDb::WorkingHistory < ProfileDb::Base # ... end # or class WorkingHistory < ProfileRecord # ... end

Slide 61

Slide 61 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class User < ApplicationRecord has_one :profile end class Profile < ProfileRecord has_many :working_histories end class WorkingHistory < ProfileRecord end ͔Μ΃͖ʜʁ

Slide 62

Slide 62 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class User < ApplicationRecord has_one :profile end class Profile < ProfileRecord has_many :working_histories end class WorkingHistory < ProfileRecord end ͜Ε͸Կ΋ߟ͑ͣʹಈ͘ͷ͔ʁ

Slide 63

Slide 63 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ User.joins(profile: :working_histories).to_a User.preload(profile: :working_histories).to_a User.eager_load(profile: :working_histories).to_a User.includes(profile: :working_histories).to_a User.includes(:profile).where(profile: { location: 'Tokyo' }).to_a ΫΠζಈ͘ͷ͸ͲΕͰ͠ΐ͏ʢprofilesͱworking_histories͕ҧ͏%#ʣ

Slide 64

Slide 64 text

©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ User.joins(profile: :working_histories).to_a User.preload(profile: :working_histories).to_a User.eager_load(profile: :working_histories).to_a User.includes(profile: :working_histories).to_a User.includes(:profile).where(profile: { location: 'Tokyo' }).to_a %#Λ·͍ͨͩ+0*/͕૸Δͱμϝ 1SFMPBEJOHʹؔͯ͠͸ࢀর͢Δ%#͕ҧ͍ͬͯͯ΋ਖ਼͘͠ಈ͘ ˞ݕূϦϙδτϦIUUQTHJUIVCDPNBXBLJBEPVCMFEC 3BJMTͰಈ࡞֬ೝ

Slide 65

Slide 65 text

©2018 Wantedly, Inc. User.joins(profile: :working_histories).to_a User.preload(profile: :working_histories).to_a User.eager_load(profile: :working_histories).to_a User.includes(profile: :working_histories).to_a User.includes(:profile).where(profile: { location: 'Tokyo' }).to_a DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ %#Λ·͍ͨͩ+0*/͕૸Δͱμϝ 1SFMPBEJOHʹؔͯ͠͸ࢀর͢Δ%#͕ҧ͍ͬͯͯ΋ਖ਼͘͠ಈ͘ ٯʹݴ͏ͱɼ+0*/͑͞ճආͰ͖Ε͹ͪΌΜͱಈ࡞͢Δʂ

Slide 66

Slide 66 text

©2018 Wantedly, Inc. DB splitting ·ͱΊ࠷খͷมߋͰࡁ·ͨ͢Ίʹ ࢀর͢Δ%#Λม͑Δʹ͸ʜ ϕʔεΫϥε͚ͩมߋ͢Ε͹0, ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ %#Λ·͙ͨ+0*/͚࣮ͩ֬ʹճආ͢Δʂ

Slide 67

Slide 67 text

©2018 Wantedly, Inc. ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ มߋߦΛ࠷খʹ͢Δ मਖ਼ՕॴΛݟಀ͞ͳ͍

Slide 68

Slide 68 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ࢀর͢Δ%#Λม͑Δʹ͸ʜ ϕʔεΫϥε͚ͩมߋ͢Ε͹0, ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ %#Λ·͙ͨ+0*/͚࣮ͩ֬ʹճආ͢Δʂ

Slide 69

Slide 69 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ࢀর͢Δ%#Λม͑Δʹ͸ʜ ϕʔεΫϥε͚ͩมߋ͢Ε͹0, ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ %#Λ·͙ͨ+0*/͚࣮ͩ֬ʹճආ͢Δʂ

Slide 70

Slide 70 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ Ͳ͏΍ͬͨΒ%#·ͨ͗+0*/Λ୳ͯ͠ࢭΊΒΕΔͷ͔ʁ HSFQ͸ݫ͍͠

Slide 71

Slide 71 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ΞάϨογϒʹϩάऩू ඞࡴPQFODMBTT ‣ "DUJWF3FDPSEʹ͸ɼΫΤϦ࣮ߦ࣌ʹઈରݺ͹ΕΔϝιου͕ଘࡏ͢Δ ͦͷλΠϛϯάͰ+0*/͞Εͦ͏ͳΒϩάΛ࢒͢ DPOUSPMMFS BDUJPO DBMMFS ؔ࿈͍ͯ͠Δςʔϒϧ FUD ‣ Ͳͷςʔϒϧ͕KPJOTJODMVEFTFBHFS@MPBE͞Ε͔ͨ͸ΫΤϦϏϧμ͕஌͍ͬͯΔ

Slide 72

Slide 72 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/MPHHFS

Slide 73

Slide 73 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/MPHHFS ݺͼग़͠ݩΞϓϦέʔγϣϯίʔυΛ୳͢ʢOPUHFNʣ

Slide 74

Slide 74 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/MPHHFS KPJOT ΍FBHFS@MPBE ͷҾ਺Ϧετ

Slide 75

Slide 75 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/MPHHFS def flatten_recursively(arr) arr.flat_map do |i| case i when Hash then hash_to_array(i) when Array then flatten_recursively(i) when Arel::Nodes::Node then i.to_sql when ActiveRecord::Associations::JoinDependency then join_dependency_to_table_names(i) else i.to_s end end end def hash_to_array(h) h.keys + (h.values.flat_map { |v| case v when Hash then hash_to_array(v) when Array then flatten_recursively(v) when Arel::Nodes::Node then v.to_sql when ActiveRecord::Associations::JoinDependency then join_dependency_to_table_names(v) else v.to_s end }) end def join_dependency_to_table_names(jd) jd.aliases.instance_variable_get(:@tables).map { |a| a.table.table_name } end flatten_recursively IVNBOSFBEBCMFͳTUSJOHʹ͢ΔͨΊͷIFMQFS

Slide 76

Slide 76 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/MPHHFS 6OJDPSOͩͬͨͷͰࡶʹάϩʔόϧม਺ʹ ʢDBMMFS͕Θ͔Ε͹֓Ͷ໰୊ͳ͍ʣ

Slide 77

Slide 77 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ caller_app = caller.find do |c| !c.to_s.start_with?(Bundler.bundle_path.to_s) && c.to_s.start_with?(Rails.root.to_s) end info = { caller: caller_app || "", table: table.name, joins: flatten_recursively(joins_values), left_outer_joins: flatten_recursively(left_outer_joins_values), eager_load: flatten_recursively(eager_load_values), includes: flatten_recursively(includes_values), joined_includes: flatten_recursively(joined_includes_values), references_eager_loaded: flatten_recursively(references_eager_loaded_tables), request_controller: $request_controller, request_action: $request_action, } TD.event.post("join_queries", info) +0*/MPHHFS 5SFBTVSF%BUBʹه࿥

Slide 78

Slide 78 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ select caller_line, collect_set(table) as base_tables, collect_set(t) as joined_tables_or_queries, collect_set(class) as classes, collect_set(request) as requests, collect_set(caller) as callers from ( select *, substring_index(caller, ':', 2) as caller_line, concat(request_controller, '#', request_action) as request from join_queries lateral view explode(joins) tmp as t where td_time_range(time, '2017-11-08 00:00:00', '2017-11-15 00:00:00') and ( ( table not in ('profiles', ..., 'working_histories', ...) and ( t = 'profile' or instr(t, 'profiles') != 0 -- ... or t = 'working_history' or instr(t, 'working_histories') != 0 -- ... ) ) or -- ... ) order by time desc ) queries group by caller_line ‣ ͬ͟ͱूܭ͢Δ DBMMFSͰHSPVQCZ +0*/ઌͱݩͷͲͪΒ͔͚ͩʹ෼ׂର৅͕ೖ͍ͬͯΔΫΤϦ ‣ ଟগࡶͰ΋0, ޡݕग़͸࠷ѱ໰୊ͳ͍ʢࣗ෼ͷνΣοΫର৅͕૿͑Δ͚ͩʣ ࿙Ε͕ແ͍Α͏ʹؾΛ͚ͭͯ42-ॻ͘

Slide 79

Slide 79 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ‣ ूܭ݁ՌΛ$47ग़ྗˠBXLͱ͔ͰνΣοΫϘοΫεʹ ‣ ͋ͱ͸ػցతʹ௵͍ͯ͘͠ includes͸ preload joins eager_load FHASELECT idAͱAWHERE id in (?, ?, ...)AʹΘ͚Δ ‣ ͍͍ͩͨ͸ࠜੑͱ3VCZྗͰ͚ͩղܾͰ͖Δ ͋ͱɼ΄Μͷগ͠ͷ42-ྗ

Slide 80

Slide 80 text

©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ײಈͷॠؒ

Slide 81

Slide 81 text

©2018 Wantedly, Inc. DB splitting ·ͱΊͲ͏΍ͬͯ੾Γग़͔͢ ࢀর͢Δ%#Λม͑Δʹ͸ʜ ϕʔεΫϥε͚ͩมߋ͢Ε͹0,ʂ ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ %#Λ·͙ͨ+0*/͚࣮ͩ֬ʹճආ͢Δʂ %#Λ·͙ͨ+0*/Λճආ͢ΔͨΊʹ͸ʜ ΫΤϦͷϩάΛऔͬͯɼ͋Ϳͳ͍+0*/Λ୳ͯ͠௵͢ʂ

Slide 82

Slide 82 text

©2018 Wantedly, Inc. DB splitting ·ͱΊͲ͏΍ͬͯ੾Γग़͔͢ ࢀর͢Δ%#Λม͑Δʹ͸ʜ ϕʔεΫϥε͚ͩมߋ͢Ε͹0,ʂ ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ %#Λ·͙ͨ+0*/͚࣮ͩ֬ʹճආ͢Δʂ %#Λ·͙ͨ+0*/Λճආ͢ΔͨΊʹ͸ʜ ΫΤϦͷϩάΛऔͬͯɼ͋Ϳͳ͍+0*/Λ୳ͯ͠௵͢ʂ 3VCZͷྗͰ γεςϚνοΫʹղܾͰ͖ͨ

Slide 83

Slide 83 text

©2018 Wantedly, Inc. Migration process

Slide 84

Slide 84 text

©2018 Wantedly, Inc. Migration process ϕʔεΫϥεΛ࡞੒ɾܧঝ
 PROFILE_DATABASE_URL͸.BJO%#ʹ޲͚ͨ $*্Ͱ͸PROFILE_DATABASE_URLΛҧ͏%#ʹ
 ޲͚ͯςετ௨͢ ͖ͬ͞ͷνΣοΫϘοΫεΛຒΊ͍ͯ͘ ProfileRecord ApplicationRecord MainDB ProfileRecord ApplicationRecord MainDB Profile DB Local / CI QA / Prod

Slide 85

Slide 85 text

©2018 Wantedly, Inc. Migration process ςετ͕ͱ͓Δ 2"੾Γସ͑ J ԼهͷҠߦϓϩηεͷϦϋʔαϧ JJ όάνΣοΫ 1SPE੾Γସ͑ J શαʔϏεΛϝϯςφϯεϞʔυʹ JJ .BJO%#ͷϦʔυϨϓϦΧΛঢ֨ JJJ 1SPpMF3FDPSEͷ޲͖Λม͑Δ JW αʔϏεΠϯ ProfileRecord ApplicationRecord MainDB ProfileRecord ApplicationRecord MainDB Profile DB Local / CI QA / Prod

Slide 86

Slide 86 text

©2018 Wantedly, Inc. Migration process ςετ͕ͱ͓Δ 2"੾Γସ͑ J ԼهͷҠߦϓϩηεͷϦϋʔαϧ JJ όάνΣοΫ 1SPE੾Γସ͑ J શαʔϏεΛϝϯςφϯεϞʔυʹ JJ .BJO%#ͷϦʔυϨϓϦΧΛঢ֨ JJJ 1SPpMF3FDPSEͷ޲͖Λม͑Δ JW αʔϏεΠϯ ProfileRecord ApplicationRecord MainDB ProfileRecord ApplicationRecord MainDB Profile DB Local / CI QA / Prod ϓϩϑΟʔϧ৘ใͷσʔλҠߦ ϦεΫ΋ίετ΋ߴ͍ͷͰແఀࢭҠߦ͸͠ͳ͍

Slide 87

Slide 87 text

©2018 Wantedly, Inc. Conclusion

Slide 88

Slide 88 text

©2018 Wantedly, Inc. Future works ŘŵŠŠ Monolithic Rails app Main DB Profile DB Read / Write Read / Write 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) GET on Kubernetes cluster

Slide 89

Slide 89 text

©2018 Wantedly, Inc. Future works Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Profile DB Read / Write Read / Write Read Update

Slide 90

Slide 90 text

©2018 Wantedly, Inc. Future works Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Profile DB Read / Write Read / Write Read Update ॻ͖ࠐΈ͸͢΂ͯQSPpMFTFSWJDFʹΑͤΔ ʢσʔλͷ੔߹ੑΛ࣮֬ʹʣ

Slide 91

Slide 91 text

©2018 Wantedly, Inc. Future works Monolithic Rails app 2 Rails apps
 (Main, Accounts) Microservices (Rails, Golang, Python) ? User/Profile service Main DB Profile DB Read / Write Read / Write Read Update ಡΈࠐΈʹؔͯ͠͸ڐ༰͢Δ ʢ"DUJWF3FDPSEΛണ͕͢ίετ͕େ͖͗͢Δʣ

Slide 92

Slide 92 text

Ͳ͜·Ͱ੾Γग़͔͢ʢத৺ͱͳΔςʔϒϧͷؔ࿈ʣʢ໌Β͔ʹผυϝΠϯͳ΋ͷʣ ࠷୹ڑ཭Λݟ͚ͭग़͢ϕʔεΫϥεͷมߋ%#·ͨ͗+0*/ͷ๷ࢭ มߋඞཁՕॴͷߜࠐΈద੾ʹύονΛ౰ͯͯɼద੾ʹϩάΛऔΓɼద੾ʹղੳ Splitting DB on "Rails"