This document has been archived and will no longer be maintained or updated. For more information go to the Storage Solutions Technical Documents page on Dell TechCenter or contact support.
THIS WHITE PAPER 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. Reproduction of this material in any manner whatsoever without the express written permission of Dell Inc. is strictly forbidden. For more information, contact Dell.
Table of Contents 1 2 3 Introduction ....................................................................................................................................................... 2 1.1 Audience ..................................................................................................................................................... 2 1.2 Terminology ......................................................................................................................................
7 6.1 Capacity .................................................................................................................................................... 30 6.2 Performance............................................................................................................................................. 30 6.3 Scalable and modular deployment architecture ................................................................................31 Conclusion ...................................
Acknowledgements This whitepaper was produced by the PG Storage Infrastructure and Solutions of Dell Inc.
1 Introduction This paper describes sizing and best practices for deploying Decision Support Systems (DSS) based on ® ™ ™ Oracle 11g Release 2 database using Dell EqualLogic storage. DSS solutions require scalable storage platforms that offer high levels of performance and capacity.
2 Storage considerations for decision support systems DSS applications are designed to support complex analytical query activities using very large data sets. The user queries executed on a DSS database typically take several minutes (or even hours) to complete and require processing large amounts of data. A DSS query is often required to fetch millions of records from the database for processing. To support these queries, the database management system (DBMS) reads table data from the storage devices.
2.2 Benefits of EqualLogic Multipath I/O Dell provides numerous value added software products as part of the EqualLogic solution. One of ® these offerings, Host Integration Tool for Linux (HIT/Linux), provides efficient iSCSI session establishment and management to each target volume in Linux environments. In a storage pool consisting of multiple EqualLogic arrays, EqualLogic’s group manager software automatically distributes the volume across multiple members.
3 DSS I/O simulation 3.1 Test methodology 3.1.1 Simulation tool and workload definition A series of I/O simulations were conducted using Oracle I/O Numbers (Orion) to understand the performance characteristics of the storage arrays. Orion is a popular I/O simulation tool used for understanding the I/O performance behavior of storage systems for Oracle databases. The tool and documentation can be downloaded from Oracle’s website at http://www.oracle.com/technetwork/topics/index-089595.html.
volume stripe will be split into 1MB chunks for storage access. Since the volume stripe size is 1MB in our configuration, it would fit within one I/O block. If volume stripe size is set at 4MB per volume, there will be 4 I/O blocks per volume level stripe. This test determined the read I/O throughput of the array(s) in an ASM like environment with a sequential I/O workload. This provides a baseline for sequential read workloads like backup and table scans.
EqualLogic storage array network statistics including TCP retransmissions and network throughput in MBps were captured via a custom script using host scripting toolkit. This monitoring script polled the storage arrays every 60 seconds for network statistics. A dedicated server was used to run this tool. The criteria established for TCP retransmissions by the arrays was under 0.5%. TCP retransmissions during all tests remained under this established limit.
3.2 Test configuration 3.2.1 SAN connectivity Server connectivity to EqualLogic storage arrays and the details of the iSCSI network are illustrated in Figure 2. Figure 2 Test server and storage connectivity ® A PowerEdge R710 server was installed with two Intel 82599EB 10 Gigabit Ethernet controllers with 2 x 10GbE SFP+ ports each. These 10GbE ports were used for iSCSI connectivity to the SAN. One port from each controller was used in configurations with up to two storage arrays.
storage controller were connected to these two switches as shown in Figure 2. One port from each controller was connected to these switches to avoid the switch as the single point of failure (SPOF). 3.2.2 Storage volume layout The configuration of volumes in EqualLogic storage is shown in Figure 3. Figure 3 EqualLogic storage volume configuration Eight EqualLogic storage volumes were created at 100GB size each in a single storage pool. This was kept constant across all Orion test configurations.
3.3 Test studies and results This section describes the Orion tests conducted and the results observed. 3.3.1 PS6010XV RAID10 and RAID50 The four Orion workloads described earlier were executed on an EqualLogic PS6010XV array in RAID 10 and in RAID 50 . The PS6010XV included 16 x 600GB 15K RPM SAS drives, with two drives being configured as hot spares in the RAID layout. The I/O throughout reported by Orion is shown in Figure 4.
Figure 5 Orion I/O throughput: PS6010XV versus PS6510E PS6510E offered the same levels of throughput as PS6010XV with sequential reads in concat mode and sequential writes using RAID 0 mode. The sequential reads in RAID 0 mode provided 12% more throughput than PS6010XV. With large random reads in RAID 0 mode, the PS6510E provided about 44% more throughput.
3.3.3 PS6010XV scaling studies The four Orion workloads were executed on EqualLogic PS6010XV arrays using RAID 50. The storage pool was configured with one array initially and then scaled up to four arrays in the same storage pool. Test volumes were recreated and mounted on a server for each scaled configuration. The I/O throughput reported by Orion across the scaled configurations is shown in Figure 6.
4 DSS application simulation 4.1 Test methodology 4.1.1 Simulation tool and workload definition ® A series of DSS application simulations were conducted using Benchmark Factory for Databases by ® Quest Software as the simulation tool running a TPC-H like workload. TPC-H is an industry standard benchmark for DSS. The DSS tests were conducted to understand the throughput behavior of the storage arrays when the Oracle Real Application Cluster (RAC) database executed DSS queries.
• Server OS Tools Red Hat Linux ifconfig utility was used for capturing iSCSI NIC statistics (RX and TX bytes). Red Hat Linux IOstat and vmstat utilities were used for capturing disk, CPU and memory statistics respectively. A server CPU utilization level of 75% was set as the threshold for DSS test runs. The logical test bed for DSS simulations including monitoring stations are shown in Figure 7.
4.2 Test configuration 4.2.1 SAN connectivity The server connectivity to EqualLogic storage arrays and the details of the iSCSI network are illustrated in Figure 8. Figure 8 • • BP1019 Test server and storage connectivity Two PowerEdge R710 Servers were installed with two Intel® 82599EB 10 Gigabit Ethernet Controllers with 2 x 10GbE SFP+ ports each. These 10GbE ports were used for iSCSI connectivity to the SAN. One port from each NIC controller on a server was used in test configurations.
• • BP1019 Two NetXtreme II BCM5709 Gigabit Ethernet Quad Port NICs were installed per server for private RAC interconnect. Two ports from each quad port NIC were used to connect to a private interconnect switch, for a total of four ports per server. The four interconnect ports were configured as a bond. Two PowerConnect 8024F switches were interconnected using a LAG via LACP. The LAG comprised of four 10GbE ports from each switch. These two switches comprised the SAN.
4.2.2 Storage volume layout The configuration of volumes in EqualLogic storage is shown in Figure 9. Figure 9 • • BP1019 EqualLogic storage volume configuration Eight EqualLogic storage volumes were created at 100GB each in a single storage pool for database data. Two volumes were created at 200GB each for database logs and one volume at 500GB was created for temporary database files.Two additional volumes were created at 1GB each for Oracle Clusterware components.
maximum of six iSCSI sessions for the entire volume. The utility eqltune installed as part of the HIT/Linux was executed, and the OS parameter recommendations from the utility were adhered to. 4.2.3 • • Oracle RAC configuration Oracle Clusterware was installed and configured on the two PowerEdge R710 servers running RHEL. This enabled them to function as two nodes of an Oracle RAC. The storage volumes shown in Figure 9 were mounted on the OS as block devices accessed via the EqualLogic HIT/Linux MPIO.
Figure 10 BP1019 ASM disk and disk group configuration Deploying Oracle DSS with EqualLogic 10 GbE iSCSI SAN 19
• • Each EqualLogic storage volume was logically mounted and mapped as an ASM Disk as shown in Figure 10.
Figure 11 Database tablespace and file layout Once the user schema, tablespaces and data files were created, the schema objects including tables and indices were created using Benchmark Factory by submitting SQL queries via the network to the database server. The create database feature of the tool enabled this object creation and also the population of the tables/indices with test data.
4.3 Test studies and results 4.3.1 PS6010XV scaling studies DSS query simulations, with the workload described in “Test methodology” on page 13, were executed on the EqualLogic PS6010XV array. As demonstrated by the Orion test studies in the earlier section, RAID 50 offered slightly higher throughput than RAID 10 and also provides higher useable capacity than RAID 10. Therefore, RAID 50 was chosen due to the balanced capacity and performance levels offered by this RAID type.
Figure 12 Storage I/O throughput – PS6010XV scalability As seen in the bar graph, both the average and maximum throughput measured scaled linearly with the number of arrays. In the two and three array cases, it was better than linear scalability. The maximum throughput scaled at 216%, 346% and 415% with two, three, and four arrays as compared to a single baseline array. The average throughput scaled at 212%, 338% and 401% with two, three, and four arrays as compared to a single baseline array.
The I/O throughput across storage array members as reported by EqualLogic SAN HQ in the four array configuration is shown in Figure 14. Figure 14 Storage I/O throughput – SAN HeadQuarters The intial ramp up in Figure 14 is during the start of the test when all 5 users login and start submitting their queries. As the users login, the database server commences query processing and generates increasing I/O requests resulting in a ramp up of I/O throughput.
CPU Statistics on RAC nodes The average CPU utilization percent and percent I/O wait time were measured during the DSS tests are shown in Figure 15. Only one RAC node database instance was used with one and two arrays. With three and four arrays both RAC node instances were used. This is because with one and two arrays, the average CPU utilization on a single instance was below the threshold of 75%.
Query Completion Time The end-to-end completion time for all 22 queries submitted by the 5 simulated users is shown in Figure 16 below. This is the duration between the time when the first query was submitted to the database and when the last query was completed. Figure 16 Query streams completion time Due to the increased storage throughput available as the number of arrays increased, data was fetched at a much faster rate by the database.
5 Best practices Best practices apply to each of the component layers in the solution stack. The considerations at each layer of the solution stack are described in the following sections. 5.1 Storage • It is an important best practice to physically run DSS workloads on a separate storage system that is optimized for DSS query I/O activity. This will prevent DSS workloads from interfering with the operation of other workloads.
• • • • • A separate switching infrastructure for other non-SAN connections should be in place to isolate the iSCSI SAN switching infrastructure exclusively for storage traffic Enable flow control on all iSCSI SAN switch ports (including LAG ports) for optimal throughput of iSCSI traffic. Flow control will help regulate packet flow across SAN fabric between nodes (server, switches, and storage) during high I/O traffic conditions.
5.5 Application – Oracle database • Storage volumes need to be appropriately sized for capacity and performance to host the data, transaction logs, and temporary database files. Split the database into at least five volumes. The temporary database will need to be appropriately provisioned with storage space, since DSS workloads often create large temporary data objects during query processing.
6 Sizing DSS application databases need to be sized for both capacity and performance. 6.1 Capacity The useable capacity of an array depends on the drive size, drive count, and RAID scheme chosen. The PS6010XV array tested has a useable capacity of 6.28 TB with RAID 50. Refer to respective array specification documents at equalogic.com for useable capacity of other array models. To determine the array count based on capacity needs: 1.
6.3 Scalable and modular deployment architecture The Oracle RAC server nodes under test were configured with two Intel Xeon CPUs (X5690) at 3.47GHz, six cores, and 48 GB RAM. One node was able to drive two PS6010XV arrays in RAID 50 within a reasonable average CPU utilization limit of 75%. Two nodes were able to drive four PS6010XV arrays in RAID 50. A single server and two storage arrays with similar specifications can be viewed as a single server/storage block driving a certain amount of I/O throughput.
Figure 18 I/O throughput block scalability As seen in the figure above, with sequential reads one block provides approximately 1300 MBps of I/O throughput and two blocks provide 2600 MBps. With sequential writes, one block provides 700 MBps and two blocks provide 1400 MBps. Large random reads map to I/O needs of DSS applications and one block provides about 900 MBps of read I/O throughput. Two blocks provide 1800 MBps of throughput.
Appendix A System A.1 Hardware and software component versions Component Details / Version Server Model PowerEdge R710 CPU 2 x Intel(R) Xeon(R) CPU X5690 @ 3.47GHz with 6 cores memory 48GB RAM Server BIOS 6.0.7 iSCSI NIC Intel® 82599EB 10 Gigabit Ethernet Controller with 2 x 10GbE SFP+ ports iSCSI NIC Driver / Firmware 3.2.9-NAPI / 0.9-3 Operating System Red Hat Enterprise Linux 5.5 iSCSI Switch Model PowerConnect 8024F Switch Firmware 4.1.0.
Appendix B Orion Detailed information on Orion tools parameters is listed here http://docs.oracle.com/cd/E11882_01/server.112/e16638/iodesign.htm#BABEIFFG B.1 OS Disk volumes Target OS disk device volumes under test were listed in a test file and provided as a parameter input for the Orion test runs. Example parameter file test.
B.3 Test run parameters Note: The below parameters apply to tests with 1 array as target storage. Configurations with more than one array had the num_large multiplied by the number of arrays under test. This change only applies to read tests and not write tests. For sequential write tests this parameter was kept constant at 1 across all test configurations as this offered the best throughput.
Appendix C Oracle C.1 Oracle ASM udev rules workaround The udev rules file content laid out below creates DM device handles for EqualLogic multipath devices in a location where the Oracle ASM utility will locate them. This is a temporary workaround for Oracle ASM with HIT/Linux 1.0.0. Future version of the HIT/Linux will address this as part of the toolkit. 1. Install this file to location: /etc/udev/rules.d/39-eqloracle.rules 2.
# /etc/multipath.conf so duplicate devices are not accidentally created. See # the Equallogic HIT Kit user guide for details. # # Skip non-DM devices: KERNEL!="dm-[0-9]*", GOTO="end_eqloracle" # For DM devices, query the table name: PROGRAM="/sbin/dmsetup info -c --noheadings -o name -j %M -m %m" # Based on the result of DM name query above, creates a /dev/dm-* device for # each user-facing (ie, mountable) equallogic device.
ALTER TABLESPACE TEMPS ADD TEMPFILE +TMP1AUDG SIZE 20480M AUTOEXTEND ON NEXT 100M MAXSIZE UNLIMITED; (REPEAT ABOVE ALTER STATEMENT 22 TIMES) CREATE USER DB1 IDENTIFIED BY DB1 DEFAULT TABLESPACE TS1 TEMPORARY TABLESPACE TEMPS; GRANT DBA TO DB1 IDENTIFIED BY DB1; ALTER DATABASE DEFAULT TEMPORARY TABLESPACE TEMPS; C.3 SQL tuning One of the 22 user queries required custom SQL Tuning. The SQL Tuning Advisor utility included within Oracle 11g R2 Enterprise Manager was used to tune this query.
Additional resources Support.dell.com is focused on meeting your needs with proven services and support. DellTechCenter.com is an IT Community where you can connect with Dell Customers and Dell employees for the purpose of sharing knowledge, best practices, and information about Dell products and your installations. Referenced or recommended Dell publications: • • Dell EqualLogic Configuration Guide: http://en.community.dell.com/techcenter/storage/w/wiki/equallogic-configurationguide.
THIS WHITE PAPER 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.
