DMS serverless replication stuck on single small table with LOBs

Question

I'm doing a test migration (Oracle to Postgres) of a single table (9K rows, 0.01GB, has 1 BLOB field) to prove that it works.
But it becomes stuck without further events for 1h+ .

Replication type: FULL_LOAD, Lob mode: Full, chunk 64kb, Max inline 1000KB.
Target table has BYTEA column type for BLOBs.

In Table stats panel, I have only "Before load" state.

In CW I have:
dms-serverless-replication-orchestrator
*'Replication is now running in FULL_LOAD mode.'*

dms-serverless-serv-res-id
*Table X contains LOB columns, change working mode to default mode  (odbc_endpoint_imp.c:6083)
 Table X has Optimized Full LOB Support, inline limit = 1024000 bytes  (odbc_endpoint_imp.c:6089)*
 
In DB as running queries, I see only:
16225	00:59:13.816955	dmsuser	**ROLLBACK**
Unsure what is trying to rollback, no data was inserted yet, the target DB contains only empty tables.

Accepted Answer

Hello,

There are many possible scenarios, I would first validate if there are any warnings/errors in the Task log. Assuming, you have already validated those, in general LOB migrations are slow.

AWS DMS migrates LOB data in two phases:

1.AWS DMS creates a new row in the target table and populates the row with all data except the associated LOB value.

2.AWS DMS updates the row in the target table with the LOB data.

I would suggest you to try Limited LOB option.

Full LOB mode – In full LOB mode AWS DMS migrates all LOBs from source to target regardless of size. In this configuration, AWS DMS has no information about the maximum size of LOBs to expect. Thus, LOBs are migrated one at a time, piece by piece. Full LOB mode can be quite slow.

Limited LOB mode – In limited LOB mode, you set a maximum LOB size for DMS to accept. That enables DMS to pre-allocate memory and load the LOB data in bulk. LOBs that exceed the maximum LOB size are truncated, and a warning is issued to the log file. In limited LOB mode, you can gain significant performance over full LOB mode. We recommend that you use limited LOB mode whenever possible.

If this doesn't help, you may need to further dive deep and review all the logs to understand the source of the issue.

Thanks

Answer

Good to know. Limited LOB helped.
Usually, it's a good practice to schedule a separate Job for LOB tables and take advantage of Table level LOB settings. Also you can explore Inline LOBs. For more information, please refer below LOB best practices

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html#CHAP_BestPractices.LOBS

DMS serverless replication stuck on single small table with LOBs

Relevant content