Solid state drives (SSDs) have influenced the design of storage infrastructure for databases in a variety of ways:
- SSDs have been added as a “tier” within traditional storage area network (SAN) and network-attached storage (NAS) environments to help accelerate hard disk drives (HDDs) performance that comprised these solutions.
- PCIe SSDs have been added as a high-performance “tier 0” on database servers and have been tiered back to the external storage using server-side software.
- All-SSD external storage solutions (also known as all-flash arrays) have become popular for high-performance storage infrastructure.
- Software-defined storage (SDS) and hyper-converged infrastructure (HCI) solutions incorporating SSDs are emerging as well.
In this blog, I give a broad overview of a new category of storage infrastructure that is tightly coupled with database application features: PCIe SSDs on local servers with application-level data mirroring protection across servers. And, I walk through some testing that Micron performed to validate the value of this combo for databases.
Value of Data Mirroring + Local SSDs
Synchronous data mirroring (or data replication) can help protect data from a single server failure while PCIe SSDs provide very high IOPS (100,000s of IOPS) and very low disk latency (microseconds). Database mirroring features combined with local PCIe SSDs can help you build a high-performance, highly available and lower-cost database infrastructure. We recently did some testing to help characterize the performance opportunity of PCIe SSD technology coupled with data mirroring protection.
We used a database server configured with two 700GB, 2.5-inch Micron P420m PCIe SSDs running Microsoft’s SQL Server 2014. To protect the data from a single drive failure, we used Windows Server 2012 R2 Storage Spaces across the two SSDs locally on the server, and we created mirror virtual disks on the two-drive storage pool to host database data. The figure below shows the Storage Spaces layout:
Establishing Baseline Performance
We executed a simulated online transaction processing (OLTP)-like workload against a single server hosting a single database —measuring database transaction throughput and server system utilization. The workload periodically reached peak CPU utilization on a two-socket, 12-core Intel server with 256GB RAM and never reached peak I/O utilization of our PCIe SSDs. We used the database transaction throughput as our baseline number for the single database server.
Setting Up Synchronous Commit Mode
Next, we built a highly available database configuration using Microsoft SQL Server 2014 AlwaysOn Availability Groups. We built an identical second server with the same set of drives using Storage Spaces and configured it as a secondary database replica server.
- The first server replicated all database transaction log content (write transactions) to the secondary server, waited for acknowledgement, and then committed and responded back to the database client on transaction completion.
- The secondary server acknowledged the transaction log data it received from the primary server only after committing the data locally onto its drives.
In this mode of operation, called synchronous commit mode, both servers maintain the data in the same consistent state, so if the primary server fails, the secondary server can continue to serve clients without data loss.
The network interconnect that we used for mirroring the two servers was a pair of load-balanced 10 GbE SFP+ Twinax Ethernet connections. The OLTP workload incurred additional latency for each write transaction due to the network transport time, secondary server processing and secondary server disk commitment. This additional latency impacted the total database transaction throughput from the primary server by only about 15 percent (see chart below). However, the total end-to-end application response times at the client were still in the very acceptable millisecond (sub-second!) range.
Compared to external storage solutions, PCIe SSDs on local servers with application-level data mirroring protection is an attractive option to enable high-performance, highly available and lower-cost databases. Additional SATA SSDs, such as Micron’s M510DC, can be added to the server for capacity and tiered using Windows Storage Spaces. The use of PCIe SSDs boosted I/O performance and relieved performance bottlenecks typical with traditional storage. What this means for your data center is that by augmenting your existing storage solutions with SSDs — or by providing alternate high-performance, highly available solutions — you can enhance database storage infrastructure and see improved performance of your business applications.