Curious regarding experiences with running ETL with Data Services XI R3.0 in a Windows 2003 Server environment vs native 64-bit DS ETL in Unix environments? We have a HIGH-end Dell Server with 32 GB RAM, fast 15k RPM drives, 2 3.16 GHz quad-core CPUS, gobs of disk space for PCACHE, Windows 2003 Enterprise Server x64, etc., etc. From over 6 months of observation it seems that the 2 GB limit on the 32-bit server for DS is slowing down the processing. I can’t see how PCACHE on disk is faster than memory??
It isn’t! Disk is several orders of magnitude slower. So if your dataflows are very memory-intensive, and you’re routinely going above 1.5Gb of RAM per al_engine.exe process, you’ll start paging and that will slow things down for sure.
So all other things being equal, if you have memory-intensive dataflows, 64-bit could provide some performance benefit.
One point to note: Patches and releases for *nix on DI lag 3+ months behind those on Windows. I still would not trade my nice Linux box in, but some people consider that a deal breaker.
IMO, you also get more native OS functionality on *nix given the broader support of open source, the variety and functionality of file manipulation tools and the lower amount of down time due to OS related patches.
32bit vs 64bit does not really have a performance difference in ETL, it is just the memory space allocation. But even there I would wonder if you can’t redesign the dataflow to consume less memory. Caching might be a neccessarity but if it can be avoided, the resulting dataflow will always be faster - no cache build time, no step wise processing,… instead a nice streaming of data all in parallel.
One thing to consider as a downside for Unix is that Intel CPUs are typically faster by far, e.g. Intel 3.2GHz vs. Unix 1.8(?) GHz? So you would need two Unix CPUs instead of one Intel CPU to offset the raw CPU performance - if that happens to be the bottleneck.
I realy look forward to the next version, DI 12.2 as there we will have Linux 64bit - Unix + Intel + 64bit…my favorite.
Redesigning the ETL would be difficult. We are a SAP/BOBJ OEM and ship a BI product based on BOE XI 3.0 and BO DS 3.0. Currently, we only support Windows platforms for both BI and the database. Would we gain anything by migrating to Solaris or HP/UX for native 64-bit Data Services code? Is DS capable of using large amounts of memory on Unix 64-bit platforms and bypass the need for time-consuming query crunching on disk-based PCACHE?
Yes, for that specific use case, it would provide a benefit.
However, even with 64 bit, you still need to think about memory management and tuning. 64-bit gets you higher theoretical limits, but most commodity (cheap) servers are still 16Gb RAM or less. You may find that spending time to make the dataflows more “streaming” rather than using memory may ultimately make them run even faster than solely moving to 64-bit.
Point well taken. However, we are shipping a product to an external customer (note that we are a SAP/BOBJ OEM). Thus, changing the ETL is not that simple, as it would be in an internal IT environment. Does that make sense?
I agree with Doug on this one. We had a pair of jobs that performed fine (~6 minutes per run) that were written for a certain expected volume and began to gradually grow in both source and target volume until they ran almost 3.5 hours per run. I rewrote / tuned them in a couple of man days (~16 hours).
Net result: Jobs combined daily run went down from 3.5 hours to 40 minutes. Even if you are very conservative and say my time is $200 an hour, $3200 is unlikely to gain you a 75% decrease in run time for a given job. Yes, this time adds up, and it takes away from new development for a time but it can save ALOT of money, resources and trouble in the long run.
By the way, I am not trying to convince you that 64 bit *nux is a bad path, I will be moving there from 32 bit *nix this year.
Understood, but if you are going to change OS that is the perfect time to also introduce new code, since you will have to do end to end testing in any case.
Pick a dataflow and send the ATL, either post it here or per email. Would like to know if there is something obvious or not. That would be the other variable, if changing the flow just a bit cuts performance by factors vs. you have to change the dataflow a lot for just little gain. If we can answer that, you would have a better idea of cost vs. benefit.
Results from the benchmark test, please find attached
Environment
Specs are identical for both the database and ETL servers.
OS - Windows 2003 Enterprise Edition x64
Database - Oracle 10.2.0.4 x64, Patch 5
ETL - Data Services XI 3.0
Dell high-end server (not sure of the model number),
32 GB RAM
15K RPM internal drives, C drive on a RAID0+1 array 272 GB, D drive on RAID+5 array, 408 GB.
2 quad-core Intel Xeon 3.16 GHz processors
1 Broadcom BCM5708C NetXtreme II 1 Gbps network card
buckshot
Source: Oracle 9i
Network source to DI: 1GBit
DI server: Win2003
4 Xeon CPUs @ 3.6GHz /3.5GB
DI: 11.7
Network DI to Target: 1GBit
Target: Oracle 9i
read (over network): 44 secs
API bulkloader: 207 secs
regular loader: 281 secs
single threaded: 22 secs
lookup DOP1: 59 secs
lookup_DOP10: 72 secs
Source: Oracle 10g
Network source to DI: 1GBit
DI server: Win2003 64bit
VMWare 2 CPU (Quad)@ 3.16Ghz/32GB
DI: 12.0
Network DI to Target: none
Target: Oracle 10g
read (over network): 34 secs
API bulkloader: 121 secs
regular loader: 289 secs
single threaded: 13 secs
lookup DOP1: 30 secs
lookup_DOP10: 10 secs
(Note: The single threaded case I changed the number from 76secs to 13secs as the monitor log showed the threads themselves have been active for this amount of time only. Maybe the truncate tabel took so long or whatever.)
Am I correct with the assumption between source and DI there is a network and DI is located at the same server as the target?
The DS and the database are located on separate servers. However, both are located in the same rack with only a high speed switch between the two. This configuration represents our internal test environment. At a customer site the network topology most likely will be different.
I was sniffing around the posts today and came across this post…
It makes perfect sense for my results, so I phoned up our server guys to check and yep our DS host server is on the same rack as the target DB host server with a high speed switch between them !
(BOB member since 2004-01-30)
(BOB member since 2005-09-16)
(BOB member since 2004-12-17)