From: Simon J Mudd
Date: August 30 2008 7:01am
Subject: Re: Thoughts on MySQL replication
List-Archive: http://lists.mysql.com/replication/1428
Message-Id: <20080830070151.GA13628@ams03.wl0.org>
MIME-Version: 1.0
Content-Type: text/plain; charset=us-ascii

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