Performance problem with Job (Flat file to Oracle)
Moderators: chulett, rschirm, roy
If network traffic/collisions is the source, you should see bad performance when you FTP a file (manually) between the machines. Take a 10Mb file and see how long that takes to transfer; a .13 turnaround time is very fast and doesn't point to a network problem.
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
There's a lot of Auto-mode-bashing goes on in this forum. What's that about? I like auto-mode.chulett wrote:That must be because you are using the dreaded 'Automatic Mode'. Yuck.
[rant]
If your transformation is a bit on the pudgy side, then you can slip an IPC just before the OCI Load without slowing the transform, and load while you transform. With manual mode, you have to serialize the transform and the load.
Manual is only useful when your transformation can easily out-perform the load - not always the case if you have a few hashed files or routine calls. Even then, you still need to compare OCI Load, SQL*Loader across the network, and SQL*Loader locally on the Oracle server. The best choice will depend on the config and load of the network and the two machines.
[/rant]
Ross Leishman
-
- Participant
- Posts: 31
- Joined: Wed Aug 31, 2005 1:55 am
I have tried all of the suggestions above:
- Running in manual mode and then doing the sqlldr part manually. This gave me a transfer rate of about 1000 rows/sec.
- Placing an IPC-stage between the Transformer and the Bulk-loader stage. This gave me a performance of about 600 rows/sec
I tried connecting the file directly to an IPC-stage and then to a local sequential file. This gave me a transfer rate of about 14000 rows/sec which I think is what the HD can deliver.
Since the file is about 200 bytes wide, a transfer rate of 600 rows/sec requires a network speed of about 0.96 MBit/s. The servers have Gigabit Ethernet cards and are both connected to the same 100 MBit switch.
Clearly there is a bottle neck here and my first guess would be the network, but some of my jobs have had increased their performance on this server compared to the old one.
Another problem is that I can't trace performance statistics on the DS-server. The log entry containing the statistics are apperar but all figures (except the row count) are zero.
- Running in manual mode and then doing the sqlldr part manually. This gave me a transfer rate of about 1000 rows/sec.
- Placing an IPC-stage between the Transformer and the Bulk-loader stage. This gave me a performance of about 600 rows/sec
I tried connecting the file directly to an IPC-stage and then to a local sequential file. This gave me a transfer rate of about 14000 rows/sec which I think is what the HD can deliver.
Since the file is about 200 bytes wide, a transfer rate of 600 rows/sec requires a network speed of about 0.96 MBit/s. The servers have Gigabit Ethernet cards and are both connected to the same 100 MBit switch.
Clearly there is a bottle neck here and my first guess would be the network, but some of my jobs have had increased their performance on this server compared to the old one.
Another problem is that I can't trace performance statistics on the DS-server. The log entry containing the statistics are apperar but all figures (except the row count) are zero.
-
- Participant
- Posts: 31
- Joined: Wed Aug 31, 2005 1:55 am
Um... no. PX only.
"All figures (except the row count) are zero"? Are you saying that you are looking in the 'Finishing (...)' messages for the active stages in the job log and only see zeroes for the CPU usage and elapsed seconds?
Or have you turned on Tracing during the job run and those entries only show zeroes?
"All figures (except the row count) are zero"? Are you saying that you are looking in the 'Finishing (...)' messages for the active stages in the job log and only see zeroes for the CPU usage and elapsed seconds?
Or have you turned on Tracing during the job run and those entries only show zeroes?
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
-
- Participant
- Posts: 31
- Joined: Wed Aug 31, 2005 1:55 am
When I look at the logs I get the following entry for performance statistics:
TestfAvtalerFraFil_MindreFil_Bulk..CTransformerStage1: DSD.StageRun Performance statistics
Name Percent Count Minimum Average
TestfAvtalerFraFil_MindreFil_Bulk..FilAvtale.AvtalerInn 0 222265 0 0
TestfAvtalerFraFil_MindreFil_Bulk..Hash_KundeKey.SjekkKunde 0 222265 0 0
TestfAvtalerFraFil_MindreFil_Bulk..Oracle_OCI_Load_10.GodkjenteAvtaler 0 222265 0 0
Status.Update 0 66 0 0
SjekkKunde.Derivation 0 222265 0 0
StageVars.Derivation 0 222265 0 0
GodkjenteAvtaler.Derivation 0 222265 0 0
TestfAvtalerFraFil_MindreFil_Bulk..CTransformerStage1: DSD.StageRun Performance statistics
Name Percent Count Minimum Average
TestfAvtalerFraFil_MindreFil_Bulk..FilAvtale.AvtalerInn 0 222265 0 0
TestfAvtalerFraFil_MindreFil_Bulk..Hash_KundeKey.SjekkKunde 0 222265 0 0
TestfAvtalerFraFil_MindreFil_Bulk..Oracle_OCI_Load_10.GodkjenteAvtaler 0 222265 0 0
Status.Update 0 66 0 0
SjekkKunde.Derivation 0 222265 0 0
StageVars.Derivation 0 222265 0 0
GodkjenteAvtaler.Derivation 0 222265 0 0
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
So Auto mode (with IPC) writes at 600/s, and Manual at 1000/s. This tells me that the maximum throughput of the transformation is 1000/s, but that the maximum write-rate slows that down to 600/s.
Since 1000/s writes to a local flat file, that eliminates Oracle and the network. Your initial post mentioned a rate of 2000/s. No matter what you do to improve the network and Oracle, the transformation will continue feeding them data at no more than 1000/s. You need to look locally on the DS server for the problem.
Have you tried mucking about with the row-buffer size?
Since 1000/s writes to a local flat file, that eliminates Oracle and the network. Your initial post mentioned a rate of 2000/s. No matter what you do to improve the network and Oracle, the transformation will continue feeding them data at no more than 1000/s. You need to look locally on the DS server for the problem.
Have you tried mucking about with the row-buffer size?
Ross Leishman
-
- Participant
- Posts: 31
- Joined: Wed Aug 31, 2005 1:55 am
The problem arises when I try to send data to another server... As I stated above, when writing to a local file inestead get a transfer rate of 14000 rows/sec.
Writing the SQLLDR datafile when using bulk loader is about 6000 rows/sec. The transfer of these data with sqlldr gives med 1000 rows/sec.
This doesn't strike me as a problem with the performance on the DS-server.
Writing the SQLLDR datafile when using bulk loader is about 6000 rows/sec. The transfer of these data with sqlldr gives med 1000 rows/sec.
This doesn't strike me as a problem with the performance on the DS-server.
What speed does a FTP from your DS server to the remote machine achieve?
<a href=http://www.worldcommunitygrid.org/team/ ... TZ9H4CGVP1 target="WCGWin">
</a>
</a>
-
- Premium Member
- Posts: 252
- Joined: Mon Sep 19, 2005 10:28 pm
- Location: Melbourne, Australia
- Contact:
Apols. I read your earlier report of 14000/s as being a simple read/write from flat file to benchmark your disk - not as a throughput figure on the job in question.
Re the quoted figure of 1000/s using sqlldr: are you running sqlldr across the network? Or locally on the Oracle server after an FTP? It would be good to have a comparison.
Also as ArndW suggested, an FTP benchmark would be worthwhile.
Re the quoted figure of 1000/s using sqlldr: are you running sqlldr across the network? Or locally on the Oracle server after an FTP? It would be good to have a comparison.
Also as ArndW suggested, an FTP benchmark would be worthwhile.
Ross Leishman