SQLIO is one of the tools provided by the Microsoft SQL team to test the I/O capacity of a storage system. 6TB of working set was configured with SQLIO on the vSphere 5.5 U2 cluster. The database simulation consisted of 100% random 8KB reads and then 100% random 8KB writes. The transaction log simulation consisted of 8KB sequential writes. Utilizing only 4 servers populated with 4 hard disks and a single SSD each, 238k 8KB random read IOPS was generated. The performance of the Springpath Data Platform is demonstrated in the following figures. See the Springpath Whitepaper for deeper dive on the Springpath Data Platform.
The 4 node cluster achieved 238k 8KB random read IOPS and 83k 8KB random write IOPS. Sequential writes logged a total throughput of 622MB/sec on the cluster. Read latency was in the microseconds (~500us) while the write latency average was 2ms. These are phenomenal numbers where most competitors are in the 100k IO range for 4 nodes and most traditional SANs with a total of 16 SATA drives wouldn’t break one thousand random read IOPS at 8KB even with a flash cache.
Springpath defaults such as thin provisioning, deduplication, and compression were enabled and did not impact performance! Can I eek out 5-10% more throughput if I use PVSCSI in the VM and separate each database and transaction log file? Maybe. The point of these tests is to show how reduced complexity is a winner and may be good enough for most use cases. There were no custom tunes. I simply deployed a couple of Windows 2012R2 VMs on the cluster and ran the test. I am working on the best practices for the power hungry benchmark crowd now. Stay tuned!
Springpath also reduces storage complexity by providing a single datastore that is easily provisioned in the vSphere Web client, with Data fully distributed across disks in all the servers in the cluster to leverage all controller resources and provide high availability. Standard VMware features such as vMotion, replication, Distributed Resource Scheduler (DRS), and high availability (HA) are all fully supported and help to increase database availability. Data deduplication and compression is in-line with no measurable overhead. Thin provisioning is enabled by default allocating space as it in consumed, preventing a LUN from holding whitespace captive. Springpath native snapshots and ReadyClones are pointer-based and are invoked directly from the Springpath plug-in in the VMware Web Client. Microsoft Volume Shadow Copy Service backup of SQL databases and transaction logs require a VSS requestor, such as Windows Server Backup. In a SQL Server environment, ReadyClones enable quick cloning of production SQL servers for test and development on an isolated network. The ability to quickly unit test database changes or deploy a hotfix for SQL and Windows to ensure functionality is not broken can save time.
In closing, the IOPS and latency numbers are impressive, and getting better with each new release of the software. My next SQL steps involve testing new performance features shipping in the next release of the Springpath Data Platform as well as adding additional database tools into the test matrix including SQLIOSim and HammerDB.
Appendix – Hardware Details:
Each server node was equipped with 4-1TB hard disk drives, 1-480GB SSD, 256GB RAM, and Intel Xeon E5 2650 family Dual socket (10 cores per socket @2.4 GHz).
Each VM was provisioned with 16GB of RAM and 8 vCPUs. The storage controller inside the VM used the default LSI Logic SAS SCSI controller with SCSI Bus Sharing set to None. CPU/Memory shares on the VM were set to normal with Limit set to unlimited. The windows power profile was set to High Performance. This is my default workload VM whereas SQLIO is not CPU or memory intensive and the requirements could have been reduced 87.5% without changing the results.