Solved

Cluster over unreliable network

Posted on 2006-10-29
15
357 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
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.

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

Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
MS Access - need to reduce row size 25 63
BACKUP of mysql database from mysql server - using Coldfusion 9 45
Mysql Left Join Case 10 101
MySQL Persistent Connections 10 30
The Delta outage: 650 cancelled flights, more than 1200 delayed flights, thousands of frustrated customers, tens of millions of dollars in damages – plus untold reputational damage to one of the world’s most trusted airlines. All due to a catastroph…
When table data gets too large to manage or queries take too long to execute the solution is often to buy bigger hardware or assign more CPUs and memory resources to the machine to solve the problem. However, the best, cheapest and most effective so…
This tutorial will walk an individual through setting the global and backup job media overwrite and protection periods in Backup Exec 2012. Log onto the Backup Exec Central Administration Server. Examine the services. If all or most of them are stop…
Two types of users will appreciate AOMEI Backupper Pro: 1 - Those with PCIe drives (and haven't found cloning software that works on them). 2 - Those who want a fast clone of their boot drive (no re-boots needed) and it can clone your drive wh…

679 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