List:Replication« Previous MessageNext Message »
From:Rick James Date:September 3 2008 8:02pm
Subject:RE: Thoughts on MySQL replication
View as plain text  
I meant to have each replication thread dedicated to one (maybe more)
database.  That is similar to your wanting one thread for each master.

 
Rick James
MySQL Geeks - Consulting & Review

 

> -----Original Message-----
> From: Simon J Mudd [mailto:sjmudd@stripped] 
> Sent: Saturday, August 30, 2008 12:02 AM
> To: replication@stripped
> Subject: Re: Thoughts on MySQL replication
> 
> On Fri, Aug 29, 2008 at 10:35:33AM -0700, Rick James wrote:
> > > ...
> > > That would give you more paralellism but really that's irrelevant:
> > > you're solving a different problem to the problem solved by the
> > > current built-in replication functionality provided by mysqld.
> > 
> > Well, the next step...  Back to the traditional 1->N replication.
> > Performance on the slave can be poor because of being 
> single-threaded.
> > With your multi-source code, one could extend things further to have
> > multiple binlogs coming out of the 1 master and being applied in
> > parallel on each slave.  This would allow [given that you 
> "know what you
> > are doing"] you to configure traditional replication to run 
> faster on
> > slaves.  :)
> 
> I think that's unlikely to work very well, unless you have separate
> threads per database. However you still have the issue that to achieve
> the same DML affects with replication you have to follow the same time
> order of executing the SQL commands as done on the source db. If you
> don't do that your slaves may end up with different data. So 
> you can only
> achieve a theoretical better performance if you split up the 
> replication
> process by database.  Perhaps my understanding is wrong.
> 
> > I have one system with 100 dbs.  These dbs are virtually 
> independent.
> > Sometimes a flood of data goes into one db, thereby impacting any
> > inserts into other dbs.  With the above code, I could have multiple
> > threads running on the slaves and get some parallelism, and 
> improve the
> > SLAs.
> 
> I'm not so sure that will work. I work with similar systems and see
> the same issue. However you really can't change the order of statement
> execution unless you don't care that the result on the slaves may be
> different.
> 
> It might be possible, and I don't know if this is what you 
> are hinting at
> that groups of tables within a database are replicated indepedently of
> other tables.  If you choose how you do this _very_ carefully 
> then yes,
> perhaps you can achieve better performance.
> 
> Also row-based replication works very well and hides many of 
> the delays
> seen with statement based replication but with BIG SQL DML changes you
> generate the same problem: lots of rows to replicate and thus delays.
> I think MySQL will try to select between row- and statement-based
> intelligently but whatever you do BIG changes take time to execute on
> any DB server, whether it's MySQL, Sybase, Oracle or whatever.
> 
> In any case for me I'd love to be able to do N:1 replication and the
> original idea to have a separate mysql replication process capable
> of doing this would be great. It may well help solve other issues
> like the one you suggest.
> 
> Simon
> 
> -- 
> MySQL Replication Mailing List
> For list archives: http://lists.mysql.com/replication
> To unsubscribe:    
> http://lists.mysql.com/replication?unsub=1
> 
> 
Thread
Thoughts on MySQL replicationSimon J Mudd28 Aug
  • Re: Thoughts on MySQL replicationJeremy Zawodny28 Aug
    • Re: Thoughts on MySQL replicationJeremy Cole28 Aug
      • Re: Thoughts on MySQL replicationSimon J Mudd29 Aug
    • Re: Thoughts on MySQL replicationSimon J Mudd29 Aug
  • RE: Thoughts on MySQL replicationRick James28 Aug
    • Re: Thoughts on MySQL replicationSimon J Mudd29 Aug
      • RE: Thoughts on MySQL replicationRick James29 Aug
        • Re: Thoughts on MySQL replicationSimon J Mudd30 Aug
          • RE: Thoughts on MySQL replicationRick James3 Sep