HP reference configuration for Scalable Warehouse Solution for Oracle: HP DL980 G7 and P2000 G3 MSA Technical white paper Table of contents Executive summary .......................................................................................................................2 Introduction ..................................................................................................................................2 Important points and caveats ..........................................................
Executive summary The HP Scalable Warehouse Solution for Oracle on Hewlett-Packard (HP) servers, storage and networking products provides a prescriptive approach for balancing server, storage, network and software configurations for architecting Oracle data warehouse solutions.
Important points and caveats The configuration described here is exclusively designed for, and is applicable to, sequential data workloads or limited mixed workloads. Use of this approach on other workload types would need to be tested and may yield configurations that are effective for other solutions.
Note The Intel Xeon processor E7 series supports Hyper-Threading (HT). HT is recommended and was tested in our configuration. However it is good practice to test HT with your particular application. Table 2.
Table 4.
System/Environment setup Storage configuration details Internal storage The DL980 G7 recommended configurations use four internal drives configured with RAID1 for the OS and 11gR2 software. The server supports up to eight internal drives, so additional drives can be added for staging data, logs, or other requirements. Table 7.
Note To locate and download the latest software and firmware update for your P2000, go to http://www.hp.com/go/p2000. Select Models – select your product P2000 G3 MSA Fibre Channel Dual Controller SFF Array System – select HP Support & Drivers, then select Download drivers and software. Also you can get support information for any HP products by going to http://www.hp.com/go/support.
Figure 1.
Figure 2. Creating a single 400GB data volume from vdisk A1.
A P2000 enclosure has two controllers each with two FC ports. These ports are labeled A1 and A2 for controller A and B1 and B2 for controller B. Fibre Channel connectivity was accomplished with the two FC ports on each controller of the P2000 going to a separate FC SAN switch and then each dual port FC card on the DL980 is connecting to the two separate SAN switches.
Once the vdisks and volumes are created for each P2000 the next step is to explicitly map the volume to the controller host ports for access by the host. Both controllers share one set of LUNs so if a controller fails the other controller can access all LUNs on the array.
In table 9 is a summary of what the volume mapping looked like for a single P2000 array. Since we have a total of 48 volumes and 24 FC HBA ports we mapped two volumes to a primary and secondary FC HBA port spread across two different controller ports and LUN numbers. Table 9. Explicit mapping of the four 400GB volumes in P2000 enclosure 12 mapped to DL980 FC HBAs, Controller ports and LUN numbers.
scale-up servers. At the core of the HP PREMA Architecture is a node controller ASIC, derived from Intel technology powering the HP Integrity Superdome 2.
Note Even though internal testing was done using a DL980 with 64 cores and 12 FC HBA cards, from an HP support requirement there is currently a support limit of 11 PCIe FC HBA cards using either QLogic or Emulex FC 8Gb/s HBA cards. With the increase in processor cores supported on the DL980, more FC IO cards may improve the overall throughput of the server. HP is working on enabling more than 11 FC cards on the DL980 that will be available by the end of 2012.
Figure 6. I/O slots vs. processor boards connectivity Note Do not use slot 1 for any HP FC PCIe HBA Cards due to low I/O performance (PCI-x 100 MHz). Make sure the FC cards have up-to-date firmware installed. Out of date firmware on Fibre Channel cards may cause performance problems under the extremely high I/O rates possible with this configuration. Oracle Automatic Storage Management (ASM) I/O configuration and disk layout Oracle 11gR2 ASM was used for the database storage.
MB, depending on the specific disk group compatibility level. Larger AU sizes typically provide performance advantages for data warehouse applications that use large sequential reads. Oracle ASM 11gR2 introduced the concept of variable extents, which adds an extra layer of complexity when determining the optimal AU size.
tablespace extent sizes. The table extent sizes didn’t make nearly as much difference whether it was 1MB or larger, also using increments of power of 2 steps (1, 4, 16, or 64). The performance results were fairly similar. Be aware that if using large table extents on tables with a large number of partitions the minimum table size can be very large, so it is best to use large extents for fact tables and very large dimension tables.
By enabling huge pages it will eliminate this page table problem. The same example 800GB SGA with 1024 users now only requires 32GB for page tables. Even with thousands of users, HUGE pages keep the page tables manageable. To make sure Oracle 11gR2 database can use Huge Pages in Red Hat Enterprise Linux 5, you also need to increase the ulimit parameter “memlock” for the oracle user in /etc/security/limits.conf unlimited.
Disable Oracle’s Automatic Memory Management (AMM) While Oracle’s automatic memory management (AMM) promises to improve memory efficiency, AMM is incompatible with huge pages. The overhead of regular size page tables far exceeds any possible gains from AMM. Overall, AMM ends up wasting memory, and it should not be used for BI Data Warehousing workloads. If you have configured your system for huge pages as described above, then AMM will already be disabled.
Note If you are doing a proof of concept (POC) where typical testing is running single queries to see how they perform, it is recommended to set parallel_degree_policy to manual. Otherwise the performance of single queries may not be as good. The benefits of auto will be for multiple user concurrency type tests. For the larger more complex queries we used the large 32k blocks for large fact tables (Oracle parameter db_32k_cache_size).
Another test2 was done using a 138GB table. The first query scan produced around 17 GB/s throughput rate according to vmstat. This result was very similar to test1 but with a lot of I/O wait. The scan time was 8.19 sec. or 17 GB/s. The same test2 query scan was executed again with parallel caching on. The results produced slightly lower I/O scan rates at 15GB/s according to vmstat. Also there was much higher user CPU consumption at 41% compared to 14% without parallel caching.
In the graph shown in figure 9 it can be seen that there is some additional benefit by enabling both queuing and parallel query caching of the queries when viewing the average query response times. Bigger benefits can be expected in more optimum conditions, where the database buffer cache size is much closer to the active data size and where there is lower concurrency.
However, note in figure 10 the difference it can make to the maximum response time a query can take to execute if queries are not queued. They end up executing in serial rather than parallel which is a single process that takes much longer time to complete larger complex queries. Alternatively large queries can be terminated if the minimum DOP requirement is not met. Figure 10.
For the last concurrent test the same 5TB of raw user data was used. No materialized views were used and this time all the data was actively queried. Tests were done in 100, 200, 300, 400 and 500 concurrent queries. The DL980/P2000 was able to consume most available resources when executing 100 concurrent queries all at the same time. This was due to the Oracle parallel management configuration set in the init.ora parameter file.
Synthetic I/O testing To get a good indication about how well this particular reference configuration would scale for throughput, a set of low level I/O testing was conducted. Table 12 shows some low level test data that was collected when testing the I/O subsystem. An HP storage I/O tool for Linux was used to perform the synthetic I/O testing. Results were close to the theoretical 19GB/sec bandwidth of this configuration. Table 12.
Best practices when deploying Linux operating system The DL980 uses HP PREMA architecture which incorporates a new node controller design with Smart CPU caching and redundant system fabric. Combined with the Red Hat Enterprise Linux operating system, these features provide a solution that is fully capable of supporting the most demanding, data-intensive workloads, with the reliability, availability, and efficiency needed to run all of your business-critical applications with confidence.
– MEMORY_MAX_TARGET = 0 (Oracle DB 11g) Use the following guidelines as a starting point for sizing memory: – Size the Oracle System Global Area (SGA) at about 70% of total memory (about 600GB for database buffer cache with the remaining 100GB for the other SGA pools – see Oracle parameters in Appendix 2). – Size the Oracle Process Global Area (PGA) at about 20% of total memory. – That leaves about 10% for the Linux system management.
Present volumes to the DL980 by presenting one primary path and one backup path. Do NOT use multiplexing. Stripe across the arrays using Oracle ASM AU 64MB stripe size. Make Oracle tablespace the same as the ASM AU stripe size of 64MB. Oracle tables can be smaller or similar size to tablespace extents. Bill of materials Figure 13 shows the recommended configuration for the Scalable Warehouse Solution for Oracle Databases. Figure 13.
Table 13. HP Scalable Warehouse Solution for Oracle bill of materials Qty Description Production Database Server Configuration 1 HP ProLiant DL980 G7 CTO system 2 HP DL980 G7 E7-4870 FIO 4-processor Kit 1 HP DL980 CPU Installation Assembly 4 HP 1200W CS Platinum Power Supply kit 128 HP 8GB 2Rx4 PC3-10600R-9 kit 8 HP DL980 G7 Memory Board 1 HP Slim 12.7mm SATA DVD Optical kit 1 512MB Flash Backed Write Cache (FBWC) 1 PCI Express I/O Exp.
Implementing a proof-of-concept As a matter of best practice for all deployments, HP recommends implementing a proof-of-concept using a test environment that matches as closely as possible the planned production environment. In this way, appropriate performance and scalability characterizations can be obtained. For help with a proof-of-concept, contact an HP Services representative (http://www.hp.com/large/contact/enterprise/index.html) or your HP partner.
Appendix 1: BIOS and OS settings The following were the settings of the indicated BIOS parameters used during these tests. The BIOS version was P66 07/27/2010.
The following limits were established for the oracle user in the limits.
Appendix 2: Oracle parameter settings The following Oracle parameters were used for these tests: compatible = 11.2.0.0.
transactions = 3379 undo_management = auto undo_retention = 900 use_large_pages = only NOTE: Oracle parameters whose names start with an underscore character (e.g., “_in_memory_undo”) are unsupported by Oracle. Extra care should be taken when changing the default setting of these so-called “underscore” parameters, especially in production environments. (The lone exception to this rule is the _enable_NUMA_support parameter, which is fully supported by Oracle.
Appendix 3: Example multipath.conf for P2000 array ### ### ### ### ### ### ### ### ### Do not edit the first two lines of this file or remove this file HP Device Mapper Multipath Enablement Kit v4.4.1 The Device Mapper Multipath Template configuration file for RHEL5U4 or later releases to be used with HP StorageWorks Arrays. Use this configuration file as your /etc/multipath.conf file. If you already have a valid working configuration file, refer here for the recommended configuration for HP arrays.
# wwid 2345234245647657 # devnode "sda" # device { # vendor "HP" # product "OPEN-*" # } #} # The multipaths section - uncomment this section to define a per multipath # device settings.
#device { # vendor "HP" # product_blacklist "OPEN-.
alias DATA_12_B1 } multipath { wwid 3600c0ff000dadc222605f94c01000000 alias DATA_12_B2 } multipath { wwid 3600c0ff000dadaf7e805f94c01000000 alias FILES_12_A1 } multipath { wwid 3600c0ff000dadaf7f505f94c01000000 alias FILES_12_A2 } multipath { wwid 3600c0ff000dadc220506f94c01000000 alias FILES_12_B1 } multipath { wwid 3600c0ff000dadc221706f94c01000000 alias FILES_12_B2 } } 38
Appendix 4: Oracle Advanced Compression Many customers are looking for solutions that provide a means for reducing the size of their rapidly growing databases without negatively affecting their end user performance. Oracle 11gR2 offers integrated database compression to address this requirement. We often think of compression as being a trade-off between performance and storage: compression reduces the amount of storage required, but the overhead of compressing and decompressing makes things slower.
For more information For additional Oracle solutions from HP, please visit http://www.hp.com/go/oracle For an overview of the HP ProLiant DL980 G7 server, http://www.hp.com/servers/dl980 HP ProLiant DL980 G7 server with HP PREMA Architecture, http://h20195.www2.hp.com/V2/GetDocument.aspx?docname=4AA3-0643ENW ProLiant DL980 G7 server QuickSpecs, http://h18000.www1.hp.com/products/quickspecs/13708_div/13708_div.
