Server Jobs Performance Very Slow post Infosphere Upgrade
Moderators: chulett, rschirm, roy
Server Jobs Performance Very Slow post Infosphere Upgrade
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
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
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.
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.
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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.
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.
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
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.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
-
- Premium Member
- Posts: 425
- Joined: Sat Nov 19, 2005 9:26 am
- Location: New York City
- Contact:
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
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
ETL Developer by choice
"Sure we have lots of reasons for being rude - But no excuses
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
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
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
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