topic Re: Fetching data from aurora postgre is very slow in Talend Studio

Fetching data from aurora postgre is very slow

Anonymous — Sat, 16 Nov 2024 04:00:10 GMT

We were using the PostgreSQL component of Talend to fetch the data from Postgres DB. It normally took 20 minutes to complete the data load. Now the data is moved on amazon aurora Postgres and now it takes more than an hour to complete the same.

Re: Fetching data from aurora postgre is very slow

Anonymous — Wed, 27 Nov 2019 14:45:20 GMT

Hi,

There could be multiple reasons for this effect. Did you check whether the query is optimized? Please refer the below link for some quick tips.

https://dzone.com/articles/simple-tips-for-postgresql-query-optimization

I will also increase the Xms and Xmx parameters and see whether there is any performance difference. Please make sure that you are having enough system memory in your job server /remote engine to handle this scenario. Also change the commit size and see the performance difference.

I will also check the CPU utilization and network bandwidth availability to make sure that data is transferring seamlessly. Could you please check all these factors and come back if the error persists?

Please also provide more details related to each section of what all changes you did during investigation.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: Fetching data from aurora postgre is very slow

Anonymous — Wed, 27 Nov 2019 16:48:39 GMT

Hi Nikhil,

I am using the same query that I run on Postgres. So the input and output remain the same.

I already checked the below factors:

1. -Xms & -Xmx value.

2. increase in cursor size.

But none is working.

Just for e.g: suppose I run a query which fetches a list of customers and then there is an iterative link which works by taking those customers. Earlier in Postgres, it took only 15 minutes and now it is taking 45-50 minutes.

In Talend, I did not change the component as the new amazon aurora is also Postgres compatible.

Re: Fetching data from aurora postgre is very slow

Anonymous — Wed, 27 Nov 2019 18:01:07 GMT

Hi,

It seems the increase the data volume has changed the execution plans. Could you please check the statistics of the query and see whether they are in optimized fashion?

Since you are doing iteration, the first result set before iteration will eventually determine the number of iterations. If the number of records are too high, it will result in too many iterations which in turn will translate to more time.

I would recommend to check these factors also. If possible, could you please share the screen shots of your jobs to understand the flow?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: Fetching data from aurora postgre is very slow

Anonymous — Wed, 27 Nov 2019 18:52:08 GMT

Hi,

Increase in data set, I do not think it is possible here.

Here is the simple example

I am fetching the total number of students of a class, suppose 400 (that counts what my actual job is fetching). Then for each, I am fetching the name only.

So how would the data set got increased?

Re: Fetching data from aurora postgre is very slow

Anonymous — Wed, 27 Nov 2019 19:10:50 GMT

Hi,

It seems the screen shot is missing.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: Fetching data from aurora postgre is very slow

Anonymous — Thu, 28 Nov 2019 11:02:58 GMT

PFA.

Talend.png

Re: Fetching data from aurora postgre is very slow

Anonymous — Thu, 28 Nov 2019 15:08:01 GMT

Hi,

Could you please get all the records to a temporary file first? Then use tParallelize to run multiple parallel processes (say 4 in your case) to read only a part of the data in each parallel process and call the subjob from each of those parallel flows?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: Fetching data from aurora postgre is very slow

Anonymous — Thu, 28 Nov 2019 15:15:36 GMT

Hi Nikhil,

Thanks for the response.

I understand that running this job in parallel will reduce the time for sure.

But my question was, here the entire structure of the job is the same as it was. The only difference is the DB from where it is fetching data is migrated from Postgre to amazon aurora Postgre and the total execution time increased to 3x times (approx). Although, amazon aurora Postgre is considered as 3x times faster than Postgre, then why I am getting the reverse effect.

Re: Fetching data from aurora postgre is very slow

Anonymous — Thu, 28 Nov 2019 15:42:28 GMT

Hi,

Is your original PostgreSQL an RDS instance or on-premise? You will have to measure the network bandwidth available between Talend and Aurora instance. Could you please advise whether your Talend Job server and Aurora are in same region?

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved

Re: Fetching data from aurora postgre is very slow

Anonymous — Thu, 28 Nov 2019 16:53:13 GMT

The PostgreSQL is an RDS instance and the Talend job and aurora are not in same place but that is the same as it was with PostgreSQL.

Re: Fetching data from aurora postgre is very slow

Anonymous — Thu, 28 Nov 2019 16:55:05 GMT

Ok. I would recommend to create a support case just to make sure that everything is fine by checking through a remote session.

Warm Regards,
Nikhil Thampi

Please appreciate our Talend community members by giving Kudos for sharing their time for your query. If your query is answered, please mark the topic as resolved