Replicating Data from oracle to PostgreSQL, loading approx 7 billion records during full load and having performance issues
I am using Qlik replicate task (full load + CDC) to load data from oracle to Postgres. In this task initial full load has to be performed for approximately 7 billion records. I have attached the screenshot and estimated finish time is 52 hrs. When I started the task it has showed estimated finish time as 18hrs but it gradually got increased. Looking for recommendations to improve the performance. Any help is deeply appreciated.
As a general performance best practice, Qlik Replicate should be deployed close to the source database, especially during the Change Processing phase. The proximity of the QEM (Qlik Enterprise Manager) is less critical in comparison.
Since both the source and target databases are remote, enabling client/server compression may help improve performance. However, the effectiveness of this approach depends on a detailed understanding of the environment’s hardware, software, and network characteristics, along with careful tuning of Replicate parameters.
We strongly recommend involving the Professional Services (PS) team (a chargeable service) to provide expert guidance and help drive the case forward effectively.
Regards,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Please enable Parallel Load to accelerate the replication of large tables. This feature works by splitting a table into multiple segments and loading them in parallel. For example, splitting a table into 2 segments may reduce the load time by approximately half; 3 segments may reduce it to about one-third.
However, increasing the number of segments does not always lead to proportionally shorter load times. The actual performance gain depends on factors such as network bandwidth, system I/O, and the target database’s ability to handle concurrent loads.
Hope this helps.
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
Hi @john_wang, Thanks for quick and prompt response.
I have already enabled parallel load on the table but yet see the same performance issue. I have used dataranges and split the data in the source table in to equal chunks.
With respect to the network bandwidth and system I/O and target DB below are the details:
Source DB instance is on UK region, where as replicate and QEM instance are on eu-west-1 and the target Db is on India location.
Any suggestions to improve performance having provided above information?
As a general performance best practice, Qlik Replicate should be deployed close to the source database, especially during the Change Processing phase. The proximity of the QEM (Qlik Enterprise Manager) is less critical in comparison.
Since both the source and target databases are remote, enabling client/server compression may help improve performance. However, the effectiveness of this approach depends on a detailed understanding of the environment’s hardware, software, and network characteristics, along with careful tuning of Replicate parameters.
We strongly recommend involving the Professional Services (PS) team (a chargeable service) to provide expert guidance and help drive the case forward effectively.
Regards,
John.
Help users find answers! Do not forget to mark a solution that worked for you! If already marked, give it a thumbs up!
