Server Jobs Performance Very Slow post Infosphere Upgrade

Post questions here relative to DataStage Server Edition for such areas as Server job design, DS Basic, Routines, Job Sequences, etc.

Moderators: chulett, rschirm, roy

nvalia
Premium Member
Premium Member
Posts: 180
Joined: Thu May 26, 2005 6:44 am

Server Jobs Performance Very Slow post Infosphere Upgrade

Post by nvalia »

Hi All,

We migrated from 7.5.1. to 9.1 (All Server Jobs Only and a lot of Basic Routines) and No Job Design changes were made. The New Infosphere has better resources (more Memory, CPUs) than the old server on which we had the 7.5.1

Along with that we also upgraded SQL Server to 2012 from SQL Server 2008 R2.

Now when we run the ETL cycle, via Batch Jobs,
1. From 9.1 pointing to New SQL Server the jobs - 10 Hrs
2. Same batch job from old 7.5 and pointing to the New SQL Server - 5 Hrs

So it is taking more than Double the time in 9.1 Vs 7.5

Appreciate if anyone can share any information or provide any direction in which we should try and analyze the reason for this performance degradation

Thanks,
NV
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

Create a dummy job in 7.5 and the same dummy job in 9.1.

RowGen (10M rows) -> Transform (int column value+1) -> peek

run it on both servers.

RowGen (10M rows) -> Transform (int column value+1) -> seq file

run it on both servers.


RowGen (10M rows) -> Transform (int column value+1), (col 2 = a mod of col 1 by 4) -> sort (col 2) -> seq file


run it on both servers.



Start debugging by identifying where your bottleneck is.


It may have absolutely nodthing with the fact that you took jobs from 7.5 and ported over to 9.1. It could be your 9.1 setup itself.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

Server jobs do run differently post version 7.5 than they did in version 7.5 and earlier, primarily because of the need to interact with the WebSphere Application Server through the Information Server Framework. This adds some overhead which results in, depending on your circumstances, as much as a 10% increase in elapsed time.

If that's all you're seeing, then you can probably blame the new version entirely, without being able to do much about it. Of course there may be opportunities to tune your server jobs, but then you'd not be comparing apples with apples.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
vmcburney
Participant
Posts: 3593
Joined: Thu Jan 23, 2003 5:25 pm
Location: Australia, Melbourne
Contact:

Post by vmcburney »

Have you opened up the new Operations Console in 9.1 and looks at the resources graphs to see if anything is causing the bottleneck? A DataStage Server job runs as a single process and probably will not benefit much from increasing RAM and CPU/Cores on the server.

I would guess one of the following:
- There are tuning settings you had on version 7 that didn't make it into your new environment. Check that Environment variables are the same, especially the DS environment variables that impact things like buffering.
- Are you hitting disk during the job? Eg. Are you writing out large hashish files or temp sort files? It could be caused by less performant disks than the previous environment.
- Is the latency between DataStage and SQL Server the same as the previous environment? This could be tested with a DataStage job that generates a million records and writes them to SQL Server. Is 9 is slower than 7 then the odds are your network/database connectivity is slower.

Edit: renamed Hash file.
Last edited by vmcburney on Tue Jan 20, 2015 11:30 pm, edited 2 times in total.
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

On a heavily loaded server, even the Ops Console can consume valuable resources.

And it's hashed files, dammit, not hash files! :wink:
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

A hash file is a tool to shape... oh, you know the drill by now - right? :wink:
-craig

"You can never have too many knives" -- Logan Nine Fingers
ray.wurlod
Participant
Posts: 54607
Joined: Wed Oct 23, 2002 10:52 pm
Location: Sydney, Australia
Contact:

Post by ray.wurlod »

vmcburney wrote:Edit: renamed Hash file.
:lol:
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
JRodriguez
Premium Member
Premium Member
Posts: 425
Joined: Sat Nov 19, 2005 9:26 am
Location: New York City
Contact:

Post by JRodriguez »

I noticed that your server is in Windows and using DataStage server jobs. DataStage jobs behave very poorly when the &PH& directory within each project directory is large ( full of a lot of old files that DS server jobs generates, the tool don't cleanup after itself). Check if this is adding to the poor performance

Last time I checked it was still an issue in 8.7 and 9.1 for Windows
Julio Rodriguez
ETL Developer by choice

"Sure we have lots of reasons for being rude - But no excuses
nvalia
Premium Member
Premium Member
Posts: 180
Joined: Thu May 26, 2005 6:44 am

Post by nvalia »

Hi All,

The ETL Cycle is almost 40-50% slower in 9.1 vs 7.5 (No Job Design Changes)
Based on the suggestions I tested for each one,

1. Check that Environment variables are the same, especially the DS environment variables that impact things like buffering.
-- They are same as below

v7.5
DS_USERNO=-3884
DS_ENABLE_RESERVED_CHAR_CONVERT=0
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DS_TDM_PIPE_OPEN_TIMEOUT=720

v9.1
DS_ENABLE_RESERVED_CHAR_CONVERT=0
DS_OPTIMIZE_FILE_BROWSE=0
DS_TDM_PIPE_OPEN_TIMEOUT=720
DS_TDM_TRACE_SUBROUTINE_CALLS=0
DS_USERNO=-9552



2. Are you hitting disk during the job? Eg. Are you writing out large hashish files or temp sort files? It could be caused by less performant disks than the previous environment.
-- Yes there are a lot of Hashed files being used but Disk Performance is not an issue as per the Administrators

3. Is the latency between DataStage and SQL Server the same as the previous environment? This could be tested with a DataStage job that generates a million records and writes them to SQL Server. Is 9 is slower than 7 then the odds are your network/database connectivity is slower.
-- We checked this and yes in the new Env is generally about 10% slower

4. DataStage jobs behave very poorly when the &PH& directory within each project directory is large
-- The &PH& folder is small at about 4-5MB in size..so it is not large

Is there anything else that we can check to figure out this major performance degradation post the migration

Thanks,
NV
qt_ky
Premium Member
Premium Member
Posts: 2895
Joined: Wed Aug 03, 2011 6:16 am
Location: USA

Post by qt_ky »

Compare the uvconfig file settings.
Choose a job you love, and you will never have to work a day in your life. - Confucius
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

Did you perform the steps I outlined to see where the bottleneck is?
nvalia
Premium Member
Premium Member
Posts: 180
Joined: Thu May 26, 2005 6:44 am

Post by nvalia »

PaulVL - Yes the bottle Neck seems to be related to Data Latency. Connecting to the same Database and same Table from 7.5 takes slower than from 9.1

For test, to read and write 3.7M records from a SQL Table using ODBC stage it takes 66 Mins in 7.5 Vs 90 Mins in 9.1. We changed the ODBC driver and then 9.1 took about 80 Mins to do exactly the same thing, but which is still 25% slower than 7.5

ODBC --> TRANSFORMER (No Transformation) --> ODBC

Checking the Server logs and Network connectivity there is nothing that stands out..What else should we look at in this case?

qt_kY -
1. We have a lot of Hashed Files, any specific params in the uvconfig file to consider ?
2. Is it recommended to alter the settings in the UVConfig file, any known risks?
3. Ours is a Windows Installation, is UVConfig file more relavant for Unix based installations or equally Imp for Windows too

Thanks,
NV
PaulVL
Premium Member
Premium Member
Posts: 1315
Joined: Fri Dec 17, 2010 4:36 pm

Post by PaulVL »

So you tried ODBC --> TRANSFORMER (No Transformation) --> PEEK on both systems?

I am assuming 7.5 software is installed on different hardware than 9.1. Is that a correct statement?
chulett
Charter Member
Charter Member
Posts: 43085
Joined: Tue Nov 12, 2002 4:34 pm
Location: Denver, CO

Post by chulett »

Server jobs... no peeking. Instead you would set a constraint of @FALSE in the output link of the transformer if you were trying to gauge extraction speed alone.
-craig

"You can never have too many knives" -- Logan Nine Fingers
nvalia
Premium Member
Premium Member
Posts: 180
Joined: Thu May 26, 2005 6:44 am

Post by nvalia »

Yes both 7.5 and 9.1 are on different servers
Post Reply