make job faster with xms and xmx

One Star

make job faster with xms and xmx

Hello !
My job(insert in a MySQL) table starts with around 300 rows/second but after one minute, it's about 50 rows and later 1row only.
I change xms and xmx(advanced settings) from xms:256 / xmx:1024 to xms:500 / xmx:1048.
Unfortunately, it changes nothing.
I tried from window -> preferences -> talend -> run/debug. It's not better.
Why number of treated row decreases like that ?
thank for your replies.

Here is my job :

tMySqlInput -> tReplace -> tMap -> tMySqlOutput
|
5 lookup tables
One Star

Re: make job faster with xms and xmx

In your example we changed memory upto 24MB only.
Try with -Xms800M / -Xmx1500M provided you have enough memory and you can use it too.
2nd reason might be that we are not flushing the data into tables periodically decrease tMySqlOutput --> Advanced Settings --> Number of Rows per Insert and try.
You can also try with option tMySqlInput --> Advanced Settings --> Enable Streams , This way all the data will not be extracted at once rather it would be streamed.
Similerly we can work on tMap also to see how it can be tuned.
Hope that helps.
--
Regards,
Vinod
One Star

Re: make job faster with xms and xmx

Thank you a lot !
It seems it's the "insert" part which takes a lot of times.
Indeed, when I replace tMysqlOutput by an OutputDelimitedFiles, the job takes less than 3 minutes to execute.
So, I think it's not about tReplace or joins (in tMap ) that we could locate the problem.
I tried the two options and xms/xms values that you adviced me but it's not better.
I don't know what to try
One Star

Re: make job faster with xms and xmx

You might consider using tMysqlOutputBulk (in combination with tMysqlBulkExec) to process the data into the database
One Star

Re: make job faster with xms and xmx

Hi,
Anything that does not require network transfer would always be faster. In this case output delimited file, from you this exercise I presume that you are not dealing with huge data volume, it is something like 100 MB or even less. If that is not the case than as suggested by our friend go ahead and use tMysqlOutputBulk component for your huge volume of data. But you also mentioned that insert is slow so possibly there are many indexes as well and the insert batch limit is less so it might be going for index updation after each batch.
I would suggest you to balance the java heap with batch inserts, but it seems there is no isse at the mysql output component as flatfile and mysql output insert time is almost similer taking 3 mins. in account of network connection creation and network transfer.
Please again check if heap can be increased further, check your system RAM and try to tune it.
--
Regards,
Vinod