Data Sync For Huge Table

[Andy Wong]

We're evaluation SQLyog as our data sync tool for a BI project. But when trying to sync a huge table (>10M rows) for the first time, it generates an error 2008 (MySQL client ran out of memory). I searched this forum and found a similar problem with a previous version but we're using the latest 9.30 which is supposed to be fixed.

Here's the log for your reference:

Quote:

Sync started at Thu Oct 20 14:52:17 2011

`gc_txlog` 11099577 0 Error No. 2008

MySQL client ran out of memory

Our environment:

Source Server: Windows 2003 server 32-bit

Source Database: MySQL 5.0.26-community-nt

Target Server: Linux

Target Database: MySQL 5.0.77

Any idea? Thanks!

[ashwin]

Hi Andy Wong,

Quote:

I searched this forum and found a similar problem with a previous version but we're using the latest 9.30 which is supposed to be fixed.

Please read 8.62 release notes:

http://www.webyog.com/blog/2010/10/08/sqlyog-mysql-gui-8-62-released/

“* SJA now supports an additional -r parameter that tells how big CHUNKS should be when copying to an empty table. The -r parameter has only effect with Data Sync jobs and is ignored with other types of jobs. This is a command line option only that is not supported from the GUI Wizard.”

Also read FAQ: http://webyog.com/faq/content/27/114/en/introduction-to-the-_sqlyog-job-agent_-sja.html

Quote from the FAQ: “Data sync jobs additionally supports a '-r' parameter (it is ignored with other types of jobs). It has only an effect when a non-empty source-table is synced to an empty target-table and defines how big CHUNKS should be fetched from source server (for a (source) HTTP connection it is 1000 rows by default if -r option is not specified explicitly). -r”2000” will copy CHUNKS of 2000 rows from source. If no -r parameter is specified SQLyog will fetch “all rows” from source server in one 'SELECT' query operation what may cause memory exhaustion on the client machine.”

Please tell if you understand how to use this information or if you need more help.

Regards,

Ashwin A

[peterlaursen]

Additional remark: this will happen only when you are syncing to an empty target. You may use the 'copy database to other host'/'copy table to other database' tool to populate for the first time.

DATA SYNC is not really intended to be a *copy tool*. It is a *sync tool* and the scenario with an empty target is not the main scope. But we did add the -R parameter to handle such situations even with large tables.

[Author]

Posts