How do I use AWS DMS to optimize migration from a source MySQL database to a target Amazon RDS for MySQL database?

Short description

AWS DMS migrates data from a source data store to a target data store. These two data stores are called endpoints. You can migrate between source and target endpoints that use the same database engine, such as from one MySQL database to another MySQL database.

DMS creates only the target schema objects required for the migration, such as tables, primary keys, and in some cases, unique indexes. DMS doesn't create objects such as secondary indexes, non-primary key constraints, and data defaults. For more information on what AWS DMS migrates, see High-level view of AWS DMS. You can also use DMS homogeneous data migration.

Use the following best practices and recommended configurations on your source and target databases.

Resolution

Follow best practices for MySQL to MySQL DMS migration

Take the following actions.

Reduce target database activity during migration

Turn off backups and database-specific logs such as bin, general, and audit on the target database during migration. You can turn them on again to troubleshoot issues. Turn off triggers, other cron jobs, and event schedulers on the target database during migration. Don't use Multi-AZ on the target Amazon RDS database during the DMS migration, and don't apply any other external client traffic to the target database.

Provision adequate resources

Provision your DMS replication instance, source, and the target databases with the required CPU, memory, storage, and input/output operations per second (IOPS) to avoid resource contention during migration.

Configure CDC before migration

Configure the source database with the prerequisites for DMS change data capture (CDC) before you start the migration.

Use optimized LOB settings

Use optimized large binary object (LOB) settings such as limited LOB and inline LOB for migration.

Split heavy workloads across multiple tasks

If the source database contains many tables with a heavy workload, then split the tables among multiple tasks. Split the tables based on their size on the source database, the application traffics pattern, and the presence of LOB columns. If the table has many LOB TEXT or JSON columns with high write traffics on the source, then create a separate task for the table. Transactional consistency is maintained within a task, so tables in separate tasks don't participate in common transactions.

Use the parallel full load mechanism for heavy source tables to reduce migration time. For more information, see Using parallel load for selected tables, views, and collections.

Manage indexes and constraints during migration

Turn off foreign key constraints on the target table during full load migration. Add secondary index to the target database before you start the CDC phase of replication.

Don't migrate default schema tables

The RDS primary user doesn't have drop and recreate privileges on the default schema tables. Don't migrate default database or schema tables from the source with DMS.

Test before production migration

Review the documentation on how to migrate from MySQL to MySQL with DMS for information on types of data that DMS can successfully migrate.

Test your workload with the default transactional CDC apply before you use the batch apply CDC method. For more information, see How do I use DMS batch apply to improve CDC replication performance?

Test the migration with the same production data on any other QA/DEV database environment before you start the production migration. Use the same DMS configuration for the production migration.

For more information, see Improving the performance of a DMS migration.

Create the tables on the target database before migration

To preserve the default data definitions, create the tables on the target database before migration. Use one of the following approaches, depending on the type of migration.

For homogeneous migrations such as MySQL to MySQL

Use native database engine utilities such as mysqldump to export the table definitions. Then, import these table definitions into the target without the data. Then, to load the data, create the DMS task with target table preparation mode set to TRUNCATE_BEFORE_LOAD.

For migrations across different database engines

Use the AWS Schema Conversion Tool (AWS SCT). AWS SCT connects to your source and target databases, and then converts the existing database schema from one database engine to another. You can also use AWS SCT for homogeneous databases. Pre-create tables on the target database with the default data definitions intact. Then, to load the data, use the DMS task with the target table preparation mode set to TRUNCATE_BEFORE_LOAD. For more information, see Converting database schemas in AWS Schema Conversion Tool.

Pre-create the table DDL on the target

Pre-create the table data definition language (DDL) on the target MySQL databases. Then, create a DMS task with target preparation mode set to DO_DOTHING or TRUNCATE to migrate only the data.

Run the following command to export the DDL structure from the source MySQL database without data:

mysqldump -h yourhostnameorIP -u root -p --no-data --skip-triggers --single-transaction --dbname > schema.sql

Note: This command dumps the DDL structure from the source without any data.

Then, run the following command to restore the DDL structure on the target:

mysql -u user -p -h YourHostNameorIPAddress YourDatabaseName < schema.sql

Note: Replace YourHostNameorIPAddress with your source database hostname or IP address, and YourDatabaseName with the name of your database.

Alternatively, allow DMS to create the tables on the target with the DROP AND CREATE target preparation mode. Then, skip to the Stop the task before and after cached changes section to add missing objects such as secondary indexes before you resume the task for the CDC phase.

Note: By default, DMS creates the table on the target with only the primary key or the unique key. It doesn't migrate any other objects to the target MySQL database.

Turn off foreign key checks during full load

During full load, DMS doesn't identify foreign key relational tables. It loads the data randomly, so the table load can fail if the target database has a foreign key checks turned on.

Set the following extra connection attribute (ECA) on the target MySQL endpoint to turn off foreign key checks for the DMS session:

initstmt=SET FOREIGN_KEY_CHECKS=0;

Stop the task before and after cached changes

In the JSON task settings, set StopTaskCachedChangesNotApplied and StopTaskCachedChangesApplied to true.

Example:

"FullLoadSettings": { "TargetTablePrepMode": "TRUNCATE_BEFORE_LOAD""CreatePkAfterFullLoad": false,"TransactionConsistencyTimeout": 600,"MaxFullLoadSubTasks": 8,"StopTaskCachedChangesNotApplied": true, <--- set this to true"StopTaskCachedChangesApplied": true, <--- set this to true"CommitRate": 50000,}

After full load completes, the task stops before it applies cached changes. While the task is stopped, create primary key indexes and secondary indexes on the target.

Next, resume the task. The task stops again after it applies cached changes.

Then, verify the migrated data with DMS validation output or manual verification before you resume the task for the CDC replication phase.

Tune the CommitRate setting

In the Task full load settings, tune the CommitRate setting to speed up the data extraction rate from the source. The default value is 10000. Increase this value when you migrate a large amount of data from the source table.

Example:

CommitRate=50000

Important: A higher CommitRate might affect performance. Monitor the replication instance and confirm that the instance has enough memory.

Configure the maxFileSize ECA

Add the following ECA on the target endpoint to specify the maximum size of any .csv file that DMS uses to transfer data to the target MySQL:

maxFileSize=250000;

The value is in KB. The default value is 32,768 KB or 32 MB. Valid values range from 1 to 1,048,576 KB or up to 1.1 GB.

Important: When you use a target instance such as MySQL, Aurora or MariaDB for full load, this option allows DMS to create a .csv file in the background to load data into the target instance. Use a value between 32 MB and 1 GB, but consider how much your target instance can handle. If multiple tasks load 1 GB of .csv file simultaneously, then this might cause overhead on your target instance. Use an instance with high computing power at the target.

Configure LOB settings for better performance

Use the limited LOB or inline LOB settings for better performance.

When you use limited LOB mode, you specify the maximum size of LOB column data. DMS pre-allocates resources and applies LOBs in bulk. If the LOB column exceeds the value that you specified in the task, then DMS truncates the data and sends warnings to the log file. DMS then sends warnings to the DMS log file. Limited LOB mode improves performance. However, before you run the task, identify the maximum LOB size of the data on the source and specify the Max LOB size parameter. It's a best practice to allocate enough memory to the replication instance to handle the task.

Inline LOB mode lets you migrate LOBs without truncation or performance. Inline LOB mode replicates both small and large LOBs. First, specify a value for the InlineLobMaxSize parameter. This parameter is available only when Full LOB mode is set to true. DMS transfers small LOBs inline for better efficiency. DMS migrates LOBs larger than the specified size in Full LOB mode through a lookup from the source table.

Important: Inline LOB mode works only during the full load phase. You must set InlineLobMaxSize in the task settings.

Determine your LOB size

Run the following query to list the tables that have LOB columns:

select tab.table_name,count(*) as columnsfrom information_schema.tables as tabinner join information_schema.columns as colon col.table_schema = tab.table_schemaand col.table_name = tab.table_nameand col.data_type in ('blob', 'mediumblob', 'longblob','text', 'mediumtext', 'longtext')where tab.table_schema = 'YOUR DATABASE NAME'. <---- enter database name hereand tab.table_type = 'BASE TABLE'group by tab.table_nameorder by tab.table_name;

Note: Replace YOUR-DATABASE-NAME with the name of your database.

Run the following query to check the size of a specific LOB column:

Select (max(length ())/(1024)) as "size in KB" from TABLE_NAME;

Note: Replace COLUMN_NAME with the name of your LOB column and TABLE_NAME with the name of your table.

Check the size of the LOB columns for all tables, and then populate the maximum size in Max LOB size (K).

Important: The Max LOB size (K) value greater than 63 KB affects the performance of a full load in limited LOB mode. During full load, DMS allocates memory by multiplying the Max LOB size (k) value by the commit rate. Then, DMS multiples the product by the number of LOB columns. When DMS can't pre-allocate that memory, DMS consumes SWAP memory. This degrades the performance of a full load. If you experience performance issues with limited LOB mode, then decrease the commit rate until you achieve an acceptable performance. Or, consider inline LOB mode for supported endpoints after you check the LOB distribution for the table.

For more information, see Setting LOB support for source databases in an AWS DMS task.

Related information

Database migration step-by-step walkthroughs

Migrating a MySQL Database to RDS for MySQL or Aurora MySQL