Solved

Cluster over unreliable network

Posted on 2006-10-29
15
358 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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
Enterprise Mobility and BYOD For Dummies

Like “For Dummies” books, you can read this in whatever order you choose and learn about mobility and BYOD; and how to put a competitive mobile infrastructure in place. Developed for SMBs and large enterprises alike, you will find helpful use cases, planning, and implementation.

 
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

Get Database Help Now w/ Support & Database Audit

Keeping your database environment tuned, optimized and high-performance is key to achieving business goals. If your database goes down, so does your business. Percona experts have a long history of helping enterprises ensure their databases are running smoothly.

Question has a verified solution.

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

Introduction This article is intended for those who are new to PHP error handling (https://www.experts-exchange.com/articles/11769/And-by-the-way-I-am-New-to-PHP.html).  It addresses one of the most common problems that plague beginning PHP develop…
Containers like Docker and Rocket are getting more popular every day. In my conversations with customers, they consistently ask what containers are and how they can use them in their environment. If you’re as curious as most people, read on. . .
This tutorial will walk an individual through configuring a drive on a Windows Server 2008 to perform shadow copies in order to quickly recover deleted files and folders. Click on Start and then select Computer to view the available drives on the se…
This tutorial will walk an individual through the process of installing the necessary services and then configuring a Windows Server 2012 system as an iSCSI target. To install the necessary roles, go to Server Manager, and select Add Roles and Featu…

751 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