Assistance Required with AWS DMS for Large Table Replication

Hi,

Did you think of an initial bulk upload of your tables and then applying DMS from a known CDC Start point ?

See https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Task.CDC.html#CHAP_Task.CDC.StartPoint

This way you can accelerate the initial load of the data in a quasi up-to-date state and catch up with updates from the CDC start point.

Best,

Didier

Thank you for providing such a detailed description of your AWS DMS replication challenges. I understand you're facing difficulties replicating a 5 TB PostgreSQL table to an Aurora cluster. Let me address your concerns and provide some recommendations.

1. Allocating additional space: The error you're encountering suggests that the Aurora cluster has run out of storage space. To resolve this, you can:

• Increase the maximum storage size for your Aurora Serverless v2 cluster. This can be done through the AWS Management Console or AWS CLI.
• Consider enabling Aurora Auto Scaling, which automatically increases storage as needed.
• If possible, temporarily move some data to another storage solution to free up space for the index creation process.

2. Strategies for efficient large table replication:

a. Table Partitioning with Parallel Load: You mentioned you've tried partitioning, which is a good approach. To optimize this further:

• Use the "parallel load" feature in AWS DMS for partitioned tables.
• Configure the auto-partition option in the table mapping of your DMS task.
• This allows DMS to load partitions in parallel, potentially speeding up the process.

b. Range Partitioning for Non-Partitioned Tables: If your table isn't partitioned on the source:

• Use the "ranges" option in DMS task settings.
• This allows you to specify range boundaries manually, enabling parallel loading of different sections of the table.

c. Optimize Task Settings:

• Adjust the BatchApplyEnabled option to group transactions, potentially improving performance.
• Tune the ChangeProcessingTuning settings to optimize change processing.

d. Use Table Mapping to Split the Large Table:

• Create multiple tasks, each replicating a subset of the large table.
• Use column filters or partition filters in the table mapping to divide the table logically.

e. Consider a Two-Step Approach:

1. Perform an initial load without primary keys for speed.
2. Set up a separate CDC (Change Data Capture) task to capture changes during and after the initial load.
3. After the initial load, create the primary keys and apply the captured changes.

This approach maintains data integrity while leveraging the speed of replication without primary keys.

f. Optimize the Target Database:

• Ensure proper indexing on the Aurora side to improve write performance.
• Monitor and adjust the Aurora cluster's configuration, particularly focusing on I/O-related parameters.

g. Use DMS Data Validation:

• Enable the data validation feature in DMS to ensure data integrity without relying solely on primary keys during replication.

Remember to thoroughly test these approaches in a non-production environment before implementing them in your production migration. Each strategy may have different impacts depending on your specific database structure and requirements.

By combining these strategies and continuously monitoring the replication process, you should be able to improve the efficiency of your large table replication while maintaining data integrity. If you continue to face issues, consider engaging AWS Support for personalized assistance with your specific use case.
Sources
AWS DMS best practices for moving large tables with table parallelism settings | AWS Database Blog
Step-by-step SQL Server AlwaysOn databases on primary replica to Amazon Aurora PostgreSQL migration walkthrough - Database Migration Guide
Troubleshooting target latency issues - AWS Database Migration Service