Active Record Mysql Master Slave
I recently had to connect to a mysql cluster configured with a single master and multiple slaves. This is a common configuration for site that have a large volumn of traffic.
Connecting
First off you'll want to support connecting to either the master or slave through the use of the seamless database pool gem. Once added to your Gemfile you can read the documentation to setup your configuration a bit.
By default, the master connection will be used for everything. The documentation will explain how to connect to slaves, but as usual I want to talk about using this with 3rd party databases.
Multiple Databases
If you are connecting to multiple database as I've discussed in the past, these base class methods can be very helpful.
require 'seamless_database_pool'
class Base < ActiveRecord::Base
# abstract
self.abstract_class = true
#
# read only
#
# leveraging seemless data pool, this ensures all connections requested are for slave
# instances instead of master
#
def self.read_only
SeamlessDatabasePool.use_persistent_read_connection do # read only
Base.cache do
return yield
end
end
end
#
# no pool
#
# code will leverage active record pool & execute statements against the database
# once complete the connection will be disassociated with the pool and closed
# if you consistently do this, no persistent connections will remain
#
def self.no_pool()
begin
Base.cache do
return yield
end
ensure
conn_pool = Base.connection_pool
conn = conn_pool.connection
Base.connection_pool.release_connection # supported by activerecord 3 & 4
conn.disconnect! # no persistent connections
end
end
#
# cache
#
# third party connections don't cache by default. this is wrapped up into read only and no pool
# to give you caching by default
#
def self.cache
Base.connection.cache { yield }
end
end
Read Only
The read only helper makes it simple to connect to slave database
result = Base.read_only do
Model.where(something: true)
end
No Pool
In my instance the Ops team had the slaves configured behind a load balancer and they did not want persistent pooled connections. The helper method above makes this simple to do
result = Base.no_pool do
Model.where(something: true)
end
Caching Queries
Active Record caches queries by default in rails, but in third party databases you have to do it explicitly. The two methods above do it by default, but if you are just querying master with pooling, you do it like this
result = Base.cache do
Model.where(something: true)
end
And you could just wrap that around all your queries or each one individually.