Sizing and Best Practices for Online Transaction Processing Applications with Oracle 11g R2 using Dell PS Series Dell EMC Engineering March 2017 A Dell EMC Technical White Paper
Revisions Date Description December 2013 Initial release March 2017 Updated for Dell EMC branding; consolidated two best practices documents (BP1003 and BP1069) Acknowledgements This best practice white paper was produced by the following members of the Dell Storage team: Engineering: Chidambara Shashikiran Technical Marketing: Chhandomay Mandal Additional contributors: Darren Miller, Henry Wong, Rob Young, Chuck Farah, Rick Sussman The information in this publication is provided “as is.” Dell Inc.
Table of contents Revisions.............................................................................................................................................................................2 Acknowledgements .............................................................................................................................................................2 Executive summary.....................................................................................................................
7 6.3 TPC-E I/O with increased write I/O ...................................................................................................................35 6.4 TPC-E on PS6110XS .......................................................................................................................................37 Best practice recommendations .................................................................................................................................39 7.1 Storage .............
Executive summary Online transaction processing (OLTP) applications — ranging from web-based e-commerce sites, to accounting systems, to customer support programs — are at the heart of today’s business functions. They are the foundation for a wide range of mission-critical applications, particularly those utilizing Oracle® real application cluster (RAC) database platforms, and depend on exceptional storage performance and reliability.
1 Introduction Different types of database applications have varying performance and capacity needs. Understanding the models for common database application workloads can be useful in predicting the possible application behavior in a given infrastructure environment. The most common database application workload models are OLTP and data warehousing (DW). This paper focuses on OLTP database workloads.
1.3 Terminology The following terms are used throughout this document: AMM: The Oracle Automatic Memory Management (AMM) feature automates and simplifies the memorytuning configuration tasks by allowing you to manage the different memory components of Oracle using a single database initialization parameter called MEMORY_TARGET. ASM: Oracle Automatic Storage Management (ASM) is a volume manager and a file system that supports Oracle database.
2 OLTP applications with PS Series arrays OLTP database applications are optimal for managing rapidly changing data. These applications typically have many users who are performing transactions simultaneously. Although individual data requests by users usually reference few records, many of these requests are being made at the same time. To develop appropriate storage performance and capacity sizing guidelines for OLTP workloads, it is essential to understand the following factors: 2.
3 Solution infrastructure The test system used to conduct ORION, Vdbench, and TPC-C/TPC-E testing for this paper is shown in this section. 3.1 Physical system test configuration The logical test configuration diagram for simulating Oracle OLTP database I/O using the ORION tool is shown in Figure 1.
The logical test configuration diagram for OLTP database application tests (TPC-E) using Quest Benchmark Factory is shown in Figure 2. TPC-E test configuration — LAN and iSCSI SAN connectivity 3.2 Storage configuration The database files, REDO logs, and system-related table spaces such as SYSTEM and UNDO were stored on PS6110XS and PS6010XV arrays in their own pool called Database pool.
3.3 Database layout The PS Series arrays were placed in different pools, and Oracle ASM disks were configured using the ASM disk layout described as follows.
Figure 3 shows the containment model and relationships between the PS Series pool and volumes, and the Oracle ASM disk groups and disks.
4 Test methodology A series of I/O simulations were conducted using ORION to understand the performance characteristics of the PS Series XV, S, and XS hybrid storage arrays. The ORION tool was installed on the operating system (Red Hat® Enterprise Linux® in this case) and used to simulate I/O on the external PS Series storage arrays. The PS Series storage volumes were presented to the OS as block I/O devices.
4.2 Test criteria The test criteria used for the study includes: 14 The storage array disk access latencies (read and write) remain below 20 ms per volume. Database server CPU utilization remains below an 80% average. TCP retransmits on the storage network remain below 0.5%. Application response times remain below two seconds, which is the industry-standard acceptable latency for Oracle OLTP database applications.
5 I/O profiling study The ORION tool uses the Oracle I/O software stack to generate simulated I/O workloads without having to create an Oracle database, load the application, and simulate users. ORION is commonly used for benchmarking the I/O performance characteristics of Oracle database storage systems. A series of ORION tests were executed on PS6010XV, PS6010S, and PS6110XS hybrid arrays for measuring the storage I/O performance and system limits for pure OLTP workloads. 5.
Key findings from the test results are summarized as follows: As expected, average IOPS increased with the queue depth. The PS6010XV array in a RAID 10 configuration produced approximately 4,000 IOPS for OLTP-type transactions while staying within the generally accepted disk latency limit of 20 ms (for both read and write IOPS). For block sizes as large as 64K, a single PS6010XV array is able to sustain approximately 3,000 IOPS for a typical OLTP workload. 5.
Key findings from the test results are summarized as follows: 5.3 For 100% read I/O with an 8K block size, the PS6010S array sustained a maximum of approximately 34,000 IOPS. For 70/30 read/write I/O with an 8K block size, the PS6010S array sustained a maximum of approximately 16,000 IOPS. At the maximum IOPS level measured for the PS6010XV array before exceeding the 20 ms disk latency threshold (4,047), the disk latency for the PS6010S array at the same IOPS level was < 2 ms.
The results collected from these tests are illustrated in sections 5.3.1 to 5.3.4. Additional I/O simulation tests were run to evaluate Oracle ASM benefits and also to determine the volume configuration which produces optimal performance. These test results are discussed in sections 5.3.5 and 5.3.6. 5.3.1 ORION (1 TB array capacity utilization, 100% random I/O and no data locality) ORION tests were run with a range of user loads on 2 x 500 GB volumes.
The SAN HQ chart in Figure 7 shows that each SSD drive was producing more than 5,300 IOPS during peak I/O activity.
5.3.2 ORION (2 TB array capacity utilization, 100% random I/O and no data locality) In this test, 4 x 500 GB volumes were used to run the ORION test. The total capacity utilization was 2 TB. Because there was no locality of data reference and the utilized capacity exceeded total SSD capacity, I/O activity was observed on both the SSD and 10K SAS drives for this completely random workload as shown in Figure 8.
The peak load was around 10,234 IOPS compared to 17,000 IOPS in the previous test configuration. The entire capacity of the SSDs was saturated when the space utilization was increased to 2 TB. The simulated I/O was completely random with no locality of data access. Consequently, the SSDs had to wait for I/O activity to complete on the 10K SAS drives and the maximum IOPS delivered was decreased to around 10,000 IOPS. 5.3.
This test on the array produced approximately 7,167 IOPS with less than 12ms latency on the storage array as shown in the SAN HQ chart in Figure 9. IOPS and latency reported by SAN HQ (4 TB capacity utilization and no data locality) The raw I/O performance from this simulation test is compared with the I/O performance when the TPC-E-like workload was run on the actual database in section 6.3.
5.3.4 Vdbench (2 TB array capacity utilization, 100% random I/O and 20% data locality) Applications such as Oracle OLTP databases typically contain both infrequently accessed and highly dynamic data within a single volume. The advanced tiering feature on PS6110XS arrays categorizes and operates on data workloads within each volume.
The SAN HQ chart in Figure 10 shows the gradual increase in IOPS from 7,000 to 12,000 over the test time span due to the automatic tiering feature of PS6110XS arrays. Test start: 7,000 IOPS Steady state: 12,000 IOPS Increase in IOPS due to PS6110XS internal tiering (as reported by SAN HQ ) 5.3.5 Number of volumes studies The primary objective of these tests was to determine the PS Series volume configuration which produces optimal performance for the Oracle OLTP-type of workload.
The total capacity utilization on the array was kept constant at 2 TB and only the volume configuration was modified as shown in Table 3.
The results displayed in Figure 11 confirm higher performance when there are eight or more volumes. Increasing the number of volumes beyond eight did not result in any significant increase in IOPS. IOPS per volume configuration 6% 23% IOPS 2 4 6 Number of volumes IOPS per volume configuration Note: A configuration with more small volumes is preferred over a low number of large volumes for better performance and simpler management of OLTP databases.
Slightly higher IOPS (4,300 compared to 4,200) were observed with the ASM configuration. The IOPS numbers maintained the generally accepted disk latency limit of 20 ms (for both read and write latencies measured separately) for a random workload. 5.4 ORION/Vdbench on PS6110XS The tests described in sections 5.3.1 to 5.3.3 were run with different capacity utilizations on the PS6110XS array.
6 OLTP performance studies using TPC-E like workload The ORION and Vdbench test results described in section 5 helped in determining the baseline performance of a typical Oracle OLTP database on PS Series arrays. Also, ORION and Vdbench are both synthetic I/O workload generation tools and they do not use the actual Oracle stack. In this phase of testing, Quest Benchmark Factory for Databases simulated a real TPC-E-style workload on the test systems using an actual 2-node Oracle 11g R2 RAC database.
Test parameters: Oracle memory management study Configuration parameters PS Series SAN 1 x PS6010XV: 16 x 300GB 15K SAS disks with dual 2-port 10GbE controllers 14 SAS disks configured as RAID 10, two hot spares Controller firmware version: 5.0.0.0 (R122845) 1 x PS6010S: 16 x 100GB SSD disks with dual 2-port 10GbE controllers 14 SSDs configured as RAID 10, with two hot spares Controller firmware version: 5.0.0.
A 9% increase in IOPS was observed when the memory was increased to 48 GB from the default setting of 38 GB. At the same time, the performance dropped when the memory was increased to a larger value such as 64 GB. A slight increase in IOPS was observed until the memory setting of 54 GB. After that, no additional improvements in performance were seen.
6.2 TPC-E (array capacity utilization study) The performance from the storage array differed based on the capacity utilization as illustrated by the ORION test results in sections 5.3.1 to 5.3.3. The TPC-E tests were also run on two capacity configurations (array capacity utilization: 1 TB and 4 TB) to evaluate the performance implications. The two test configuration details are listed in Table 5.
IOPS, latency, and disk IOPS reported by SAN HQ (TPC-E I/O with 1 TB array capacity utilization) 32 Sizing and Best Practices for Online Transaction Processing Applications with Oracle 11g R2 using Dell PS Series | BP1003
Also, as you can see from the IOPS activity on the disks, complete I/O activity was handled by the SSDs and there was no I/O activity on the 10K SAS drives. This is because the array capacity utilization was 1 TB and all the data was able to fit within the SSDs. 6.2.2 4 TB array capacity utilization The entire database was able to fit within the SSDs in the scenario described in section 6.2.1.
IOPS, latency, and disk IOPS reported by SAN HQ (TPC-E I/O with 4 TB array capacity utilization) 34 Sizing and Best Practices for Online Transaction Processing Applications with Oracle 11g R2 using Dell PS Series | BP1003
As seen in the SAN HQ chart in Figure 14, I/O activity was observed on both the SSD and 10K SAS drives. A peak IOPS of 19,000 was observed compared to almost 27,000 as described in section 6.2.1 (1 TB array capacity utilization). The total capacity utilization on the array was about 4 TB, so the data was spread across both the SSD and 10K SAS drives.
TPC-E transactions were simulated using this modified transaction mix to determine the performance from a PS6110XS array when exposed to heavy write-intensive I/O workload, which is a worst-case scenario. No SQL queries were modified in this process. Around 8,704 IOPS were observed with the read/write ratio of almost 90/10. The SAN HQ chart for this test is shown in Figure 16.
Oracle AWR reports were captured while running these tests and constantly monitored for any RAC or database related bottlenecks. The top 10 events of the AWR report are shown in Figure 17. Top 10 events in AWR report As you can see from the AWR report, the top event is db file sequential read, which means that the disks were saturated and there were no other bottlenecks related to RAC or database configuration parameters. 6.
When the capacity utilization was increased to 4 TB, the IOPS changed from 25,675 to 19,000 (see section 6.2.2). This is because the entire data set could not be stored within the SSDs and had to be spread across all of the SSDs and 10K SAS drives. The SSDs also had to wait for the I/O activity to complete on the 10K SAS drives. The PS6110XS arrays performed well, even with the modified heavy write-intensive TPC-E-style I/O operations as described in section 6.3.
7 Best practice recommendations 7.1 Storage A configuration with more small volumes is preferred over a low number of large volumes for better performance and simpler management of OLTP databases. It is recommended to use at least eight or more volumes per PS6110XS array member for achieving optimal performance. Increasing the number of volumes beyond eight may not result in significant performance improvements. 7.
7.4 Oracle database application 7.4.1 Database volume layout The following are database volume layout best practices: 7.4.2 Oracle recommends using ASM for simplified administration and optimal I/O balancing across disk groups. ASM external redundancy is preferred while configuring ASM disk groups. There is no need to use other types of redundancy because the PS Series array will provide the required redundancy. Use same-sized ASM disks within ASM disk groups.
The ASM instance on nodes will terminate as soon as the /dev/shm size is modified. Complete the following to bring up ASM instances on database nodes. Log in to each node as grid user and export ORACLE_SID with ASM instance. Enter the following commands to bring up the ASM instance. Repeat the same steps on all database nodes. [grid@oranode1 bin]$ export ORACLE_SID=+ASM1 [grid@oranode1 bin]$ pwd /u01/app/11.2.0/grid/bin [grid@oranode1 bin]$ ./ASMCMD -bash: .
Note: To deploy Oracle ASM on RHEL 6.x kernels without ASMLib support from Oracle, use the procedure described in the article, Configure Oracle ASM on EqualLogic without ASMLib on RHEL 6.x. The described procedure uses udev (device manager for Linux which manages all device nodes in /dev) rules on a RHEL 6 server to configure Oracle ASM.
8 Conclusion Based on the tests explained in sections 5.1 through 5.4, the key findings while comparing performance of PS6010S and 6010XV arrays are summarized in the following. A 7x IOPS performance increase was measured for the PS6010S over the PS6010XV for 100% read 8K block I/O. The PS6010S can sustain a 4x IOPS performance increase over the PS6010XV at the 20ms disk latency threshold (16,000 compared to 4,000) for 70/30 read/write workloads.
A Configuration details This section contains an overview of the configurations used for the testing described in this document.
Test configuration: software components Test configuration: software components 45 Database servers Quest Benchmark Factory 2 x virtual machines used (one as the Benchmark Factory console and one for launching agents) 2 x Windows Server 2008 R2 Enterprise Edition Quest Benchmark Factory 6.9.2 2 agents from each VM Oracle 11.2.0.3 client installed Oracle 11.2.0.3 client installed VMware vSphere ESX version 5.
Additional resources Dell.com/Support is focused on meeting customer needs with proven services and support. Dell TechCenter is an online technical community where IT professionals have access to numerous resources for Dell EMC software, hardware, and services. Storage Solutions Technical Documents on Dell TechCenter provide expertise that helps to ensure customer success on Dell EMC storage platforms.
