Solved

Cluster over unreliable network

Posted on 2006-10-29
15
352 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!
0
Comment
Question by:Chatable
  • 5
  • 5
  • 3
  • +2
15 Comments
 
LVL 14

Expert Comment

by:racek
ID: 17830244
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).
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17831547
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
0
 
LVL 6

Expert Comment

by:chigs20
ID: 17834888
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.
0
 
LVL 7

Author Comment

by:Chatable
ID: 17836692
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?
0
 
LVL 7

Author Comment

by:Chatable
ID: 17836708
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?
0
 
LVL 35

Accepted Solution

by:
Raynard7 earned 500 total points
ID: 17837472
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.
0
 
LVL 6

Expert Comment

by:chigs20
ID: 17837615
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)
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 7

Author Comment

by:Chatable
ID: 17852903
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.
0
 
LVL 6

Expert Comment

by:chigs20
ID: 17852994
That is correct.  The slave cannot update the master (it's a one way channel).
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17853280
A slave can not act as a master,

however a master can also be a slave.
0
 
LVL 7

Author Comment

by:Chatable
ID: 17861529
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.
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17861993
no,

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

Expert Comment

by:Sheeri
ID: 17868644
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.  
0
 
LVL 35

Expert Comment

by:Raynard7
ID: 17870922
>> 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
0
 
LVL 7

Author Comment

by:Chatable
ID: 17884443
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.
0

Featured Post

How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

A quick step-by-step overview of installing and configuring Carbonite Server Backup.
The article will include the best Data Recovery Tools along with their Features, Capabilities, and their Download Links. Hope you’ll enjoy it and will choose the one as required by you.
This tutorial will walk an individual through the steps necessary to enable the VMware\Hyper-V licensed feature of Backup Exec 2012. In addition, how to add a VMware server and configure a backup job. The first step is to acquire the necessary licen…
To efficiently enable the rotation of USB drives for backups, storage pools need to be created. This way no matter which USB drive is installed, the backups will successfully write without any administrative intervention. Multiple USB devices need t…

760 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now