Benchmark YOUR hardware and compare the results V1.1

[ClintL]

Werner, I'm not sure why you say my numbers are 40% less than others. I've had a look at the table of results and the other numbers are 1305s, 252s, 147s, 161s, so mine seems about average I would say.

[Werner Daehn]

Yes, but given the power of your servers I would have expected you lead the list. So I am asking myself, is it SQL Server 2005 vs. 2008? VMWare? Shouldn't be the network as the reading is fast.

Anyway, certainly nothing wrong with your numbers as such!

[Johannes Vink]

Lots of unknowns in the environment and hardware, but here are some results that I recently ran to check a new server:

Source/target db is the same, also the same schema.

Source database type Oracle 11g
Source database hardware Type Unknown
Source database hardware CPU Unknown
Source database hardware Disk Unknown
Target database type Oracle 11g
Target database hardware Type Unknown
Target database hardware CPU Unknown
Target database hardware Disk Unknown
Network from JobServer from/to database 10 GBit Ethernet
DataIntegrator Hardware VM VMWare, unknown with what kind of hardware
Likely all hardware is in the same data center.

Name of the test DS 3.1 Dev
Network from source database to Jobserver 10 GBit Ethernet
DataIntegrator Hardware Type Windows 2003 Standard Edition SP2 32 bit
DataIntegrator Hardware CPU 1 cpu 4 core AMD Opteron 8389 @ 2.91 GHz
DataIntegrator Hardware Memory 3,75 GB

(12.1) 03-21-12 14:51:23 (6636:8152) DATAFLOW: Process to execute data flow <DF_Benchmark_read> is completed.
(12.1) 03-21-12 14:51:23 (5960:6188) PRINTFN: Time______________________________ 221 secounds
(12.1) 03-21-12 15:08:01 (5592:7052) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 03-21-12 15:08:01 (5960:6188) PRINTFN: Time______________________________ 997 secounds
(12.1) 03-21-12 15:17:57 (4124:1088) DATAFLOW: Process to execute data flow <DF_Benchmark_regular_load> is completed.
(12.1) 03-21-12 15:17:57 (5960:6188) PRINTFN: Time______________________________ 592 secounds
(12.1) 03-21-12 15:18:25 (5856:1572) DATAFLOW: Process to execute data flow <DF_Benchmark_single_thread> is completed.
(12.1) 03-21-12 15:18:25 (5960:6188) PRINTFN: Time______________________________ 13 secounds
(12.1) 03-21-12 15:18:50 (3892:7568) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP1> is completed.
(12.1) 03-21-12 15:18:50 (5960:6188) PRINTFN: Time______________________________ 25 secounds
(12.1) 03-21-12 15:19:17 (2400:1532) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP10> is completed.
(12.1) 03-21-12 15:19:18 (5960:6188) PRINTFN: Time______________________________ 27 secounds


Name of the test DS 3.1 Dev
DataIntegrator Hardware Type Windows Server Enterprise Edition SP2 32 bit
DataIntegrator Hardware CPU 2 cpu 4 core AMD 8389 @ 2.51GHz
DataIntegrator Hardware Memory 9 GB

(12.1) 03-22-12 09:39:04 (5340:6792) DATAFLOW: Process to execute data flow <DF_Benchmark_read> is completed.
(12.1) 03-22-12 09:39:04 (7364:4740) PRINTFN: Time______________________________ 215 secounds
(12.1) 03-22-12 09:52:29 (7536:4932) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 03-22-12 09:52:29 (7364:4740) PRINTFN: Time______________________________ 805 secounds
(12.1) 03-22-12 10:03:42 (6332:5656) DATAFLOW: Process to execute data flow <DF_Benchmark_regular_load> is completed.
(12.1) 03-22-12 10:03:43 (7364:4740) PRINTFN: Time______________________________ 670 secounds
(12.1) 03-22-12 10:04:23 (4380:5020) DATAFLOW: Process to execute data flow <DF_Benchmark_single_thread> is completed.
(12.1) 03-22-12 10:04:23 (7364:4740) PRINTFN: Time______________________________ 26 secounds
(12.1) 03-22-12 10:04:58 (5172:6948) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP1> is completed.
(12.1) 03-22-12 10:04:59 (7364:4740) PRINTFN: Time______________________________ 35 secounds
(12.1) 03-22-12 10:05:46 (8156:7144) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP10> is completed.
(12.1) 03-22-12 10:05:46 (7364:4740) PRINTFN: Time______________________________ 47 secounds


Name of the test DS 4.0 SP2 Dev
DataIntegrator Hardware Type Windows Server 2008 Enterprise Edition 64 bit
DataIntegrator Hardware CPU 5 cpu 6 core Xeon X7542 @ 2.66GHz
DataIntegrator Hardware Memory 22,5 GB

(14.0) 03-22-12 10:37:08 (5720:7016) DATAFLOW: Process to execute data flow <DF_Benchmark_read> is completed.
(14.0) 03-22-12 10:37:09 (5324:6344) PRINTFN: Time______________________________ 217 secounds
(14.0) 03-22-12 10:48:37 (3768:1624) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(14.0) 03-22-12 10:48:37 (5324:6344) PRINTFN: Time______________________________ 688 secounds
(14.0) 03-22-12 10:57:51 (3892:1664) DATAFLOW: Process to execute data flow <DF_Benchmark_regular_load> is completed.
(14.0) 03-22-12 10:57:51 (5324:6344) PRINTFN: Time______________________________ 550 secounds
(14.0) 03-22-12 10:58:16 (5780:6816) DATAFLOW: Process to execute data flow <DF_Benchmark_single_thread> is completed.
(14.0) 03-22-12 10:58:16 (5324:6344) PRINTFN: Time______________________________ 11 secounds
(14.0) 03-22-12 10:58:38 (6204:2588) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP1> is completed.
(14.0) 03-22-12 10:58:38 (5324:6344) PRINTFN: Time______________________________ 21 secounds
(14.0) 03-22-12 10:58:59 (6876:6160) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP10> is completed.
(14.0) 03-22-12 10:58:59 (5324:6344) PRINTFN: Time______________________________ 21 secounds

Interesting to note is that the middle server in the list is better than the first server, but did not perform better. Only difference is the GHz... But I'll re-execute the middle one as that server can be heavily utilized from time to time.


P.s. secounds is a type in the ATL I got from the SAP BODS wiki...

[bernard timbal]

Database : MSSQL 2008 R2 SP1
BODI 12.2.0.1
OS : Windows 64 bits
RAM : 16 GB
Core I7-2760 QM CPU @2.40 GHz (4 cores)

[dnewton]

I can't quite match Bernal's CPU numbers, but check out these I/O rates... this is using a FusionIO SSD-on-PCI storage system.

- BODS 14.0.2 (x64) - server 4-core Xeon, 3.2Ghz, Win 2008r2

- SQL server - separate server, SQL 2008r2, 40-core Xeon, array of 4 FusionIO SSD cards

- 10Gb ethernet, on same subnet

- DSCONFIG set to Global DOP of 4

Read - 16
API bulkload - 28
Regular load - 127
Single thread - 6
DOP 1 - 18
DOP 10 - 6


Werner - note the ATL needs adjustment to force the DOP of "single thread" and "DOP 1" dataflows to be a 1, not a 0.

Solid State Disk is the way to go... http://www.fusionio.com/

[Werner Daehn]

Arrgh. "Seconds" Copy and paste error. I'll correct both things.

[Werner Daehn]

Data added, ATL file corrected: the Seconds and the DOP=1 instead of DOP=0. (DOP=0 means "default" as stored in the DSConfig and there is is set to 1 - so usually it will be the same)

@Johannes: While the CPU numbers are fine, reading and loading is not. There is something wrong, either with the test or the computers. Did the monitor log show in the read case that it was reading from multiple partitions in parallel. In other words, there have been many monitor log lines for the reader? Same with the load?
What is most interesting is the fact the API load is slower than a regular SQL insert? Even if the database would enforce logging ("alter table nolog") there should not be that much of a difference. Was the enable-partition loader option set?

@Bernard: Any idea regarding the load times? Why are they the same and why is the number the worst here? Reading is excellent - was there a network inbetween btw??, and CPU is excellent, best numbers we ever had.

@Doug: Finally we can see the 10Gb Ethernet in action. Nice. Writing is just 3 to 5 times faster then the best number we ever got here Amazing.

[Johannes Vink]

Source/target db is the same, also the same schema. Database is different this time as the benchmark was run on the production server.

Source database type Oracle 11g
Source database hardware Type Unknown
Source database hardware CPU Unknown
Source database hardware Disk Unknown
Target database type Oracle 11g
Target database hardware Type Unknown
Target database hardware CPU Unknown
Target database hardware Disk Unknown
Network from JobServer from/to database 10 GBit Ethernet
DataIntegrator Hardware VM VMWare, unknown with what kind of hardware
Likely all hardware is in the same data center.

Name of the test DS 3.1 Prod
Network from source database to Jobserver 10 GBit Ethernet
DataIntegrator Hardware Type Windows 2003 Enterprise Edition SP2 32 bit
DataIntegrator Hardware CPU 1 cpu 4 core AMD Opteron 8389 @ 2.51 GHz
DataIntegrator Hardware Memory 11,7 GB

(12.1) 03-25-12 17:09:36 (4140:4552) DATAFLOW: Process to execute data flow <DF_Benchmark_read> is completed.
(12.1) 03-25-12 17:09:36 (4144:2812) PRINTFN: Time______________________________ 233 secounds
(12.1) 03-25-12 17:22:39 (5880:4908) DATAFLOW: Process to execute data flow <DF_Benchmark_API_bulkloader> is completed.
(12.1) 03-25-12 17:22:39 (4144:2812) PRINTFN: Time______________________________ 783 secounds
(12.1) 03-25-12 17:32:08 (4948:6088) DATAFLOW: Process to execute data flow <DF_Benchmark_regular_load> is completed.
(12.1) 03-25-12 17:32:08 (4144:2812) PRINTFN: Time______________________________ 565 secounds
(12.1) 03-25-12 17:32:36 (3584:3204) DATAFLOW: Process to execute data flow <DF_Benchmark_single_thread> is completed.
(12.1) 03-25-12 17:32:36 (4144:2812) PRINTFN: Time______________________________ 14 secounds
(12.1) 03-25-12 17:33:05 (6084:1896) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP1> is completed.
(12.1) 03-25-12 17:33:05 (4144:2812) PRINTFN: Time______________________________ 28 secounds
(12.1) 03-25-12 17:33:59 (2976:5356) DATAFLOW: Process to execute data flow <DF_Benchmark_lookup_DOP10> is completed.
(12.1) 03-25-12 17:33:59 (4144:2812) PRINTFN: Time______________________________ 53 secounds

Again as Werner said, CPU is fine. Database and i/o is not. Monitor log shows round robin, lots of readers etc.

But I missed this comment:

[bernard timbal]

[Werner Daehn]

I have added a Hana run of that benchmark as well. Only tuning is to use a columnar table "create column table TARGET (....".

The machine had low GHz numbers but 128 cores. As all is set to DOP=10 at max, more than 10 cores are never used actually, well maybe 12 cores overall.

Also keep in mind, loading Hana is disk-based as well, not just storing the data in memory. The point in time DS does a commit is the point where the data has to be flushed to disk so in case of a power cut, committed data is not lost.

Comparing the numbers with Doug's recent ones show that. The DS-only cases this machine was slower as it had less CPU power per core. The database was slightly faster. I am not sure if the disk system contains regular disks or SSDs.

[aaronwilker]

Are you still taking benchmarks of hardware? Also, does the server have to only be running the benchmark test and nothing else?

[Werner Daehn]

This is a service for YOU! You can take any hardware you like and run the benchmark job. I will document it and e.g. make people aware that it was a shared server and if we see something out of the ordinary we try to help.

Usually the hardware used for DS is running the database in addition. Servers with DS exclusively are less common.

[dnewton]

[Werner Daehn]

You could assign a subset of the cpus/cores to DS on the large server!

[dnewton]

In Windows? How?

And more importantly -- my understanding is - the SAP licensing group wouldn't recognize this as a valid restriction or means to stay in compliance.