Optimizing an Oracle® Database with Dell™ Compellent™ Automated Tiered Storage Implementation Best Practices of Dell™ Compellent™ Data Progression with Oracle® OLTP workloads Wendy Chen and Tom Dau Database Solutions Engineering Dell Product Group October 2012
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage This document is for informational purposes only and may contain typographical errors and technical inaccuracies. The content is provided as is, without express or implied warranties of any kind. © 2012 Dell Inc. All rights reserved. Dell and its affiliates cannot be responsible for errors or omissions in typography or photography. Dell, the Dell logo, Compellent, PowerConnect, and PowerEdge are trademarks of Dell Inc.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage October 2012| Rev 2.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Contents Executive summary ....................................................................................................... 2 Introduction ................................................................................................................ 3 Dell solutions for Oracle database ..................................................................................... 4 Technology overview ...............................
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Executive summary Enterprise data can be categorized based on criteria such as performance needs, access frequencies, I/O patterns, and business values. With the exponential data growth, the traditional “one size fits all” data storage strategy of storing all data into a uniform storage media has become costly and less efficient. To solve this challenge, many IT organizations have adopted the tiered storage solution.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Introduction Today’s storage devices come in many choices ranging from high performance and high cost storage devices such as the flash memory-based Solid State Drives (SSD), to slower performance and less expensive storage devices like the mechanical hard disk drives (HDD).
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage expensive SATA drives in tier 3. However, assigning data to a particular storage tier may be an ongoing and complex activity which may take a lot of time and effort from the administrators if it is done manually. Automation is a must to support the tiered storage infrastructure.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage For more information concerning Dell Solutions for Oracle Database, visit www.dell.com/oracle. Technology overview Dell Compellent Automated Tiered Storage – Data Progression The Dell Compellent Storage Center Storage Area Network (SAN) provides a highly efficient and flexible foundation for enterprise and the cloud.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Figure 2. Dell Compellent Automated Tiered Storage - Data Progression Unlike many newcomers to the storage tiering market, Dell Compellent Data Progression is built right into the virtualized storage platform. It realizes the full potential of automated tiered storage. True Virtualization. Dell Compellent engineered its Fluid Data architecture from the ground up to include Data Progression.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Data migration occurs automatically at a set time or on demand. The migration process does not affect data availability or application performance. Dell 12th Generation PowerEdge Servers Dell 12th Generation PowerEdge servers are the newest addition to the PowerEdge server family.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Oracle Databases with Dell Compellent Data Progression Dell Compellent Data Progression is configured by assigning Storage Profiles to volumes. Storage Profiles define the RAID level and storage tiers on which data blocks of a volume can be stored. If Data Progression is licensed, data can be migrated between RAID levels within a tier and between tiers.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage users. The database configuration used in the tests was a 2-node Oracle 11g R2 (11.2.0.3) Real Application Cluster (RAC). The database schema was populated by Benchmark Factory. The TPC-C test runs conducted simulated loads from 100 to 3,000 concurrent users in an increment of 100. Test outputs include metrics such as the average transaction response time and transaction per second (TPS).
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Table 1. Hardware and software configurations for Benchmark factory test HARDWARE AND SOFTWARE CONFIGURATIONS FOR BENCHMARK FACTORY TEST Two PowerEdge R720 servers, each with: • Two Intel Xeon 8-core 2.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage distribution. As you can see, all data pages reside on RAID 10 of tier 2 storage, and there’re no replay pages. Figure 4. Volume distribution of the baseline configuration without Data Progression Benchmark Factory TPC-C test, which consists of approximately 70% of read IOs and 30% of write IOs, was conducted on the baseline configuration.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage 2,200 concurrent users in an increment of 100. Test metrics include transaction per second (TPS) and average transaction response time in seconds. Test results are discussed in the Test Results section below. Enabling Data Progression and simulating read-only workload Following the TPC-C test of the baseline configuration, Data Progression and Data Instant Replay are enabled on the Oracle storage volumes except the REDO volumes.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage levels. Figure 5 shows the new volume distribution of the same database volumes illustrated in Figure 4. Figure 5.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage The volume distribution of the DATA volume shows that Data Progression effectively redistributes its data pages to span on all three tiers. Data pages that are moved to tier 1 represent the most active data pages that can benefit from the high performing SSD drives in the tier 1 storage. Data pages that are moved to tier 3 represent less active data pages.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Figure 6. Oracle TPC-C Transaction per second (TPS) vs.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Figure 7. 4 Oracle TPC-C average response time (second) vs. user load Oracle TPC-C Response Time Comparison Avg.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage As illustrated in Figure 8, using Data Progression to move active data sets in OLTP applications from SAS disks to SSD disks can reduce transaction response time by 43 percent. Figure Dell Compellent Data Progression transaction response Figure 1. 8. Dell Compellent Data Progression reducesreduces transaction response time oftime of Oracle OLTP workload Oracle OLTP workload Response Time Reduction 0.9 0.819 0.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage Figure 9. Dell Compellent Data Progression improves application throughput in TPS of Oracle OLTP workload TPS Improvement 140 43% 115.96 120 100 80.
Optimizing an Oracle Database with Dell Compellent Automated Tiered Storage 2. The architectural advantages of Dell Compellent automated tiered storage, a Dell technical white paper. http://content.dell.com/us/en/gen/d/business~solutions~power~en/documents~ps2q11cse20110332-comp2.pdf.aspx 3. Storage Center 6.1 System Manager User Guide.