Hello:
We will be setting up MySQL replication for a load balanced environment,
but we have to separate reads and writes.
How can we modify rails to do so? Has anyone done this already?
Thank you in advance!
Dan
On Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan F. wrote:
We will be setting up MySQL replication for a load balanced environment,
but we have to separate reads and writes.
No you don’t.
How can we modify rails to do so? Has anyone done this already?
You don’t have to.
- Matt
On Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan F. wrote:
We will be setting up MySQL replication for a load balanced environment,
but we have to separate reads and writes.No you don’t.
Can you elaborate on this? If you have a simple setup with a single
master, how can you not need to split reads and writes to make sure
the
writes go to the master?
-philip
Hi,
On 7/20/06, Philip H. [email protected] wrote:
writes go to the master?
-philip
Anyone else? I’d be interested in an answer to this too.
Eaden
On Jul 21, 2006, at 10:10 PM, Eaden McKee wrote:
sure the
writes go to the master?-philip
Anyone else? I’d be interested in an answer to this too.
I’m not speaking for Philip, but I think he might have been
suggesting that you’re going about load balancing MySQL the
wrong way. Why not just use their clustering stuff, and get
a simpler solution?
–
– Tom M.
Replication and load balancing are totally different issues. I take
Dan’s
original question to mean load balancing is already set up on his
network,
now he wants to split his database onto different machines using
replication
to handle the already balanced load. And Rails needs access to those
multiple db servers.
Technically you don’t need to separate reads/writes, because a slave can
also be a master. With 2 machines A can be master to B, and B master to
A.
With a larger group you can chain the relations together A to B, B to C,
C
to A, etc. as long as each slave has only one master.
But, you might be able to split reads/writes in Rails by having
appropriate
connections in database.yml (one for reading, one for writing), then
making
sure your methods use the appropriate connection with
establish_connection.
I do think that could get messy in a hurry, but it might be an option.
TM2C,
Jeff
On Jul 22, 2006, at 9:19 AM, Jeff Everett wrote:
Can you elaborate on this? If you have a simple setup with a
Replication and load balancing are totally different issues. I take
Dan’s original question to mean load balancing is already set up on
his network, now he wants to split his database onto different
machines using replication to handle the already balanced load. And
Rails needs access to those multiple db servers.
I’m clear that they’re separate issues, but MySQL since 5.0 has had a
clustering
option which handles both replication and load balancing as a
combination, giving
a very good solution to the problem Dan is trying to solve.
In addition, it guarantees coherency across the cluster so that
several machines
act as one large DB.
Also, are you certain you can do two way replication as you’ve
suggested? I’m
curious as to what happens when both machines modify the same row
concurrently.
Additionally, I’ve never heard anyone else suggest it was possible,
or suggest
they were doing so.
–
– Tom M.
Tom M. wrote:
On Jul 22, 2006, at 9:19 AM, Jeff Everett wrote:
Can you elaborate on this? If you have a simple setup with a
Replication and load balancing are totally different issues. I take
Dan’s original question to mean load balancing is already set up on
his network, now he wants to split his database onto different
machines using replication to handle the already balanced load. And
Rails needs access to those multiple db servers.I’m clear that they’re separate issues, but MySQL since 5.0 has had a
clustering
option which handles both replication and load balancing as a
combination, giving
a very good solution to the problem Dan is trying to solve.In addition, it guarantees coherency across the cluster so that
several machines
act as one large DB.Also, are you certain you can do two way replication as you’ve
suggested? I’m
curious as to what happens when both machines modify the same row
concurrently.
Additionally, I’ve never heard anyone else suggest it was possible,
or suggest
they were doing so.–
– Tom M.
If you want a real example of a MySQL replication with two masters I
found something that might interest you there:
http://poocs.net/articles/2006/03/13/the-adventures-of-scaling-stage-1
Yes, you can absolutely replicate in the fashion I have suggested. You
can
see High Performance MySQL from O’Reilly (ISBN 0-596-00306-4), if you
need
someone else to suggest to you that it will work.
Or maybe the MySQL documentation, specifically the following two
sections
which discuss some of the issues with multiple-master replication:
http://dev.mysql.com/doc/refman/5.0/en/replication-faq.html
http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html
However, if you have MySQL 5, the hardware to support it, and can meet
the
network and security constraints: cluster away. It’s a good option as
well.
Jeff
On Jul 22, 2006, at 10:52 AM, Julien S. wrote:
I’m clear that they’re separate issues, but MySQL since 5.0 has had a
clustering option which handles both replication and load
balancing as a
combination, giving a very good solution to the problem Dan is
trying to
solve.If you want a real example of a MySQL replication with two masters I
found something that might interest you there:
0歳の日帰り旅行はいつから大丈夫？持ち物や注意点も知ろう！
Thanks, Julien.
–
– Tom M.
On Jul 22, 2006, at 11:05 AM, Jeff Everett wrote:
http://dev.mysql.com/doc/refman/5.0/en/replication-auto-increment.html
Thanks, Jeff.
It’s weird that I’ve never run into that. Every example I’ve ever
seen was master/slave.
I guess it comes from hardly paying any attention to MySQL at all. 
–
– Tom M.
Tom M. <tmornini@…> writes:
replication:
Dear All:
Thank you for the input. A couple points of clarification on what
and why we are thinking of going down the replciation route.
-
We’ve tested mysql 5.0, and the memory only cluster option
that it offers would result in us having to have A LOT of memory
on our nodes. Our databases are huge. -
We’ve tested mysql 5.1 cluster w/ the disk option and it is full
of bugs w/ the current beta release. In the future, when a stable
release is made we will probably go down this route. -
So our next option was Master/Slave replication.
My initial research in this matter indicated that WRITES are done
to the MASTER, and reads are done to a CNAME or other load
balancing setup through the SLAVES. By default however, Ruby only
makes one connection, so my first instinct was to see if anyone on
this list had done this yet.
Now from what I’m reading, it seems that the consensus is that
this isn’t required, and that a multiple MASTER environment will help.
Can you please clarify. Is what being suggested basically the option
to create a chain of all MASTERS? So that way the load balancing
setup would be communicating to strictly masters?
Thank you for everyone’s help!
I’m not speaking for Philip, but I think he might have been
I’m clear that they’re separate issues, but MySQL since 5.0 has had a
clustering
option which handles both replication and load balancing as a combination,
giving
a very good solution to the problem Dan is trying to solve.
There are limits to the NDB cluster though… 8k max row lengths
(excluding blobs), updates/deletes can’t operate on more than 32K rows,
select queries with an OR’d LIKE don’t work, etc…
It does solve the problem though if you can work around these.
On 7/22/06, Dan F. [email protected] wrote:
seen was master/slave.
- We’ve tested mysql 5.0, and the memory only cluster option
to the MASTER, and reads are done to a CNAME or other loadThank you for everyone’s help!
I don’t know the specifics, but I went to a presentation by a very
well known, high-volume website a couple days ago.
They said there most critical data is sourced realtime from other
services (gov’t, I believe) and distributed thru a 19-node hierarchal
MySql replication farm. They claim to have had very few issues in the
18-months it have been using replication in production.
OTOH, their less critical (to them) user registration, polls, etc. is
handled by a 4-node MySQL replication ring. Here each node accepts
writes and forwards them to the next node in the ring. When a write
gets back around to the originator, the node-id is a match and
therefore it gets dropped (as required since it got applied at the
beginning.). Be sure you have unique node-ids.
Greg
Greg F.
The Norcross Group
Forensics for the 21st Century