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 -- Posted via http://www.ruby-forum.com/.
On Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan Forootan 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 Forootan 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 Hallstrom <rails@philip.pjkh.com> wrote:> > > On Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan Forootan 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 > _______________________________________________Anyone else? I''d be interested in an answer to this too. Eaden -------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060722/40f80ff6/attachment.html
On Jul 21, 2006, at 10:10 PM, Eaden McKee wrote:> On 7/20/06, Philip Hallstrom <rails@philip.pjkh.com> wrote: > On > Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan Forootan 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 > _______________________________________________ > > 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 Mornini
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 7/21/06, Tom Mornini <tmornini@infomania.com> wrote:> > On Jul 21, 2006, at 10:10 PM, Eaden McKee wrote: > > > On 7/20/06, Philip Hallstrom <rails@philip.pjkh.com> wrote: > On > > Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan Forootan 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 > > _______________________________________________ > > > > 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 Mornini > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060722/9241da3d/attachment.html
On Jul 22, 2006, at 9:19 AM, Jeff Everett wrote:> On 7/21/06, Tom Mornini <tmornini@infomania.com> wrote: >> On Jul 21, 2006, at 10:10 PM, Eaden McKee wrote: >> >> > On 7/20/06, Philip Hallstrom <rails@philip.pjkh.com> wrote: > On >> > Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan Forootan 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? >> >> 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? > > 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 Mornini
Tom Mornini 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 MorniniIf 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 -- Posted via http://www.ruby-forum.com/.
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 7/22/06, Tom Mornini <tmornini@infomania.com> wrote:> > On Jul 22, 2006, at 9:19 AM, Jeff Everett wrote: > > > On 7/21/06, Tom Mornini <tmornini@infomania.com> wrote: > >> On Jul 21, 2006, at 10:10 PM, Eaden McKee wrote: > >> > >> > On 7/20/06, Philip Hallstrom <rails@philip.pjkh.com> wrote: > On > >> > Wed, Jul 19, 2006 at 09:27:45PM +0200, Dan Forootan 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? > >> > >> 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? > > > > 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 Mornini > > _______________________________________________ > Rails mailing list > Rails@lists.rubyonrails.org > http://lists.rubyonrails.org/mailman/listinfo/rails >-------------- next part -------------- An HTML attachment was scrubbed... URL: http://wrath.rubyonrails.org/pipermail/rails/attachments/20060722/9c8f5783/attachment.html
On Jul 22, 2006, at 11:05 AM, Jeff Everett wrote:> 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.htmlThanks, 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 Mornini
On Jul 22, 2006, at 10:52 AM, Julien Schmurfy wrote:> Tom Mornini 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. > > 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-1Thanks, Julien. -- -- Tom Mornini
Tom Mornini <tmornini@...> writes:> > On Jul 22, 2006, at 11:05 AM, Jeff Everett wrote: > > > 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 > > 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. >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. 1. 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. 2. 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. 3. 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!
>>>> >> 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? >>> >>> 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? >> >> 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.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 Forootan <dforootan@ezpublishing.com> wrote:> > Tom Mornini <tmornini@...> writes: > > > > > On Jul 22, 2006, at 11:05 AM, Jeff Everett wrote: > > > > > 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 > > > > 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. > > > > > 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. > > 1. 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. > > 2. 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. > > 3. 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 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 Freemyer The Norcross Group Forensics for the 21st Century