This topic describes how to troubleshoot the issue if incremental data migration or synchronization is delayed.

Issue

When Data Transmission Service (DTS) performs incremental data migration or synchronization, the migration task or synchronization task is delayed.

Cause

  • Cause 1: The specifications of the DTS instance cannot meet your requirements. 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.

    In the DTS console, you can view the data volume or click Quick Diagnostics to check whether the current instance reaches the specification limit. 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 instance is an ApsaraDB RDS for MySQL instance, you can view the usage details of CPU, memory, and I/O resources on the Monitoring and Alerts page in the ApsaraDB RDS console. You can check whether the limits of the current instance type are reached. For more information, see View the resource, engine, 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 cause updates of hot rows. Frequent updates of a single table or a single row in the source database may also cause hotspot updates.

    For example, if the source instance is an ApsaraDB RDS for MySQL instance, you can view the transactions executed by the source instance on the SQL Explorer and Audit page. Then, you can check whether hotspot updates exist, for example, the transactions are concentrated on a specific table. 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 is exhausted.

    For example, if the source instance is an ApsaraDB RDS for MySQL instance, you can view metrics such as IOPS on the Monitoring and Alerts page in the ApsaraDB RDS console. You can check whether the limits of the current instance type are reached. For more information, see View the resource, engine, 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 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.

    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. The task is in 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 in AnalyticDB for MySQL but fails to execute the operations. Therefore, the data synchronization task is delayed.

  • Cause 6: The destination database contains triggers, which results in high latency.
    For example, you can run the following command 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 is performed in the source database.

    For example, if the source instance is an ApsaraDB RDS for MySQL instance, you can view the execution status of DDL statements on the SQL Explorer and Audit page. For more information, see Use the SQL Explorer and Audit feature on an ApsaraDB RDS for MySQL instance.

Solution

  • 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, see Change the specifications of an ApsaraDB RDS for MySQL instance.

  • Solution 3: If hot rows are being updated, wait until hotspot updates are completed. 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, see Change the specifications of an ApsaraDB RDS for MySQL instance.

  • Solution 5: View the error messages in the console and fix the issues based on the causes.

    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 is in the Retrying state. To resume the data synchronization task, perform the following steps: 1. Remove the tables on which DDL operations are performed from the objects to be synchronized. 2. Wait until the synchronization latency drops to 0 milliseconds. 3. Remove the corresponding tables from the destination database. 4. 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 run the DROP TRIGGER [ IF EXISTS ] [Database name] <Trigger name>; command to delete a trigger in a MySQL database.
    Note If a source database contains a trigger, you can configure a DTS task by referring to Configure a data synchronization task for a source database that contains a trigger.
  • Solution 7: Check the lock status, slow SQL queries, and table schemas of the destination database. Change the write settings or table 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 run the following commands to fix the issue:
    • Lock status and slow SQL queries: Run the SHOW PROCESSLIST; command to query locked tables or slow SQL processes. Then, run the KILL [CONNECTION | QUERY]thread_id command to delete the processes.
    • Table schemas: Run the show create table <Database name>.<Table name>; command to view the table schemas. If the table has a unique key, evaluate whether the unique key is required. If not, you can run the alter table <Database name>.<Table name> drop index <Unique key name>; command to delete the unique key in the destination database.
  • Solution 8: If the DTS task is running, we recommend that you do not perform DDL operations in the source database. 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.