By using AWS re:Post, you agree to the Terms of Use

ETL from an Oracle database into RDS Postgres too slow

0

What performance improvements can be implemented when using AWS Database Migration Service (DMS) to migrate a full load from Oracle on EC2 to AWS Relational Database Service (RDS) for PostgreSQL?

1 Answer
1
Accepted Answer

Here are some of the basic points that you should be aware while from Oracle (on EC2 or on-premises), especially applicable to large source databases.

  • By default DMS migrates 8 tables in parallel. If you have larger number of tables that are small in size, recommended would be to increase this setting to migrate more than 8 tables at a time (up to 49). This can be done modifying task "Advanced settings" or by modifying the parameter "MaxFullLoadSubTasks" from CLI.

  • By default DMS uses commit rate of 10000 which can be increased up to 50000. This indicates the maximum number of events that can be transferred together and will help in one-time migration. This can be done modifying task "Advanced settings" or by modifying the parameter "CommitRate" from CLI.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Tasks.CustomizingTasks.TaskSettings.FullLoad.html

  • You can improve the performance of full load by using parallel load strategy of DMS using which DMS will read data from single table in multiple threads. This can be done by modifying the task table mappings to specify the number of threads.

https://aws.amazon.com/blogs/database/introducing-aws-dms-replication-engine-version-3-1-2/

  • It is always recommended to create task with limited lob size. So recommendation would be to identify the size of lob columns from source database and use it as the "maximum size of lob column" in task settings.

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_BestPractices.html

  • You can increase the maximum size (in KB) of any .csv file used to transfer data to PostgreSQL by modifying the extra connect attribute (maxFileSize) for target Postgres endpoint. By default it is set to 32MB and you can increase it up to 1.1 GB

https://docs.aws.amazon.com/dms/latest/userguide/CHAP_Target.PostgreSQL.html#CHAP_Target.PostgreSQL.ConnectionAttrib

EXPERT
answered 2 years ago

You are not logged in. Log in to post an answer.

A good answer clearly answers the question and provides constructive feedback and encourages professional growth in the question asker.

Guidelines for Answering Questions