Syncronization never finishing

[james3]

I have a large database (~3 million entries which have varchar, text, and longtext columns) for which I have a live version and a development version. I am trying to use SQLyog to synchronize them. They currently differ by about 80,000 entries. Given the size of each row, I would anticipate that the data which SQLyog is trying to syncrhonize totals about 4 gig, while the entire database totals 100 gig (that's not a typo).

I run the syncrhonization wizard and everything starts fine. SQLyog quickly figures out “SrchRows” and “TgtRows”, and then starts working (I guess…). It never finishes. I have tried this at least 3 times, letting it run for up to 10 hours (both boxes are Redhat 9 w/ Pentium 4 w/ at least 1 gig ram).

I have been watching the local machine with the system monitor. It seems to go through phases. When synchronization first starts, memory usage and CPU usage go up to nearly 100%, and the hard drive light is constantly on. Eventually, CPU usage drops to about 15% and the hard drive light goes out. Currently (after about 10 hours) the hard drive light is back on again, but CPU usage is still low (15% — not low enough that it doesn't seem to be doing *something*).

The SQLyog interface does not appear “hung” — I can still move the window around, and hit the “Terminate” button at any time (if I do so, the hard drive on the local machine continues to chug along for quite some time). (I am running SQLyog on Win XP by the way) But, I don't know what is going on. Is it that computing the checksums for this amount of data is a ridiculous task? Is it ever going to finish, or should I just give up?

UPDATE: The CPU usage on the local MySQL server has dropped to 0, and the hard drive is no longer constantly running. But, nothing has changed as far as the Syncrhonization Wizard goes (still sitting there with a count for SrcRows and TgtRows, but nothing under “Inserted”.) Something that I find odd is that when I do a count(*) in the database, it appears that about 50 rows have been inserted. 0 I could see. 80,000 I could see. A constantly changing number as it inserted rows I could see. But to add 50 out of 80,000 and then sit there?

UPDATE 2: I have continued to let it run. It has been running for over 32 hours now and is still not done. The cycling continues. I am guessing that it is going back and forth between the two machines, asking one to compute checksums, and then the other, and then comparing them (though I do not see why it would not do this simultaneously, and so have the CPU and/or hard drive constantly working). Because I only ever see it add rows in increments of 10, I am assuming this is programmed behavior (save up the changes til it has 10, then commit them). It really would be nice if there was some kind of progress indicator. I have no idea if it will be done in five minutes, or a month (or if there is a problem and it will never finish…).

Thanks,

James

http://www.freepatentsonline.com

[Ritesh]

Let us start by synchronizing our terminologies!

I think the aforementioned “database” of ~3 million entries is really a single table containing those rows. If that is the case, we need to reduce the no. of test cases by trying to synchronize all other tables other than this table. If that works, then we have only got this table to look into. You could then send us the table structure and some sample data ( if not confendential ). We would immediately start working on it.

Please send the table structure, etc to [email protected]

[Author]

Posts