Re: Architectural Help
Hi Anupam, You can achive this by circular replication, Circular replication between 4 nodes works fine. but not sure with 50 node server how practical it was. Each database node will be a data center, *Node 1 - data center 1* *-* *Node 2 - Data center 2* *-* *Node 3 - Data Center 3* *-* *Node 4 - Data Center 4* *- .. Node 50 - Data Center - 50*. so basicaly it will Multimaster Replication System; Fail over :- eg. data center 20 have network issue , in that case Node 19 replicate from Node 21, bypass node 20 -- need to do some scripting. http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html Thank You. On Thu, May 24, 2012 at 4:41 PM, Rick James rja...@yahoo-inc.com wrote: I deal with dozens of systems, all doing cross-country or cross-ocean replication. The only viable approach (that we have deployed in production) is * Dual-master, single writer -- That is, all 49 clients write to one machine * The two masters are geographically separate. * Failover involves switching to the 'other' master. It is _not_ fully automated. * Slaves, as needed, scattered around the world -- This provides read scaling. 1M writes per day -- that is an average of 12/sec. Yawn. Replication delay -- you probably will almost never see any. Network outages -- one of many things that can cause trouble. The sooner you write the data _and_ copy it to a _remote_ site, the sooner you are immune to floods, tornados, cyclones, power outages, motherboard death, etc. -Original Message- From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 3:39 AM To: Nigel Wood Cc: mysql@lists.mysql.com Subject: Re: Architectural Help Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood nw...@plus.net To: Anupam Karmarkar sb_akarmar...@yahoo.com; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? Network Outages is problem, between data centres When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? Yes central server can update same row or local server but not at same time, Few data will be updated in central location and others majorly in local.One Application Writes in Centeral and Others in local, Local app and central app shares some data. Is there any natural segmentation point within the data? No Do the data centers have diverse networks so that connections to some data centers may remain when others? Yes, we have diverse nework , so connectivity will be avaiable in other data centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Loca application should always write in local database, Central Application Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? We dont have text search or we dont query large data Are you in a position to modify the applications? No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql -- Best Regards, Prabhat
RE: Architectural Help
Caution!! If any one of them dies and cannot be recovered, you will have a mess to fix. From: Prabhat Kumar [mailto:aim.prab...@gmail.com] Sent: Wednesday, May 30, 2012 1:43 PM To: Rick James Cc: Anupam Karmarkar; Nigel Wood; mysql@lists.mysql.com Subject: Re: Architectural Help Hi Anupam, You can achive this by circular replication, Circular replication between 4 nodes works fine. but not sure with 50 node server how practical it was. Each database node will be a data center, Node 1 - data center 1 - Node 2 - Data center 2 - Node 3 - Data Center 3 - Node 4 - Data Center 4 - .. Node 50 - Data Center - 50. so basicaly it will Multimaster Replication System; Fail over :- eg. data center 20 have network issue , in that case Node 19 replicate from Node 21, bypass node 20 -- need to do some scripting. http://onlamp.com/onlamp/2006/04/20/advanced-mysql-replication.html Thank You. On Thu, May 24, 2012 at 4:41 PM, Rick James rja...@yahoo-inc.commailto:rja...@yahoo-inc.com wrote: I deal with dozens of systems, all doing cross-country or cross-ocean replication. The only viable approach (that we have deployed in production) is * Dual-master, single writer -- That is, all 49 clients write to one machine * The two masters are geographically separate. * Failover involves switching to the 'other' master. It is _not_ fully automated. * Slaves, as needed, scattered around the world -- This provides read scaling. 1M writes per day -- that is an average of 12/sec. Yawn. Replication delay -- you probably will almost never see any. Network outages -- one of many things that can cause trouble. The sooner you write the data _and_ copy it to a _remote_ site, the sooner you are immune to floods, tornados, cyclones, power outages, motherboard death, etc. -Original Message- From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.commailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 3:39 AM To: Nigel Wood Cc: mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Re: Architectural Help Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood nw...@plus.netmailto:nw...@plus.net To: Anupam Karmarkar sb_akarmar...@yahoo.commailto:sb_akarmar...@yahoo.com; mysql@lists.mysql.commailto:mysql@lists.mysql.com mysql@lists.mysql.commailto:mysql@lists.mysql.com Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? Network Outages is problem, between data centres When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? Yes central server can update same row or local server but not at same time, Few data will be updated in central location and others majorly in local.One Application Writes in Centeral and Others in local, Local app and central app shares some data. Is there any natural segmentation point within the data? No Do the data centers have diverse networks so that connections to some data centers may remain when others? Yes, we have diverse nework , so connectivity will be avaiable in other data centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Loca application should always write in local database, Central Application Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? We dont have text search or we dont query large data Are you in a position to modify the applications? No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.commailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.commailto:mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we
Re: Architectural Help
is the central database server just ONE server, to which all your 50 data center app connects On Thu, May 24, 2012 at 2:47 PM, Anupam Karmarkar sb_akarmar...@yahoo.comwrote: Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam
RE: Architectural Help
A few questions: which is more or a problem: network outages, network capacity or query latency? When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Can any one of the 49 local servers potentially update/delete the same rows or data? Is there any natural segmentation point within the data? Do the data centers have diverse networks so that connections to some data centers may remain when others? In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Do your applications produce/examine large data set querying by secondary keys or using tull text search? Are you in a position to modify the applications? __ From: Anupam Karmarkar [sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql
Re: Architectural Help
Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood nw...@plus.net To: Anupam Karmarkar sb_akarmar...@yahoo.com; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? Network Outages is problem, between data centres When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? Yes central server can update same row or local server but not at same time, Few data will be updated in central location and others majorly in local.One Application Writes in Centeral and Others in local, Local app and central app shares some data. Is there any natural segmentation point within the data? No Do the data centers have diverse networks so that connections to some data centers may remain when others? Yes, we have diverse nework , so connectivity will be avaiable in other data centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Loca application should always write in local database, Central Application Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? We dont have text search or we dont query large data Are you in a position to modify the applications? No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql
RE: Architectural Help
I deal with dozens of systems, all doing cross-country or cross-ocean replication. The only viable approach (that we have deployed in production) is * Dual-master, single writer -- That is, all 49 clients write to one machine * The two masters are geographically separate. * Failover involves switching to the 'other' master. It is _not_ fully automated. * Slaves, as needed, scattered around the world -- This provides read scaling. 1M writes per day -- that is an average of 12/sec. Yawn. Replication delay -- you probably will almost never see any. Network outages -- one of many things that can cause trouble. The sooner you write the data _and_ copy it to a _remote_ site, the sooner you are immune to floods, tornados, cyclones, power outages, motherboard death, etc. -Original Message- From: Anupam Karmarkar [mailto:sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 3:39 AM To: Nigel Wood Cc: mysql@lists.mysql.com Subject: Re: Architectural Help Dear Nigel, Thank for reply.. See my comments below --Anupam From: Nigel Wood nw...@plus.net To: Anupam Karmarkar sb_akarmar...@yahoo.com; mysql@lists.mysql.com mysql@lists.mysql.com Sent: Thursday, 24 May 2012 3:35 PM Subject: RE: Architectural Help A few questions: which is more or a problem: network outages, network capacity or query latency? Network Outages is problem, between data centres When you say near real-time do you need transactional consistent view on all 49 servers or can some lag be tolerated? Some lag can be tolerated considering network outage Can any one of the 49 local servers potentially update/delete the same rows or data? Yes central server can update same row or local server but not at same time, Few data will be updated in central location and others majorly in local.One Application Writes in Centeral and Others in local, Local app and central app shares some data. Is there any natural segmentation point within the data? No Do the data centers have diverse networks so that connections to some data centers may remain when others? Yes, we have diverse nework , so connectivity will be avaiable in other data centers. In the event that a local data centre is totally isolated from the others what data should it be allowed to update? Loca application should always write in local database, Central Application Updated will not be available to local. Do your applications produce/examine large data set querying by secondary keys or using tull text search? We dont have text search or we dont query large data Are you in a position to modify the applications? No, Micro changes ok __ From: Anupam Karmarkar [sb_akarmar...@yahoo.com] Sent: Thursday, May 24, 2012 10:17 AM To: mysql@lists.mysql.com Subject: Architectural Help Hi All, I need architectural help for our requirement, We have nearly 50 data centre through out different cities from these data center application connect to central database server currently, there are conectivity and nework flcutions issues for different data center, so we comeup with solution each data center we should have local database server which will keep syncing with other server so that application doesnt fail , User data can be updated in any of server and should reflect in every server. Application consists of write/read/delete operations, Current writes each day central server 1million. Only 1/1000 need to be distrubuted acrross servce rest need to be in central server. How can we achive this ? solution needs very much real time data accepting nework lags. Solution Collect all changes in other 49 server into 1 central server(How can we collect data) 49 keeps updating data into local database from central server(Using Repliation Can be done) --Anupam -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe: http://lists.mysql.com/mysql -- MySQL General Mailing List For list archives: http://lists.mysql.com/mysql To unsubscribe:http://lists.mysql.com/mysql