Drastic slowdown of large hash file lookups
Posted: Fri Feb 01, 2008 4:12 pm
Hi,
We have just experienced a dramatic decrease of performance of large hash file lookups on our Test server and are having a lot of trouble diagnosing the reason for this.
Summary:
- As an example - a step in job looks up a 4.7M row hash file with a DATA.30 file of 650Mb and OVER.30 of 208Mb
- We have stripped down the job to just the lookup step with sequential files as both input and ouput in order to eliminate any doubt about network/database perfromance. The problem is definitely the slow lookup of hash files.
- The the lookup used to run at 10000 rows per second on the Test server and suddenly started running at 350 rows per second FOR THE SAME DATA - yes we are rerunning identical data.
(This job is one example however all jobs with large hash file lookups seem to have been affected on the Test server)
- Running the same job for the same data on our Development server consistently runs at around 10000 runs per second.
- Monitoring resources on the Test server shows that is not being pushed for either CPU or memory or semingly I/O.
- There have been no system changes to the Test server since the job was running normally to explain why it has slowed down (Antivirus is turned off)
- I have tried the following of combinations on the Test server to see if they make a difference but none has:
a.) storing the hash file on different drives (E drive is a local 150Gb 15k SCSI drive and F drive is 300Gb SAN attached storage)
b.) Running the job from a different project after re-deloying the master copy of teh job from source control
c.) The hash file with both the preload to memory option turned on and off (seems to be a bit slower when not preloading to memory i.e. around 250 rows/s)
Any help or suggestions on what else we can test will be much appreciated
Thanks,
Alex
We have just experienced a dramatic decrease of performance of large hash file lookups on our Test server and are having a lot of trouble diagnosing the reason for this.
Summary:
- As an example - a step in job looks up a 4.7M row hash file with a DATA.30 file of 650Mb and OVER.30 of 208Mb
- We have stripped down the job to just the lookup step with sequential files as both input and ouput in order to eliminate any doubt about network/database perfromance. The problem is definitely the slow lookup of hash files.
- The the lookup used to run at 10000 rows per second on the Test server and suddenly started running at 350 rows per second FOR THE SAME DATA - yes we are rerunning identical data.
(This job is one example however all jobs with large hash file lookups seem to have been affected on the Test server)
- Running the same job for the same data on our Development server consistently runs at around 10000 runs per second.
- Monitoring resources on the Test server shows that is not being pushed for either CPU or memory or semingly I/O.
- There have been no system changes to the Test server since the job was running normally to explain why it has slowed down (Antivirus is turned off)
- I have tried the following of combinations on the Test server to see if they make a difference but none has:
a.) storing the hash file on different drives (E drive is a local 150Gb 15k SCSI drive and F drive is 300Gb SAN attached storage)
b.) Running the job from a different project after re-deloying the master copy of teh job from source control
c.) The hash file with both the preload to memory option turned on and off (seems to be a bit slower when not preloading to memory i.e. around 250 rows/s)
Any help or suggestions on what else we can test will be much appreciated
Thanks,
Alex