When data sets are too large to fit into memory, storage performance is paramount. SSD storage like the Micron 5200 PRO enterprise SATA SSD enables fast access to immense, mission-critical data sets, enabling transaction processing with ultra-low and consistent latency where access delays can be extremely costly.
Of the many relational database management systems (RDBMS) in use, a March 2018 DB-Engines ranking showed Oracle Database as the most popular.
This technical brief discusses how we measured Oracle Database commits per minute (CPM) with enterprise SATA SSDs using standardized OLTP performance metrics and a data set that exceeded available system memory to test storage system I/O. We also included a legacy HDD configuration for reference.
We used the same base hardware (server, CPUs and DRAM) with both storage configurations:
- 5200 PRO Configuration: 8x 1.9TB 5200 PRO SSD, Oracle Automatic Storage Management (ASM) Normal Redundancy.
- Legacy Configuration: 16x 300GB 15K RPM HDD configured RAID 10 (Oracle ASM External Redundancy). This configuration is included for comparison.
Fast FactsMicron® 5200 PRO SATA SSDs deliver with Oracle and OLTP:
- Enormous commits per minute, faster and more consistent responses
- Lower overall CPU utilization with higher CPU-based licensing value1
- 18X higher performance1
- 95% lower average response time
- 93% lower 90th percentile response time
- 15X better power efficiency2
(compared to a legacy configuration)
- In this document, “performance” and “database commits per minute (CPM)” are used interchangeably. Higher CPU-based licensing value defined as more CPU resources (expressed as %CPU utilization) used for real application work for platforms with the same number of CPUs installed and the same licensing model. Actual results depend on licensing model, server configuration and other factors.
- Power efficiency = CPM/watts consumed (see details later in this document).
5200 PRO Drives 18X More Commits per Minute (1.4 Million)Enterprise SSDs like the 5200 PRO 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.
This is especially true for high-performance, IO-intensive workloads like OLTP.
As more OLTP platforms have moved to SSDs, the reasons for doing so have become very clear: the differences between SSD capabilities and what we used to think of as a performance legacy configuration are greater than ever, with legacy configurations (like a 15K RPM HDD setup) being painfully slow in camparison. With Oracle Database OLTP, more commits can represent more value.
Figure 1a: 5200 PRO Configuration Commits per Minute
Figure 1b: Legacy Configuration Commits per Minute
The magnitude of the difference between enterprise SATA SSDs and a legacy configuration is evident in Figures 1a and 1b, which show each configuration’s CPM: SSDs delivered 1.4 million CPM, or about 18X what the legacy configuration did.
(Figures 1a and 1b show values measured at a system load just before the test reached a stop condition. See the How We Tested section for stop condition details.)
More Value from Per-CPU Oracle Licensing: CPU UtilizationOracle Database software is licensed in several ways including per-CPU3 (details on licensing are beyond the scope of this document). When licensing Oracle Database software per CPU, getting more results—more real application work per CPU—is highly desirable.
Per CPU, one can evaluate how the storage system affects this by measuring the percentage of CPU resources being used (%CPU utilization) for real application work and comparing it to the percentage spent waiting for data or write completion (storage system response).
Figures 2a and 2b compare %CPU utilization by task type for each configuration. In each figure, task types are shown in four categories by color: real application work (in green—the %CPU used to generate real workload value), kernel operations (purple), IO Wait (in dark grey, %CPU spent waiting on data or write complete acknowledgement – waiting for storage to respond) and unused (in light grey, indicating that the CPU was not the bottleneck for this test).
Figure 2a – 5200 PRO Configuration %CPU Utilization
Figure 2b – Legacy Configuration %CPU Utilization
Fast, Consistent ResponsesAs shown in Figures 3a, 3b, 4a and 4b below, we calculated and compared the mean response time (latency) and the 90th percentile response time (a good indicator of latency consistency) at system load just before the test reached a stop condition for both the storage configurations (see the How We Tested section for stop condition details). We used the same metrics, database and test parameters for each configuration.
Figure 3a: 5200 PRO Configuration Mean Latency
Figure 3a: 5200 PRO Configuration Mean Latency
Figure 4a: 5200 PRO Configuration 90th Percentile Latency
Figure 4b: Legacy Configuration 90th Percentile Latency
Higher Power EfficiencyTo evaluate power efficiency, we divided CPM by measured average instant power consumption for each configuration:
Figure 5a: 5200 PRO Configuration Power Efficiency
Figure 5b: Legacy Configuration Power Efficiency
The Bottom LineMission-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 — Oracle Database Server and OLTP. Supporting far greater CPM with lower and more consistent latency means more orders and more transactions completed faster and more consistently.
How We TestedTo ensure a fair assessment of the expected maximum NOPM CPM of each configuration, we took a configuration-specific approach. We measured each configuration’s CPM, latency and power efficiency at the maximum load the platform could reasonably support, as opposed to comparing them at an arbitrary load.
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 CPM, latency and power 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 1: Stop Conditions4,5
Table 2: Threshold Limits
Determining Maximum Load by Configuration
For each drive configuration, we performed a one-run sweep across a range of loading values and Oracle SGA memory settings until we hit one or more stop conditions. After finding the rough value, we executed a large number of tests on the configurations around the stop condition to measure maximum performance. This section shows the test condition(s) that established each configuration’s maximum load. The specific loading conditions are seeded based on past observations and iterated based on live results.
Legacy Configuration Stop Condition: CPM Plateau
Figure 6 shows the legacy configuration’s CPM started to plateau at the system load shown on the far right.
As we increased system load, we observed peak performance as shown at far right. Above this loading level, we had to lower the SGA, which resulted in lower CPM.
Neither CPU utilization nor response times reached a stop condition.
Figure 6: Legacy Configuration Stop Condition
- 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.
5200 PRO Configuration Stop Condition: CPM Plateau
Figure 7 shows that the 5200 PRO configuration’s CPM plateaus at the system load shown on the far right.
As we increased system load, CPM started to decrease.
Oracle Server Settings
Table 3 shows specific Oracle Server configuration settings used for all testing.
The settings yielded the test results shown; other settings may yield different results.
Figure 7: 5200 PRO Stop Condition
Table 3: Oracle Server Settings
Additional Configuration Details
Table 4: Hardware Configuration
To download a pdf version of this Technical Brief, click here