Performance Tuning

[Anonymous]

Hi Team,

We are having multiple lookups for our data load. The performance has been greatly reduced. Do we have any performance tuning stpes?

Can anyone suggest us?

Thanks in advance,

Ramya.

[Anonymous]

Hi Ramya,

      The first thing I would say is to remove doing lookup of almost 9 tables. We might be missing something in the long list of mapping. Have 2 or 3 lookups per tMap. I f you need to do more mapping, do in multiple stages of tMap.

     Now, we don't know which lookup (or main flow) is taking lot of time. Did you observe the throughput of data for each lookup and time taken to extract the records? Also are you taking any full table lookup with lot of records? All these are symptoms of issues.

     Please also check the performance details from DB. The DB reports also can give you valuable insights about any possible issues in queries or indexes. These issues need to be fixed at DB level instead of ETL layer.

     So in short, you need to check various aspects for performance tuning of a job. Please let us know with more details and we are happy to help you.

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 🙂

[manodwhb]

@rchinta ,you can improve the performance of tmap by using below options.

1) remove the unwanted/un lookup data before doing lookup

2) remove the unwanted columns form lookups and main flows.

3) Store on disk

4) increase the max buffer size in setting of tmap.

[Anonymous]

Thank you Nikhil and Manohar.

I will follow your suggested steps. Small update is: The insert is taking 11000 rows/3mins but the update is taking 35 mins. 

Also wanted to know what is meant by 

Store on disk

and how to increase the max buffer size in setting of tmap

Thanks,

Ramya

[Anonymous]

Hi,

    Did you check whether the columns on which you are doing update is part of primary key or atleast an index is present?

     This could be the main reason for the issue.

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 🙂

[Anonymous]

There is a primary key in each.

Thanks,

Ramya

[Anonymous]

Hi,

    Did you check the DB performance stats from DBA?

     I suspect there is some issue with DB level. You can also do a memory run to see whether there is any issue with Talend layer.

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 🙂

[Anonymous]

Sorry I am a newbie. Can you please elaborate on the process Nikhil?

[Anonymous]

Hi Ramya,

     I assume you are using MySQL as the target DB where you are facing problem.

     Below are some of the articles related to MySQL Performance measurement which will give you some idea about how it is happening at DB layer.

https://dev.mysql.com/doc/workbench/en/wb-performance.html

https://haydenjames.io/mysql-performance-tuning-tips-scripts-tools/

     I would suggest to get a DB layer professional also while doing this exercise as many terms might be quite new to you. If you have the luxury to get a DBA to access the details, it will be really great.

    But I could feel that the issue is mainly related to update for target table which means there is some bottleneck there. It could be even network layer bottleneck also. So do all the analysis related to it and feel free to comeback if you see there are any issues at Talend while pushing the data.

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 🙂

[Anonymous]

Thank you for the advice. We are using Redshift as the DB.