How I can use partitioned database with RoR?

Question

Hi,

I want to ask this question with a specific example as I am looking for a concrete answer. :) Let's say I have a set of MySQL databases sharded on user_id. For example, all users who have ids 1-10000 will go into database D1, user ids with 10001 - 20000 will go into database D2 so on.. I have a model "User" in my RoR application. Depending upon the user_id for which information required this model should query appropriate database and return the results back. Can any RoR expert tell how to make it possible?

A related question is that, let's say I created N databases D1, D2 ... DN on the same box where MySQL running and a memcached cache layer infront of these databases. Does structuring database in this way result in poor performance? (I am worried will there be many cache misses at DB layer and memcache layer.)

Answer 1

I think that what you need is: http://partitioned.rubyforge.org/

Answer 2

Take a look at the DataFabric gem which adds database sharding support to Active Record. It lets you do things like:

class User < ActiveRecord::Base
  data_fabric :replicated => true, :shard_by => :user_id
end

Answer 3

What you're looking for is commonly called sharding. There's a pretty comprehensive article on wikipedia about it, which you should definitely read and a good article on the high scalability blog.

When it comes to sharding with rails I'd recommend the data fabric gem which supports application level database sharding as well as master/slave replication.

I hope this helps!