New Gem Generator is now version 0.7.2
Dr Nic's Magic Models is now version 0.9.1
Composite Primary Keys is now version 0.8.2
Magic Multi-Connections: A “facility in Rails to talk to more than one database at a time�?
At this point in time there’s no facility in Rails to talk to more than one database at a time.
I possibly have such a facility. Perhaps it will help, and I will get some DHH-love and perhaps a free Twitter account for my troubles. Or perhaps a t-shirt.
As a bonus, the solution even includes decent Ruby-fu syntax. So, if you’re just here for the view:
class PeopleController < ApplicationController
def index
@people = conn::Person.find(:all)
end
end
That code just there solves all our problems. It will invoke Person.find(:all) on a random database connection to (assumably) a clone database. Awesomeness I think. I hope it helps Twitter and all the Twit-sers (or whatever you call a user of Twitter).
This solution comes from the magic_multi_connections gem.
What is going on here?
I think a tutorial is the best way to demonstrate what is happening here. So, let’s create a rails app and mix in the magic_multi_connections gem.
First, get the gem. Second, create a rails app:
$ sudo gem install magic_multi_connections $ rails multi -d sqlite3
Now edit the config/database.yml file to create some more databases:
development: adapter: sqlite3 database: db/development.sqlite3 timeout: 5000 development_clone1: adapter: sqlite3 database: db/development_clone1.sqlite3 timeout: 5000 development_clone2: adapter: sqlite3 database: db/development_clone2.sqlite3 timeout: 5000
But please pretend these are uber-MySQL clusters or whatever.
Think of :development as the read-write connection, and the :development_cloneN connections are for read-only access.
At the bottom of your environment.rb file, add the following:
require 'magic_multi_connections'
connection_names = ActiveRecord::Base.configurations.keys.select do |name|
name =~ /^#{ENV['RAILS_ENV']}_clone/
end
@@connection_pool = connection_names.map do |connection_name|
Object.class_eval <<-EOS
module #{connection_name.camelize}
establish_connection :#{connection_name}
end
EOS
connection_name.camelize.constantize
end
Let’s test what this gives us in the console:
$ ruby script/console >> @@connection_pool => [DevelopmentClone1, DevelopmentClone2] >> DevelopmentClone1.class => Module >> DevelopmentClone1.connection_spec => :development_clone1
Our new modules will act as connections. One module per connection. The code above gives them names to match the connection names, but its really irrelevant what they are called, thanks to the mysterious conn method.
So, go create some models and some data. I’ll use Person as the class here.
To setup the schemas in our clone databases, we’ll use rake db:migrate. To do this:
$ cp config/environments/development.rb config/environments/development_clone1.rb $ cp config/environments/development.rb config/environments/development_clone2.rb $ rake db:migrate RAILS_ENV=development $ rake db:migrate RAILS_ENV=development_clone1 $ rake db:migrate RAILS_ENV=development_clone2
To differentiate the databases in our example, assume there are two Person records in the :development database, and none in the two clones. Of course, in real-life, they are clones. You’d have a replicate mechanism in there somewhere.
Now, we can access our normal Rails modules through our connection modules. Magically of course.
>> ActiveRecord::Base.active_connections.keys => [] >> Person.count => 2 >> ActiveRecord::Base.active_connections.keys => ["ActiveRecord::Base"] >> DevelopmentClone1::Person.count => 0 >> ActiveRecord::Base.active_connections.keys => ["ActiveRecord::Base", "DevelopmentClone1::Person"]
Wowzers. Person and DevelopmentClone1::Person classes?
But note - Person.count => 2 and DevelopmentClone1::Person.count => 0 - they are accessing different databases. The same class definition Person is being used for multiple database connections. We never defined more Person classes. Just the standard default one in app/models/person.rb.
The active_connections result shows that DevelopmentClone1::Person has its own connection. Yet you never had to manually call DevelopmentClone1::Person.establish_connection :development_clone1 - it was called automatically when the class is created.
Of course, DevelopmentClone2::Person is automatically connected to :development_clone2, and so on.
Behind the scenes
Let’s look at our generated classes:
$ ruby script/console >> DevelopmentClone1::Person => DevelopmentClone1::Person >> Person => Person >> DevelopmentClone1::Person.superclass => Person
That is, there is a DevelopmentClone1::Person class, automagically generated for you, which is a subclass of Person, so it has all its behaviour etc.
Dynamic connection pools within Rails controllers
The magic of the conn method will now be revealed:
$ ruby script/console >> def conn >> @@connection_pool[rand(@@connection_pool.size)] >> end >> conn::Person.name => "DevelopmentClone2::Person" >> conn::Person.name => "DevelopmentClone1::Person"
The conn method randomly returns one of the connection modules. Subsequently, conn::Person returns a Person class that is connected to a random clone database. Booya. Free Twitter swag coming my way.
Place the conn method in the ApplicationController class, and you can get dynamic connection pooling within Rails actions as needed, as in the following example (from the top of the article):
class PeopleController < ApplicationController
def index
@people = conn::Person.find(:all)
end
end
Decent Ruby-fu, I think. Certainly better than manually calling establish_connection on model classes before each call (or in a before_filter call, I guess).
This is just a concept
I know this tutorial above works. But that might be the extent of what I know. Let me know if this has any quirks (especially if you solve them), or if this is a stupid idea for implementing connection pooling with nice Ruby syntax.
Hope it helps.
- Posted in Ruby, Ruby on Rails, Magic Models, Programming, Announcement, Trick, DSL
- Meta
20 comments,
permalink,
rss
Trackbacks
Use this link to trackback from your own site.
日本語�?�翻訳
Deutsch
Español
Nice article. I think I may have to explore and expand upon this great idea.
By the way, I do hope you get your swag!
Why not push the random connection selection mechanism down into the adapter, replacing the actual ActiveRecord::Base.connection method, so this becomes entirely transparent?
@xilo - me too, I love free stuff.
@james - that would be great if you didn’t care which requests went to which connection. I think some apps might want write requests to go to the master DB, and read requests (select) to be distributed to the slaves.
Though perhaps we could use your idea and pass a :connection option in the crud methods; or perhaps :connection_group.
Not only would some apps want to write to the master and read from salves, but this would also be great for when you need to scale to ginormous sizes and have to data across federated tables - ie. user’s 1 - 100k are on slave1, users 101k-200k on slave 2, etc.
Obviously not a common case, but even having the standard master-slave setup isn’t very common either.
Nice work Nic.
Great idea, and your timing rocks. I was just about to look into implementing exactly this!
Pushing it down into the connection adapter to make it transparent to the application would utterly rock, though. I guess you could have all the writes going to the master and reads from the slaves by adding an extra key in
config/database.ymlalong the lines ofread_onlywhich defaults to false. If it’s true, then that particular db isn’t considered part of the write connection pool. Then all the write-related SQL statements have to be flagged in some way and pushed towards the write connection pool, whereas read connections can come from anywhere…@mathie - I think something like is a good idea, in terms of configuration.
Or, embed the read-only connections within the main connection specification:
production: adapter: mysql ... read_only: clone1: ... clone2: ...I think I like the latter one, plus I think it might be easier to implement
This is so great! Timing is indeed perfect, I just needed something like this. Connecting to a different database based on certain parameters is now possible!
I like the idea, and it’s something I had thought about. I love that you created the concept. The problem that I was thinking about was the whole master_write slaves_read would be rather database specific, but that’s no reason to stop going.
I also thought about somehow automatically routing all select type calls (find, count, sum, etc) to read from the pool, and all the write calls go to the master. This might eventually be a scaling issue as well, but then you could get into a multi-master DB where db1 is all even IDs and db2 is odd… but that’s beyond me right now.
Either way, thanks for getting the ball rolling on this…
[…] Dr. Nic has posted an article detailing it. It requires the magic_multi_connections gem and a few modifications to your environment.rb file. You can then specify clones to connect to and distribute load on the database backend. […]
[…] He also points to an excellent proof-of-concept how-to for an in-Rails load balancing solution. […]
Val Aleksenko is about to release a plugin to support the read-only connections for read-only operations, and a write connection for the master database.
http://revolutiononrails.blogspot.com/2007/04/actsaswithreadonly-to-support-read-only.html
Dr Nic makes Rails talk easily with multiple read/write databases…
Remember that point about Rails lacking an easy-to-use way of dealing with multiple read/write databases? Strike that. Nic Williams has……
[…] Update: David Heinemeier Hanson, aka “DHH�?, the primary developer of Ruby on Rails, has posted that Nic Williams has created a 75-line ROR plug-in that solves the multiple database problem. He somewhat ungraciously blames Twitter for not taking a look and figuring this out themselves, however, technically at least I have to agree with him. I couldn’t imagine anything inherent in any language or platform that would prohibit access to more than one database. So even though such a simple and obvious thing should have been included in Rails, and it’s telling that until now apparently they’ve felt no need for it, it does seem to be true that Twitter gave up a little too easily. So that’s the other lesson here — don’t assume too quickly that a platform is fundamentally flawed, either, at least to the extent that in an effort to Do Something you try to inject a whole new platform into the mix. That’s expensive and problematic, too. digg_url=’http://bobondevelopment.com/2007/04/13/ruby-on-rails-hits-a-wall-twitter-stutters/’; digg_skin = ‘button’; digg_bgcolor = ‘#FFFFFF’; digg_title = ‘Ruby on Rails Hits a Wall; Twitter Stutters’; digg_bodytext = ‘’; digg_topic = ‘’; Powered by Gregarious (34) […]
[…] World watch out - Rails now connects to multiple databases quickly and easily. In response to an article mentioning a lack of multiple database support in Rails, Dr Nic worked his magic and released magic_multi_connections. Rails apps can now connect to multiple databases without having to refactor existing models. This sweetness was done in less then 75 lines of code. Read all about it and give Dr Nic some good diggage. Read DHH’s blog to see what he thinks. […]
[…] This is way cleaner than anything I had. I bow down to the Ruby-Fu of Nic Williams. […]
[…] So, we’ve moved into rough territory, but someone goes and does something nice (Woo hoo!). Dr Nic writes a plugin to use multiple databases with Rails. Way cool. Really way cool. Awesome job Nic. This is the part of open source that I love, when people help each other just because it’s an interesting problem and they’re a nice person. Hugs and kittens all around. […]
Congratulations for Dr. Nic for yet another great solution! I just posted about this at my brazilian rails blog. I assumed some things though:
- this is not a drop in sollution that you just plug and everything works
- I assume that you don’t have to change any write operations such as Person.create
- I do assume that you have to change every read operation prepending them with the conn proxy method as in conn::Person.find
Are these assumptions correct?
Anyway, thanks again for this great plugin.
Awsome! So simple. So beatiful.
@fabio (akita) - The tutorial I wrote works - there is nothing missing. You’ll want to ensure the slaves are synchronised with the master very quickly of course.
Write operations would not use the
conn::prefix, correct. But, I have a sneaky suspicion that the defaultActiveRecord::Base.connectionmight be defaulted to one of the slaves if you call theconnmethod before aPerson.xxxCRUD method.Yes, you’d need to change all your read operations. And so having said that…
Val Aleksenko just released acts_as_readonlyable plugin which offers an even cleaner solution (notably your code doesn’t have the
conn::all through it), so that’s definitely worth checking out too.The MMC was built for a different purpose from the master-slave solution I wrote up here - to allow you to create an admin site that connects to many databases. But when Twitter announced they needed a solution, I thought the MMC might help. The acts_as_readonlyable solution has been extracted from a production app - I think its probably a more guaranteed solution in this case!!
[…] We’re in uncharted waters in terms of this sort of throughput, although others have suggestions and maybe even solutions. […]