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.
> 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.
Unimin Corporation - Spruce Pine