What do I do if latency occurs for incremental data tasks? - Data Transmission Service

This topic describes the common causes of latency for incremental data tasks and provides the related solutions. You can troubleshoot the issue based on these causes and resume the tasks based on the solutions.

Issue

When Data Transmission Service (DTS) is performing incremental data migration or synchronization, a latency of over 1,000 milliseconds occurs.

Possible causes

Cause 1: The specifications of your instance cannot meet your business requirements, and the amount of data in the incremental task exceeds the limit. Data migration instances or 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, a latency occurs. For example, large transactions exist in the source database.
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 business 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 of the instance in the ApsaraDB RDS console. You can also check whether the limits of the instance are reached. For more information, see View monitoring information and Instance types for standard primary ApsaraDB RDS for MySQL instances (original x86 architecture).
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 of the intance in the ApsaraDB RDS console. 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.
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 of the instance in the ApsaraDB RDS console. For more information, see View monitoring information and Instance types for standard primary ApsaraDB RDS for MySQL instances (original x86 architecture).
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, latency occurs during data synchronization. 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, latency occurs during data synchronization and the task 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, latency occurs during data synchronization.
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 that are 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 of the instance in the ApsaraDB RDS console. 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.
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 in the DTS console.
Cause 10: The tables in the source and destination databases have different schemas.
Cause 11: A latency exists between two consecutive data updates in the DTS console.

Solutions

Solution 1: Upgrade the specifications of the data migration instance or the data synchronization instance to improve performance. For more information, see Upgrade a DTS instance.
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 instance specifications. 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 instance specifications. 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, latency occurs during data synchronization and the task 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 and Modify the objects to be synchronized.
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.
Solution 10: Modify the schema of the table in the source or destination database based on your business requirements to ensure that the schemas of tables in the source and destination databases are consistent.
Solution 11: Wait a few minutes and refresh the page.

References

For more information about how to troubleshoot the issue that full data tasks slow down, see What do I do if full data tasks stop or slow down?