Forum: Ruby on Rails Databases... write master, multiple read-only slaves.. will

Announcement (2017-05-07): www.ruby-forum.com is now read-only since I unfortunately do not have the time to support and maintain the forum any more. Please see rubyonrails.org/community and ruby-lang.org/en/community for other Rails- und Ruby-related community platforms.
Philip H. (Guest)
on 2006-05-15 21:23
(Received via mailing list)
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 K. (Guest)
on 2006-05-15 21:39
(Received via mailing list)
On May 15, 2006, at 10:25 AM, Philip H. 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 H. (Guest)
on 2006-05-15 21:51
(Received via mailing list)
> On May 15, 2006, at 10:25 AM, Philip H. 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 M. (Guest)
on 2006-05-16 07:13
(Received via mailing list)
> 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 M.
Philip H. (Guest)
on 2006-05-16 08:17
(Received via mailing list)
> 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 (Guest)
on 2006-05-16 17:19
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?
James M. (Guest)
on 2006-05-16 18:04
(Received via mailing list)
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 (Guest)
on 2006-05-16 22:46
I think that code allows you to specify one database per model, not two
(one for read & one for write)...


James M. 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
benv (Guest)
on 2007-03-21 23:54
This topic is locked and can not be replied to.