BOB: Business Objects Board
Not endorsed by or affiliated with SAP

Register | Login 

Launch Works 
Launch Works (Opens a new window)  

General Notice: No events within the next 45 days.

Benchmark YOUR hardware and compare the results V1.1
1 members found this topic helpful
Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8, 9, 10  Next
 
Search this topic... | Search DI: Performance and Tuning... | Search Box
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Author Message
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Tue Sep 07, 2010 3:44 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

For now I would be graceful if anybody would run the test so I would know they work.

Rerunning all tests is not required, the only number that was collected wrong in the previous version was for ORACLE, the READ test if a NETWORK was involved there. Oracle does compress identical rows in the network layer and hence we never got the network to be busy there. Didn't know that.

The other tests were okay, I would not expect major differences.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
mikebowen
Senior Member
Senior Member



Joined: 10 Aug 2010

Posts: 93
Location: Seattle, WA


flag
PostPosted: Tue Mar 22, 2011 5:24 pm 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

Just saw this and thought I would give it a try.

Database: SQL Server 2008 Enterprise Edition 64 bit
Windows: Windows Server 2008 64 bit
Data Services: XI R3.2


DS Server: 2.67GHz Xeon (2 cpu), 8GB RAM, running on VMware (not sure the details, that's for the operations guys)

DB Server: 2.13 GHz Xeon (2 cpu), 12GB RAM, can't remember the exact specs on the drives but its some whiz-bang fiber EVA thing or something icon_redface.gif

(12.2) 03-22-11 14:48:45 (1064:4260) DATAFLOW: Process to execute data flow <DF_Benchmark_read> is completed.
(12.2) 03-22-11 14:48:45 (1520:4108) PRINTFN: Time______________________________ 57 secounds
(12.2) 03-22-11 15:10:30 (3644:4444) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.2) 03-22-11 15:10:30 (1520:4108) PRINTFN: Time______________________________ 1305 secounds
(12.2) 03-22-11 15:17:22 (2264:4000) DATAFLOW: Process to execute data flow <DF_Benchmark_regular_load> is completed.
(12.2) 03-22-11 15:17:22 (1520:4108) PRINTFN: Time______________________________ 412 secounds
(12.2) 03-22-11 15:17:32 (3808:1860) DATAFLOW: Process to execute data flow <DF_Benchmark_single_thread> is completed.
(12.2) 03-22-11 15:17:32 (1520:4108) PRINTFN: Time______________________________ 9 secounds
(12.2) 03-22-11 15:18:05 (5024:4548) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP1> is completed.
(12.2) 03-22-11 15:18:05 (1520:4108) PRINTFN: Time______________________________ 33 secounds
(12.2) 03-22-11 15:18:22 (5104:2348) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP10> is completed.
(12.2) 03-22-11 15:18:22 (1520:4108) PRINTFN: Time______________________________ 17 secounds
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Wed Mar 23, 2011 9:22 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

dnewton wrote:
So I repeated my October 2008 test, but on a slightly newer Windows server, and more importantly, this time the underlying disk subsystem is a NetApp 3160 SAN, RAID 4. With SQL 2005 and
DI 11.7.

Results from June 2009 test

<DF_Benchmark_read_MS>: 107 seconds
<DF_Benchmark_API_bulkloader_MS>: 238 seconds
<DF_Benchmark_regular_load_MS>: 382 seconds
<DF_Benchmark_single_thread_MS>: 19 seconds
<DF_Benchmark_lookup_DOP1_MS>: 71 seconds
<DF_Benchmark_lookup_DOP10_MS>: 305 seconds

And once again, even on this brand-new machine and new environment ... on that last test (DOP10), when I change the DF from Pageable to In Memory, the time dropped down from 305 to 20 seconds. It's interesting that the bulk load is 25% faster but the regular load is 45% slower...


New Test as of March 2011

DS XI 3.2 (12.2.2.3) on Win2008 x64, 80Gb RAM, SQL Server 2008 x64, on a dedicated NetApp 3160 SAN w/ 28 (?) spindles in RAID 4. DI and SQL Server all on the same machine. Same hardware as my previous test, but with a newer DS and newer SQL Server version.

Results

<DF_Benchmark_read_MS>: 87 seconds
<DF_Benchmark_API_bulkloader_MS>: 252 seconds
<DF_Benchmark_regular_load_MS>: 446 seconds
<DF_Benchmark_single_thread_MS>: 16 seconds
<DF_Benchmark_lookup_DOP1_MS>: 50 seconds
<DF_Benchmark_lookup_DOP10_MS>: 15 seconds


A couple of observations - for some reason our disk performance seems to be getting worse in this latest combination (compared to the same hardware in the previous test).

Also, I think we're cheating a bit by forcing the dataflows to in-memory, since it's not the default behavior, nor is it the safest behavior for a real production environment, where data volumes can be highly variable.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Thu Mar 24, 2011 2:56 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

@Mike: DF_Benchmark_API_bulkloader: 1305 seconds????

Why is the API bulkloader slower than the regular load??? Can you try again but this time set the number of loaders option in the target table to the same value as in the regular dataflow? Or did my script include that setting already for SQL Server??


@Doug: Cheating? Memory?? The only place where I cache data is for the lookup tables, isn't it? It seems I am not getting your point, can you elaborate a bit.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Thu Mar 24, 2011 7:08 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

wdaehn wrote:
@Doug: Cheating? Memory?? The only place where I cache data is for the lookup tables, isn't it? It seems I am not getting your point, can you elaborate a bit.


The 1.0 test left the dataflows in the default Pageable mode. (And without stats to back it up when running it, it would run in Pageable.) Dataflows seem to run faster when set to In-Memory, assuming that they fit within the 2Gb process space. The 1.1 test forces them to In-Memory, which isn't the default dataflow setting. Many customers over the years have been advised by tech support (including us) to disable the usage of stats because it made things unstable.

But the word I used (cheating) is probably too strong, I apologize. For the purposes of comparing apples to apples, as long as everyone running the test has the same setting, then it's good.

My concern is just that comparing the 1.0 test to the 1.1 test isn't valid, it makes it look like performance has increased on the same hardware, but setting the DFs back to Pageable erases that increase.
Back to top
mikebowen
Senior Member
Senior Member



Joined: 10 Aug 2010

Posts: 93
Location: Seattle, WA


flag
PostPosted: Thu Mar 24, 2011 10:52 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

Werner, the number of loaders option on the API load table is greyed out (along with all the other options in the grid)
Back to top
mikebowen
Senior Member
Senior Member



Joined: 10 Aug 2010

Posts: 93
Location: Seattle, WA


flag
PostPosted: Thu Mar 24, 2011 11:19 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

Just fiddling with the bulk loader options, I set the rows per commit to 10000 from 1000 and set the network packet size to 64k from 4k. Big improvement.

(12.2) 03-24-11 09:01:11 (5104:3368) DATAFLOW: Process to execute data flow <DF_Benchmark_read> is completed.
(12.2) 03-24-11 09:01:11 (3228:2352) PRINTFN: Time______________________________ 70 secounds

(12.2) 03-24-11 09:03:38 (3232:4668) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.2) 03-24-11 09:03:38 (3228:2352) PRINTFN: Time______________________________ 147 secounds

(12.2) 03-24-11 09:08:18 (2560:1516) DATAFLOW: Process to execute data flow <DF_Benchmark_regular_load> is completed.
(12.2) 03-24-11 09:08:18 (3228:2352) PRINTFN: Time______________________________ 279 secounds

(12.2) 03-24-11 09:08:27 (5104:3652) DATAFLOW: Process to execute data flow <DF_Benchmark_single_thread> is completed.
(12.2) 03-24-11 09:08:27 (3228:2352) PRINTFN: Time______________________________ 8 secounds

(12.2) 03-24-11 09:09:07 (5044:3704) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP1> is completed.
(12.2) 03-24-11 09:09:07 (3228:2352) PRINTFN: Time______________________________ 40 secounds

(12.2) 03-24-11 09:09:23 (4796:2468) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP10> is completed.
(12.2) 03-24-11 09:09:23 (3228:2352) PRINTFN: Time______________________________ 16 secounds
Back to top
dnewton
Forum Addict
Forum Addict



Joined: 30 Jan 2004
ASUG Icon
Posts: 3456
Location: Massachusetts, USA


flag
PostPosted: Thu Mar 24, 2011 11:34 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

mikebowen wrote:
Werner, the number of loaders option on the API load table is greyed out (along with all the other options in the grid)


Right - with SQL Server there can only be one bulk loader running per target at at ime.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Fri Mar 25, 2011 1:02 pm 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

In-Memory versus Pageable has impact only if data is actually cached. I would state that a regular source-query-target dataflow will show the same execution times regardless of the memory settings.

It is not 100% correct what I am saying as the image activation takes a few ms longer but precise enough.

Do you disagree? Any proof points you can show me for analysis?


That network package size worries me in regards to fair comparisons. Don'tknow what do to about it. Suggestions?

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Fri May 20, 2011 5:35 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

If possible I'd like to give that topic some more attention again.
* I have updated the Results page with the recent information.
* I have made up my mind on what to tune like the network packet size: Don't, or we will not be comparable. The goal is not to get the best number, it is to compare each other's value.
* I corrected one error in the database script, the source table had no partitionkey column although it was used in the dataflow.
* Please check the API bulkloader settings. enable partition is preferred, if not possible the number of loaders should be set to 8. Only if that's not allowed either use a single bulkloader thread.

Okay with that?

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Thu May 26, 2011 1:26 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

@Doug, Mike: When you ran your tests, were the 10 reader threads for the read case and 10 loaders for the two load cases?
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
ClintL
Senior Member
Senior Member



Joined: 06 Jan 2011

Posts: 44


flag
PostPosted: Wed Aug 03, 2011 5:15 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

Source database type = SQL Server 2005
Source database hardware Type = Windows Server 2003 R2 Enterprise x64 Edition SP2
Source database hardware CPU = 8 cores E5430 @ 2.66GHz
Source database hardware Disk = 16 disks IBM ServeRAID 8k/8k-l for the database
Network from source database to Jobserver = 1GBit Ethernet
Data Integrator Version = 12.1
DataIntegrator Hardware Type = Windows Server 2003 R2 32bit Enterprise Edition SP2
DataIntegrator Hardware CPU = 8 cores E5430 @ 2.66GHz - 16Gb RAM
DataIntegrator Hardware VM = No
Network from JobServer to target database = 1GBit Ethernet
Target database type = SQL Server 2005
Target database hardware Type = Windows Server 2003 R2 Enterprise x64 Edition SP2
Target database hardware CPU = 8 cores E5430 @ 2.66GHz
Target database hardware Disk = 16 disks IBM ServeRAID 8k/8k-l for the database

(12.1) 08-02-11 16:55:01 (9044:9444) DATAFLOW: Process to execute data flow <DF_Benchmark_read> is completed.
(12.1) 08-02-11 16:55:01 (10192:10224) PRINTFN: Time______________________________ 108 secounds
(12.1) 08-02-11 16:59:10 (12112:5136) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-02-11 16:59:10 (10192:10224) PRINTFN: Time______________________________ 248 secounds
(12.1) 08-02-11 17:04:29 (8904:8828) DATAFLOW: Process to execute data flow <DF_Benchmark_regular_load> is completed.
(12.1) 08-02-11 17:04:29 (10192:10224) PRINTFN: Time______________________________ 319 secounds
(12.1) 08-02-11 17:04:40 (9336:9228) DATAFLOW: Process to execute data flow <DF_Benchmark_single_thread> is completed.
(12.1) 08-02-11 17:04:40 (10192:10224) PRINTFN: Time______________________________ 10 secounds
(12.1) 08-02-11 17:05:02 (8744:11820) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP1> is completed.
(12.1) 08-02-11 17:05:02 (10192:10224) PRINTFN: Time______________________________ 22 secounds
(12.1) 08-02-11 17:05:10 (11844:11788) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP10> is completed.
(12.1) 08-02-11 17:05:10 (10192:10224) PRINTFN: Time______________________________ 8 secounds

_________________
SAP CERTIFIED ASSOCIATE (Data Services)
Ixia Consulting
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Wed Aug 03, 2011 7:05 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

Nice. I have updated the result page. Everything looks okay, the API load times could be better. Either it is because of SQL Server 2005, VMWare loading over the network or something else.

Maybe you want to play with the packet sizes in the loader to see what the best value would be for you. Could make a difference. But as said a few posts earlier, these numbers would not be published, it will be just so we know.

_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
ClintL
Senior Member
Senior Member



Joined: 06 Jan 2011

Posts: 44


flag
PostPosted: Thu Aug 04, 2011 9:10 am 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

Hi Werner,

For interest here are some results of just the API load dataflow using different packet sizes, these were done over two days and you can see that the results are not consistent. Yesterday 64Kb performed best (261s), but today 16Kb performed best (254s). And no other jobs were running at the time of the tests, and the SQL database is dedicated to BODS, so I think it must be simply other network traffic that is causing the inconsistent results. Nevertheless, 254 seconds was the best I managed to achieve.

Network Packet Size
16Kb (12.1) 08-03-11 14:26:29 (6940:9644) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-03-11 14:26:30 (8444:10248) PRINTFN: Time______________________________ 275 secounds
32Kb (12.1) 08-03-11 15:10:00 (8272:10776) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-03-11 15:10:00 (6356:8936) PRINTFN: Time______________________________ 264 secounds
64Kb (12.1) 08-03-11 15:33:45 (10216:9288) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-03-11 15:33:45 (6268:9108) PRINTFN: Time______________________________ 261 secounds
4Kb (12.1) 08-03-11 15:41:07 (6208:6756) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-03-11 15:41:07 (10632:6260) PRINTFN: Time______________________________ 273 secounds
64Kb (12.1) 08-04-11 15:27:13 (7020:2604) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-04-11 15:27:13 (5280:2892) PRINTFN: Time______________________________ 256 secounds
4Kb (12.1) 08-04-11 15:37:50 (2984:10384) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-04-11 15:37:50 (11084:7792) PRINTFN: Time______________________________ 268 secounds
16Kb (12.1) 08-04-11 15:46:30 (10084:11204) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 08-04-11 15:46:31 (2456:6040) PRINTFN: Time______________________________ 254 secounds

_________________
SAP CERTIFIED ASSOCIATE (Data Services)
Ixia Consulting
Back to top
Werner Daehn
Forum Devotee
Forum Devotee



Joined: 17 Dec 2004

speaker.gif*6
Posts: 10590
Location: Germany


flag
PostPosted: Thu Aug 04, 2011 11:29 pm 
Post subject: Re: Benchmark YOUR hardware and compare the results V1.1

Now I would be interested even more why your number is 40% less of what others have seen. But how do find out??
_________________
Werner Daehn
Product Manager Data Services
SAP AG
“The opinions expressed in this post are my own views and not those of SAP.”

PLEASE SUPPORT THIS: Run one ATL job and tell us the benchmark result. Details to be found here.
Back to top
Display posts from previous:   
Register or Login to Post    Forum Index -> Data Integrator -> DI: Performance and Tuning  Previous TopicPrint TopicNext Topic
Page 8 of 10 All times are GMT - 5 Hours
Goto page Previous  1, 2, 3, 4, 5, 6, 7, 8, 9, 10  Next
 
Jump to:  

Index | About | FAQ | RAG | Privacy | Search |  Register |  Login 

Get community updates via Twitter:

Not endorsed by or affiliated with SAP
Powered by phpBB © phpBB Group
Generated in 0.1209 seconds using 17 queries. (SQL 0.0033 Parse 0.1037 Other 0.0139)
CCBot/2.0 (http://commoncrawl.org/faq/)
Hosted by ForumTopics.com | Terms of Service
phpBB Customizations by the phpBBDoctor.com
Shameless plug for MomentsOfLight.com Moments of Light Logo