Getting ActiveRecord to talk to multiple databases is easier than you might think. It’s possible to override the connection settings in database.yml at the model level by doing something like:
establish_connection( :adapter => "mysql", :host => "localhost",
:username => "myuser", :password => "mypass", :database =>
establish_connection method at the model level simply
overrides the ActiveRecord connection object for the local model. At
Galaxy Zoo we needed to do something a little
different: initially we were write dominated at the database layer - 16
million classifications in the last month peaking at about 50
classifications per second on launch day. However as things have settled
down and we’ve been adding more user-centric features to the Galaxy Zoo
site, we’ve been finding that a significant amount of out database load
has been coming from more complicated queries (reads) rather than lots
of writes. An ideal solution for a situation like this is to introduce
some kind of MySQL
thus distributing the load across multiple databases. Rather than
introducing the complexity of offset primary keys in a Master/Master
configuration we’ve opted for a standard MySQL Master/Slave
configuration sending the writes to the Master and reads to the Slave.
But how to accomplish this with ActiveRecord?
» Enter Masochism
Masochism is a
Rails plugin by Rails core team’s technoweenie (Rick Olson). It works by
overriding the ActiveRecord
connection object with
that (by default) sends writes to the Master MySQL database and reads to
the Slave. We’ve been running in production now for about 2 weeks using
Masochism and so far there’s not much to say other than it works!
We’ve made a couple of optimisations along the way after examining the production logs: When writing a classification to the database there’s a couple of writes, then some reads, then some writes… In the log you see something like this:
Switching to Master Switching to Slave Switching to Master Switching to Slave Switching to Master Switching to Slave
Obviously this switching between the Master and Slave database repeatedly in the same method call is less than ideal. Thankfully it’s possible to override the database so that within the method only one of the databases is used:
around_filter ActiveReload::MasterFilter, :only => [:create]
Masochism is a nice solution to a common problem - using ActiveRecord in a replicated database environment. I can already see us outgrowing Masochism - specifically it doesn’t support multiple slave databases which is a shame. When that day comes we’ll no doubt look to an alternative such as FiveRuns’ DataFabric or MySQL Proxy. But for now, Masochism works, and I can highly recommend it.