topic Re: Load data into MySQL DB stops without error message with large dataset in Talend Studio

Load data into MySQL DB stops without error message with large dataset

HungryOctopus — Thu, 22 Feb 2024 08:03:07 GMT

I run this job on several data sets and for most of them, it works fine, when there is around 300.000 rows coming from MappedItemAssets.

But when there is more data (here 625.000 rows), the job eventually won't continue, even if I wait several hours. The job just stops without any error messages.

I already tried the following, but it didn't solve the problem:

- Drop and Write table instead of truncate

- change tDBOutput component to a JDBC Component

- Sort on disc the tSortRow, use of disk for the tUniqRow, store on disk for MapAsset

- I also replaced the tDBOutput with a tFileOutputDelimited for control, exactly 98094 rows are written before the job stops.

I use Talend Studio 7.3., the DB is MySQL 5
Any help would be greatly appreciated!

Thank you

Re: Load data into MySQL DB stops without error message with large dataset

Shicong_Hong — Tue, 27 Feb 2024 09:58:36 GMT

Hello

This looks weird if the job stops with any error. This job use many memory-consuming components such as tHashOutput, tSortRow, tUniqRow etc. Using 'store on disk' option is a workaround to resolve the out of memory error. Try to allocate more memory to job execution and avoid using many memory-consuming components tHashInput/tHashOutput, for large of data set.

Regards

Shicong

Re: Load data into MySQL DB stops without error message with large dataset

HungryOctopus — Tue, 27 Feb 2024 12:28:40 GMT

Hi Shicong,

thanks for your response.

The store on disk option didn't solve my problem, but allocate more memory to the job did (12G instead of 8G)

Many greetings,
HungryOctopus

Re: Load data into MySQL DB stops without error message with large dataset

jlolling — Tue, 27 Feb 2024 21:22:43 GMT

If your output is a database table, you do not need to sort the records. A database can do this much more efficient using an appropriated index.