we are in a situation where we have a few publications and each mobilink publication works as expected.
Though, when initially synchronizing one of the publication it takes about half an hour before the download of data starts. The second time around it just goes really fast and other publications that download even more data work quite fast as well.
We have seen something like this before when database queries are really slow on the consolidated database. Unfortunatly our DBA cannot see any problems here this time.
We are going to have someone over from Oracle to check the database for problems...
Is there anything else that we should take in account when investigating something like this?
Things we have checked: we've done some network tests to see if things are slow here... but it appears not the consolidated database (oracle) has been monitored for slow queries and locks. non have been found. Load on the database server is very low.
asked 03 Dec '14, 09:02
Oracle consultant has done some checks and could confirm what I was seeing in mobilink log. 3 slow download_cursors => 3 slow queries.
The reason why the query was going fast when I was running it manually is because it used a different executionplan because the query in the download_cursor was just a tiny bit differently written (extra spaces or something).
our DBA had noticed a lot of IO but was not sure this was a problem. The oracle consultant confirmed that this really is a problem and the amount of IO was caused of not having a big enough memory Buffer for oracle. Yesterday we added more RAM memory and after half a day of synchronizations everything has come up to speed.
Everyone thanks for the feedback. Upvotes for all !
answered 10 Dec '14, 02:19
There's a couple of things you need to do to find out where the issue is:
Note that an initial synchronization will download all data to the remote, and the remote will then have to insert all the changes into it's DB, indexing as it goes. I have a publication that takes over 10 minutes for an initial sync to be applied after download, so it's not unusual.
answered 03 Dec '14, 12:54
If the SQL in the three slow download_cursors runs quickly when executed directly then it suggests a concurrency problem with the scripts (since the MobiLink server has many connections that are simultaneously active). For example, if in the MobiLink Monitor you see long phases that end around the same time (though starting at different times) it indicates that the first one is blocking the others.
FYI with v16, the ML server detects such blocking and the ML Profiler highlights it.
We've also seen syncs against Oracle slow to a crawl due to Oracle "background" processes that become bottlenecks under high load. For example, a customer found that under high sync loads Oracle DB archive logs would fill up, causing a checkpoint and swapping of archive logs, during which synchronizations could not proceed, and there appeared to be no load on the Oracle server. Other culprits might include Oracle streams, database flashback, or other administrative processes.
We've also found deadlock warnings in the MobiLink server log when Oracle DBAs had said there was no deadlock.