How can I improve the speed of an AWS DMS task that has LOB data?

Last updated: 2019-10-01

My AWS Database Migration Service (AWS DMS) migration task is slow. How can I improve the speed of an AWS DMS task that has large object (LOB) data?

Short Description

AWS DMS offers the following options when migrating LOB data:

  • Full LOB mode
  • Limited LOB mode
  • Inline LOB mode

Resolution

Full LOB mode

When using Full LOB mode, AWS DMS migrates LOBs regardless of their size. Because AWS DMS doesn't know the size of the LOB data that needs to be migrated, AWS DMS migrates LOBs one at a time, which can slow your task. Although the migration is slower, the advantage is that data isn't truncated. To improve the performance of a task that uses Full LOB mode and has multiple tables, first identify the size of the largest LOB in your database. Then, you can use Limited LOB mode if the size of the largest LOB size isn't more than a few megabytes. If you have LOBs that are larger than a few megabytes, then you can create a separate AWS DMS task with Full LOB mode. It's a best practice to create the separate task on a new replication instance to migrate these tables alone.

Limited LOB mode

When using Limited LOB mode, you specify the maximum size of LOB column data, which allows AWS DMS to preallocate resources and apply the LOBs in bulk. If the size of the LOB columns exceeds the size that is specified in the task, then AWS DMS truncates the data and sends warnings to the AWS DMS log file. Using Limited LOB mode can improve performance, but before you run the task, you must identify the maximum LOB size of the data on the source. Then, you must specify the Max LOB size parameter when using Limited LOB mode. It's a best practice to be sure that you have enough memory allocated to the replication instance to handle the task.

Inline LOB mode

When using Inline LOB mode, you can migrate LOBs without truncating the data or slowing the performance of your task by replicating both small and large LOBs. First, specify a value for the InlineLobMaxSize parameter, which is available only when Full LOB mode is set to true.The AWS DMS task transfers the small LOBs inline, which is more efficient. Then, AWS DMS migrates the large LOBs by performing a lookup from the source table. When you choose Inline LOB mode, AWS DMS checks which LOBs to migrate inline. Then, LOBs larger than the specified size are migrated in the Full LOB mode. However, Inline LOB mode works only during the full load phase.

Important: You must set the InlineLobMaxSize when you specify the task settings for your task.

{
"TargetMetadata": {
"TargetSchema": "",
"SupportLobs": true,
"FullLobMode": false,
"LobChunkSize":
    64,
"LimitedSizeLobMode": true,
"LobMaxSize": 32,
"InlineLobMaxSize": 5,
"LoadMaxFileSize": 0,
"ParallelLoadThreads": 0,
"ParallelLoadBufferSize":0,
"BatchApplyEnabled": false,
"TaskRecoveryTableEnabled": false
}