logo-micron

Add Bookmark(s)


To:

Email


Bookmark(s) shared successfully!

Please provide at least one email address.

Achieving Over a Million Orders Per Minute with S650DC/S630DC SSDs, Microsoft SQL Server OLTP, and DataON’s Cluster-in-a-Box

Overview

Solution Fast FactsOur S650DC and S630DC 12Gb SAS SSDs offer tremendous capability in both raw I/O performance and low latency. Offered in standard 2.5-inch disk form factors and supporting a dual port SAS interface, these SSDs lead a complete Micron SAS SSD product line designed to meet the varied needs of high speed, low latency flash-based storage platforms.

Microsoft’s SQL Server is broadly deployed across businesses in a wide variety of markets and supports workloads as diverse as Online Transaction Processing (‘OLTP’) to Business Intelligence / Decision Support Systems (‘BI/DSS’).

Because IT teams often need to find the limits of their platforms (for best cost or space efficiency, budgetary or planning reasons), we wanted to explore the limits of a small, all SSD, Cluster-in-a-Box platform using Microsoft Scale Out File Server.  We scaled the number of database nodes attached to the platform from one to two to four to see how well the SSDs could optimize increasingly intense storage IO demands.

OLTP is a random, mixed IO profile (read and write traffic) whose applications demand fast storage IO along with low and consistent database response time.  To ensure we were pushing the S650DC / S630DC tiered storage, we used a large database (about a terabyte – far too large to fit into memory) to ensure substantial SSD IO.  We also scaled the database nodes (from one to four) with each accessing the common S650DC / S630DC tiered storage in a DataON™ Cluster-in-a-Box platform (running Microsoft Scale Out File Server).

We found that the storage platform with the S650DC / S630DC tiered storage smoothly scaled its performance (measured in Orders Per Minute) and maintained excellent latency (both average and quality of service) as we added database servers to the workload.  By the time we reached the fourth database server attachment, the platform was delivering just over a million Orders per Minute (‘OPM’) and showed signs that it could deliver even more.

How We Got More Than One Million Orders Per Minute: Benchmarking

OLTP: Order Per Minute by Node CountWe emulated an order processing system – using Orders per Minute as the performance metric.

In these tests, we used 384 users to access 10,000 warehouses and ensured that the entire database was accessed equally (no ‘hot spots’).  This results in performance that is more consistent with, and similar to optimizations most users would make to their environment.

For each test, we dropped any pre-existing databases, loaded the test database and started a test run.  We used a 15-minute ramp up time followed by a 15-minute test period (during which we captured performance data).  At the end of each run, we repeated the process.

In reviewing the performance measured, note that the performance shown is useful when judging how well the S650DC / S630DC tiered storage supported the increasing database load, but the performance numbers may not directly comparable to a different database or a different configuration. Orders Per Minute based on Number of Database Servers

Low And Consistent Latency

Response Time By Node CountWhile Order Per Minute is an excellent metric for measuring and comparing database throughput, the responsiveness of the database and its consistency are also important.  We also measured the average and 99th percentile latencies for the same test runs.  Figure 2 shows these results.

As the number of database nodes scales from one to two then to four (along the x-axis, from left to right), the average response time remains under 50ms.

We see a similarly shaped trend for the 99.9th percentile latency as we see in the average with lower values at low node count increasing as the node count increases.

Essential Components

These sections discuss some additional details of the S650DC and S630DC SSDs and the test platforms used.

S650DC and S630DC: High Performance Tiered Storage

We designed the S650DC for high speed, mixed IO loads (read and write traffic) and the S630DC for more read focused IO.  These characteristics make the S650DC a great choice for mixed IO loads seen in caching applications and the S630DC a great choice for capacity tier storage1 making a great combination when used in a tiered storage configuration

We used DataON’s CiB-9224 V12 “Cluster-in-a-Box” all in one storage appliances.  We configured the CiB-9224 V12 with a pair of compute nodes for in-node high-availability supporting 12Gb/s SAS connectivity2 and installed four Micron S650DC 800GB SSDs as a cache tier and sixteen S630DC 1.6TB SSDs as the capacity tier (Figure 1).  We installed the four S650DC/800GB cache tier SSDs on the left (green outline) and the sixteen S630DC/1.6TB capacity tier SSDs on the right (blue outline).  We connected the CiB-9224 V12 (2x Xeon-EP E5-2623 v3 CPUs and 256GB DRAM per compute node, 2 nodes total) to a pair of 10GigE switches with two links per compute node (total of 4 links).

The high performance all flash storage was shared via Microsoft’s Scale-Out File Server on Windows Server 2012R2 (and Windows failover clustering).

Robust Database Servers and a Rock-solid Load Generator

We knew that the S650DC / S630DC combination would be extremely capable, so we also knew we would need very robust database servers to keep the SSDs busy. We used identical database nodes (we scaled the tests from one node to two then to four) – 2U / 2 processor servers with E5-2690 v3 CPUs and 256GB DRAM. Each database server booted from a two-drive SSD array and ran Microsoft SQL Server 2014 on Windows Server 2012R2.  We connected each database server via four 10GigE ports.

Because we wanted to push the SSDs, we used a large database (10,000 warehouses or about 1TB).  This helped ensure the majority of the database could not be cached in memory, forcing IO to the SSDs.  Because we were using tiered storage, we also wanted to ensure that the database did not entirely fit in the cache tier.  To help spread the storage IOs across both tiers, we chose a 1:4 ratio of cache tier to capacity tier.  This made about 200GB of the cache tier and about 800GB of the capacity tier available to each database server.

Because of the size of this database, we also had to consider NUMA spinlocks (meaning that only half of the CPUs might be used). To combat this we added trace flags 8015 and 8048 to ignore NUMA node balancing.  This can cause a side effect: higher CPU utilization but at lower performance than could be achieved without these trace flags on smaller databases.

We also knew that with this much database server horsepower, we would have to work to keep them busy.  We equipped our load generator server with four Xeon E7-4850 v2 processors and 768GB of DRAM.  This server’s only role was to generate database loading.

Conclusion

In this Brief, we wanted to push our S650DC and S630DC 12Gb/s SAS SSDs with an OLTP workload in Microsoft SQL Server to see what our customers could expect in similar conditions.  We explored the performance edges of these SSDs in a tiered storage configuration with a DataON CiB-9224 V12 Cluster-in-a-Box storage platform (four S650DC cache tier and sixteen S630DC capacity tier SSDs in the CiB-9224) with Scale-out File Server on Server 2012R2. We started with one database node, scaled to two, and finally to four.

We found that Orders Per Minute (a good measure of database throughput) scaled as we added database nodes, while maintaining low and consistent latency.

This combination of S650DC and S630DC SSDs smoothly scaled its performance as we added database servers to the workload.  By the time we reached the fourth database server attachment, the platform was delivering just over a million Orders per Minute with less than 50ms average latency.


1 Click here for additional details on the S600DC family of 12Gb/s SAS SSDs.
2 Click here for more information on the DataON CiB-9224 V12.

Products

S600DC SSD

S600DC

Modernize your IT infrastructure with our enterprise-hardened, dependable S600DC series SSDs, ensuring uninterrupted delivery of your data.

View S600DC Products

Resources

Blog

TCO. ROI. Data center managers are ultimately looking for new technology that can help them tackle the huge explosion of data and mange it more effectively. Approximately 2.5 quintillion bytes of data are generated every day. Sensor, social, transactional, GPS, medical data,...

Technical Brief

This technical brief compares the performance advantages of a pair of Micron® 1.6TB S650DC SAS SSDs (RAID 1) over a legacy HDD array with 1.8TB of SAS HDDs (12 drives, 300GB each, RAID 10).

Technical Brief

IT budgets shrink year on year, but demand continues to rise. As we generate more data, users and applications demand better access. These unrelenting challenges drive new tactics, new solutions and new answers. Because legacy platforms are no longer capable of meeting demands...

Technical Brief

Ramp up your web serving and OLTP in MySQL Community Edition with the S650DC — 100X more transactions per second and 130X lower average response times than legacy HDD-based arrays (70% read, 128 users) — the upgrade is easy!

Sign up for updates

Get Updates From Micron Storage

Subscribe