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