Syncronizing Large Databases

Discussion of open issues, suggestions and bugs regarding ADO.NET provider for MySQL
Post Reply
muhammadd
Posts: 1
Joined: Wed 15 Nov 2017 16:32

Syncronizing Large Databases

Post by muhammadd » Wed 15 Nov 2017 16:54

Hi,

Is it feasible to implement the synchronization of large databases (~20M Rows). I Implemented synchronization of 1-100 rows it works all well and good.As soon as I increase the database size to 1M rows and ran the code, my CPU ram increases to 500MB and synchronization didn't occur without throwing any exceptions(provisioning did occur for both server and client but initially there weren't any rows in server database). If I decide to use the batching to synchronize the data, would I be able to solve this problem.

Code: Select all

using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Windows.Forms;
using Devart.Data.MySql;
using Devart.Data.Synchronization;
using Microsoft.Synchronization.Data;
using Devart.Data.MySql.Synchronization;
using Microsoft.Synchronization;

namespace DataConsolidationApplication
{
    public partial class Form1 : Form
    {
        public Form1()
        {
            InitializeComponent();
        }

        private void Form1_Load(object sender, EventArgs e)
        {
            MySqlConnection serverConnection = new MySqlConnection(@"Database=ptls;host=localhost;user id=root;password=;Connect Timeout=1500");
            MySqlConnection cloudConnection = new MySqlConnection(@"Database=ptls;host=master.cegtagfijlyq.us-west-2.rds.amazonaws.com;user id=root;password=;Connect Timeout=1500");
            try {
                provisionServer(serverConnection);
                provisionCloud(serverConnection, cloudConnection);
                syncronize(serverConnection, cloudConnection);
            }
            catch (System.InvalidCastException ev) {
                MessageBox.Show(ev.Message);
            }
            this.Close();
        }

        public void provisionServer(MySqlConnection server_connection) {
            MySqlSyncScopeProvisioning config = new MySqlSyncScopeProvisioning(server_connection);
            
            if(!config.ScopeExists("DialsScope"))
            {
                // Define the scope, named ProductsScope
                DbSyncScopeDescription scopeDesc = new DbSyncScopeDescription("DialsScope");
                // Retrive the description for the Products table from the database 
                DbSyncTableDescription tableDesc  = MySqlSyncDescriptionBuilder.GetDescriptionForTable("dials", server_connection);
                scopeDesc.Tables.Add(tableDesc);
                config.PopulateFromScopeDescription(scopeDesc);
                config.SetCreateTableDefault(DbSyncCreationOption.CreateOrUseExisting);
                config.Apply();
            }
        }

        public void provisionCloud(MySqlConnection server_connection, MySqlConnection cloud_connection) {
            MySqlSyncScopeProvisioning config = new MySqlSyncScopeProvisioning(cloud_connection);

            if (!config.ScopeExists("DialsScope"))
            {
                // Define the scope, named ProductsScope
                DbSyncScopeDescription scopeDesc = MySqlSyncDescriptionBuilder.GetDescriptionForScope("DialsScope",server_connection);
                config.PopulateFromScopeDescription(scopeDesc);
                config.Apply();
            }
        }

        public void syncronize(MySqlConnection server_connection, MySqlConnection cloud_connection)
        {
            SyncOrchestrator syncOrchestrator = new SyncOrchestrator();

            //Specify source database
           // remote.MemoryDataCacheSize = 1000;
            syncOrchestrator.RemoteProvider = new MySqlSyncProvider("DialsScope", server_connection, null, null);
            //local.MemoryDataCacheSize = 1000;
            syncOrchestrator.LocalProvider = new MySqlSyncProvider("DialsScope", server_connection, null, null);

            syncOrchestrator.Direction = SyncDirectionOrder.Download;
            SyncOperationStatistics syncStats = syncOrchestrator.Synchronize();

            MessageBox.Show("Start Time : "+ syncStats.SyncStartTime + "Total Changes Uploaded : "+ syncStats.UploadChangesTotal + "Total Changes Downloaded : "+syncStats.DownloadChangesTotal+ "Complete Time : "+syncStats.SyncEndTime);
        }

        public void deprovision(MySqlConnection connection)
        {
            // Remove the "DialsScope" scope from the server database.       
            MySqlSyncScopeDeprovisioning scopeDeprovision = new MySqlSyncScopeDeprovisioning(connection);
            // Remove the scope.
            scopeDeprovision.DeprovisionScope("DialsScope");
        }
    }
}

Pinturiccio
Devart Team
Posts: 2420
Joined: Wed 02 Nov 2011 09:44

Re: Syncronizing Large Databases

Post by Pinturiccio » Fri 17 Nov 2017 16:57

Thank you for the provided information. We are working on the reproducing the issue. We will post here when we get any results.

Post Reply