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

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"