[resolved] tMysqlInput performance enhancement

One Star

[resolved] tMysqlInput performance enhancement

Hi,
I'm using tMysqlInput to input data from a remote server. I delete the imported rows in parallel. The input rate of tMysqlInput is around 5 rows/s. Can i increase its performance so that it input fast? the 'enable stream' option is checked. any idea?
regards.

Accepted Solutions
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Notice "Action on data" of tMysqlOutput. Check "Delete" here.
The following image will help you.
tMysqlOutput can insert, update and delete records.
Don't forget to check "Use an existing connection".
Regards,
Pedro

All Replies
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Link tMysqlInput with tJavaRow. Does it speed up this time? What's the rate?
I guess maybe tMysqlInput is not the bottleneck of your job.
A screenshot of the job is preferred.
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi pedro,
can you explain please?
Attached is the job image, any suggestions?
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Please do as i said. Link tMysqlInput with tJavaRow. Does it speed up this time? What's the rate?
By using this way, you can find the bottleneck.
Wait for your feedback.
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Put tMysqlCommit under tMysqlInput.
tMysqlConnection
|
OnSubjobOk
|
tMysqlInput ---tmap---tFileOutputDelimited--tMysqlRow
|
OnSubjobOK
|
tMysqlCommit
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi,
using
tMysqlConnection
|
OnSubjobOk
|
tMysqlInput ---tmap---tFileOutputDelimited--tMysqlRow
|
OnSubjobOK
|
tMysqlCommit
now rows/s are around 10 per second. so should i use tMysqlRow or tJavaRow?
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Why do you check 'enable stream' here?
How many rows do you need to load?
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi,
My initial design was causing error where i used tMysqlCommit under tMysqlRow and unchecked 'enable stream'. I checked it and it worked fine.
i have about 200 thousand rows.
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Please show me the settings of tMysqlInput and the sql of tMysqlRow.
Screenshots are preferred.
Then i can know what you want to do with tMysqlRow.
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Try to remove tMysqlRow and check the speed this time.
What we do now is to find bottleneck.
If you remove tMysqlRow, the rate increases.
We can make sure that there must be something wrong with the sql in tMysqlRow.
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi,
let me explain you what i'm doing here. I take data from remote server through tMysqlInput, then i output the data into csv file. I delete the copied rows from the source server. tMysqlRow has the query which deletes the copied rows from the source server.
i"ve also attached the images.
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi,
After removing tMysqlRow, the rows/s jumped to 1000 rows/s. This means that i need to modify the query? its a very simple query though :s
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Try this.
tMysqlConnection
|
OnSubjobOk
|
tMysqlInput ---tmap---tFileOutputDelimited
|
OnSubjobOK
|
tFileInputDelimited--tMysqlRow
|
OnSubjobOk
|
tMysqlCommit
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
I have tested this issue.
Here is conclusion.
Do not use tMysqlRow here.
It is inefficient. Replace it with tMysqlOutput.
You will get perfect performance.
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi,
Thank you for your help. just one thing, i want the output in a file and want to delete the copied rows from the source. tMysqlOutput will output in a table. How can i achieve that?
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi
Notice "Action on data" of tMysqlOutput. Check "Delete" here.
The following image will help you.
tMysqlOutput can insert, update and delete records.
Don't forget to check "Use an existing connection".
Regards,
Pedro
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi pedro,
you are awesome Smiley Happy
thanks a million for all the help Smiley Happy
regards,
One Star

Re: [resolved] tMysqlInput performance enhancement

Hi,
I am a newbie to Talend. I am running a job to transfer data from SQL server to Excel. I came across the same error. I read through the above post but I am unable to find the check box for 'enable stream'. Would anyone know why? 
Would appreciate any help or thoughts!
Thank you.
Pallak
Seventeen Stars

Re: [resolved] tMysqlInput performance enhancement

hi,
in advanced setting of tMysqlInput component.
regards
laurent