OLTP Performance on an All Flash NVMe + SATA Configuration – Storage Spaces Direct Cluster with 144 SQL Server VMs

Previous blogs in our series on Windows Server* 2016 with Storage Spaces Direct (S2D) were focused on IOPS, providing you an overview of performance with three Intel-Microsoft recommended configurations.

Our analysis demonstrated that a Hyper-converged throughput/capacity optimized NVM Express & SATA SSD storage configuration can deliver up to 1.54 million IOPS [4K 100% Random Reads] with low latencies – refer to the earlier blog. This is a good performance story, and we wanted to add to this by showing an end-to-end Data Center scenario relevant to current production enterprise environments.

Using Microsoft SQL Server 2016, we are now able to demonstrate leading edge OLTP performance on Windows Server 2016 using Storage Spaces Direct. Microsoft SQL Server* with its breakthrough scalability, highly secure database features, advanced analytics and consistent experience both for on premise and cloud deployments creates a very compelling deployment story when integrated with Windows Server 2016 and Storage Spaces Direct in a hyper-converged infrastructure with the recommended all flash local storage of NVMe and SATA SSDs.

To effectively demonstrate the capabilities of the S2D cluster, our configuration was designed to represent a typical OLTP workload.  We employed a synthetic database schema designed to measure the performance with a mix of transactions that were IO intensive, CPU compute intensive, sensitive to IO latency & memory capacity. Microsoft’s Cloud Database Performance group enabled us with a framework to simulate the OLTP workload on a Storage Spaces environment and generate results.

System Configuration

The Throughput/Capacity Optimized NVMe+SSD configuration setup consisted of four 2U Intel® Server Systems equipped with Intel® Server Board S2600WTT. The configuration for each server included: 

Processors:
2 x Intel® Xeon® processor E5-2695 v4 (45M Cache, 2.1GHz, 18 cores, 120W)

Storage:
Cache Tier: 4 x 800GB Intel® SSD DC P3700 Series
Capacity Tier: 20 x 1.6TB 2.5” SATA Intel® SSD DC S3610 Series

Network:
1 x 10GbE Dual-port Chelsio* T520 adapter

Memory:
256 GB DDR4 @ 2133MHz

Operating System Software:
Windows Server* 2016 RTM [build 14393]

Application Software:
Microsoft SQL Server* 2016

2

Total storage capacity of this clustered configuration is 128 TB, and with three-way mirroring we have 42.67 TB total available space (128TB/3 = 42.67 TB).

We deployed 144 Windows Server 2016 VMs across four S2D nodes (36 VMs per node) and each VM has 2 virtual processors and 3.5 GB of virtual memory assigned.  Each VM also has a 60GB OS VHD and a 150GB Database VHD attached.  The SQL database is sized based on a “Scale Factor” [SF] and we built a 4,000 SF database such that:

SQL Database Size = 104GB

SQL Log Size = 10GB

templog/tempdb files size = 20GB

The size of the VHDs were chosen such that ~70% of the total storage capacity was utilized, ensuring database/content does not fit in the S2D cache.

The backend S2D storage was configured as a 3-Way mirror such that NVMe SSDs were used as a Write-Only cache and the SATA SSDs were used as capacity devices.  SQL Server engine was configured to use 90% of max server memory.  SQL client connections used TCP/IP.

Methodology

Using two physical driver machines each with two driver engines as load generators to drive the database transactions, we simulated 100 to 250 independent user streams of user activity per SQL server Database instance inside each SQL VM. With 36 SQL VMs per Server node, that brought the total number of SQL VMs sustaining a constant load to 144. We ran this workload consistently for 1 hour for all the VMs.

The Figure below shows the system under test with 4 Hyper-converged nodes each with a dedicated set of Intel® NVM Express and Intel® SATA SSDs. All 144 SQL VMs were configured such that SQL Server had access to 3.1 GB of memory. Microsoft SQL Server managed and consumed all of the memory that was needed to generate a sustained throughput that represented current production environments hosting typical SQL Server configurations.

Capture

OLTP Workload Setup

Results

With 36 SQL VMs per node, for a total of 144 SQL VMs and SQL Databases hosted on the NVMe+SSD Storage Spaces Direct pool, we were able to achieve a maximum sustained throughput of 28,223 Transactions per Second [TPS].  This throughput was achievable with 28,800 independent streams of users exercising user activity simulating OLTP transactions. This amounts to 196 TPS per SQL VM!

The chart and table below highlights per Node TPS and CPU utilization as we scaled the user thread streams.

Capture2

User Thread Streams Scaling: TPS vs. % CPU Utilization

CPU utilization reaches peak capacity at this performance level. In addition to the throughput, the framework also provides us an opportunity to record database transaction response times for each transaction type. For the maximum throughput of 28,233 TPS, the average 95th percentile response time for each transaction type measured per SQL VM is under 2 sec.

Production environments, however, will not be fully constrained on compute resources all the time and so for a lower CPU utilized configuration, we eased the number of simulated users to about 180 per SQL VM and were able to achieve 25,920 TPS. This resulted in an average CPU utilization of about 72% per VM across all 4 nodes.  The average 95th percentile response time for each transaction type reduced to under 1 sec which is great for this kind of a configuration.

What we observe is very consistent throughput between each server node in the cluster for any given user thread stream as we scale the workload to higher utilization levels. An enterprise workload could be at any of the above utilizations at a given point in time. This chart highlights the consistency as well as scalability of a Storage Spaces Direct software stack when a highly scalable Microsoft SQL Server Database application is deployed using the latest Intel platform and storage technologies.

Capture3

Using Microsoft Perfmon, we collected relevant Clustered Shared Volume File System (CSVFS) metrics.  Clustered Share Volume (CSV) is a distributed file access solution that provide multiple nodes in the cluster with simultaneous access to the same file system.  The chart below highlights total CSVFS Read and Write IOPs and CPU utilization as we experimented with thread counts.

Capture4

User Thread Streams Scaling: CSVFS IOPs vs % CPU Utilization

Average CSVFS Read latency measured for 200 and 180 thread count across all 4 nodes is ~0.63ms and average CSVFS Write latency measured is ~1.61ms.

 Conclusion

  • Using Microsoft SQL Server 2016, we are able to simulate greater than 28,000 independent user streams generating an aggregate 28,233 OLTP transactions per second (TPS)
  • The results demonstrate the great scalability of Windows Server 2016 Storage Spaces Direct across the 4 hyper-converged nodes as we scale the number of user streams from 100 to 250 per VM
  • The Throughput/Capacity Optimized NVMe+SSD hyper-converged configuration with Intel® NVM Express SSDs acting as a caching tier and Intel SATA SSDs as a capacity tier provides a blend of high performance and storage capacity which works well for OLTP applications/workloads requiring lower latency for faster decision support and general virtualization.

 Disclaimers

Results have been estimated based on internal Intel analysis and are provided for informational purposes only. Any difference in system hardware or software design or configuration may affect actual performance. Software and workloads used in performance tests may have been optimized for performance only on Intel microprocessors. Performance tests, such as HammerDB, are measured using specific computer systems, components, software, operations and functions. Any change to any of those factors may cause the results to vary. You should consult other information and performance tests to assist you in fully evaluating your contemplated purchases, including the performance of that product when combined with other products. Source: Internal Testing.* Other names and brands names may be claimed as the property of others.