We help IT Professionals succeed at work.

Cluster over unreliable network

Chatable
Chatable asked
on
Medium Priority
388 Views
Last Modified: 2013-11-15
Hello DB experts,
We are trying to create a website using a MySQL backend. We would like to create a few mirror servers for the site. We thought that instead of manually syncing the servers with each-other, we could use the clustering feature of MySQL and simply make them use the same database.
Unfortunately, although we have some database experience, none of us has ever been a DBA of a huge database and we have never used this feature before. Another issue that troubles us is the fact that the mirrors may very well be very far from each-other (in different countries) - and connected only through the Internet.
My question is mostly theoretical - How well does a MySQL cluster preform over an unreliable network such as the Internet. Please relate to issues such as the network being slow, servers being unreachable for some time (due to network problems) and even servers crashing unexpectedly.
Also if you think that this is a good solution for my case, please advise on a good way to implement this. If you think this is a bad idea, please offer alternatives.

Thank you all!
Comment
Watch Question

Commented:
Read about the replication http://dev.mysql.com/doc/refman/5.0/en/replication.html
You can have one master and several slaves. All inserts, updates and deletes must be done on the master, selects can you make on the slave(s).
Top Expert 2006

Commented:
Hi,

In my experience having a very distributed cluster does not work - becuase the clusters are always talking to each other there is increased traffic over the network.  there is also more time in maintaining a cluster than there is having replication.

I'd suggest that if you have this solution that you have replication setup on the same schema - if you setup an offset for your auto incrment tables and an increment value of the number of servers you have then there should not be a problem.

the only problem with this solution is how long the updates are going to be apart on the various servers - if they are a long time and you are not just using incriments as unique keys on your tables you can end up with duplicate key errors which need to be monitored.  This is an issue because mysql will continue loading the binary log files from the databases you are replicating from but will not be executed until you have decided what to do with the error - also coordinating and verifying the data on all servers can be difficult,

but if you have network reliability problems it should be beter with replication than with clustering

Commented:
Network being slow
 MySQL Cluster does not perform well when each node is segregated.  Cluster has very high requirements for the current version, it must be in a gigabit network (all Nodes) and data & indices must be stored in memory.  Cluster uses specific algothrims and a 'time frame' to determine if the nodes are available. If they are not due to a slow network, server crash, etc. then the transaction is rolled back.

I recommend racek's and Raynard7's alternative solution of using Replication.

Author

Commented:
I understand, but (correct me if I'm wrong) - this will disable any interactive feature on the website (i.e. if it has a forum, users will only be able to post on the main server). Right?

Author

Commented:
chigs20 - "If they are not due to a slow network, server crash, etc. then the transaction is rolled back." - You mean that if one server is down then the database cannot be updated at all?
Top Expert 2006
Commented:
Hi,

With clustering if one server is down there should be no problem -as the cluster would account for that (providing you were using at least 4 servers)

With replication you can get tables replicating to the same table on different servers (basically a chain) so if you make changes to server a table a this will be populated on server b table a - and if you make changes to server b table a they will be replicated on server a table a - provided they are setup correctly.

For an unreliable network this should better suit your needs.

Not the solution you were looking for? Getting a personalized solution is easy.

Ask the Experts

Commented:
Sorry, I clumped my response to your initial questions.
Slow Network: MySQL Cluster has a set time to get an initial response from each server before each request.  In the event the server does not get a return request within sub-seconds (stating the servers are awake and ready for requests) then the cluster assumes the servers are not ready and will not execute the query.  This is why MySQL AB highly recommends you use a Gigabit network and that all the servers are in the same location.

Redundancy / High Availability: As long as you have one server w/ all the data then it will remain up (as Raynard7 stated)

Author

Commented:
Raynard7 - According to the MySQL reference manual:
"MySQL features support for one-way, asynchronous replication, in which one server acts as the master, while one or more other servers act as slaves. This is in contrast to the synchronous replication which is a characteristic of MySQL Cluster (see Chapter 15, MySQL Cluster)."
What I understand from this is that the master can update the slave but not the other way around.

Commented:
That is correct.  The slave cannot update the master (it's a one way channel).
Top Expert 2006

Commented:
A slave can not act as a master,

however a master can also be a slave.

Author

Commented:
Raynard7 - "master can also be a slave", you mean that suppose that a is the master of b, then b can be the master of c, but b cannot be the master of a.
In other words, I cannot keep a two-way sync between my servers without implementing it myself.
Top Expert 2006

Commented:
no,

a can be the master of b and b can be the  master of a for the same tables.  

Commented:
Correct, if you want asynchronous 2-way sync you have to set it up yourself.  That may cause errors, though, both logical and physical.  ie, if both servers update a field and can't talk to each other, then server a will eventually get server b's request and server b will eventually get server a's request, and they won't be sync'd.

Now imagine dropping a table before a lagging server is done with it -- you'll cause a replication error and replication will break, by trying to update a table that doesn't exist.

If you want 2-way sync, that's cluster.  If you want asynchronous 1-way sync, that's replication.  
Top Expert 2006

Commented:
>> If you want 2-way sync, that's cluster.  If you want asynchronous 1-way sync, that's replication.

granted - this is normally how you would do it - however if you have an unreliable or slow network cluster will give you more errors than an asynchronous 2-way synch

Author

Commented:
Well, I looked deep into the documentation and like Raynard7 said, two-way replication is possible but they also note it could lead to sync problems. As I cannot guarantee that the tables won't be modified at the same time, it's not a perfect solution. Maybe some additional client code will help. Thank you for your advice.
Access more of Experts Exchange with a free account
Thanks for using Experts Exchange.

Create a free account to continue.

Limited access with a free account allows you to:

  • View three pieces of content (articles, solutions, posts, and videos)
  • Ask the experts questions (counted toward content limit)
  • Customize your dashboard and profile

*This site is protected by reCAPTCHA and the Google Privacy Policy and Terms of Service apply.

OR

Please enter a first name

Please enter a last name

8+ characters (letters, numbers, and a symbol)

By clicking, you agree to the Terms of Use and Privacy Policy.