CODEWITHSUNDEEP
postgresqlamazon-web-servicesrds
ddd
ddd

Reputation: 5029

Loading data to Postgres RDS is still slow after tuning parameters

We have created a RDS postgres instance (m4.xlarge) with 200GB storage (Provisioned IOPS). We are trying to upload data from company data mart to the 23 tables in RDS using DataStage. However the uploads are quite slow. It takes about 6 hours to load 400K records.

Then I started tuning the following parameters according to Best Practices for Working with PostgreSQL:

autovacuum  0
checkpoint_completion_target  0.9
checkpoint_timeout  3600
maintenance_work_mem  {DBInstanceClassMemory/16384}
max_wal_size  3145728
synchronous_commit  off

Other than these, I also turned off multi AZ and back-up. SSL is enabled though, not sure this will change anything. However, after all the changes, still not much improvement. DataStage is uploading data in parallel already ~12 threads. Write IOPS is around 40/sec. Is this value normal? Is there anything else I can do to speed up the data transfer?

Upvotes: 1

Views: 2285

Answers (1)

Joe Love
Joe Love

Reputation: 5932

In Postgresql, you're going to have to wait 1 full round trip (latency) for each insert statement written. This latency is the latency between the database all the way to the machine where the data is being loaded from.

In AWS you have many options to improve performance.

  1. For starters, you can load your raw data onto an EC2 instance and start importing from there, however, you will likely not be able to use your dataStage tool unless it can be loaded directly on the ec2 instance.

  2. You can configure dataStage to use batch processing where each insert statement actually contains many rows.. generally, the more, the faster.

  3. disable data compression and make sure you've done everything you can to minimize latency between the two endpoints.

Upvotes: 1

Related Questions