Rails - May 2006 - 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

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

> 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

> 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

>> 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

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/.

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

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
-~----------~----~----~----~------~----~------~--~---