This topic describes how to troubleshoot incremental migration latency or synchronization latency of Data Transmission Service (DTS).

Issue

When DTS performs incremental data migration, the migration task or synchronization task is delayed.

Cause

  • Cause 1: The instance specification is limited. Data migration instances and data synchronization instances have limits on records per second (RPS). If the amount of data written to the source database reaches the specification limit, the task will be delayed.
  • Cause 2: The specification of the destination database instance is limited. The write performance of the destination database instance reaches the bottleneck.
  • Cause 3: The performance is affected by hotspot updates. Tables without primary keys may cause updates of hot rows. Frequent updates of a single table or a single row in the source database may also cause hotspot updates.
  • Cause 4: The read performance of the source database instance reaches the bottleneck or the network bandwidth is exhausted.
  • Cause 5: If the destination database instance is an AnalyticDB for MySQL instance and data definition language (DDL) operations are performed on the source database to change field types, data synchronization is delayed. This is because AnalyticDB for MySQL does not support field type changes. However, DTS retries multiple times for failed SQL statements, which results in synchronization latency.
  • Cause 6: The destination database contains triggers, which results in high latency.
  • Cause 7: The destination database supports dual-write, or the tables synchronized to the destination database instance has complicated schemas, for example, the tables contain both primary keys and unique keys.

Solution

  • Solution 1: Upgrade the specification of the data migration instance or the data synchronization instance to improve performance. For more information, see Update instance specification.
  • Solution 2: Upgrade the specification of the destination database instance to improve the write performance.
  • Solution 3: If hotspot rows are being updated, wait until hotspot updates are complete. You can also avoid hotspot updates for business considerations.
  • Solution 4: Upgrade the specification or network bandwidth of the source database instance.
  • Solution 5: If the destination database instance is an AnalyticDB for MySQL instance, we recommend that you remove the tables on which DDL operations are performed. Wait until the synchronization delay drops to 0, remove the corresponding tables from the destination database, and then add the tables back to the objects to be synchronized.
  • Solution 6: We recommend that you delete or disable the triggers in the destination database.
  • Solution 7: Check the lock status of the destination database instance and slow SQL queries. Change the write settings or schemas of the destination database to address write locks and slow SQL queries.