multi_db

– This GEM was inspired by Rick Olson’s “masochism”-Plugin

multi_db uses a connection proxy, which sends read queries to slave databases, and all write queries to the master database (Read/Write Split). Within transactions, while executing ActiveRecord Observers and within “with_master” blocks (see below), even read queries are sent to the master database.

Important changes in 0.2.0

As of this version, ActiveRecord::Base.connection does not return the connection proxy by default anymore (therefore the jump to 0.2.0). Only models inheriting from AR::B return the proxy, unless they are defined as master_models (see below). If you want to access the connection proxy from AR::B directly, use ActiveRecord::Base.connection_proxy.
This version is the first attempt for thread-safety of this gem. There might still be some threading issues left!. So please test your apps thoroughly and report any issues you might encounter.
CGI::Session::ActiveRecordStore::Session is now automatically registered as a master model.

Caveats

works with activerecord 2.1, 2.2, 2.3, and 3.0

Install

Rails 2

gem install multi_db

Then add this to your environment.rb:

config.gem 'multi_db', :lib => 'multi_db'

Rails 3

put this in your Gemfile

gem 'multi_db'

Setup

In your database.yml, add sections for the slaves, e.g.:

production: # that would be the master
  adapter: mysql
  database: myapp_production
  username: root
  password: 
  host: localhost

production_slave_database: # that would be a slave 
  adapter: mysql
  database: myapp_production
  username: root
  password: 
  host: 10.0.0.2

production_slave_database_2: # another slave
  ...
production_slave_database_in_india: # yet another one
  ...

NOTE: multi_db identifies slave databases by looking for entries of the form “<environment>_slave_database<_optional_name>”. As a (useless) side effect you get abstract classes named MultiDb::SlaveDatabaseInIndia etc. The advantage of specifying the slaves explicitly, instead of the master, is that you can use the same configuration file for scripts that don’t use multi_db. Also, when you decide to disable multi_db for some reason, you don’t have to swap hosts in your database.yml from master to slave (which is easy to forget…).

To enable the proxy globally, add this to your environment.rb, or some file in config/initializers:

MultiDb::ConnectionProxy.setup!

If you only want to enable it for specific environments, add this to the corresponding file in config/environments:

config.after_initialize do
  MultiDb::ConnectionProxy.setup!
end

In the development and test environments, you can use identical configurations for master and slave connections. This can help you finding (some of the) issues your application might have with a replicated database setup without actually having one on your development machine.

Using with Phusion Passenger

With Passengers smart spawning method, child processes forked by the ApplicationSpawner won’t have the connection proxy set up properly.

To make it work, add this to your environment.rb or an initializer script (e.g. config/initializers/connection_proxy.rb):

if defined?(PhusionPassenger)
  PhusionPassenger.on_event(:starting_worker_process) do |forked|
    if forked
      # ... set MultiDb configuration options, if any ...
      MultiDb::ConnectionProxy.setup!
    end
  end
else # not using passenger (e.g. development/testing)
  # ... set MultiDb configuration options, if any ...
  MultiDb::ConnectionProxy.setup!
end

Thanks to Nathan Esquenazi for testing this.

Forcing the master for certain actions

Just add this to your controller:

around_filter(:only => :foo_action) { |c,a| ActiveRecord::Base.connection_proxy.with_master { a.call } }

Forcing the master for certain models

In your environment.rb or an initializer, add this before the call to setup!:

MultiDb::ConnectionProxy.master_models = ['CGI::Session::ActiveRecordStore::Session', 'PaymentTransaction', ...]
MultiDb::ConnectionProxy.setup!

NOTE: You cannot safely add more master_models after calling setup!.

Making one slave database sticky during a request

This can be useful to leverage database level query caching as all queries will be sent to the same slave database during one web request.

To enable, add this to your environment.rb just before MultiDb::ConnectionProxy.setup!:

MultiDb::ConnectionProxy.sticky_slave = true

And add this to your ApplicationController:

after_filter { ActiveRecord::Base.connection_proxy.next_reader! }

NOTE: It’s not possible to toggle this mode in a running process, as the dynamically generated methods will have the initially defined “stickyness” built in.

Using the weighted scheduler

The standard scheduler roundrobins queries to evenly to all slaves. This means that if you’re using servers with different capacity (slower machines, some slaves receiving traffic from other apps etc) you might run into problems. The weighted scheduler tries to address this by assigning a weight attribute to each slave and distribute queries evenly among the server pool.

In your database.yml file add your weights like so:

test_slave_database_1:
  <<: *creds
  host: my.slavedb_1
  weight: 1

test_slave_database_2:
  <<: *creds
  host: my.slavedb_2
  weight: 10

The above configuration will lead to slavedb_2 to receive 9 times more queries than slavedb_1. Adding in a new slave with:

test_slave_database_3:
  <<: *creds
  host: my.slavedb_3
  weight: 5

leads to a distribution of 1:10:5. For 100k queries the numbers could look like this:

Slave 1, with weight 1: 6302 queries
Slave 2, with weight 10: 62764 queries
Slave 3, with weight 5: 30934 queries

The weighted scheduler does not guarantee that the same slave will not receive two queries in a row. We feel this is not an issue, or rather, that such a guarantee doesn’t help much as it’s the complexity of the queries rather than the number that creates problems.

If no weight param is given for a slave, a weight of 1 is assumed. A weight of 0 is caught and silently transformed into a weight of 1.

Usage outside of Rails

You can use multi_db together with other framworks or in standalone scripts. Example:

require 'rubygems'
require 'active_record'
require 'multi_db'

ActiveRecord::Base.logger = Logger.new(STDOUT)
ActiveRecord::Base.configurations = {
  'development' => {
    'adapter'  => 'mysql',
    'host'     => 'localhost',
    'username' => 'root',
    'database' => 'multi_db_test'
  },
  'development_slave_database' => {
    'adapter'  => 'mysql',
    'host'     => 'localhost',
    'username' => 'root',
    'database' => 'multi_db_test'
  }
}
ActiveRecord::Base.establish_connection :development
MultiDb::ConnectionProxy.setup!

class MyModel < ActiveRecord::Base
  # ...
end

# ...

Note that the configurations hash should contain strings as keys instead of symbols.

Differences to “masochism”:

Supports multiple slave databases (round robin)
It sends everything except “select …” queries to the master, instead of sending only specific things to the master and anything “else” to the slave. This avoids accidential writes to the master when there are API changes in ActiveRecord which haven’t been picked up by multi_db yet. Note that this behaviour will also always send helper methods like “quote” or “add_limit!” to the master connection object, which doesn’t add any more load on the master, as these methods don’t communicate with the db server itself.
It uses its own query cache as the slave’s cache isn’t emptied when there are changes on the master
It supports immediate failover for slave connections
It will wait some time before trying to query a failed slave database again
It supports nesting “with_master”-blocks, without unexpectedly switching you back to the slave again
It schedules a reconnect of the master connection if statements fail there. This might help with HA setups using virtual IPs (a test setup would be nice to verify this)
You specify slave databases in the configuration instead of specifying an extra master database. This makes disabling or removing multi_db less dangerous (Update: Recent versions of masochism support this, too).
There are no set_to_master! and set_to_slave! methods, just with_master(&block)
All proxied methods are dynamically generated for better performance

Contributors

David Palm github.com/dvdplm
Matt Conway github.com/wr0ngway
Matthias Marshall github.com/webops

Ideas

See: github.com/schoefmax/multi_db/wikis/home

Running specs

If you haven’t already, install the rspec gem, then create an empty database called “multi_db_test” (you might want to tweak the spec/config/database.yml). From the plugin directory, run:

rspec spec