topic Re: Job memory performance in Talend Studio

Job memory performance

castiellll — Tue, 07 May 2019 14:39:12 GMT

Hello,

I have a job that takes data from multiple ODS tables join them with multiple tMap and insert them to a table. I should have around 80GB of data and the main flow has around 85 000 000 rows (around 15 GB).

All the lookup tables are stored in temp files and RAM available is 25 GB for this job.

The insert are in batch and manual commit.

Even with this, the job is quit slow and turns for several days without ending yet.

Is there another kind of optimization I can do beside changing Talend maps to sql code ?

The problem is clearly not coming from the SQL engine.

What do you think is the average time for Talend to manage 80 to 100 Gb of data ?

Thanks in advance

Regards,

Sofiane

Re: Job memory performance

David_Beaty — Wed, 08 May 2019 17:02:02 GMT

Hi,

I'd look at:

Check that all of the DB inputs that feed the tMap lookups are reading in only the rows and columns that are needed.
Consider splitting the flow into 2 sections , outputting the main flow to a temporary file, just after the 3rd tUnite and then reading back in from the temporary file into the tMap after.
Externalise the 2 tMap on a tMap sections into a single SQL, again only reading in the rows and columns needed.
Remove the tMap that has no lookups near the beginning of the main flow and replace with a tJavaRow.

Re: Job memory performance

Anonymous — Wed, 08 May 2019 18:14:11 GMT

Hi,

I would break the flow in slightly different way. I will merge all the initial data to a temporary table and it will be my stage 1 (if the processes inside the merge process is taking time, you can do them parallel fashion using tparallelize and merge them later).

Now this temp table will be my source and each of the look stages will be handled by fetching the data by joining with lookup tables within DB itself and push the result set to a new temp table. This means that you are not extracting full lookup table information and we are doing the filtering at the source itself.

This approach means there will be lot of writes to temp tables and this can be made faster by using Bulk components instead of normal ones (considering the high input volume).

In a way, we are bypassing the full lookup table download to disk within Talend (which is time consuming) and all the interim table writes are also made faster using Bulk components.

Could you please create a duplicate job current flow using this approach and let us know the results?

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: Job memory performance

castiellll — Thu, 09 May 2019 16:34:16 GMT

Hi,

Thanks for your replies. I've tried to split the job and follow your suggestion but I still have the memory error. The main job is too big even for 25GB of RAM.

Any other idea to manage the memory ?

Thanks in advance.

Sofiane.

Re: Job memory performance

David_Beaty — Thu, 09 May 2019 16:49:20 GMT

Hi

All of the tMap components should have the “Store on disk” enabled, along with a directory path and sensible number of rows. Say, 1,000,000

Re: Job memory performance

castiellll — Sun, 12 May 2019 16:23:30 GMT

Hello,

Thanks for replying.

The flow is already set this way. After 2 days running, I've got this error : java.lang.RuntimeException:java.io.IOException

Let's retry.

Re: Job memory performance

David_Beaty — Sun, 12 May 2019 19:32:59 GMT

Hi,

So, being connected for an extended duration of time is probably now your issue.

You could try output the data locally to a file, find some kind of blocking key in the data (say year and month) and iterate through the file multiple times, reading from the file filtering on the blocking key and writing to the destination table, connecting and disconnecting the DB connection.

Re: Job memory performance

castiellll — Wed, 15 May 2019 09:08:41 GMT

Hello everybody,

I've splitted the tMaps into multiple sub-jobs and reduced the batch and commit size from 100000 to 50000. This way, at least, the job turns without closing connection.

It is currently turning for two days.

Have a good day.

Sofiane.

Re: Job memory performance

castiellll — Thu, 16 May 2019 13:16:55 GMT

Hello everybody,

I again face the java.sql.SQLException:Invalid state, the Connection object is closed. issue while everything is ok in the sql server.

Is there a parameter or a timeout set somewhere in talend ?

The flow is super simple reading from a temporary table with 2 tmaps and writing into another table. There is around 110000000 rows witch is nothing for a DWH.

Thanx for your help.

Sofiane

Re: Job memory performance

Anonymous — Thu, 16 May 2019 13:58:45 GMT

Hi Sofiane,

Did you check the maximum connection open time allowed for your SQL Server? Usually DBAs set a value to stop connections to remain open for very long time. This could have prompted the error.

Considering your data size, could you please use bulk components to load the data instead of using normal tDBOutput? This could change the whole equation of processing.

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: Job memory performance

castiellll — Thu, 16 May 2019 14:05:52 GMT

Hello,

We are working together with the dba on these flows and everything is ok from that side.

For the bulk component I've read that the running job should be in the same server as the SQL server which is not the case for me.

Thanx

Sofiane

Re: Job memory performance

JaneYu — Thu, 16 May 2019 17:22:10 GMT

did you try to increase this option in Run Job tab

Use specific JVM arguments by increasing the Xms and Xmx?

the default is: Xms256M, Xmx1024M

You could increase to Xms1024M, Xmx4096M

Re: Job memory performance

castiellll — Thu, 16 May 2019 17:52:16 GMT

Hello,

I am using 30GB of ram already and this is not a ram problem.

Thanx

Sofiane

Re: Job memory performance

David_Beaty — Fri, 17 May 2019 08:04:45 GMT

Hi,

The message you are getting, as it says, is that the connection of closed. So, this could be one of 2 things really:

The Server is closing the connection.
Your Talend job is closing the connection.

The most likely cause is the 1st option, check with the DBAs if there's any open connection timeouts, etc. Is the destination on-premise or cloud or elsewhere (with network contention)? Either way, I'd consider splitting the job into 2 distinct sections, one of accumulating the data you want to put into the DB (into a temp file) and the actual output of the data into the DB (from temp file to DB).

Re: Job memory performance

castiellll — Fri, 17 May 2019 09:18:37 GMT

David,

Thank you for your reply.

For sure it doesn't come from server, as previously told, I work with the DBA on it.

Is there a possibility to set the Talend timeout to 0 ( I don't know where is this parameter) I only know that JDBC timeout is default to 0 ?

The infra is all on-premise and Talend server is connected to SQL server through Intranet.

Is it possible to use the bulk component while Talend job is not in the same server as SQL ? Or should I manually manage the temp file ?

Have a good day.

Sofiane.

Re: Job memory performance

David_Beaty — Fri, 17 May 2019 10:50:44 GMT

There may be some additional connection parameters you could pass regarding inactivity timeouts, which you could pass in the “Additional parameters”.

Previously, for SQL server, I’ve only done the bulk load from a file in the server, it was a file share I could write to. However, for Vertica, the bulk file could be remote.

Maybe create yourself a small test job to try it. I’m not able to test it right now to confirm.

Thanks