Discover the Key to Maximizing Oracle® OLTP Per-CPU Licensing

By Doug Rollins - 2018-06-21

Online transaction processing (OLTP) database workloads are some of the most demanding—and most pervasive. In addition to online ordering, OLTP systems are broadly deployed—in banks, retail outlets, communication systems, manufacturing—anywhere users or systems conduct a massive number of short, nearly instant transactions.

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.

We found that the 5200 PRO SSD configuration generated far higher CPM with lower and more consistent latency—as well as better power efficiency—than the baseline configuration to bring more value to OLTP workloads on Oracle Database 12c Enterprise Edition.

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.

Oracle 1

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 Utilization

Oracle Database software is licensed in several ways including per-CPU (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).

Oracle 2

The 5200 PRO configuration shows far higher %CPU utilization for real application work (13X that of the legacy configuration), while spending 90% less of its resources waiting for data or for write completion acknowledgement.

Fast, Consistent Responses

As 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.

Oracle 3

The SSD configuration responds much faster with a mean response time of just 23ms, 95% lower than the 470ms of the legacy configuration. Comparing 90th percentile latency shows similar results.

Oracle 4

Figures 4a and 4b show that the SSD configuation has a 93% lower 90th percentile response time, indicating that 93% of its operations repond much, much faster than the legacy configuration.

Higher Power Efficiency

To evaluate power efficiency, we divided CPM by measured average instant power consumption for each configuration:

Oracle 12

Comparing the legacy configuration’s CPM/watt to the same metric for the 5200 PRO configuration (Figures 5a and 5b) shows a dramatic power efficiency advantage (15X) for the 5200 configuration.

Oracle 6

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 — 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. You can see the full details of our testing here.

How We Tested

Oracle 7To 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.

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

Oracle 8Figure 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.

5200 PRO Configuration Stop Condition: CPM Plateau

Oracle 8Figure 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.

Oracle 10

Additional Configuration Details

Oracle 11

Doug Rollins

Doug Rollins

Doug Rollins is a principal technical marketing engineer for Micron's Storage Business Unit, with a focus on enterprise solid-state drives. He’s an inventor, author, public speaker and photographer. Follow Doug on Twitter: @GreyHairStorage.