A recent DB-Engines ranking
shows that Microsoft®
is one of the most popular database management systems in the world. Top database platforms manage high-capacity, high-bandwidth transaction-based applications for online transaction processing (OLTP) workloads like order entry and fulfillment, real-time data analysis, e-commerce and real-time order management systems. These systems give fast access to mission-critical data, enabling transaction processing with ultra-low and consistent latency, where access delays can be extremely costly.
This technical brief discusses how we used standardized OLTP performance metrics and a data set that exceeded available system memory (to test storage system I/O) to compare new orders per minute (NOPM), database average response time and response time consistency using SQL Server with Enterprise SATA SSDs. (We also included a legacy HDD configuration for reference).
We used the same base hardware (server, CPUs and DRAM) with all three storage configurations:
- SATA Configuration 1: 4x 1.9TB 5200 PRO, RAID 10
- SATA Configuration 2: 8x 1.9TB 5200 PRO, RAID 10
- Legacy Configuration: 16x 300GB 15K RPM HDD configured RAID 10 (baseline configuration, included for comparison)
We found that the 5200 PRO SSDs generated far greater business throughput (NOPM) than the baseline configuration to bring more value to OLTP workloads on a Microsoft SQL Server.
- Complete more transactions with 5200 PRO SATA SSDs — more orders, more fulfillment, more for your bottom line
- Higher performance and data density enable significant cost saving in many data centers
- Compared to the legacy 16x HDD configuration:
- 4x 5200 PRO SSDs supported 11X more new orders per minute
- 8x 5200 PRO SSDs supported 12X more new orders per minute
- Databases hosted on 5200 PRO SSDs response times were 96% lower and much more consistent
More Orders per Minute Brings More Value
SSDs are a mainstay of high-performance, low-latency IT systems. High-capacity, high-performance Enterprise SATA SSDs drive those systems farther and faster, processing more data and bringing more value.
More and more, OLTP platforms are moving to SSDs, and the differences between their capabilities and what we used to think of as a performance HDD configuration are greater than ever, with the legacy configuration’s 15K RPM HDDs being painfully slow in camparison. In OLTP systems, additional orders processed can represent more fulfillment or more detailed analysis — all bringing more value.
Figure 1: Relative Business Throughput (NOPM) for Each Configuration
The magnitude of the difference between Enterprise SATA SSD platforms and legacy standards is evident in Figure 1, which shows each configuration’s NOPM relative to the baseline, legacy conifiguration. Figure 1 shows values measured at a system load just before the test reached a stop condition. (See the How We Tested section for stop condition details.)
Faster, More Consistent Responses
Many applications require high NOPM while time-sensitive applications may require quick, consistent database response (low and consistent latency). SSDs deliver here as well.
Figure 2a: Database Responsiveness
Figure 2b: Response Consistency
As shown in Figures 2a and 2b below, we calculated and compared the mean latency and the 99.9th percentile latency (a good indicator of latency consistency) at system load just before the test reached a stop condition (see the How We Tested section for stop condition details) for each of the three storage configurations. We used the same metrics, database and test conditions for each configuration.
Both SSD configurations show low mean transaction times compared to the baseline configuration. Figure 2a shows that the 4x 5200 and 8x 5200 configurations measured 96% lower latency than the baseline.
Figure 2b shows that both of the SSD configuation response times were more consistent than the baseline configuration (indicated by a lower 99.9% transaction response time).
The two comparisons indicate that both SSD configurations respond more quickly and more consistently than the baseline configuration. The legacy configuration shows the opposite — much higher mean transaction response time that is far less consistent.
The Bottom Line
Mission-critical data can’t wait. Access delays or inconsistency can be extremely costly. Using Enterprise SATA SSDs like the 5200 PRO can enable fast transaction processing and fast, consistent response times.
In our testing, these SSD configurations demonstrated tremendous benefits and new capabilities for one of the most popular database management systems and most challenging workloads — Microsoft SQL Server and OLTP. Supporting far greater NOPM with lower and more consistent latency means more orders and more transactions completed faster and more consistently.
Learn more about our 5200 SATA SSD family and their transformative effect on your business at micron.com and stay up to date on what’s trending in storage by reading Micron’s Storage Blog
and following us on Twitter @MicronStorage.
How We Tested
To ensure a fair assessment of the expected maximum NOPM of each configuration, we took a configuration-specific approach. We measured each configuration’s NOPM at the maximum load the platform could reasonably support, as opposed to comparing NOPM and latency at an arbitrary load.
Table 1: Stop conditions1,2
Prior to testing, we established stop conditions (Tables 1 and 2). As we tested, we increased the load until the test reached a stop condition, after which we stopped increasing the load and used the NOPM and latency values recorded when we reached the stop condition.
We set the 90th percentile transaction response time to the values in Table 2, which each reflect common tolerance limits.
Table 2: Threshold limits
- We set the stop condition for CPU utilization at 80%. Many IT organizations plan for a platform upgrade when CPU utilization reaches 50% and implement that plan when it reaches 80%.
- We sized the data set to ensure it was large enough to ensure storage I/O (data set size about 2X the memory size) but did not occupy more than 80% storage capacity.
Determining Maximum Load by Configuration
This section shows the test condition(s) that established each configuration’s maximum load.
Legacy Configuration Stop Condition: Average Log WRITE Latency
Figure 3 shows the legacy configuration’s average log disk (partition) WRITE latency by load. The legacy configuration’s stop condition is shown in red. At this point, the average log disk (partition) WRITE latency exceeds our 5ms stop condition.
4x SATA SSD Configuration Stop Condition: Average Log WRITE Latency
Figure 3: Legacy Configuration Stop Condition
Figure 4 shows the 4x SATA configuration’s average log disk (partition) WRITE latency by load. This configuration’s stop condition is also shown in red. At this point, the average log disk (partition) WRITE latency exceeds our 5ms stop condition.
8x SATA SSD Configuration Stop Condition: Average Log WRITE Latency
Figure 4: 4x SATA SSD Stop Condition
Figure 5: 8x SATA SSD Stop Condition
Figure 5 shows the 8x SATA SSD configuration’s average log disk (partition) WRITE latency by load. This configuration’s stop condition is also shown in red. At this point, the average log disk (partition) WRITE latency exceeds our 5ms stop condition.
Table 3 shows the database server hardware configuration details.
Table 3: Hardware Configuration
To download a pdf version of this Technical Brief, click here.