Master/Slave Databases with Rails

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 => "somedatabase" )

Calling the 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 replication 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 ConnectionProxy 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.