Chatable
asked on
Cluster over unreliable network
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!
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!
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
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
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.
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.
ASKER
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?
ASKER
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?
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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)
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)
ASKER
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.
"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.
That is correct. The slave cannot update the master (it's a one way channel).
A slave can not act as a master,
however a master can also be a slave.
however a master can also be a slave.
ASKER
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.
In other words, I cannot keep a two-way sync between my servers without implementing it myself.
no,
a can be the master of b and b can be the master of a for the same tables.
a can be the master of b and b can be the master of a for the same tables.
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.
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.
>> 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
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
ASKER
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.
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).