How do I troubleshoot high source latency on an AWS DMS task?

Short description

Source latency occurs during the CDC phase of a task. To monitor source latency, review the CDCLatencySource Amazon CloudWatch metric.

To determine whether the bottleneck is source-side or target-side, compare the CDCLatencySource and CDCLatencyTarget metrics. If both metrics are high, then investigate source latency first because target latency is always equal to or greater than source latency. If CDCLatencySource is high and CDCLatencyTarget equals CDCLatencySource, then the bottleneck is at the source endpoint. If only CDCLatencyTarget is high, then investigate target latency instead.

The following conditions cause high source latency:

• The source database has limited resources.
• The AWS DMS replication instance has limited resources.
• The network speed between the source database and the AWS DMS replication instance is slow.
• The source transaction log contains large uncommitted transactions.
• The task migrates large objects (LOBs) or has too many tables in a single task.
• The Oracle source database uses LogMiner for ongoing replication.
• The task was stopped for an extended period and must consume the transaction backlog on resume.
• The DMS instance storage exceeds 90%.
• The source, target, and replication instance are in different AWS Regions.

Resolution

Resolve source database resource limitations

Use native monitoring for your source DB engine to confirm that the database doesn't have a performance bottleneck such as memory contention or I/O saturation.

Check source transaction log growth. If the number or size of transaction logs increased during the latency period compared to before, then the source workload exceeds the replication instance's processing capacity.

Resolve replication instance resource limitations

Monitor the replication instance metrics such as CPUUtilization, FreeStorageSpace, and FreeableMemory. Confirm that the replication instance has enough resources to manage your task. If the replication instance doesn't have enough resources to manage your task, then scale up to a larger instance type.

If FreeStorageSpace drops below 10% of total allocated storage, then AWS DMS pauses reads from the source. Identify the "Reading from source is paused. Total disk usage exceeded the limit 90%" message in the task logs.

Resolve slow network between the source and replication instance

A single AWS DMS task can't use the full network bandwidth. If you have a busy production database with a high volume of changes, then increase the network bandwidth. For example, use AWS Direct Connect connections.

If the source, target, and replication instance are in different Regions, then network latency increases. Place the replication instance in the same Region as the source database to reduce CDC latency.

Resolve latency from large uncommitted transactions

During ongoing replication, AWS DMS reads incoming changes from the transaction logs. AWS DMS forwards only committed changes to the target. If the source has large uncommitted transactions, then latency increases while AWS DMS waits for the commit.

When the source database writes a large dataset with fewer commits, AWS DMS continues to read from the transaction log. But, AWS DMS doesn't apply changes to the target until the entire transaction is committed. As source latency increases, target latency also increases.

To confirm that the task is progressing, monitor the replication task metrics for CDC and turn on detailed debug logging for the SOURCE_CAPTURE component.

Resolve latency from large objects or too many tables

AWS DMS migrates LOB data for ongoing replication in two phases. First, AWS DMS creates a new row in the target table with all columns except those that have LOBs. Then, AWS DMS updates the rows that have LOBs. If you have a source database that frequently updates tables that have LOB columns, then you might see source latency.

To reduce LOB-related latency, use limited LOB mode or inline LOB mode instead of full LOB mode. For more information, see Migrating LOBs.

If the task has too many tables, or multiple tables contain LOB columns, then split your task into multiple tasks. If you have sets of tables that don't participate in common transactions, then divide your migration into multiple tasks. This can help increase performance. Transactional consistency is maintained within a task, so it's important that tables in separate tasks don't participate in common transactions. Also, each task independently reads the transaction stream, so don't put too much stress on the source database. For more information, see the Best practices for AWS Database Migration Service.

Resolve latency on Oracle sources that use LogMiner

If your source database generates a large number of redo logs, then use the binary reader instead of LogMinor.

To determine the redo log generation rate, run the following query on the source:

SELECT
    trunc(COMPLETION_TIME, 'HH') Hour,
    thread#,
    round(sum(BLOCKS * BLOCK_SIZE) / 1024 / 1024 / 1024) GB
FROM v$archived_log
WHERE completion_time > sysdate - 1
GROUP BY trunc(COMPLETION_TIME, 'HH'), thread#
ORDER BY 1;

Use the following thresholds to determine the appropriate action:

• If redo generation exceeds 10 GB per hour, then use Binary Reader instead of LogMiner.
• If redo generation exceeds 80 GB per hour, then AWS DMS might not keep up with the source changes regardless of the reader method.

To switch to Binary Reader, set the following extra connection attributes (ECAs) on the Oracle source endpoint:

useLogminerReader=N;useBfile=Y

Binary Reader also supports Oracle ASM sources. For Oracle ASM, set parallelASMReadThreads to a value up to 8 and readAheadBlocks to a value up to 200000. Set asmUsePLSQLArray=true for up to 10x improvement in redo transfer speed. For more information, see Using Oracle LogMiner or AWS DMS Binary Reader for CDC.

For Oracle RAC, use the SCAN listener in the asm_server ECA for load-balancing across nodes.

Confirm that ArchivedLogsOnly is not set to Y. This setting forces AWS DMS to wait for redo logs to archive before it reads them and increases latency.

If supplemental logging is active on all columns, then reduce it to only the required columns. Supplemental logging on all columns generates extra redo, and increases the volume AWS DMS must process.

Resolve latency on MySQL sources

Long-running transactions cause latency spikes on MySQL sources because MySQL writes only committed transactions to the binary log. To identify long-running transactions, run the following command on the source:

SHOW FULL PROCESSLIST;

Also check the slow query log. Restructure source transactions to reduce query run time or increase commit frequency.

If the source has a high volume of transactions, then run the following command to compare the number and size of binary logs that generated during the latency period to a normal period:

SHOW BINARY LOGS;

To retrieve the current DMS processing position, turn on debug logging for the SOURCE_CAPTURE component.

By default, AWS DMS polls the binary log every 5 seconds. Reduce the EventsPollInterval value to improve performance for high-throughput sources.

For MySQL 5.7.2 and later, split tables into separate tasks to take advantage of reduced lock contention on the binary log. For MySQL versions earlier than 5.7.2, consolidate tasks that have CDC components because the binary log lock creates contention across concurrent readers.

Resolve latency on SQL Server sources

Source latency on SQL Server usually results from transaction log scan throttling due to resource constraints.

Complete the following steps:

1. Check the time of latency spikes by reviewing CDCLatencySource CloudWatch metrics or Throughput Monitoring messages in the task logs.
2. Check whether the size of active transaction logs or log backups increased during the spike.
3. Verify whether a maintenance job or index rebuild ran during that time.

Index rebuilds generate large amounts of transaction log activity and cause latency spikes. To reduce the impact, use the BULK_LOGGED recovery model for offline rebuilds to reduce the number of logged events. If possible, stop the task during index rebuilds. Also schedule index rebuilds during non-peak hours.

For Amazon RDS for SQL Server, adjust the MS-CDC maxscans and maxtrans parameters so that maxtrans multiplied by maxscans equals the average number of DML events per day. To check log reuse wait, run the following query:

SELECT log_reuse_wait, log_reuse_wait_desc, name FROM sys.databases WHERE name = db_name();

If log_reuse_wait_desc returns REPLICATION, then the log backup retention is caused by MS-CDC latency.

Increase executeTimeout and cdcTimeout to prevent AWS DMS queries from timing out during high-latency periods.

For Multi-AZ RDS instances, set the CDC polling interval to 86399 on both primary and secondary nodes.

For AlwaysOn configurations, use the listener or primary replica IP as the endpoint address. Set MultiSubnetFailover=Yes for multi-subnet configurations.

Resolve latency on PostgreSQL sources

Long-running transactions prevent PostgreSQL from releasing WAL segments, which increases source latency. To identify long-running transactions, query the pg_replication_slots view. If restart_lsn doesn't update, PostgreSQL can't release WALs.

Run the following query to identify active long-running transactions:

SELECT
    pid,
    age(clock_timestamp(), query_start),
    usename,
    query
FROM pg_stat_activity
WHERE query != ''
    AND query NOT ILIKE '%pg_stat_activity%'
ORDER BY query_start desc;

If the source has a high workload and uses the test_decoding plugin, switch to the pglogical plugin. The test_decoding plugin sends all database changes to the replication instance, and AWS DMS filters them after receipt. The pglogical plugin filters WAL changes at the source and sends only relevant changes, which reduces network throughput and source latency.

For Aurora PostgreSQL version 13 and later, the logical_decoding_work_mem parameter (default 64 MB) determines memory allocation for logical decoding. If data exceeds this limit, AWS DMS spills transaction data to disk, which causes high source latency. Set logical_decoding_work_mem significantly higher than work_mem to prevent spill files.

Reduce long-running transactions and sub-transactions. Avoid large burst operations such as deleting an entire table in one transaction.

Monitor TransactionLogsDiskUsage and ReplicationSlotDiskUsage CloudWatch metrics to identify WAL accumulation.

General configuration improvements

Take the following actions:

• Turn on BatchApplyEnabled for sources that have a large number of changes to improve target apply performance.
• Use limited or inline LOB mode instead of full LOB mode.
• If you start a CDC-only task from a position far in the past, expect increased latency until AWS DMS processes the backlog.
• Upgrade to the latest AWS DMS version. Newer versions include performance improvements and bug fixes.

Related information

Improving the performance of an AWS DMS migration

SQL Server diagnostic support scripts

Oracle diagnostic support scripts

Diagnostic support scripts for MySQL-compatible databases

Working with diagnostic support scripts in AWS DMS