For some database engines, you can segment the tables and views by existing partitions or sub partitions. Otherwise, you can segment any table or view by ranges of column values that you specify.
That would mean imagine you have a table which is very large and using parallel-load mapping rule, you can migrate the table using parallel threads.
For example, if I create the table on the source :
SQL> select * from prashant.table1;
ID
----------
1
2
3
4
5
6
7
8
8 rows selected.
I gave the following mapping rule:
{
"rules": [
{
"rule-type": "selection",
"rule-id": "1",
"rule-name": "1",
"object-locator": {
"schema-name": “prashant",
"table-name": “table1"
},
"rule-action": "include",
"filters": []
},
{
"rule-type": "table-settings",
"rule-id": "2",
"rule-name": "2",
"object-locator": {
"schema-name": “prashant",
"table-name": “table1"
},
"parallel-load": {
"type": "ranges",
"columns": [
"ID"
],
"boundaries": [
[
"2"
],
[
"4"
],
[
"6"
]
]
}
}
]
}
The above mapping rule will fetch in the following way :
==> 1st segment will be below 2
==> 2nd segment will be from 2 to 4
==> 3rd segment will be from 4 to 6
==> 4th segment will be from 6 and above
This will pull the data for the concerned table in parallel and load the data to the target in parallel as well.
For more information, please give it a read to understand with which engines you can use parallel load
[+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.
https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TableMapping.htmlSelectionTransformation.Tablesettings.html#CHAP_Tasks.CustomizingTasks.TableMapping.SelectionTransformation.Tablesettings.ParallelLoad
-------------------------DB2 ROW TABLES COUNT boundaries------------------
WITH NTILE_VALUES AS (
SELECT
COLUMN_NAME,
NTILE(10) OVER (ORDER BY COLUMN_NAME) AS BUCKET_NUMBER
FROM YOUR_TABLE
)
SELECT
BUCKET_NUMBER,
MIN(COLUMN_NAME) AS LOWER_BOUND,
MAX(COLUMN_NAME) AS UPPER_BOUND,
COUNT(*) AS PARTITION_SIZE
FROM NTILE_VALUES
GROUP BY BUCKET_NUMBER
ORDER BY BUCKET_NUMBER;
To improve the performance when migrating a large table, break the migration into more than one task. To break the migration into multiple tasks using row filtering, use a key or a partition key.
For example, if you have an integer primary key ID from 1 to 8,000,000, you can create eight tasks using row filtering to migrate 1 million records each.
DMS Task To summarize these changes suggestion consider setting for parallel load the following
FullLoadSettings
MaxFullLoadSubTasks default 8 > 49 Migrate the maximum number of tables to load in parallel
TransactionConsistencyTimeout > 60 Start the DMS Tasks immediately
CommitRate > 50000 Migrate data in transaction sizes
StreamBufferSettings
StreamBufferCount > 12 Increasing number improve data extraction
StreamBufferSizeInMB default 8 MB > 32 Increasing migration Task Log & need to increase the value for this option when you work with very large LOBs
CtrlStreamBufferSizeInMB default 5 > 8 Set to the maximum when working with a very large number of tables, such as tens of thousands of tables
TargetMetadata
LoadMaxFileSize > 0 increased maxFileSize to 500 MB to ensure faster migration of the single table initially.
AWS DMS During a full load task, AWS DMS performs a full table scan of the source table for each table processed in parallel.
If you find that you're overburdening your source database, reduce the number of tasks or tables for each task for your migration. Each task gets source changes independently, so consolidating tasks can decrease the change capture workload.
A task settings JSON file can look like the following.
{
"TargetMetadata": {
"TargetSchema": "",
"SupportLobs": true, LOB Setting
"FullLobMode": false,If set option to true, then you must enter a value for the
LobChunkSize option
"LobChunkSize": 64, Increasing the value for LobChunkSize above 64 kilobytes can
cause task failures.
"LimitedSizeLobMode": true, LOB Setting
"LobMaxSize":32, recommended value for LobMaxSize is 102400 kilobytes (100 MB)
"InlineLobMaxSize": 0, the range is 1 –102400 kilobytes (100 MB)
"LoadMaxFileSize": 0, LOB Setting
"ParallelLoadThreads": 0,
"ParallelLoadBufferSize":0, maximum no of records to store value is 1,000
"ParallelLoadQueuesPerThread": 1,
"ParallelApplyThreads": 0,
"ParallelApplyBufferSize": 100,
"ParallelApplyQueuesPerThread": 1,
"BatchApplyEnabled": false, running ongoing replication from a source where rate of change is high
"TaskRecoveryTableEnabled": false
},
"FullLoadSettings": {
"TargetTablePrepMode": "DO_NOTHING", Depends upon the migration strategy
"CreatePkAfterFullLoad": false,
"StopTaskCachedChangesApplied": false,
"StopTaskCachedChangesNotApplied": false,
"MaxFullLoadSubTasks": 8, 49 Migrate the maximum number of tables
"TransactionConsistencyTimeout": 600, 60 Start the DMS Tasks immediately
"CommitRate": 10000 50000 Migrate data in transaction sizes
},
"TTSettings" : { log and debug replication tasks
"EnableTT" : true,
"TTS3Settings": {
"EncryptionMode": "SSE_KMS",
"ServerSideEncryptionKmsKeyId": "arn:aws:kms:us-west-2:112233445566:key/myKMSKey",
"ServiceAccessRoleArn": "arn:aws:iam::112233445566:role/dms-tt-s3-access-role",
"BucketName": "myttbucket",
"BucketFolder": "myttfolder",
"EnableDeletingFromS3OnTaskDelete": false
},
"TTRecordSettings": {
"EnableRawData" : true,
"OperationsToLog": "DELETE,UPDATE",
"MaxRecordSize": 64
}
},
"Logging": {
"EnableLogging": false
},
"ControlTablesSettings": {
"ControlSchema":"",
"HistoryTimeslotInMinutes":5,
"HistoryTableEnabled": false,
"SuspendedTablesTableEnabled": false,
"StatusTableEnabled": false
},
"StreamBufferSettings": {
"StreamBufferCount": 3, 12 Increasing number improve data extraction
"StreamBufferSizeInMB": 8, 32 Increasing migration Task Log
"CtrlStreamBufferSizeInMB" 5 8 Set to the maximum
},
"ChangeProcessingTuning": {
"BatchApplyPreserveTransaction": true, during change data capture (CDC)
"BatchApplyTimeoutMin": 1,
"BatchApplyTimeoutMax": 30,
"BatchApplyMemoryLimit": 500,
"BatchSplitSize": 0,
"MinTransactionSize": 1000,
"CommitTimeout": 1,
"MemoryLimitTotal": 1024,
"MemoryKeepTime": 60,
"StatementCacheSize": 50 applying changes to the target maximum value is 200
},
"ChangeProcessingDdlHandlingPolicy": {handle change processing DDL
"HandleSourceTableDropped": true,
"HandleSourceTableTruncated": true,
"HandleSourceTableAltered": true
},
"LoopbackPreventionSettings": {
"EnableLoopbackPrevention": true,
"SourceSchema": "LOOP-DATA",
"TargetSchema": "loop-data"
},
"CharacterSetSettings": {
"CharacterReplacements": [ {
"SourceCharacterCodePoint": 35,
"TargetCharacterCodePoint": 52
}, {
"SourceCharacterCodePoint": 37,
"TargetCharacterCodePoint": 103
}
],
"CharacterSetSupport": {
"CharacterSet": "UTF16_PlatformEndian",
"ReplaceWithCharacterCodePoint": 0
}
},
"BeforeImageSettings": {
"EnableBeforeImage": false,à Turns on before imaging when set to true
"FieldName": "", àWhen EnableBeforeImage is true, Fieldname can't be empty.
"ColumnFilter": pk-only
},
"ErrorBehavior": {
"DataErrorPolicy": "LOG_ERROR",
"DataTruncationErrorPolicy":"LOG_ERROR",
"DataErrorEscalationPolicy":"SUSPEND_TABLE",
"DataErrorEscalationCount": 50,
"TableErrorPolicy":"SUSPEND_TABLE",
"TableErrorEscalationPolicy":"STOP_TASK",
"TableErrorEscalationCount": 50,
"RecoverableErrorCount": 0,
"RecoverableErrorInterval": 5,
"RecoverableErrorThrottling": true,
"RecoverableErrorThrottlingMax": 1800,
"ApplyErrorDeletePolicy":"IGNORE_RECORD",
"ApplyErrorInsertPolicy":"LOG_ERROR",
"ApplyErrorUpdatePolicy":"LOG_ERROR",
"ApplyErrorEscalationPolicy":"LOG_ERROR",
"ApplyErrorEscalationCount": 0,
"FullLoadIgnoreConflicts": true
},
"ValidationSettings": {
"EnableValidation": false,
"ValidationMode": "ROW_LEVEL",
"ThreadCount": 5,
"PartitionSize": 10000,
"FailureMaxCount": 1000,
"RecordFailureDelayInMinutes": 5,
"RecordSuspendDelayInMinutes": 30,
"MaxKeyColumnSize": 8096,
"TableFailureMaxCount": 10000,
"ValidationOnly": false,
"HandleCollationDiff": false,
"RecordFailureDelayLimitInMinutes": 1,
"SkipLobColumns": false,
"ValidationPartialLobSize": 0,
"ValidationQueryCdcDelaySeconds": 0
}}
https://aws.amazon.com/blogs/database/debugging-your-aws-dms-migrations-what-to-do-when-things-go-wrong-part-3/