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

Resolution

Full LOB mode

When you use Full LOB mode, AWS DMS migrates LOBs regardless of their size. Because the maximum LOB size isn't defined, AWS DMS migrates LOBs one at a time. This can slow down your task. Although the migration is slower, AWS DMS doesn't truncate the data.

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, use Limited LOB mode when the largest LOB size is a few MB or less.

If you have LOBs that are larger than a few MBs, then 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 the tables.

Limited LOB mode

When you use Limited LOB mode, you specify the maximum size of LOB column data. This allows AWS DMS to allocate memory, and then 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.

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, specify the Max LOB size parameter when you use Limited LOB mode. To manage the task, it's a best practice to allocate enough memory to the replication instance.

Inline LOB mode

When you use Inline LOB mode, you replicate both small and large LOBs. This allows you to migrate LOBs and not truncate data or slow your task performance.

First, specify a value for the InlineLobMaxSize parameter that's available only when Full LOB mode is set to true. The AWS DMS task transfers the small LOBs inline, and the task is more efficient. Then, AWS DMS performs a lookup from the source table to migrate LOBs that are larger than the specified size in Full LOB mode. However, Inline LOB mode works only during the full load phase.

Important: When you specify the task settings, you must set the InlineLobMaxSize parameter.

Example task settings JSON file:

  
 {  "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