Performance Tuning

Five Stars

Performance Tuning

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?WorkForceEvent.PNG

 

Thanks in advance,

Ramya.

Employee

Re: Performance Tuning

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 :-)

Forteen Stars

Re: Performance Tuning

@Ramya_16 ,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.

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Five Stars

Re: Performance Tuning

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

Employee

Re: Performance Tuning

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 :-)

Five Stars

Re: Performance Tuning

There is a primary key in each.

 

Thanks,

Ramya

Employee

Re: Performance Tuning

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 :-)

Five Stars

Re: Performance Tuning

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

 

Employee

Re: Performance Tuning

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 :-)

 

Five Stars

Re: Performance Tuning

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

Forteen Stars

Re: Performance Tuning

@Ramya_16 ,check below links may help.

 

http://talendexpert.com/talend-redshift-performances/

https://community.talend.com/t5/Design-and-Development/Performance-issue-with-Redshift-component/td-...

Manohar B
Don't forget to give kudos/accept the solution when a replay is helpful.
Employee

Re: Performance Tuning

Hi Ramya,

 

    Updates are always costly operation in Redshift since its more tuned for data inserts as it is a data warehouse.

 

    Please refer below post update which will give you some better performance.

 

https://community.talend.com/t5/Design-and-Development/how-to-update-bulk-records-in-redshift/td-p/2...

 

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 :-)

Five Stars

Re: Performance Tuning

Thanks for the suggestions. But I am still unable to find a solution.

Quick question: I am using Open Studio but does that have any difference with the Enterprise Edition?

 

Thanks,

Ramya.

Employee

Re: Performance Tuning

Hi Ramya,

 

     There should not ba any major changes for this component but support team will work with you to do the resolution within SLAs for Enterprise edition.

 

     Sometimes a Support patch, which is having on demand change for the customer might do the magic.

 

     I would also suggest to raise the issue with Talend Product Team through JIRA.

 

https://jira.talendforge.org

 

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 :-)

 

2019 GARNER MAGIC QUADRANT FOR DATA INTEGRATION TOOL

Talend named a Leader.

Get your copy

OPEN STUDIO FOR DATA INTEGRATION

Kickstart your first data integration and ETL projects.

Download now

What’s New for Talend Summer ’19

Watch the recorded webinar!

Watch Now

Best Practices for Using Context Variables with Talend – Part 1

Learn how to do cool things with Context Variables

Blog

Migrate Data from one Database to another with one Job using the Dynamic Schema

Find out how to migrate from one database to another using the Dynamic schema

Blog

Best Practices for Using Context Variables with Talend – Part 4

Pick up some tips and tricks with Context Variables

Blog