0

I'm using SDS to migrate data from a SQL server to a Mysql database. My tests of moving the data of a database that was not in use worked correctly though they took like 48 hours to migrate all the existing data. I configured dead triggers to move all current data and triggers to move the new added data.

When moving to a live database that it is in use the data is being migrated too slow. On the log file I keep getting the message:

[corp-000] - DataExtractorService - Reached one sync byte threshold after 1 batches at 105391240 bytes. Data will continue to be synchronized on the next sync

I have like 180 tables and I have created 15 channels for the dead triggers and 6 channels for the triggers. For the configuration file I have:

job.routing.period.time.ms=2000

job.push.period.time.ms=5000

job.pull.period.time.ms=5000

I have none foreign key configuration so there wont be an issue with that. What I would like to know is how to make this process faster. Should I reduce the number of channels?

I do not know what could be the issue since the first test I ran went very well. Is there a reason why the threshold is not being clreared.

Any help will be apreciated. Thanks.

jameslimousin
  • 330
  • 3
  • 11

1 Answers1

2

How large are your tables? How much memory does the SymmetricDS instance have?

I've used SymmetricDS for a while, and without having done any profiling on it I believe that reloading large databases went quicker once I increased available memory (I usually run it in a Tomcat container).

That being said, SymmetricDS isn't by far as quick as some other tools when it comes to the initial replication.

Have you had a look at the tmp folder? Can you see any progress in file size. That is, the files which SymmetricDS temporarily writes to locally before sending the batch off to the remote side? Have you tried turning on more fine grained logging to get more details? What about database timeouts? Could it be that the extraction queries are running too long, and that the database just cuts them off?

sbrattla
  • 5,274
  • 3
  • 39
  • 63
  • thanks for the response sbrattla, I turned off the progress files on SDS and it worked, now it only use memory buffer to gather the information. The SDS instance has 1024M RAM available so that is not the issue. The issue that I have now is that some of my reload batches stays in QY status on the outgoing_batch table, do you know why is stat there is no doc about that state. – jameslimousin Jan 04 '13 at 14:11
  • what other tools do you know that do a similar job that SDS? – jameslimousin Jan 04 '13 at 14:11
  • @mongaru : I believe QY means that a table is being queried. How large are your tables (how many rows)? – sbrattla Jan 05 '13 at 08:40
  • the table that was making things slow has over 3M rows, when using the file buffer it had 100mb after disabling the file buffer it started migrate correctly but at some point I got the QY and it never got out of there. I have to try configure a greater level of log to see if there is any information about it. – jameslimousin Jan 07 '13 at 13:25
  • I don't think you should disable the file buffer. If you do that, I believe SymmetricDS will attempt to store the entire table in memory...which isn't a good solution. However, 3M rows will take a little time to extract and send off to the remote site... – sbrattla Jan 07 '13 at 14:27
  • When disabled the buffer it stored the data until the max threshold and then send the information to the other database I think. I agree that it will take some time to send off that amount of data but it hang there for days and the buffer file stuck at 100mb even if I modified the jvm max memory. It is working now luckly but it is a weird episode. Do you know another replication tool? – jameslimousin Jan 08 '13 at 19:30
  • There are others, such as JobAgent/SQLyog MySQL. However, these tools usually do a full database comparison each time you run them. I'd say its worth investigating SymmetricDS more and figure out why it doesn't work. Go to the SymmetricDS forum and read posts. It's worth it. – sbrattla Jan 09 '13 at 06:27