Microsoft SQL Server Performance, Consistency at Peak, and The Art of Possible with Datrium
HammerDB is a free database load testing and benchmarking tool for databases. For this HammerDB test, I have chosen the TPC-C schema to build a roughly 500 GB database (to start with) of 200 warehouses and used 100 concurrent users to drive the database transactions with no think-time enabled.
TPC-C involves a mix of five concurrent transactions of different types and complexity either executed on-line or queued for deferred execution. The database consists of nine types of tables with a wide range of records and population sizes. TPC-C is measured in transactions per minute (tpmC). While the benchmark portrays the activity of a wholesale supplier, TPC-C is not limited to the activity of any particular business segment, but rather represents an industry that must manage, sell, or distribute a product or service.
TPC-C simulates online transaction processing (OLTP) workloads that are typically identified by a database receiving both requests for data and multiple changes to the data from user transactions. HammerDB simulated roughly a 70:30 split of read/write transactions at 8KB block size in most of the test runs.
The virtual machine was configured with 16 CPU(s) and 64 GB RAM. The server is a PowerEdge R930 – E7-8890 v4 @ 2.20GHz / 2016 model with 8 x 1.9TB SATA Samsung GC57 (MZ7LM240HMHQ0D3/2016). The Datrium Datanode F12X2.
I'm basically using a 4-year-old hardware configuration.
Performance and Consistency at Peak
The figure below demonstrates the performance achieved during a steady-state run with the resources made available through the virtual machine configuration. We define steady-state when data services are turned on (Datrium Default); and that includes erasure coding (three-way replication), compression, deduplication, space reclamation, and snapshots.
The system achieved over 1.07 Million transactions per minute (TPM) with HammerDB TPC-C workload in steady-state. Measured by the hypervisor, the maximum read latency was 1.3ms and the maximum write latency was 1.1ms, during a nearly 100% write burst.
While performance is important for business-critical applications, consistency during high peak workloads is as important, if not more important, and the figure below demonstrates that during the HammerDB workload Datrium was able to maintain consistent IO and throughput.
Bottlenecks and The Art of Possible
The particular workload above was bottlenecked by the amount of CPU available to the virtual machine and Microsoft SQL Server. The benchmark in the next section demonstrates the art of possible when bottlenecks are eliminated.
Increasing the virtual machine CPU count from 16 to 64 the number of transactions per minute (TPM) more than doubled, while Datrium is also able to maintain consistent IO and throughput.
From a performance perspective, even this workload isn’t really scratching the surface of what Datrium is capable of producing. In average terms, Datrium is capable of producing 1500 MiB/s per host and this HammerDB workload produced an average of 430 MiB/s.
Further optimizations are also possible, such as optimizing the host hardware in use, including making use of faster processors or NVMe devices. However, most enterprise-grade workloads will likely fall well below what Datrium is capable of delivering using older CPU generations and just a couple of older flash drives.
You you cans see, IT and SQL administrators can take full advantage of the performance gains of the Datrium solution, deploy production databases and leverage the advances brought by newer CPUs, local flash storage access, and data protection management to meet the needs of today’s modern datacenters.
This and other gems, including a Microsoft SQL Server with HammerDB Disaster Recovery to VMware Cloud on AWS, are topics in an upcoming Datrium paper.
This article was first published by Andre Leibovici (@andreleibovici) at myvirtualcloud.net