How can I improve the speed of an AWS DMS task that has 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. This can slow down 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 with multiple tables, 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. This allows AWS DMS to pre-allocate resources and apply the LOBs in bulk. If the size of the LOB columns exceeds the size that you specified in the task, then AWS DMS truncates the data. AWS DMS then sends warnings to the AWS DMS log file. Using Limited LOB mode improves 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 data or slowing your task performance, 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 LOBs that are larger than the specified size in Full LOB mode by performing a lookup from the source table. 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": "abc",
    "SupportLobs": true,
    "FullLobMode": true,
    "LobChunkSize": 64,
    "LimitedSizeLobMode": false,
    "LobMaxSize": 0,
    "InlineLobMaxSize": 32,
    "LoadMaxFileSize": 0,
    "ParallelLoadThreads": 0,
    "ParallelLoadBufferSize": 0,
    "BatchApplyEnabled": false,
    "TaskRecoveryTableEnabled": false,
    "ParallelLoadQueuesPerThread": 0,
    "ParallelApplyThreads": 0,
    "ParallelApplyBufferSize": 0,
    "ParallelApplyQueuesPerThread": 0
  }

Related Information

Migrating Large Binary Objects (LOBs)

Target Metadata Task Settings

Best Practices for AWS Database Migration Service