Optimizing AWS DMS performance

After preparing the source database, you can set up the AWS DMS service to create a task to perform one-time migration of data or to perform change data capture on an ongoing basis (full load, full load plus CDC or CDC only task). You will need to configure both the source and target endpoints. The only requirement to use AWS DMS is that one of your endpoints must be on an AWS service. You can't use AWS DMS to migrate from an on-premises database to another on-premises database.

Apart from the source database configuration, the networking between source database and AWS resources, and the target configuration, it is important to monitor the AWS DMS performance and ensure that you have chosen the right replication instance type to prevent any latency issues. The key bottlenecks are normally CPU, memory and network throughput of the selected replication instance. You will need a review of the CloudWatch metrics such as SwapUsage, CPUUtlization, NetworkTransmitThroughput, and NetworkReceiveThroughput (to name a few) to ensure the replication instance is not under resource pressure. Ideally, swap usage should be zero. You can find more replication instance metrics in the AWS DMS documentation. Choosing the appropriate replication instance depends on several factors of your use case. Refer to Choosing the best size for a replication instance for more information.

It is important to take into account the data volume while selecting the replication instance. The amount of parallelism is determined by CPU utilization. This also helps determines the number of tasks you can possibly have. Large objects (LOBs) also impact performance and memory consumption. Load frequency and transactions per second (TPS) influence memory usage. A high number of TPS or data manipulation language (DML) activities leads to high usage of memory. This happens because AWS DMS caches the changes until they are applied to the target. During CDC, this leads to swapping (writing to the physical disk due to memory overflow), which causes latency.

To speed up full load and also improve the CDC process, it’s a good practice to create separate AWS DMS tasks for tables which have a huge number of records or high volume of DML activities to prevent data migration from other smaller tables from slowing down. Note that too many DMS tasks will result in more concurrent transactions on your Redshift Datawarehouse which could impact performance of the data migration as well as impact other workloads using the Redshift cluster.

During a full load migration task, you can accelerate the migration of large or partitioned tables by splitting the table into segments and loading these segments in-parallel into the target. This can be accomplished by creating a rule of type table-settings with the parallel-load option in the migration task. Note that parallel load for use with table-setting rules are supported for some target endpoints. Using this parallel-load process, you can first have multiple threads unload multiple tables and views in parallel from the source endpoint. You can then have multiple threads migrate and load the same tables and views in parallel to the target endpoint. With this option, AWS DMS splits a full-load task into threads, with each table segment allocated to its own thread.

For some database engines, you can leverage existing partitions or sub-partitions itself to perform the parallel load. For tables with no partitions, you have the option of creating logical segments by defining boundaries explicitly in the form of ranges which can be loaded in parallel to improve the performance and to reduce the amount of time it takes for the full load.

For more examples, refer to Table and collection settings rules and operations. To indicate the maximum number of tables to load in parallel during the full load, set the MaxFullLoadSubTasks option.

For example, we have seen scenarios where a full load for a table with 1.2 billion records on source (Oracle) took 48 hours. The Parallel-load feature was used and the table was split into logical partitions by specifying ranges of column values with 0.5 million values in each segment, as shown in the following code snippet.

Ensure that the columns you choose to partition the data is either the primary key of the table, or a partition key or are indexed columns to significantly improve performance.

{
   "rule-type":"table-settings",
   "rule-id":"4",
   "rule-name":"4",
   "object-locator":{
      "schema-name":"*****",
      "table-name":"*****"
   },
   "parallel-load":{
      "type":"ranges",
      "columns":[
         "PURCHASE_ORDERS_ID"
      ],
      "boundaries":[
         [
            "500000"
         ],
         [
            "1000000"
         ],
         [
            "1500000"
         ],
         [
            "2000000"
         ],
                .
                .  // more segments 
                .
         [
            "22500000"
         ]
      ]	
   }
}

Along with the previous configuration, MaxFullLoadSubTasks was also increased from default eight to 44 in the Full Load task settings. With these settings, the full load time reduced from 48 hours to two hours 38 minutes.

Full load of a table having 1.2 billion rows completed in two hours 38 min

AWS periodically releases new versions of the AWS DMS replication engine software, with new features and fixes for known issues. Each version of the replication engine software has its own version number, to distinguish it from other versions. It is recommended to use latest version of AWS DMS service to leverage performance improvements as well as to get access to the new functionality.