Performance problem

One Star

Performance problem

Hello,
I made talend job which calls other exported talend jobs with their sh files. For this purpose I use tSystem. So far so good. My problem is when I put Oracle connection (tOracleConnection) in the exported talend jobs the performance goes down very very badly.
In my main Talend job I calls 10 other exported talend jobs by sh files. If I don 't have tOracleConnection in the exported jobs everything is fine - I have immediately execution of all 10 jobs. If I put tOracleConnection then the processing time for every next called jobs is increased progressive - 1th job for 2 sec, 2th job for 5sec, 3th job for 50sec...10th job for 5-10 min. Of course I put tOracleClose in every jobs and I release the connection. I cannot use tRunJob because all jobs will live as standalone applications and they will be exported with sh files. Maybe there is Talend pool of DB connections which size must be increased ?!
Any idea how I can sove this performance problem?
Regards
Six Stars

Re: Performance problem

Hey,
If the jobs do massive insert delete or update operations on same db object then you need to take a look at locking object in the database when running your jobs in parallel. The joke is that when oracle locks some object in isolated trasaction, even if the transaction is already commited and job finished, the lock on the object could still appears in the database. You can call it war for objects :-) You need to investigate it on db side I think.
Ladislav

Re: Performance problem

would it not be possible to use *row* locking as opposed to table locking? (it has a while since i last used oracle)
i have seem similar problems with MSSQL as service tried to access the same table creating a 'war for objects'.
but archenroot is correct as you will have to investigate the DB end
One Star

Re: Performance problem

Thank you for your answer. The joke is that in the called jobs I removed all tOracleInput and I have only one tOracleConnection (to open connection) and tOracleClose (to close it) and the performance is same - very very very very slow. So the problem is not on the INSERT/DELETE/UPDATE operation Smiley Sad. Any other ideas ?
Six Stars

Re: Performance problem

huh, that's strange, how many connection do initiate in parallel? What edition of Oracle do you use?
Anyway you schould check and probably increase following parameters in DB, because the defaults could fit your performance requirements:
sqlplus "/as sysdba"
or
sqlplus '/as sysdba'


sql> show parameter sessions
sql> show parameter processes
sql> show parameter transactions

To see how much resources are used you schould run following statements after talend job started:
select count(*) from v$process;
select count(*) from v$session;
select count(*) from v$transaction;

You can forget transaction system table as soon as you are not manipulating data. You better use some tool for doing this, sqldeveloper or toad can help you with this.
It is recomended to increase these parameters in Oracle db using exactly following or similar formula based on your environment:
processes=x
sessions=x*1.1+5
transactions=sessions*1.1

You schould take a look into documentation also...
On the other hand you also could face this issue because of just some network problem, resolving ip address from host name or error in route table. I would try to run nslookup on the oracle host, try to traceroute the host and check the routing table in general. I have already faced similar situations and it was mostly clear network problem. This is just hint.
One Star

Re: Performance problem

everything on Oracle side is checked...Everything is OK there Smiley Happy - I started from this point with same SQL as yours...Do you know is there limitation in the DB pool connection size in TALEND ? Every jobs and the oracle are on same machine and DB access is by localhost
Six Stars

Re: Performance problem

As soon as you run each job in private process you needn't to take care about this, it's always one instance of JDBC driver which connect only once.
I have an idea how this issue could be maybe workarounded, your jobs still can stay separated on filesystem as they are executed also separated and you can then create some main job where you will connect only once and share the connection to child jobs using tRunJob.
Six Stars

Re: Performance problem

I would try it just on localhost database also and see the results if you can.
One Star

Re: Performance problem

archenroot I really thank you about this discussion, you're the only one which responds me in the forum about my problem.
In fact I tried your proposal with tRunJob and dynamic call of jobs. Unfortunately the performance problem is same there. That's why I pointed my brain in the direction of pool problem or late JDBC release of resources in every called job.
What exactly I have in my jobs structrure:
1 main job which decides to call some sequence of jobs based on specific dynamic logic
I tried to use tRunJob (dynamic jobs invocatin) and tSystem - the performance is exactly same - very bad. The problem is there and in case of static usage of tRunJob but with check for "Use independent process to run subjob"
Of course I checked my Oracle 11g server for session, transaction etc.. everything is fine there. The problem is comming when every job is in different process so that's why I'm really confused what can be the reason if the Talend uses only one JDBC connection for several processes.
The talend jobs and oracle are on same machine and the oracle is accessible on the localhost. I'm really blocked and confused.
Any other ideas ?
Six Stars

Re: Performance problem

why I'm really confused what can be the reason if the Talend uses only one JDBC connection for several processes

Maybe I misunderstood it correctly, I though that each process has it's own connection and connection close component.
What's in a log of the jobs you run? Here is for example how I run jobs via cron and forward both standard and error output to log file:
#!/bin/bash
# Script has symlink from directories /etc/cron.*, function is determined by their names.
# For example symlink with name AUTO_P01_Synchronizace_Ciselniku_NAV.sh calls script
# /ip/talend/jobs/AUTO_P01_Synchronizace_Ciselniku_NAV/AUTO_P01_Synchronizace_Ciselniku_NAV/AUTO_P01_Synchronizace_Ciselniku_NAV_run.sh
# Skript first check if it's executed on active node of the cluster
HOSTNAME=$(/bin/hostname)
/usr/sbin/crm_resource -W -r Shared-IP-Address 2>/dev/null | /bin/grep -sq $HOSTNAME || exit
script=$(basename ${0%.sh}) # name of script without '.sh'
exec >>/ip/log/talend/${script}.log 2>&1 # write STDOUT and STDERR into log
date "+=== %Y/%m/%d %H:%M:%S %z Node $HOSTNAME ==="
su -s /bin/bash iprun -c "sh /ip/talend/jobs/${script}/${script}/${script}_run.sh"

The point is what's in the log of the child jobs, did they ever executed correctly?
Anyway, I am running out of ideas :-(
One Star

Re: Performance problem

Sorry, all jobs contain different tOracleConnection and tOracleClose. When I start the main job, it calls 10 others (just tOracleConnection and tOracleClose, no other components) and they are executed fine but for 5 min. archenroot I would like to say thank you again for your ideas. I'm continuing with the investigation how this problem can be resolved.
Six Stars

Re: Performance problem

Dont thank me, the issue still exists...hehe...
You schould know how the 10 subprocesses are executed. If you do not use any log file, just edit the .sh files of the jobs and put to the end of statement something like this:
>>/home/user/talend/job1.log 2>&1

This will create log file with both error and standard messages and you will see how the processes behave.
One Star

Re: Performance problem

Hi Roman,
maybe it is unrelated - maybe not. I've discussed with an Oracle DBA about another topic - and one of result was, that connecting to Oracle DB itself is time expensive. It simply takes time to connect. So if you have no shared connection or pool, connecting itself may be delaying a lot. (even it doesn't explain increasing response time in your jobs). I even used subjobs working with the same connection, was ok. Just I didn't need many parallel subjobs so far Smiley Sad Just tring to point out an idea where the delay could originate, still I have no solution for you.
Carpe diem
Gabriel
One Star

Re: Performance problem

Hi
Currently, tDBConnection doesn't support JDBC pool.
'Shared DB Connection Name' is a workaround, but you said "all jobs contain different tOracleConnection and tOracleClose".
I have only opinion at TOS side, but i'm not sure. Can you separate your ten jobs into multi thread in your main job by Checking 'Multi thread excution'?
Or these jobs must be excuted in logic order?
Best regards!
Pedro
One Star

Re: Performance problem

Hi Pedro,
Thank you for your answer.
Unfortunately all jobs must follow logic order and multi threads are not possible.
Regards
Roman
Six Stars

Re: Performance problem

Try to run them one by one.
One Star

Re: Performance problem

I think that I found the problem, unfortunately I cannot be 100% sure but:
I start same job - main and subjobs in Windows 32bit JDK and on Linux 64bit JDK. The result is : perfect performance on 32bit JDK and very very bad on 64bit JDK (the usual environment for my jobs). So my conclusion is that all performance problem is coming from the 64bit JDK. I followed many documentations and I discovered that on 64bit Java it's possible big big delays because platform and garbage collectors reasons. I will try to do some tuning of my 64bit JDK, but I'm sure 80% that this is the main reason for my problems with Talend performance. Did Anybody meet 64bit JDK problems with Talend jobs ?
Regards
Roman
Six Stars

Re: Performance problem

I didn't face that issue and run jobs on RHEL64bit with JDK 1.6 update 2x 64bit. Did you google for this issue. Send me the jdbc drivers directly from your Talend installation I could try to simulate parallel connection in simple java project in eclipse or any other ide.
Ladislav