Upgrade to Pro — share decks privately, control downloads, hide ads and more …

Splitting DB on "Rails"

Splitting DB on "Rails"

9eed44f137609e6ce3b6f1e14f80b9e1?s=128

Masayuki Izumi

May 12, 2018
Tweet

More Decks by Masayuki Izumi

Other Decks in Programming

Transcript

  1. Splitting DB on "Rails" How to split DB efficiently with

    Ruby "magic" RejectKaigi 2018 12.May.2018 - @izumin5210
  2. 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
  3. Background ͳͥ%#Λ෼ׂ͢Δඞཁ͕͔͋ͬͨ Splitting DB on Rails ͍͔ʹͯ͠%#෼ׂΛਐΊ͍͔ͯ͘ Agenda

  4. ©2018 Wantedly, Inc. Background WHY did we need to split

    the database?
  5. ©2018 Wantedly, Inc. Background History of our apps 2012 ~

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

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

    2016.01 ~ 2016.11 ~ Monolithic Rails app 2 Rails apps
 (Main, Accounts)
  8. ©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)
  9. ©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
  10. ©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
  11. ©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
  12. ©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 ‣ ͦͦ͜͜େن໛  ࡀ൒͘Β͍  ىಈ΋$*΋ʢଞʹൺ΂Ε͹ʣ஗͘ͳ͖ͬͯͨ ‣ ଞͷαʔϏεͰ͔ͭ͏ʮϓϩϑΟʔϧ৘ใʯ΋͜͜ʹ௥Ճ͢Δͷ͔ʁ ‣ ͜ͷαʔϏεࣗମ͕ѻ͏Ϣʔβ਺ɾτϥϑΟοΫ͕ͦ΋ͦ΋ଟ͍ ͳΔ΂͘ͳΒ͜͜Λܦ༝ͤͣϓϩϑΟʔϧΛऔಘ͍ͨ͠
  13. ©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
  14. ©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
  15. ©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
  16. ©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
  17. ©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͕࢖͑ͳ͘ͳΔ ͜͜ͷҠߦ͕Ұ൪Ωπ͍
  18. ©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 ͍͖ͳΓཧ૝ΛٻΊΔͷ͸ݫ͍͠ʜ ஈ֊తʹҠߦ͸Ͱ͖ͳ͍͔ʁ
  19. ©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
  20. ©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Λ࢖͍ଓ͚ΒΕΔ
  21. ©2018 Wantedly, Inc. Background Ideal architecture - intermediate ͱΓ͋͑ͣ%#Λ෼͚Δͱ͜Ζ͔Β

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

      DPNNJUT   JTTVFTBOEQVMMSFRT   SPVUJOHT
  23. ©2018 Wantedly, Inc. Background Ideal architecture - intermediate

  24. ©2018 Wantedly, Inc.  )PSJ[POUBM7FSUJDBM w *%˓˓˓˓Ҏ߱͸ͬͪ͜ͷ%#ʹʙ͜ͷลΓͷςʔϒϧΛผ%#ʹʙ w ࠓճ͸ޙऀ 

    'VODUJPO%PNBJO w 'VODUJPOFHಈըΞοϓϩʔυʹؔ࿈͢ΔςʔϒϧΛʙ w %PNBJOFHϒϩάαʔϏεʹؔ࿈͢ΔςʔϒϧΛʙ DB splitting
  25. ©2018 Wantedly, Inc.  )PSJ[POUBM7FSUJDBM  'VODUJPO%PNBJO w 'VODUJPOFHಈըΞοϓϩʔυʹؔ࿈͢ΔςʔϒϧΛʙ w

    %PNBJOFHϒϩάαʔϏεʹؔ࿈͢ΔςʔϒϧΛʙ w $PSFEPNBJO  αʔϏε಺ͷ͋ΒΏΔͱ͜ΖͰར༻͞ΕΔɼαʔϏεͷ֩ʹ͍ۙςʔϒϧΛʙ  FH6TFS "DDPVOU 1SPpMF FUD DB splitting
  26. ©2018 Wantedly, Inc. ίΞΛ੾Γग़͢೉͠͞ ‣ Ͳ͜·Ͱ੾Γग़͔͢  Abelongs_to :userAͳϞσϧɼ͍ͭ͋͘Δʁ ‣

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

    DB splitting ޮ཰తɾ࣮֬ʹίΞυϝΠϯ੾Γग़͠Λ΍Γ਱͛ΔͨΊͷϨʔϧ͕ඞཁ
  28. ©2018 Wantedly, Inc. Splitting DB on "Rails" How to split

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

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

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

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

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

  34. ©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
  35. ©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 ย૝͍
  36. ©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 ย૝ΘΕ
  37. ©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 ย૝͍ͷย૝͍
  38. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 21SPpMFʹؔ࿈Λ࣋ͭNPEFM͸ͲΕʁ

  39. ©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
  40. ©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
  41. ©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]
  42. ©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]
  43. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ [2] pry(main)> Profile.reflect_on_all_associations.map(&:table_name).uniq =>

    ["users", # ... "academic_records", "working_histories", # ... "posts", #... ] ย૝͍Ұཡ͕औΕͨʂ
  44. ©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) ย૝ΘΕҰཡ΋ಉ͡Α͏ʹͯ͠औΕΔʂ
  45. ©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)
  46. ©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
  47. ©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*తʹ΋ϓϩϑΟʔϧը໘ʹग़ͯ͘Δ৘ใʣ
  48. ©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͸Ϣʔβ౤ߘςʔϒϧʣ
  49. ©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", # ... ]
  50. ©2018 Wantedly, Inc. DB splitting Ͳ͜·Ͱ੾Γग़͔͢ 2 1SPpMFʹؔ࿈Λ࣋ͭNPEFM͸ͲΕʁ " Areflect_on_all_associationsAͰػցతʹ୳͢

    ผυϝΠϯͬΆ͍΋ͷ͸আ֎ ࣍ɼ࣍ͷBTTPDJBUJPO͘Β͍·ͰݟͯɼαʔϏεʹৄ͍͠ਓͱ౴͑߹Θͤ͢Δ ˞࣮ࡍʹ࢖ͬͨεΫϦϓτIUUQTHJTUHJUIVCDPNJ[VNJOFCCGBEDCEDEFDDE
  51. ©2018 Wantedly, Inc. ‣ Ͳ͜·Ͱ੾Γग़͔͢ ‣ Ͳ͏΍ͬͯ੾Γग़͔͢ Splitting DB on

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

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

  54. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ class ApplicationRecord < ActiveRecord::Base

    self.abstract_class = true end class Profile < ApplicationRecord # ... end class WorkingHistory < ApplicationRecord # ... end
  55. ©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Ͱ઀ଓઌΛม͑ΔΑ͋͘Δύλʔϯ
  56. ©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 ৽͘͠ϕʔεΫϥεΛ࡞ͬͯɼܧঝͤ͞Δ
  57. ©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 ϞδϡʔϧΛ੾Δʁ
  58. ©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 ϞδϡʔϧΛ੾Βͳ͍ʁ
  59. ©2018 Wantedly, Inc. DB splitting ࠷খͷมߋͰࡁ·ͨ͢Ίʹ .PEVMFΛ੾Δʁ ‣ ੾Δʁ 

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

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

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

    has_one :profile end class Profile < ProfileRecord has_many :working_histories end class WorkingHistory < ProfileRecord end ͜Ε͸Կ΋ߟ͑ͣʹಈ͘ͷ͔ʁ
  63. ©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͕ҧ͏%#ʣ
  64. ©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Ͱಈ࡞֬ೝ
  65. ©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*/͑͞ճආͰ͖Ε͹ͪΌΜͱಈ࡞͢Δʂ
  66. ©2018 Wantedly, Inc. DB splitting ·ͱΊ࠷খͷมߋͰࡁ·ͨ͢Ίʹ ࢀর͢Δ%#Λม͑Δʹ͸ʜ ϕʔεΫϥε͚ͩมߋ͢Ε͹0, ϕʔεΫϥεͷมߋ͚ͩͰਖ਼͘͠ಈ࡞ͤ͞ΔͨΊʹ͸ʜ %#Λ·͙ͨ+0*/͚࣮ͩ֬ʹճආ͢Δʂ

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

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

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

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

  71. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ΞάϨογϒʹϩάऩू ඞࡴPQFODMBTT ‣ "DUJWF3FDPSEʹ͸ɼΫΤϦ࣮ߦ࣌ʹઈରݺ͹ΕΔϝιου͕ଘࡏ͢Δ

     ͦͷλΠϛϯάͰ+0*/͞Εͦ͏ͳΒϩάΛ࢒͢  DPOUSPMMFS BDUJPO DBMMFS ؔ࿈͍ͯ͠Δςʔϒϧ FUD ‣ Ͳͷςʔϒϧ͕KPJOTJODMVEFTFBHFS@MPBE͞Ε͔ͨ͸ΫΤϦϏϧμ͕஌͍ͬͯΔ
  72. ©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
  73. ©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ʣ
  74. ©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 ͷҾ਺Ϧετ
  75. ©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
  76. ©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͕Θ͔Ε͹֓Ͷ໰୊ͳ͍ʣ
  77. ©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ʹه࿥
  78. ©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-ॻ͘
  79. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ‣ ूܭ݁ՌΛ$47ग़ྗˠBXLͱ͔ͰνΣοΫϘοΫεʹ ‣ ͋ͱ͸ػցతʹ௵͍ͯ͘͠

     includes͸ preload  joins eager_load  FHASELECT idAͱAWHERE id in (?, ?, ...)AʹΘ͚Δ ‣ ͍͍ͩͨ͸ࠜੑͱ3VCZྗͰ͚ͩղܾͰ͖Δ  ͋ͱɼ΄Μͷগ͠ͷ42-ྗ
  80. ©2018 Wantedly, Inc. DB splitting ӨڹՕॴΛݟಀ͞ͳ͍ ײಈͷॠؒ

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

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

    %#Λ·͙ͨ+0*/Λճආ͢ΔͨΊʹ͸ʜ ΫΤϦͷϩάΛऔͬͯɼ͋Ϳͳ͍+0*/Λ୳ͯ͠௵͢ʂ 3VCZͷྗͰ γεςϚνοΫʹղܾͰ͖ͨ
  83. ©2018 Wantedly, Inc. Migration process

  84. ©2018 Wantedly, Inc. Migration process  ϕʔεΫϥεΛ࡞੒ɾܧঝ
 PROFILE_DATABASE_URL͸.BJO%#ʹ޲͚ͨ  $*্Ͱ͸PROFILE_DATABASE_URLΛҧ͏%#ʹ


    ޲͚ͯςετ௨͢  ͖ͬ͞ͷνΣοΫϘοΫεΛຒΊ͍ͯ͘ ProfileRecord ApplicationRecord MainDB ProfileRecord ApplicationRecord MainDB Profile DB Local / CI QA / Prod
  85. ©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
  86. ©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 ϓϩϑΟʔϧ৘ใͷσʔλҠߦ ϦεΫ΋ίετ΋ߴ͍ͷͰແఀࢭҠߦ͸͠ͳ͍
  87. ©2018 Wantedly, Inc. Conclusion

  88. ©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
  89. ©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
  90. ©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ʹΑͤΔ ʢσʔλͷ੔߹ੑΛ࣮֬ʹʣ
  91. ©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Λണ͕͢ίετ͕େ͖͗͢Δʣ
  92. Ͳ͜·Ͱ੾Γग़͔͢ʢத৺ͱͳΔςʔϒϧͷؔ࿈ʣʢ໌Β͔ʹผυϝΠϯͳ΋ͷʣ  ࠷୹ڑ཭Λݟ͚ͭग़͢ϕʔεΫϥεͷมߋ %#·ͨ͗+0*/ͷ๷ࢭ  มߋඞཁՕॴͷߜࠐΈద੾ʹύονΛ౰ͯͯɼద੾ʹϩάΛऔΓɼద੾ʹղੳ Splitting DB on "Rails"