multi_db

IMPORANT: version with some hacks for compatibility with ultrasphinx. Used in qik.com

– 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 only with activerecord 2.1, 2.2 and 2.3

Install

gem sources --add http://gems.github.com # only if you haven't already added github
gem install glebpom-multi_db

When using Rails, add this to your environment.rb:

config.gem 'glebpom-multi_db', :lib => 'multi_db', :source => 'http://gems.github.com'

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.

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

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:

spec spec