AWS DMS全量导入大表反复重启问题求助

问题背景 #

• 迁移Microsoft SQL Server大型数据库，部分表数据量超8000万行，采用全量加载+CDC模式
• 全量加载阶段约60分钟后任务自动重启重试，CloudWatch日志报端点断开错误，默认重试10次后任务进入FAILED状态
• 已调整SQL Server端QueryTimeOut为2小时，问题未得到解决

当前任务配置 #

{
    "TargetMetadata": {
        "TargetSchema": "",
        "SupportLobs": true,
        "FullLobMode": false,
        "LobChunkSize": 0,
        "LimitedSizeLobMode": true,
        "LobMaxSize": 102400,
        "InlineLobMaxSize": 0,
        "LoadMaxFileSize": 0,
        "ParallelLoadThreads": 0,
        "ParallelLoadBufferSize": 0,
        "BatchApplyEnabled": false,
        "TaskRecoveryTableEnabled": false
    },
    "FullLoadSettings": {
        "TargetTablePrepMode": "DROP_AND_CREATE",
        "CreatePkAfterFullLoad": true,
        "StopTaskCachedChangesApplied": false,
        "StopTaskCachedChangesNotApplied": false,
        "MaxFullLoadSubTasks": 16,
        "TransactionConsistencyTimeout": 600,
        "CommitRate": 50000
    },
    "Logging": {
        "EnableLogging": true,
        "LogComponents": [
            {
                "Id": "SOURCE_UNLOAD",
                "Severity": "LOGGER_SEVERITY_DEFAULT"
            },
            {
                "Id": "TARGET_LOAD",
                "Severity": "LOGGER_SEVERITY_DEFAULT"
            },
            {
                "Id": "SOURCE_CAPTURE",
                "Severity": "LOGGER_SEVERITY_DEFAULT"
            },
            {
                "Id": "TARGET_APPLY",
                "Severity": "LOGGER_SEVERITY_DEFAULT"
            },
            {
                "Id": "TASK_MANAGER",
                "Severity": "LOGGER_SEVERITY_DEFAULT"
            }
        ]
    },
    "ControlTablesSettings": {
        "historyTimeslotInMinutes": 5,
        "ControlSchema": "",
        "HistoryTimeslotInMinutes": 5,
        "HistoryTableEnabled": false,
        "SuspendedTablesTableEnabled": false,
        "StatusTableEnabled": false
    },
    "StreamBufferSettings": {
        "StreamBufferCount": 3,
        "StreamBufferSizeInMB": 8,
        "CtrlStreamBufferSizeInMB": 5
    },
    "ChangeProcessingDdlHandlingPolicy": {
        "HandleSourceTableDropped": true,
        "HandleSourceTableTruncated": true,
        "HandleSourceTableAltered": true
    },
    "ErrorBehavior": {
        "DataErrorPolicy": "LOG_ERROR",
        "DataTruncationErrorPolicy": "LOG_ERROR",
        "DataErrorEscalationPolicy": "SUSPEND_TABLE",
        "DataErrorEscalationCount": 0,
        "TableErrorPolicy": "SUSPEND_TABLE",
        "TableErrorEscalationPolicy": "SUSPEND_TABLE",
        "TableErrorEscalationCount": 0,
        "RecoverableErrorCount": -1,
        "RecoverableErrorInterval": 5,
        "RecoverableErrorThrottling": true,
        "RecoverableErrorThrottlingMax": 1800,
        "ApplyErrorDeletePolicy": "IGNORE_RECORD",
        "ApplyErrorInsertPolicy": "LOG_ERROR",
        "ApplyErrorUpdatePolicy": "LOG_ERROR",
        "ApplyErrorEscalationPolicy": "LOG_ERROR",
        "ApplyErrorEscalationCount": 0,
        "ApplyErrorFailOnTruncationDdl": false,
        "FullLoadIgnoreConflicts": true,
        "FailOnTransactionConsistencyBreached": false,
        "FailOnNoTablesCaptured": false
    },
    "ChangeProcessingTuning": {
        "BatchApplyPreserveTransaction": true,
        "BatchApplyTimeoutMin": 1,
        "BatchApplyTimeoutMax": 30,
        "BatchApplyMemoryLimit": 500,
        "BatchSplitSize": 0,
        "MinTransactionSize": 1000,
        "CommitTimeout": 1,
        "MemoryLimitTotal": 1024,
        "MemoryKeepTime": 60,
        "StatementCacheSize": 50
    },
    "PostProcessingRules": null,
    "CharacterSetSettings": null,
    "LoopbackPreventionSettings": null
}

CloudWatch错误日志 #

2022-08-21T13:34:59 [SOURCE_UNLOAD ]E: Endpoint is disconnected [1020414] (endpointshell.c:3807)
2022-08-21T13:34:59 [SOURCE_UNLOAD ]E: Error executing source loop [1020414] (streamcomponent.c:1872)
2022-08-21T13:34:59 [TASK_MANAGER ]E: Stream component failed at subtask 5, component st_5_STAX7TAMIRB2R3PAIOSXO6TO7KT6PPIVMPGYLNQ [1020414] (subtask.c:1414)
2022-08-21T13:34:59 [SOURCE_UNLOAD ]E: Stream component 'st_5_STAX7TAMIRB2R3PAIOSXO6TO7KT6PPIVMPGYLNQ' terminated [1020414] (subtask.c:1594)
2022-08-21T13:34:59 [TASK_MANAGER ]E: Task error notification received from subtask 5, thread 0 [1020414] (replicationtask.c:2880)
2022-08-21T13:34:59 [TASK_MANAGER ]E: Endpoint is disconnected; Error executing source loop; Stream component failed at subtask 5, component st_5_STAX7TAMIRB2R3PAIOSXO6TO7KT6PPIVMPGYLNQ; Stream component 'st_5_STAX7TAMIRB2R3PAIOSXO6TO7KT6PPIVMPGYLNQ' terminated [1020414] (replicationtask.c:2888)

解决方案 #

一、调整DMS任务重试次数 #

修改任务ErrorBehavior配置中的RecoverableErrorCount参数，将其从默认的-1（无限重试）改为3或4：

"ErrorBehavior": {
    // 保留其他原有配置
    "RecoverableErrorCount": 3, // 可设置为3或4
    // 保留其他原有配置
}

二、解决全量加载端点断开问题 #

针对超大型表的迁移场景，从以下维度优化配置：

1. 优化全量加载并行度与批次设置

• 降低MaxFullLoadSubTasks：当前设置为16，过高的并行度会导致源端SQL Server连接过载，建议调整为8或更低
• 减小CommitRate：当前50000的批次量过大，改为20000-30000，降低单批次数据传输压力
• 启用任务恢复表：在TargetMetadata中设置TaskRecoveryTableEnabled为true，避免重试时重复加载已完成的数据

修改示例：

"TargetMetadata": {
    // 保留其他原有配置
    "TaskRecoveryTableEnabled": true
},
"FullLoadSettings": {
    // 保留其他原有配置
    "MaxFullLoadSubTasks": 8,
    "CommitRate": 25000
}

2. 优化源端SQL Server连接超时配置

• 调整SQL Server的remote query timeout参数，设置为大于3小时（10800秒）：

  sp_configure 'remote query timeout', 10800;
  RECONFIGURE;
  

• 检查SQL Server连接池设置，确保有足够的连接数分配给DMS任务

3. 调整流缓冲区与内存配置

• 增大StreamBufferSizeInMB：当前为8MB，建议调整为16或32MB，提升数据传输缓冲区容量
• 增大MemoryLimitTotal：当前1024MB，建议调整为2048MB，为大表迁移提供足够内存资源

修改示例：

"StreamBufferSettings": {
    "StreamBufferCount": 3,
    "StreamBufferSizeInMB": 16,
    "CtrlStreamBufferSizeInMB": 5
},
"ChangeProcessingTuning": {
    // 保留其他原有配置
    "MemoryLimitTotal": 2048
}

4. 拆分大表加载（终极方案）

若以上优化仍无效，可将超大型表按主键、时间字段等维度拆分，分批次创建多个DMS任务分别加载，最终合并到目标表。

内容的提问来源于stack exchange，提问作者Bob