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"