Philip Hallstrom
2006-May-15 17:22 UTC
[Rails] Databases... write master, multiple read-only slaves.. will this work?
Hi all - I have a need to have my models write to one database and read from another. From what I''ve read, Rails doesn''t do this out of the box. I came up with something that does work, but I''m wondering if there is something I''m missing in the code below that''s going to bite me, such as establish_connection creating a huge number of objects as time progresses. Or is there a better way to flip the connection back and forth? The below does work though. Writes do go to :writemaster and reads go to :readslave... Comments? class MyModel < ActiveRecord::Base #..... def before_save ActiveRecord::Base.establish_connection(:writemaster) end def before_destroy ActiveRecord::Base.establish_connection(:writemaster) end def after_save ActiveRecord::Base.establish_connection(:readslave) end def after_destroy ActiveRecord::Base.establish_connection(:readslave) end end
Jeremy Kemper
2006-May-15 17:37 UTC
[Rails] Databases... write master, multiple read-only slaves.. will this work?
On May 15, 2006, at 10:25 AM, Philip Hallstrom wrote:> I have a need to have my models write to one database and read from > another. From what I''ve read, Rails doesn''t do this out of the > box. I came up with something that does work, but I''m wondering if > there is something I''m missing in the code below that''s going to > bite me, such as establish_connection creating a huge number of > objects as time progresses. Or is there a better way to flip the > connection back and forth?Opening and closing connections is costly. Ideally, you''d pool them both. If you''re running PostgreSQL, however, pgpool can get you started with master/slave balancing with no code changes. http://pgpool.projects.postgresql.org/ Best, jeremy
Philip Hallstrom
2006-May-15 17:49 UTC
[Rails] Databases... write master, multiple read-only slaves.. will this work?
> On May 15, 2006, at 10:25 AM, Philip Hallstrom wrote: >> I have a need to have my models write to one database and read from >> another. From what I''ve read, Rails doesn''t do this out of the box. I >> came up with something that does work, but I''m wondering if there is >> something I''m missing in the code below that''s going to bite me, such as >> establish_connection creating a huge number of objects as time progresses. >> Or is there a better way to flip the connection back and forth? > > Opening and closing connections is costly. Ideally, you''d pool them both.That''s what I couldn''t figure out... it seems like establish_connection doesn''t actually open/close it just "saves" it somewhere and when it gets used, then it gets setup. But I''ll admit I got a bit lost in the active_record source... I tried moving those calls to the top, then doing @connection = :writemaster, but that *didnt''* work.> If you''re running PostgreSQL, however, pgpool can get you started with > master/slave balancing with no code changes. > > http://pgpool.projects.postgresql.org/I wish, but alas, no. Mysql. I''ve even searched for a "mypool" but nothing exists... -philip
James Moore
2006-May-16 03:12 UTC
[Rails] Databases... write master, multiple read-only slaves.. will this work?
> Opening and closing connections is costly. Ideally, you''d pool them > both.I''d say this is in the "that depends" category. We got quite acceptable performance out of mysql even a couple years ago doing connect/<select/insert/whatever>/disconnect operations on a fairly large-scale consumer facing site. (MP3.com; specifically, the my.mp3.com bits). On the other hand, no one sane would attempt this talking to Oracle. Particularly if you''re talking to an instance that''s on the same machine (common for slaves; we just slapped a mysql slave db on every server) the overhead of the connection setup and teardown just isn''t that large. You''ve probably got other places to worry about optimizing before this one. - James Moore
Philip Hallstrom
2006-May-16 04:15 UTC
[Rails] Databases... write master, multiple read-only slaves.. will this work?
>> Opening and closing connections is costly. Ideally, you''d pool them >> both. > > I''d say this is in the "that depends" category. We got quite acceptable > performance out of mysql even a couple years ago doing > connect/<select/insert/whatever>/disconnect operations on a fairly > large-scale consumer facing site. (MP3.com; specifically, the my.mp3.com > bits). On the other hand, no one sane would attempt this talking to Oracle. > > > Particularly if you''re talking to an instance that''s on the same machine > (common for slaves; we just slapped a mysql slave db on every server) the > overhead of the connection setup and teardown just isn''t that large. You''ve > probably got other places to worry about optimizing before this one.Now unless I''m mistaken, isn''t this a moot issue since the only times the connection switch will happen is *if* I save a record? If the updates are only coming from a single "content update server", then shouldn''t I not have any performance hit at all? My concern was more along the lines of does multiple calls establish_connection with the same parameters generate multiple objects, or just update some existing hash table? -philip
Kris
2006-May-16 13:19 UTC
[Rails] Re: RE: Databases... write master, multiple read-only slaves
There must be a standard way of doing this, there are a load of Rails deployment setups out there that use replicated Memcached read databases and a Master write database... Does any know how they do this? Is it coded in Rails or is it dealt with by the some kind of proxy/router before the databases? -- Posted via http://www.ruby-forum.com/.
James Moore
2006-May-16 14:01 UTC
[Rails] Databases... write master, multiple read-only slaves..will this work?
grep -r establish_connection /cygdrive/c/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/ | less These lines look interesting: /cygdrive/c/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib /active_record/base.rb: # Connections are usually created through ActiveRecord::Base.establish_c onnection and retrieved by ActiveRecord::Base.connection. /cygdrive/c/InstantRails/ruby/lib/ruby/gems/1.8/gems/activerecord-1.14.2/lib /active_record/base.rb lines 206-262 I think answer the question: # == Connection to multiple databases in different models # # Connections are usually created through ActiveRecord::Base.establish_connection and retrieved by ActiveRecord::Base.connection. # All classes inheriting from ActiveRecord::Base will use this connection. But you can also set a class-specific connection. # For example, if Course is a ActiveRecord::Base, but resides in a different database you can just say Course.establish_connection # and Course *and all its subclasses* will use this connection instead. # # This feature is implemented by keeping a connection pool in ActiveRecord::Base that is a Hash indexed by the class. If a connection is # requested, the retrieve_connection method will go up the class-hierarchy until a connection is found in the connection pool. And I''d go read the code itself if I needed more details (or, if I weren''t on my way to get my morning coffee...), but the comments are pretty straightforward here. - James
kris
2006-May-16 18:46 UTC
[Rails] Re: RE: Databases... write master, multiple read-only slaves
I think that code allows you to specify one database per model, not two (one for read & one for write)... James Moore wrote:> > # == Connection to multiple databases in different models > # > # Connections are usually created through > ActiveRecord::Base.establish_connection and retrieved by > ActiveRecord::Base.connection. > # All classes inheriting from ActiveRecord::Base will use this > connection. > But you can also set a class-specific connection. > # For example, if Course is a ActiveRecord::Base, but resides in a > different database you can just say Course.establish_connection > # and Course *and all its subclasses* will use this connection > instead. > # > # This feature is implemented by keeping a connection pool in > ActiveRecord::Base that is a Hash indexed by the class. If a connection > is > # requested, the retrieve_connection method will go up the > class-hierarchy > until a connection is found in the connection pool. > > And I''d go read the code itself if I needed more details (or, if I > weren''t > on my way to get my morning coffee...), but the comments are pretty > straightforward here. > > - James-- Posted via http://www.ruby-forum.com/.
See this thread: http://www.ruby-forum.com/topic/70740#96624 -- Posted via http://www.ruby-forum.com/. --~--~---------~--~----~------------~-------~--~----~ You received this message because you are subscribed to the Google Groups "Ruby on Rails: Talk" group. To post to this group, send email to rubyonrails-talk-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org To unsubscribe from this group, send email to rubyonrails-talk-unsubscribe-/JYPxA39Uh5TLH3MbocFFw@public.gmane.org For more options, visit this group at http://groups.google.com/group/rubyonrails-talk?hl=en -~----------~----~----~----~------~----~------~--~---