Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

We have our main MySQL server in city A. We have the bulk of our customer service staff in city B. The staff are complaining that the connection is too slow to the server. The options are:

  1. Have them go out over the public internet instead of our direct link between cities; that way, ping might be longer but they aren't competing with the VOIP traffic between cities

  2. Cluster the database so that writes and reads can happen simultaneously on local servers in both cities.

2 is much preferable, but as you can probably tell by my description I have absolutely no clue how to implement it or even if it is 'clustering.' The best I could find for MySQL Cluster is that it would require using NDB tables and I'd rather not convert our entire database to that. What are my options here? Thanks.

share|improve this question
    
You probably want Replication, not clustering. –  ypercube Apr 3 '12 at 21:27
    
We have replication - from the master in city A to the slave in city B. But the staff in B need full read-write access, which still means going through the database in city A, which means slowness. –  Andrew Apr 4 '12 at 13:32
    
You could set the master at city B (but that would mean slowness in city A, if that is an option anyway) –  ypercube Apr 4 '12 at 13:48
    
I've read about (but never used) Master-Master replication (or Dual-Master replication). Perhaps someone who has actual experience can recommend. –  ypercube Apr 4 '12 at 13:54
    
Yeah, we can't move masters; just because B's complaining now doesn't mean A wouldn't complain if we switched cities. :) –  Andrew Apr 4 '12 at 14:14

1 Answer 1

up vote 0 down vote accepted

How far apart (ping time) are the two cities? 80ms is what we experience going across the US. It is not bad.

Writing to both heads of Master-Master is possible, but has lots of pain points.

NDB Cluster allows hot-hot, but (as you say) requires some conversion.

So, back to what I see as the only viable solution: A single writable master, plus any number of slaves.

One thing that can make a remote master painful is if the user's "unit" of action translates into many SQL statements. That can/should be solved by (1) Rethink the code to use fewer statements (2) Use a Stored Procedure to encapsulate as many of the SQL statements as possible, then deploy that on the remote Master.

Reads (other than "critical reads") can/should go to a slave, behind a load balancer. And some mechanism should ensure that reads are usually "local".

share|improve this answer
    
It's not the ping; it's the fact that we're sending all connections over a T1 line that also carries our phones, so there's a healthy amount of bandwidth competition. And yes, I'm positive that a rewrite would probably help a lot. –  Andrew Apr 6 '12 at 13:47

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.