Kevin Burton <[EMAIL PROTECTED]> 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
