Very Large ASYNC_NETWORK_IO on SQL Server when migrating data from SQL Server EC2 to Aurora Postgres using DMS

Hi,

Based on description here seems like there is a CDC Latency issue here.

Basically "Reading from source is paused. Total storage used by swap files exceeded the limit 1048576000 bytes" This is indicating target can not keep up with the change for some reason thus in order to prevent storage full for replication instance, we have a hard code limit for swap file set to 1 GB. When reading pause from source, this can increase CDCLatencySource as well can eventually enter in a bad latency loop.

In such case, you will be able to see high CDCLatencyTarget: [+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Monitoring.html#CHAP_Monitoring.Metrics.Task

The gap, in seconds, between the first event timestamp waiting to commit on the target and the current timestamp of the AWS DMS instance. CDCLatencyTarget represents the latency between replication instance and target. When CDCLatencyTarget is high, it indicates the process of applying change events to the target is delayed. To identify latency in an ongoing replication, you can view this metric together with CDCLatencySource. If CDCLatencyTarget is high but CDCLatencySource isn’t high, investigate if:

    No primary keys or indexes are in the target

    Resource bottlenecks occur in the target or replication instance

    Network issues reside between replication instance and target

To resolve the issue I would suggest first go over [+] https://aws.amazon.com/premiumsupport/knowledge-center/dms-high-target-latency/ for general guidance.

Identify any performance bottle neck from target, if performance insight is enabled, that will be helpful in case to troubleshoot query causing wait time block or lock.

Once Target Latency goes down make sure no Source Latency by referring to [+] https://aws.amazon.com/premiumsupport/knowledge-center/dms-high-source-latency/

Also note: If ECA such as readBackupOnly is used for source endpoint, then it is likely to see some source latency. [+] https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Source.SQLServer.html#CHAP_Source.SQLServer.ConnectionAttrib

I hope above information can help resolving the issue.

If there's not a huge load into the source database, this could be a problem with the Replication Instance itself. Can you please open up an AWS support case and they can check up on that.