Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Why does everyone tell me that master-master always ends in tears and should be avoided?

share|improve this question

5 Answers 5

up vote 4 down vote accepted

Because no two people use the term exactly the same, no two people setup the servers exactly the same, nobody automates failover the same, and very few people have the mysql mastery to pull it off in a way that actually helps the environment rather than just adding complexity.

It will end in tears, but if you do it right they're your tears not your employers or customers tears. And after all, isn't that what sysadmins are for?

Here's some of the best reading you can do on it: http://www.mysqlperformanceblog.com/2009/10/09/finding-your-mysql-high-availability-solution-the-definitions/
http://www.mysqlperformanceblog.com/2009/10/16/finding-your-mysql-high-availability-solution-–-the-questions/
http://www.mysqlperformanceblog.com/2009/11/13/finding-your-mysql-high-availability-solution-–-replication/

share|improve this answer
4  
"if you do it right"... caveat of the century. –  womble Dec 8 '09 at 22:50
1  
It's not entirely up to the sysadmin though. Even if you do your job absolutely perfectly, your developers are going to need to change the way they think of their CRUD operations in order to be more tolerant (EG INSERT ... ON DUPLICATE KEY UPDATE instead of plain INSERT) –  rodjek Dec 8 '09 at 22:51

Check out what the author Of High Performance MySQL (2ed) and the Maatkit tools has to say about the subject: http://www.xaprb.com/blog/2008/08/06/how-to-scale-writes-with-master-master-replication-in-mysql/

Cheers

share|improve this answer

Inevitably both will get updated in contradictory ways in between updates, and all hell will break loose...Or at least you'll get unpredictable behaviour.

Imagine a case where two people update the same record in different ways. Which one is right? In a Master<->Master environment, there is no right. Both records are equally correct. So it'll go through and apply them sequentially, causing a data inconsistency.

If you're using mysql, you also tend to end up with problems from row-level locking...Those rows often don't replicate. And you have to do an ugly hack to get around the problem of duplicate autonumber fields (server1 auto increments odd 1,3,5 and server2 auto increments even 2,4,6).

It's just not pretty.

share|improve this answer
1  
The technical term for this state is called split brain. –  3dinfluence Dec 8 '09 at 22:45

Because it always ends in tears and should be avoided.

More specifically, MySQL replication is fragile, and doing it twice in a row has some sort of exponential effect on the fail factor. Also, a pile of assumptions that developers make about what you can and can't do in your database suddenly disappear (like auto increment, unique indexes, etc etc) and whilst in theory you can train developers out of those bad assumptions, in practice you've got more chance of hitching a lift to the moon.

Oh, and the workarounds to the problems with multimaster replication (like the autoincrement skipping) mean that scaling by adding another DB server is a hellish process.

share|improve this answer
    
Hah! I hadn't even thought of trying to add a 3rd server. How do you set up autoincrement to increment by prime numbers? –  Satanicpuppy Dec 9 '09 at 1:16
    
I'm sure there's a rigorous mathematical proof thatthere exists integer values of X and Y such that X*A = Y*B, for all integer values of A and B. –  womble Dec 9 '09 at 1:55

Part of the issue is that there is pretty much never a reason to have a master-master environment. The right way to do things is to use caching layers and batch your writes to the database.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.