This topic describes how to troubleshoot the issue where an incremental migration or synchronization task of Data Transmission Service (DTS) is delayed.

Problem description

An incremental migration or synchronization task of DTS is delayed.

Possible causes

  • Cause 1: The specifications of your DTS instance cannot meet your requirements. Data migration instances and data synchronization instances of DTS have limits on records per second (RPS). If the amount of data written to the source database reaches the RPS limit when DTS performs an incremental migration or synchronization task, the task is delayed.

    In the DTS console, you can view the data volume involved in the task or click Quick Diagnostics to check whether the RPS limit of the current data migration or synchronization instance is reached. For more information, see Monitor task performance, Specifications of data synchronization instances, and Specifications of data migration instances.

  • Cause 2: The specifications of the destination database instance cannot meet your requirements. The write performance of the destination database instance reaches the bottleneck.

    For example, if the destination database instance is an ApsaraDB RDS for MySQL instance, you can view the CPU utilization, memory usage, and I/O load of the instance on the Monitoring and Alerts page in the ApsaraDB RDS console. This way, you can check whether the limits of the destination database instance are reached. For more information, see View the resource metrics, engine metrics, and deployment metrics of an ApsaraDB RDS for MySQL instance and Primary ApsaraDB RDS for MySQL instance types.

  • Cause 3: The performance is affected by hotspot updates. Tables without primary keys may involve updates on hot rows. Frequent updates on a single table or a single row in the source database may also cause hotspot updates.

    For example, if the source database instance is an ApsaraDB RDS for MySQL instance, you can view the transactions executed in the instance on the SQL Explorer and Audit page. This way, you can check whether hotspot updates exist. For example, you can check whether the transactions are concentrated on some tables. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

  • Cause 4: The read performance of the source database instance reaches the bottleneck, or the network bandwidth of the source database instance is exhausted.

    For example, if the source database instance is an ApsaraDB RDS for MySQL instance, you can view metrics such as IOPS of the instance on the Monitoring and Alerts page in the ApsaraDB RDS console. For more information, see View the resource metrics, engine metrics, and deployment metrics of an ApsaraDB RDS for MySQL instance and Primary ApsaraDB RDS for MySQL instance types.

  • Cause 5: If DTS cannot connect to the destination database or an exception occurs when DTS writes data to the destination database, DTS retries multiple times. As a result, the data synchronization task is delayed. In this case, you can check whether the task is in the Retrying state, and click Troubleshoot to view the cause and fix the issue in the DTS console.

    For example, if the destination database instance is an AnalyticDB for MySQL instance and DDL operations are performed on the source database to change field types, the data synchronization task is delayed and enters the Retrying state. This is because the destination AnalyticDB for MySQL instance does not support field type changes. However, DTS retries the DDL operations multiple times on the AnalyticDB for MySQL instance, and the execution always fails. As a result, the data synchronization task is delayed.

  • Cause 6: The destination database contains triggers, which results in high latency.
    For example, you can execute the following statement to check whether a MySQL database contains triggers: 
    select * from information_schema.triggers where trigger_schema='<Database name>';
  • Cause 7: The tables synchronized to the destination database have complex schemas. For example, the tables have both primary keys and unique keys. In this case, if systems other than DTS write data to the destination database, the tables may be locked and slow SQL queries occur.
  • Cause 8: A large number of DDL operations are performed on the source database.

    For example, if the source database instance is an ApsaraDB RDS for MySQL instance, you can view the execution status of DDL statements in the instance on the SQL Explorer and Audit page. This way, you can check whether a large number of DDL operations are performed on the instance. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

  • Cause 9: Network latency is caused by long-distance transmission.

    You can view the regions in which the source database instance and destination database instance reside on the Data Migration and Data Synchronization pages.

Solutions

  • Solution 1: Upgrade the specifications of the data migration instance or the data synchronization instance to improve performance. For more information, see Upgrade instance specification.
  • Solution 2: Upgrade the specifications of the destination database instance to improve the write performance.

    For more information about how to upgrade the specifications of an ApsaraDB RDS for MySQL instance, see Change the specifications of an ApsaraDB RDS for MySQL instance. Refer to this topic to upgrade the specifications of your destination database instance.

  • Solution 3: If hot rows are being updated, wait until the updates are complete. We recommend that you do not perform hotspot updates for business considerations.
  • Solution 4: Upgrade the specifications or network bandwidth of the source database instance.

    For more information about how to upgrade the specifications of an ApsaraDB RDS for MySQL instance, see Change the specifications of an ApsaraDB RDS for MySQL instance. Refer to this topic to upgrade the specifications of your destination database instance.

  • Solution 5: View specific error messages in the DTS console and fix the errors accordingly.

    In this example, DTS fails to perform DDL operations on the destination AnalyticDB for MySQL instance multiple times. As a result, the data synchronization task is delayed and enters the Retrying state. To resume the data synchronization task, perform the following steps: Remove the tables on which DDL operations are performed from the objects to be synchronized. Wait until the synchronization latency drops to 0 milliseconds. Remove the corresponding tables from the destination database. Then, add the tables back to the objects to be synchronized. For more information, see Data synchronization FAQ, Add an object to a data synchronization task, and Remove an object from a data synchronization task.

  • Solution 6: We recommend that you delete or disable the triggers in the destination database.
    For example, you can execute the DROP TRIGGER [ IF EXISTS ] [Database name] <Trigger name>; statement to delete a trigger from a MySQL database.
    Note For more information about how to configure DTS tasks if the source database contains triggers, see Configure a data synchronization task for a source database that contains a trigger.
  • Solution 7: Check the lock status, slow SQL queries, and schemas of the destination database. Change the write settings or schemas of the destination database to eliminate write locks and slow SQL queries.
    For example, if the destination database is a MySQL database, you can use the following methods to troubleshoot the issues:
    • Lock status and slow SQL queries: Execute the SHOW PROCESSLIST; statement to view locked tables or slow SQL processes. Then, execute the KILL [CONNECTION | QUERY]thread_id statement to delete the processes.
    • Schemas: Execute the show create table <Database name>.<Table name>; statement to view the schema of the specified table. For example, if the table has a unique key, evaluate whether the unique key is required. If the unique key is not required, execute the alter table <Database name>.<Table name> drop index <Unique key name>; statement in the destination database to delete the unique key.
  • Solution 8: If the DTS task is running, we recommend that you do not perform multiple DDL operations on the source database at a time. If you must perform DDL operations, we recommend that you perform the operations during off-peak hours.
    Note When you configure the task, we recommend that you select only DDL operations that you need to synchronize.
  • Solution 9: Network latency caused by long-distance transmission cannot be avoided. You can connect the source and destination database instances over Express Connect to reduce the latency.