In the last three blogs (Part I, Part II and Part III), we were talking about the Oracle Ready Solutions running on diverse storage platforms ranging from PowerMax (formerly called VMAX) to XtremIO. In the previous blogs we highlighted the scalability, performance, and effectiveness of Oracle solutions on diverse storage platforms. In this blog, we demonstrate that Oracle solutions not only run on multiple platforms, but also effectively support multiple and diverse workloads that emanate from multiple RDBMS databases like Oracle and SQL Server.
Mixed database workloads combine online transaction processing (OLTP), decision support systems (DSS), and snapshot OLTP (Snap OLTP) workloads that have historically been difficult to manage on the same infrastructure, while maintaining consistent performance and higher scalability. Heterogeneous databases (like Oracle and SQL Server) with different functionality and architectures feature competing data center demands and requirements including CPU usage, memory utilization, and storage and networking requirements.
The biggest challenge in operating these types of heterogeneous workloads and databases is that the storage system cannot be tuned for one workload or the other; instead, it must be balanced to support both database loads at performance levels that meet service level agreements (SLAs).
The PowerMax 2000 with NVM Express (NVMe) flash drives introduces improvements in performance and parallelism that provide an ideal match for mixed database workloads. NVMe flash drives offer increased speed and the ability to service more requests in parallel. The storage system (PowerMax 2000) combined with the robust compute layer (PowerEdge MX7000 modular chassis) forma lethal combination which drive mixed workloads toward improved, predictable, and consistent performance with greater IOPS, scalability, management consolidation, lower response time , and decreased latency and risk.
Figure 1. Architecture of mixed workloads
Now, let’s dive deep and understand the inside architecture of this Oracle solution (depicted in Figure 1) before we introduce the outstanding bench-marking numbers attained during the testing.
In the above figure, two different databases (Sql Server and Oracle) are running in parallel within the DellEMC PowerEdge servers using the PowerMax 2000 storage. In this solution architecture, we used the Dell EMC PowerEdge MX7000 modular chassis, which provides a high-performance data center infrastructure, for both compute and network resources. The compute or server resources for this reference architecture are:
The software stack for this solution is described in the figure below.
Figure 2. The software stack for DellEMC mixed workloads solution
The details of the entire architectural design is discussed in the validation guide here.
The Dell EMC engineers have performed three use cases to understand the efficacy of this solution. The use cases are
After running several in-house stress tests with bench-marking tools like TPC-C and TPC-H, the Dell EMC engineers have achieved some spectacular results in terms of CPU utilization, transactions per minute (TPM), IOPS, NOPM, storage IOPS ,latency and throughput. The results are displayed in this document in the form of graphs. I will include a detailed analysis of the different bench-marking numbers in my next blog.
The IOPS performance parameter is very important because it shows how fast transaction data can be retrieved from storage by the compute layer. Figure 3 shows that during mixed workload stress-testing, more than 100,000 IOPS were generated with less than 1 milli-second of latency (depicted in figure 4) using a small 24 NVMe flash drive configuration. In Figure 3 we also observe that 3 different workloads (OLTP, DSS, and snapshot OLTP) ran in parallel and generated remarkable performance numbers (Use Case 3).
Figure 3. IOPS performance numbers of Oracle Solutions with mixed workloads
Figure 4. Consolidated IOPS and latency numbers for the Use case 3
As shown in Figure 5, IOPS performance remained consistent between the baseline OLTP test and the test with all workloads running. When all workloads were running in parallel, IOPS remained within 4 percent of the baseline. Average read latency for all the databases remained under 1 ms with one exception: Snapshot OLTP SQL Server 1. Because this database was simulating a test and development workload, the slightly higher latency was not significant in evaluating overall performance.
Figure 5. Consolidated IOPS, latency, and throughput numbers for use case 3
The other salient feature of this mixed workload solution’s performance is that the average write latency for all databases remained under 1 ms. Even with eight databases with mixed workloads running in parallel, most of the write latencies were under .31 ms. The exception was the OLTP Oracle database with .75 ms average writes for logs (still under 1 ms). This characteristic can be observed in Figure 6 below.
Figure 6. Average write latency of mixed workloads
Another important performance parameter is the throughput for the decision support system as depicted in Figure 5 and in Figure 7 below. As shown in both figures, the throughput improves or remains constant with the addition of new workloads/applications/databases. The greater the number of databases, the more IOPS on the storage array and the greater the latency. This trade off among throughput, IOPS, and latency happens over time as more complex workloads are run. Initially the storage performance is good, and databases have low latency times. With time, more applications are added to the array and the trade-off is weighted toward IOPS, which adversely impacts database and application performance. The throughput numbers are not greatly impacted by the addition of workloads. On the contrary, throughput actually improves for the SQL Server databases 1 and 2, as shown in Figure 7.
Figure 7. Average throughput of Decision Support workload of Oracle Solutions with mixed workloads
I will describe and analyze some other performance numbers like TPM, NOPM, CPU, memory utilization, etc. in my next blog.
The Dell EMC Ready Solution for Oracle is an effective solution that has been internally tested and proven to offer greater IOPS, transactions per second (TPS), bandwidth, scalability, consolidation agility, operational efficiencies and resiliency in a mixed workload solution for bare metal and virtualized environments. This mixed database/mixed workload system, which uses two PowerEdge MX840c servers and an entry-level PowerMax 2000 storage array with 24 NVMe flash drives, is a powerful and cost-effective solution. The PowerMax 2000 storage array delivered over generated 106,139 IOPS using a small 24 NVMe flash drive configuration with latencies under 1 ms. The PowerMax configuration can scale to 96 NVMe flash drives, which is four times larger than the configuration that we used in our testing.
When the IOPS or the workload increases, the throughput also increases. In our findings, we observed that throughput improved with the scaled complexity of the mixed workloads. For example, when the IOPS (OLTP + DSS) on a SQL Server database was 6,720, the throughput was 625 MB/s, and when the IOPS (OLTP + DSS + SNAP) was 7,587, the throughput was 714 MB/s. Increasing workloads did not greatly affect Oracle database throughput either.
Obviously, companies benefit from reducing financial costs like CAPEX and OPEX. In summary, the Dell EMC Ready Solution for Oracle is a winning, integrated, validated, flexible and configurable solution that combines the best of all worlds for the datacenter environment. In the next blog, we will continue discussing additional features of this solution along with the backup and recovery options.