Troubleshooting incremental migration or synchronization latency

Problem

A DTS incremental migration or synchronization task experiences a latency of over 1,000 milliseconds.

Possible causes

• Cause 1: The instance specifications are insufficient for the workload. Each DTS instance for incremental data migration or synchronization has a specific RPS (records per second) limit. If the write workload on the source database is too high, for example, due to large transactions, the task may be throttled, causing latency.

  You can check the task's data flow or use the Quick Diagnostics feature to determine if the instance has reached its specification limits. For more information, see Monitor task performance, Specifications of data synchronization instances, and Specifications of data migration instances.

• Cause 2: The destination instance specifications are insufficient. The write performance of the destination database instance has reached a bottleneck.

  For example, if the destination is an ApsaraDB RDS for MySQL instance, you can check resource utilization metrics such as CPU, memory, and I/O on the Monitoring and Alerts page in the ApsaraDB RDS console. Compare these metrics with the instance's specifications to see if it has reached its performance limits. For more information, see View monitoring information and ApsaraDB RDS for MySQL instance types.

• Cause 3: Hotspot updates are affecting performance. Updates on a table without a primary key or frequent updates to a single table or row in the source database can cause hotspot updates.

  For example, if the source is an ApsaraDB RDS for MySQL instance, you can use the SQL Explorer and Audit feature to examine transactions and identify if hotspot updates are occurring, such as transactions concentrated on specific tables. For more information, see SQL Explorer and Audit.

• Cause 4: The read performance of the source database has reached a bottleneck, or its network bandwidth is saturated.

  For example, if the source is an ApsaraDB RDS for MySQL instance, you can check metrics such as IOPS on the Monitoring and Alerts page in the ApsaraDB RDS console. Compare these metrics with the instance's specifications to see if it has reached its performance limits. For more information, see View monitoring information and ApsaraDB RDS for MySQL instance types.

• Cause 5: When DTS cannot connect to the database or fails to write data to the destination database due to other errors, it performs multiple retries, causing synchronization latency. You can check if the task status is Retrying in the console. Click Troubleshoot to view the cause and resolve the issue.

  For example, if the destination database is an AnalyticDB for MySQL instance and you run a DDL operation to change a column type on the source database, the DTS synchronization task experiences latency and enters the Retrying state. This is because the destination AnalyticDB for MySQL instance does not support column type changes. DTS repeatedly attempts to apply the DDL operation, but these attempts fail, leading to the latency.

• Cause 6: The destination database has a trigger, which can cause significant latency.

  For a MySQL database, you can use the following command to check for triggers:

  select * from information_schema.triggers where trigger_schema='<database_name>';

• Cause 7: If tables being synchronized to the destination database have complex schemas, such as both primary keys and unique keys, and services other than DTS also write to the destination database, table locks and slow SQL queries may occur on the destination database.

• Cause 8: A large number of DDL operations are running on the source database.

  For example, if the source is an ApsaraDB RDS for MySQL instance, you can use SQL Explorer and Audit to check the execution of DDL statements and determine if there is a high volume of DDL operations. For more information, see SQL Explorer and Audit.

• Cause 9: Long-distance data transmission is causing network latency.

  You can check the regions of the source and destination databases on the task list page.

• Cause 10: The table schemas in the source and destination databases do not match.

• Cause 11: The latency value displayed in the console may be delayed.

Solutions

• Solution 1: Upgrade the instance specifications for the incremental data migration or synchronization task to improve performance. For more information, see Upgrade a DTS instance.

• Solution 2: Upgrade the destination database instance specifications to improve its write performance.

  For an ApsaraDB RDS for MySQL instance, see Change instance specifications to upgrade the configuration.

• Solution 3: If hotspot updates are occurring, wait for them to complete. Alternatively, consider redesigning your application logic to avoid hotspot updates.

• Solution 4: Improve the performance of the source database instance or upgrade its network bandwidth.

  For an ApsaraDB RDS for MySQL instance, see Change instance specifications to upgrade the configuration.

• Solution 5: Check the specific error message in the console and resolve the issue accordingly.

  If DTS fails to apply DDL operations on an AnalyticDB for MySQL destination, resolve the issue by first removing the table with the failed DDL operation from the synchronization objects. After the latency drops to zero, delete the corresponding table in the destination database. Finally, add the table back to the synchronization objects. For more information, see Data synchronization FAQ and Modify synchronization objects.

• Solution 6: Delete or disable triggers in the destination database.

  For a MySQL database, use the command DROP TRIGGER [ IF EXISTS ] [database_name] <trigger_name>; to delete a trigger.

  Note

  If the source database has a trigger, see Configure a synchronization task when the source database has a trigger to configure the DTS task.

• Solution 7: Check the lock status, slow SQL queries, and table schema of the destination database. Adjust the write operations or schema to resolve write locks and slow SQL queries.

  This example for a MySQL database shows how to query the lock status, slow SQL queries, and table schema, and then how to resolve the issues.

  • Lock status and slow SQL queries: Run SHOW PROCESSLIST; to find locking processes or slow queries. Then, run KILL [CONNECTION | QUERY] thread_id to terminate the process.

  • Table schema: Run show create table <database_name>.<table_name>; to view the table schema. If a unique key exists, evaluate whether it is necessary for your business. If not, run alter table <database_name>.<table_name> drop index <unique_key_name>; in the destination database to delete it.

• Solution 8: If the DTS task is running successfully, avoid executing DDL operations in bulk on the source database. If DDL operations are necessary, run them during off-peak hours.

  Note

  When you configure the task, do not select DDL operations that you do not need to synchronize.

• Solution 9: To reduce the unavoidable network latency from long-distance transmission, use Express Connect to connect the source and destination database instances.

• Solution 10: Adjust the table schemas to ensure they are consistent between the source and destination databases.

• Solution 11: Wait a few minutes and refresh the console.

References

For solutions to slow full data tasks, see Troubleshoot slow or stuck full data migration.