List:General Discussion« Previous MessageNext Message »
From:SGreen Date:June 20 2005 9:03pm
Subject:Re: Will multiple MASTER threaded writes break replication?
View as plain text  
Kevin Burton <burton@stripped> wrote on 06/20/2005 01:35:38 PM:

> We're noticing a problem where if we were to write to the master with
> multiple threads that our slave DB will fall behind.

> Note that we're trying to perform as many inserts as humanly possible
> and the load on the master is 1.

> My theory is that the master, since it can write to multiple tables, is
> faster due to the IO controller being able to more efficiently command
> queue and buffer IO.

> Since replication is only one thread its not able to benefit from these
> optimizations and hence is prone to falling behind.

> Kevin

> --

> 
> Use Rojo (RSS/Atom aggregator)! - visit http://rojo.com.
> See irc.freenode.net #rojo if you want to chat.

> Rojo is Hiring! - http://www.rojonetworks.com/JobsAtRojo.html

> Kevin A. Burton, Location - San Francisco, CA
> AIM/YIM - sfburtonator,  Web - http://peerfear.org/
> GPG fingerprint: 5FB2 F3E2 760E 70A8 6174 D393 E84D 8D04 99F1 4412

To answer your subject line: No, and for the very reason you state. All of 
the master commands are serialized. You slave won't be broken, just slower 
because each command has to happen after the one before while your 
multi-user commands can interleave (assuming the row/table locks let them) 
on the master. However as each transaction on the master commits its 
changes (the binlog is nearly 100%INSERTS, UPDATES, and DELETES with a few 
administrative commands thrown in) the master writes what just committed 
to the BINLOG. Because the master is logging a statement only as it 
completes, the BINLOG should provide to the slave a serialized record of 
the changes that occur on the master. 

However, if you create changes on the master AND changes on the slave, 
that _will_ break things. Let me walk you through a scenario. An INSERT 
happens on the master to TableA and autogenerates the PK 9368. Someone 
else does an INSERT on the slave's copy of TableA, also generating the PK 
9368. When the command to create that row makes it from the Master through 
the binlog to the slave, you will generate an error because the slave 
won't be able to have two rows with the same PK value.

What is missing from MySQL to allow for multi-master replication is (at 
least) the facility necessary to make multi-server locking work. They are 
close to achieving this in the Cluster product but it's not available in 
any other engine. In our made-up scenario, the slave would need to 
"reserve" the auto_inc value 9386 for itself AND on the master the create 
the record. That way the master couldn't make a duplicate record. 
Multi-master updates are VERY hard to implement because what happens if 
BOTH servers are still up but only their connection is lost. If they BOTH 
think they are the surviving service then BOTH would carry on (in an 
attempt to minimize service loss). Should their connections to each other 
be restored, chaos will ensue until they can be brought back into synch. 
That's one of the hardest scenarios to deal with and the developers are 
working on control methods to mitigate such scenarios. Like I said, it's 
coming but not yet.

Shawn Green
Database Administrator
Unimin Corporation - Spruce Pine


Thread
Will multiple MASTER threaded writes break replication?Kevin Burton20 Jun
  • Re: Will multiple MASTER threaded writes break replication?SGreen20 Jun
  • Re: Will multiple MASTER threaded writes break replication?Kevin Burton20 Jun
    • Re: Will multiple MASTER threaded writes break replication?Ware Adams21 Jun
  • Re: Will multiple MASTER threaded writes break replication?Atle Veka22 Jun
    • Re: Will multiple MASTER threaded writes break replication?Kevin Burton22 Jun
      • Re: Will multiple MASTER threaded writes break replication?Atle Veka22 Jun