Data Transmission Service:What do I do if full data tasks stop or slow down?

Issue

When Data Transmission Service (DTS) is performing full data synchronization or migration, the synchronization or migration takes a long time or stops.

Possible causes

• The volume of data to be synchronized or migrated from the source database is large, or the table to be synchronized or migrated does not have a primary key.

• Multiple DTS tasks are created in the source database.

• DTS fails to connect to the source or destination database. For example, a whitelist is configured for the database, the database account does not have the required permissions, or the database account or password is invalid.

• The performance, network latency, or network bandwidth of the source or destination database does not meet the business requirements.

• The data cannot be written to the destination database. For example, the destination database is a read-only database, the disk of the destination database is full, or a deadlock occurs in the destination database.

Troubleshooting

You can use the following methods to troubleshoot the issue:

• Check the source database:

  • Estimate the migration time:

    1. Check the data volume of the objects to be migrated from the source database.

    2. Migrate some tables first to evaluate the total amount of time required to migrate all tables. For example, you can estimate the overall migration time based on the time that it takes to migrate 10 GB of data.

  • Check concurrent tasks: Check whether multiple tasks run at the same time.

    Note

    Concurrent tasks may result in temporary resource shortages. We recommend that you run tasks in separate time periods.

  • Check whether the username and password of the source database are valid.

    Check whether you can connect to the source database from a remote host by using the username and password that are specified for the DTS task. For example, to connect to a MySQL database, run the following command: mysql -h<IP address or domain name of source database> -P<Port number of source database> -u<Username> -p<Password>.

  • Check whether the whitelist settings are valid. If the source database is a self-managed database, you can check whether the CIDR blocks of DTS servers are added to the security settings of the source database. For more information, see Whitelist DTS IP ranges for your user-created database.

  • Check whether all large tables in the source database contain primary keys or unique indexes. For example, to check whether a large table in a MySQL database contains primary keys or unique indexes, run the following command: show create table <Database name>.<Table name>;.

    Note

    If the source database contains only common indexes or no indexes, DTS may migrate duplicate data records to the destination database.

• Check the destination database:

  • Check whether the username and password of the destination database are valid.

    Check whether you can connect to the destination database from a remote host by using the username and password that are specified for the DTS task. For example, to connect to a MySQL database, run the following command: mysql -h<IP address or domain name of destination database> -P<Port number of destination database> -u<Username> -p<Password>.

  • Check whether the whitelist settings are valid. If the destination database is a self-managed database, you can check whether the CIDR blocks of DTS servers are added to the security settings of the destination database. For more information, see Whitelist DTS IP ranges for your user-created database.

  • Check whether the destination database instance is read-only.

    For example, to query and modify the status of a MySQL database, run the following commands:

    1. Run the show global variables like '%read_only%'; command. If read_only is returned and super_read_only is ON, the MySQL database is set to read-only.

    2. Run the following command to change the status of the MySQL database from read-only to read/write:

       unlock tables;
       set global read_only=0;

    Note

    If the MySQL database is in the read-only state, set the read_only parameter to 1 or TRUE. If the MySQL database is in the read/write state, set the read_only parameter to 0 or FALSE. For more information, see MySQL documentation.

  • Check whether the destination database has available disk space.

    For example, you can view the storage usage of an ApsaraDB RDS for MySQL instance in the ApsaraDB RDS console. For more information, see Troubleshoot insufficient storage issues on an ApsaraDB RDS for MySQL instance.

  • Check whether the performance of the destination database has reached 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).

  • Check whether the destination database has deadlocks.

    For example, if the destination database is a MySQL database, run the following commands to query and delete deadlocks:

    1. Run the show OPEN TABLES whereIn_use > 0; command to check whether the table is locked.

    2. Run the SHOW PROCESSLIST; command to query locked tables or slow SQL processes.

    3. Run the KILL [CONNECTION | QUERY]thread_id command to delete a process.

    Note

    If the destination database has deadlocks, data cannot be written to the database.

• Check the network connectivity and whether the network bandwidth is limited. We recommend that you upgrade the network bandwidth if necessary.

  Note

  You can run OS commands such as the ping and telnet commands or use third-party tools to test the network connectivity and network latency.

References

For more information about how to deal with the latency of incremental data migration or synchronization tasks, see What do I do if latency occurs during incremental data migration or synchronization?