StorageReview’s SQL Server OLTP testing protocol employs the current draft of the Transaction Processing Performance Council’s Benchmark C (TPC-C), an online transaction processing benchmark that utilizes a combination of read-only and update-intensive transactions to simulate the activities found in complex database application environments.
StorageReview’s SQL Server OLTP testing protocol employs the current draft of the Transaction Processing Performance Council’s Benchmark C (TPC-C), an online transaction processing benchmark that utilizes a combination of read-only and update-intensive transactions to simulate the activities found in complex database application environments. While the TPC-C benchmark cannot simulate the complete range of database applications in use, it comes much closer than simpler synthetic performance benchmarks when gauging the performance strengths and bottlenecks of storage and server infrastructure in database environments.
The TPC-C simulates an environment where many terminal operators execute transactions against a database, including essential online transaction processing (OLTP) activities such as entering and delivering orders, recording payments, checking the status of orders, and monitoring the level of stock at warehouses. The scenario and workload is modeled on the requirements and workloads of a wholesale supplier, but is intended to broadly reflect any industry that must manage, sell, or distribute a product or service.
Five types of transactions are used to model this business activity, with the results expressed in terms of orders that can be fully processed per minute. The TPC-C benchmark requires the simultaneous execution of multiple transaction types of different complexities, processing for online and deferred transactions, transaction integrity (ACID properties), non-uniform distribution of data access through primary and secondary keys, and contention on data access and update.
Dell Benchmark Factory for Databases
Dell Benchmark Factory for Databases is a database performance testing tool that is intended to test patches and other new code, perform migrations, consolidate databases, virtualize existing databases, and make other changes to hardware or software environments. Benchmark Factory for Databases also features a workload capture and replay technology that scripts Oracle and SQL Server production workloads on test environments to measure performance, functionality which we use as part of our SQL Server OLTP benchmark protocol.
Windows Server 2012
Windows Server 2012 incorporates a wide variety of server functionality including storage, networking, virtualization, and automation capabilities. Windows Server 2012 can make use of the Hyper-V virtualization platform with up to 64 virtual processors and 1TB of memory per virtual machine.
SQL Server 2012 SP1
SQL Server 2012 is a platform for on-premise and public cloud information storage, and incorporates AlwaysOn high availability functionality, consistent data via BI Semantic Model and Data Quality Services, and data discovery via Power View and PowerPivot. According to Microsoft, SQL Server 2012’s xVelocity in-memory technologies provide query performance gains over SQL Server 2008.
SQL Server OLTP Testing Environment
Storage solutions are tested with the SQL Server OLTP benchmark in the StorageReview Enterprise Test Lab utilizing multiple servers connected over a high-speed network. We utilize the Dell PowerEdge R730 for different segments of the Environment, including four R730s acting as ESXi 5.1 hosts, one R730 running a VMware vCenter Appliance, and one R730 as a bare-metal server running SQL Server 2012 on top of Windows Server 2012. The Dell PowerEdge brand is currently our top choice when designing this platform, as it offers superior performance and still driving great value. The PowerEdge line also offers excellent hardware compatibility, which is an absolute must as we incorporate different forms of storage and networking technology into our testing platform.
Mellanox 56Gb InfiniBand interconnects were used to provide the highest performance and greatest network efficiency on each ESXi vSphere host to ensure that the VMs connected are not network-limited. We use one single-port Mellanox ConnectX-3 NIC operating in IPoIB mode giving us a 56Gb/s link between each server. This alleviates any network constraints and reduces the complexity of the environment in our multi-use testing infrastructure.
First Generation SQL Server OLTP Benchmark Factory LoadGen Equipment
Second Generation SQL Server OLTP Benchmark Factory LoadGen Equipment
SQL Server OLTP Benchmark Results
The primary performance metric reported by TPC-C is a “business throughput” measurement for the number of orders processed per minute, expressed as transactions-per-minute-C (tpmC). Other metrics including price-per-tpmC and watts-per-tpmC are also reported.
The Company portrayed by the benchmark is a wholesale supplier with a number of geographically distributed sales districts and associated warehouses. As the simulated business expands, new warehouses and sales districts are created. Each regional warehouse covers 10 districts and each district serves 3,000 customers. Each simulated warehouse has ten terminals and all five transactions are available at each terminal. The tpm-C metric is the number of ‘New Order’ transactions executed per minute.
In our Benchmark Factory testing environment, we use a modified TPC-C testing scheme, with VU latencies set to 1/4 of their original figures to drive additional I/O to the storage medium being tested. We use 1,500 scale and 3,000 scale database size for testing different classes of products, which works out to database sizes measuring 333GB and 685GB respectively.
Newer SAN and Hyper-converged platforms run 4 VMs simultaneously to show how well multiple workloads operate at the same time on each. This testing methodology helps demystify the performance comparisons between newer hyper-converged systems against traditional SAN storage arrays.
Hyper-Converged / SAN Virtualized SQL Performance Results (4 VM Aggregate)
| Device | 15k Virtual User Aggregate TPS | 15k Virtual User Average Response Time (ms) |
|---|---|---|
| X-IO ISE 860 (4) Dell R730, X-IO ISE 860 AFA (2) 10TB Volumes | 12,565 | 13 |
| DotHill Ultra48 Hybrid (4) Dell R730, DotHill Ultra48 Hybrid (2) 14-disk RAID1 Pools, 40 1.8TB HDDs, 8 400GB SSDs | 12,480 | 65.5 |
| VMware VSAN (ESXi 6.0) (4) Dell R730xd, 80 1.2TB HDDs, 16 800GB SSDs | 12,437 | 74 |
Local Flash Storage Virtualized SQL Performance Results (4 VM Aggregate)
| Device | 15k Virtual User Aggregate TPS | 15k Virtual User Average Response Time (ms) |
|---|---|---|
| Intel SSD DC P3700 3.2TB | 12,629.366 | 7.0 |
| HGST Ultrastar SN100 AIC 3.2TB | 12,599.87 | 7.0 |
| SanDisk PX600 3.2TB | 12,628.64 | 7.0 |
| Huawei ES3000v2 3.2TB | 12,629.37 | 7.0 |
| Samsung XS1715 3.2TB | 12,628.641 | 7.0 |
| Memblaze PBlaze4 3.2TB | 12,628.447 | 7.5 |
| Memblaze PBlaze4 6.4TB | 12,628.24 | 7.5 |
PCIe Application Accelerator / Multi-SSD/HDD RAID Performance Results (3,000 scale)
| Device | 30k Virtual User Average TPS | 30k Virtual User Average Response Time (ms) |
|---|---|---|
| Fusion ioDrive2 Duo 2.4TB eMLC PCIe | 6322.78 | 3 |
| Huawei ES3000 2.4TB MLC PCIe | 6321.67 | 3 |
| Fusion SX300 3.2TB MLC PCIe | 6321.1 | 3 |
| Fusion ioDrive2 Duo 1.2TB SLC PCIe | 6320.93 | 3 |
| Huawei ES3000 1.2TB MLC PCIe | 6320.76 | 3 |
| Memblaze PBBlaze3L 2.4TB MLC PCIe x 1 | 6320.7 | 3 |
| Fusion PX600 2.6TB MLC PCIe | 6320.5 | 3 |
| Fusion ioDrive2 1.2TB eMLC PCIe | 6319.61 | 4 |
| Hitachi SSD800MM 400GB eMLC SAS x 4, Windows Storage Spaces, Mirror | 6316.21 | 7 |
| Infortrend ESDS S16F-R2651 Sandisk Optimus Eco 400GB x 16, RAID10 (1) Pool | 6316.08 | 7 |
| Memblaze PBBlaze3L 1.2TB MLC PCIe x 1 | 6315.3 | 7 |
| Virident FlashMAX II 2.2TB MLC PCIe, HP mode | 6314.71 | 8 |
| Fusion ioScale 3.2TB MLC PCIe | 6314.26 | 8 |
| Samsung XS1715 NVMe 1.6TB MLC PCIe | 6311.9 | 10 |
| Samsung 845DC PRO 400GB MLC SATA x 4 | 6309.4 | 12 |
| OCZ ZD-XL 1.6TB (Flash Volume) MLC PCIe | 6309.12 | 12 |
| LSI Nytro WarpDrive 800GB MLC PCIe | 6306.10 | 15 |
| Intel P3700 NVMe 800GB MLC PCIe | 6303.72 | 15 |
| Sandisk Optimus Eco 400GB MLC SAS x 4, Windows Storage Spaces, Mirror | 6300.18 | 19 |
| Micron P420m 1.4TB MLC PCIe | 6299.99 | 19 |
| Hitachi SSD800MM 400GB MLC SAS x 8, RAID10, 10GbE x 2 iSCSI from Supermicro S2012 R2 FileServer | 6298.17 | 21 |
| OCZ Talos 2 400GB MLC SAS x 4, Windows Storage Spaces, Mirror | 6297.17 | 21 |
| Samsung SM843T 480GB MLC SATA x 4 | 6290.4 | 26 |
| Sandisk CloudSpeed 1000E MLC SATA x 4, Windows Storage Spaces, Mirror | 6270.32 | 42 |
| Intel SSD S3500 480GB MLC SATA x 4, Windows Storage Spaces, Mirror | 6265.92 | 46 |
| Samsung PM853T 960GB MLC SATA x 4 | 6262.6 | 48 |
| Samsung SSD 845DC EVO 480GB TLC SATA x 4 | 6244.8 | 62.0 |
| Micron P400m 400GB MLC SATA x 4, Windows Storage Spaces, Mirror | 6231.87 | 72 |
| Micron M500 960GB MLC SATA x 8, RAID50, 10GbE x 2 iSCSI from Supermicro S2012 R2 FileServer | 6222.76 | 79 |
| X-IO ISE 710 FC Hybrid | 6153.12 | 133 |
| Dell EqualLogic PS6210XS iSCSI Hybrid | 6135.52 | 146 |
| Dell EqualLogic PS6110XS iSCSI Hybrid | 5794.10 | 434 |
| Seagate Turbo SSHD 600GB SAS H/W LSI 9286-8e x 16, RAID10 | 4689.80 | 1,523 |
| WD Xe 900GB 10K SAS H/W LSI 9286-8e x 16, RAID10 | 3729.85 | 3,303 |
| Samsung SSD 840 Pro 512GB MLC SATA x 4, Windows Storage Spaces, Mirror | 2616.08 | 6,725 |
Individual SAS / SATA SSD Performance Results (3,000 scale)
| Device | 30k Virtual User Average TPS | 30k Virtual User Average Response Time (ms) |
|---|---|---|
| Toshiba HK3R2 960GB MLC SATA x 1 | 6309.7 | 12 |
| SanDisk CloudSpeed Eco 960GB cMLC SATA x 1 | 6307.0 | 14 |
| OCZ Intrepid 3600 400GB MLC SATA x 1 | 6281.2 | 32 |
| Hitachi SSD800MM 800GB eMLC SAS x 1 | 6277.76 | 36 |
| Intel S3700 800GB eMLC SATA x 1 | 6219.12 | 82 |
| Toshiba PX02SM 800GB eMLC SAS x 1 | 6193.63 | 102 |
| Toshiba PX03SN 800GB eMLC SAS x 1 | 6181.64 | 111 |
| Samsung SSD 840 EVO 1TB MLC SATA x 1 | 2560.33 | 6,974 |
| Micron M500 960GB MLC SATA x 1 | 107.13 | 275,318 |
| Toshiba HG6 512GB MLC SATA x 1 | 96.1 | 307,558 |
Caching Solution Performance Results (1,500 scale)
| Device | 15k Virtual User Average TPS | 15k Virtual User Average Response Time (ms) |
|---|---|---|
| Seagate Turbo SSHD 600GB SAS H/W LSI 9286-8e x 16, RAID10 | 3049.46 | 176 |
| Seagate Savvio 10k.7 600GB SAS H/W LSI 9286-8e x 16, RAID10 | 3027.22 | 212 |
| Seagate Enterprise Capacity SAS 1.2TB H/W LSI 9286-8e x 16, RAID10 | 3001.5 | 255 |
| WD Se 4TB SATA H/W LSI 9286-8e x 16, RAID10 | 2948.79 | 347 |
| WD RE4 4TB SAS H/W LSI 9286-8e x 16, RAID10 | 2629.40 | 964 |
| WD Xe 900GB 10K SAS H/W LSI 9286-8e x 16, RAID10 | 2187.89 | 2,114 |