How do I optimize migration of MySQL databases in AWS DMS?

Resolution

Prerequisite: Make sure that you activate premigration assessments for a task.

Monitor your migration task

To check the task progress, turn on monitoring for the AWS DMS task. To troubleshoot issues, view the Amazon CloudWatch metrics and AWS DMS task logs.

Improve the performance of a AWS DMS task

To improve the performance of your AWS DMS task, run shorter queries and review your LOB settings.

Long-running queries

To check for long-running queries on the source MySQL database, use the SHOW FULL PROCESSLIST statement. For more information, see SHOW FULL PROCESSLIST on the MySQL website.

To optimize the query run time in MySQL, set timeouts, and breakdown complex queries, use the EXPLAIN statement to identify and fix active queries. For more information, see EXPLAIN on the MySQL website.

To troubleshoot high latency, check the source and target metrics.

Large tables

If you migrate a large number of tables, then use multiple tasks across multiple replication instances so that the load is evenly shared. To migrate the large tables separately, use row filtering. To migrate tables with LOB columns separately, configure the task settings. To handle LOB migration on per-table basis, specify the LOB settings for a selected table or view. It's a best practice to monitor the memory usage of your replication instance.

To specify the number of threads that AWS DMS uses to load each table into the target database, use ParallelLoadThreads for MySQL as an extra connection attribute.

To automatically resolve mismatches in table metadata (DDL) changes on the source, use the CleanSourceMetadataOnMismatch=true extra connection attribute. For more information, see Endpoint settings when using My SQL as a source for AWS DMS.

If your source or target database reach the parameter maximum, then you get the "Too many connections" error message. To resolve this error, check the max_connections quota on your source MySQL. To find the current value of max_connections parameter, run the following query:

mysql> show variables like 'max_connections';

To prevent disconnections on the source MySQL instance, make sure that the MySQL endpoint has the net_read_timeout, net_write_timeout, and wait_timeout endpoint settings set to a minimum of 300 seconds. For more information, see Connections to a target MySQL instance are disconnected during a task on Troubleshooting migration tasks in AWS Database Migration Service.

Resolve data inconsistency issues

To monitor consistency issues, turn on DMS validation.

Validation adds more load on both source and target databases and DMS instances, such as CPU, memory, and IOPS. If you have an independent validation-only task, then schedule validation separately from the migration task. It's a best practice to optimize AWS DMS validation-only tasks.

Related information

Improving the performance of an AWS DMS migration