Unexpected Performance Recovery and Reduced Target DB Load After Switching CDC Mode from Batch Optimized Apply to Transactional Apply

[iti-attunity-sup]

Source: Oracle 11g / Target: MySQL 5.7. We are executing Change Data Capture (CDC) across 7 tasks.
All tasks were previously running in Batch optimized apply mode.

Over the past three weeks, performance degradation occurred on the target database, so we implemented tuning measures such as adding indexes and modifying RDS parameters, which yielded a certain level of performance improvement.
However, despite these performance optimizations, 1 of the 7 tasks continued to encounter target replication latency of over 1 hour that lasted for several consecutive hours.

As an experiment, we changed the replication mode of the delayed task from Batch mode to Transactional apply mode.
As a result, the replication latency for this task improved rapidly and was completely resolved. At the same time, the performance load on the target database decreased, and application response times improved noticeably.

Following these results, we changed another task that was running in Batch mode to Transactional mode as well. This further reduced the load on the target database, and application responsiveness improved dramatically.

Since changing these two tasks to Transactional apply mode, the previous performance problems disappeared as if they were never there, and the multiple reported application response issues were resolved all at once.

＜Questions＞
According to the Qlik Replicate documentation, Batch optimized apply mode is the default and is generally recommended for significantly faster processing speed compared to Transactional apply mode. What could be the potential reason why switching to Transactional apply mode dramatically reduced and eliminated replication latency in our environment?

What could be the reason why changing the replication mode for only 2 out of 7 tasks resulted in a lower load on the target database and resolved application response issues across the system?

If there are similar known technical advisory cases or specific points/metrics we should review or verify, we would greatly appreciate your insights and guidance.

[john_wang]

Hello Team, @iti-attunity-sup 

I'm not sure which Replication version you are currently running, but it appears that it may have already reached EOS. Nevertheless, we'd like to do our best to help.

What you described matches my experience with MySQL targets over the past several years. In some scenarios, Transactional Apply Mode can perform significantly better than Batch Apply Mode, especially when the workload contains a large number of DELETE operations. INSERT and UPDATE operations generally don't have as much of an impact.

With Batch Apply Mode, additional latency can be introduced by the UNION operation between the net change table and the target table, particularly when the target table is wide. In some worse cases, the apply process could become stuck, requiring us to stop the process and recover it.

However, this behavior is specific to certain target endpoints, and MySQL is one example. From a performance tuning perspective, it is difficult to provide a simple formula because performance can be affected by many factors, including the hardware, software, operation types  (INSERT/UPDATE/DELETE), and even the data wide itself.

That's also why you may not find a specific recommendation for this scenario in the User Guide. In practice, we usually need to test and tune the configuration in the actual environment to determine the most suitable parameters and settings.

The good news is that you have identified a solution that works well for your particular scenario. Well done, and thank you very much for your excellent support!

Best Regards,

John.