Hi all, We''re thinking of moving mysql to master/slave. What I''d like to do is configure a reader and writer db in database.yml, then AR::Base#create and AR::Base#update would use the writer connection. Initially we''d just do 1 reader db. A more complex solution might configure more and have each fcgi choose one at random at startup. If internally AR had the concept of reader_connection and writer_connection, it would be easy to build plugins around this. Seems like this would be pretty easy to do, but are there complications I''m ignoring? Has anyone already done this? Good? Bad? - Ryan
I''ve been thinking about this as well, as our sites are now starting to push the limits of a single MySQL database. I think, however, that the selection of master or slave database has to persist for the entire page request or you risk inconsistent data and transactions (even reads within a transaction can have locking implications). Pages would probably need to be designated as read- only or read-write and use the same throughout the page serve. I was thinking that one way of doing this in a backwards compatible manner would be specify that certain actions in the controller were guaranteed to be read only, any other actions would be presumed to require the master database. An exception could be generated if a write were attempted with a read-only connection. Maybe something like this: class Entry < ApplicationController read_only :index, :view def index; end def view; end def add; end def delete; end end The read_only attribute could set a thread-local flag that would cause ActiveRecord::Base#connection to choose either a master or one of several configured slave databases. -Bob. On Nov 23, 2005, at 1:57 PM, Ryan Carver wrote:> Hi all, > > We''re thinking of moving mysql to master/slave. What I''d like to do > is configure a reader and writer db in database.yml, then > AR::Base#create and AR::Base#update would use the writer > connection. Initially we''d just do 1 reader db. A more complex > solution might configure more and have each fcgi choose one at > random at startup. > > If internally AR had the concept of reader_connection and > writer_connection, it would be easy to build plugins around this. > > Seems like this would be pretty easy to do, but are there > complications I''m ignoring? Has anyone already done this? Good? Bad? > > - Ryan > > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core
search the ror trac for ''replication'' http://dev.rubyonrails.org/ticket/2041 hth -- courtenay On 11/23/05, Robert Cottrell <bob@robotcoop.com> wrote:> I''ve been thinking about this as well, as our sites are now starting > to push the limits of a single MySQL database. > > I think, however, that the selection of master or slave database has > to persist for the entire page request or you risk inconsistent data > and transactions (even reads within a transaction can have locking > implications). Pages would probably need to be designated as read- > only or read-write and use the same throughout the page serve. > > I was thinking that one way of doing this in a backwards compatible > manner would be specify that certain actions in the controller were > guaranteed to be read only, any other actions would be presumed to > require the master database. An exception could be generated if a > write were attempted with a read-only connection. Maybe something > like this: > > class Entry < ApplicationController > read_only :index, :view > > def index; end > def view; end > def add; end > def delete; end > end > > The read_only attribute could set a thread-local flag that would > cause ActiveRecord::Base#connection to choose either a master or one > of several configured slave databases. > > -Bob. > > > On Nov 23, 2005, at 1:57 PM, Ryan Carver wrote: > > > Hi all, > > > > We''re thinking of moving mysql to master/slave. What I''d like to do > > is configure a reader and writer db in database.yml, then > > AR::Base#create and AR::Base#update would use the writer > > connection. Initially we''d just do 1 reader db. A more complex > > solution might configure more and have each fcgi choose one at > > random at startup. > > > > If internally AR had the concept of reader_connection and > > writer_connection, it would be easy to build plugins around this. > > > > Seems like this would be pretty easy to do, but are there > > complications I''m ignoring? Has anyone already done this? Good? Bad? > > > > - Ryan > > > > _______________________________________________ > > Rails-core mailing list > > Rails-core@lists.rubyonrails.org > > http://lists.rubyonrails.org/mailman/listinfo/rails-core > > _______________________________________________ > Rails-core mailing list > Rails-core@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails-core >
On 11/24/05, Robert Cottrell <bob@robotcoop.com> wrote:> I''ve been thinking about this as well, as our sites are now starting > to push the limits of a single MySQL database. > > I think, however, that the selection of master or slave database has > to persist for the entire page request or you risk inconsistent data > and transactions (even reads within a transaction can have locking > implications). Pages would probably need to be designated as read- > only or read-write and use the same throughout the page serve.This is a critical point really. The entire request is either: * Read Only * Read Write The only problem I can see is with rendering components. What do we do with an action marked read_only which renders a component marked read_write? Leave it as an issue for users? Also, does mysql support multi master replication? Finally, does rails itself have to support this, or is it a candidate for a plugin? -- Cheers Koz
On Nov 23, 2005, at 4:54 PM, Michael Koziarski wrote:> On 11/24/05, Robert Cottrell <bob@robotcoop.com> wrote: > > Also, does mysql support multi master replication?MySQL 5.0 added support for auto-increment offsets: http:// tinyurl.com/7t5hr , which makes support within rails for master- master replication unnecessary *except* for accounting for consistency across servers.> Finally, does rails itself have to support this, or is it a > candidate for a plugin?It needs to be well documented, whatever the case. ;-) b. _______________________________________________ Rails-core mailing list Rails-core@lists.rubyonrails.org http://lists.rubyonrails.org/mailman/listinfo/rails-core