3

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.

Improve this question
5
  • 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
0

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".

Improve this answer
1
  • 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

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

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