1 / 18

Maximizing performance on Oracle 11g SE RAC

Maximizing performance on Oracle 11g SE RAC. Wendy Chen, Systems Engineer Naveen Iyengar, Systems Engineer Global Solutions Engineering, Dell Inc. DELL CONFIDENTIAL. SE vs. EE. Oracle 11g database is available in multiple editions Enterprise Edition (EE) Single or clustered servers

galia
Download Presentation

Maximizing performance on Oracle 11g SE RAC

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Maximizing performance on Oracle 11g SE RAC Wendy Chen, Systems Engineer Naveen Iyengar, Systems Engineer Global Solutions Engineering, Dell Inc. DELL CONFIDENTIAL

  2. SE vs. EE • Oracle 11g database is available in multiple editions • Enterprise Edition (EE) • Single or clustered servers • No limit on the maximum number of CPU (up to the maximum number of nodes supported in a RAC cluster) • Contains all Oracle database components, and can be further enhanced with the purchase of options and packs • Standard Edition (SE) • Single or clustered servers • Up to a maximum of 4 CPU sockets in the single or clustered servers • Includes selected, but not all the features come with EE • Built from the same code base as EE • Ideally suited to the needs to SMB with enterprise level performance

  3. Feature availability • A number of features that come with Oracle 11g EE are not available in SE. For example, • Data guard • Rolling upgrades • Online index and table organization • Parallel backup and recovery • Tablespaces point-in-time recovery • Flashback table / transaction / database • Parallel query / statistics gathering / index builds / data pump export and import • Transportable tablespaces • Infiniband support • Before deploying SE, make sure that you do not require any of the non-supported features.

  4. Pay-as-you-grow scalability • “Pay-as-you-grow” methodology to scale up to four single socket machines

  5. Testing performance capabilities of 11g SE rac • Quest Benchmark Factory TPC-C • A single node Oracle 11.1.0.7 SE database: 100 to 10,000 users • Two-node Oracle 11.1.0.7 SE RAC database: 100 to 10,000 users • Three-node Oracle 11.1.0.7 SE RAC database: 100 to 10,000 users • Four-node Oracle 11.1.0.7 SE RAC database: 100 to 10,000 users

  6. Performance monitoring • CPU utilization: SAR (System Activity Report) nohupsar 1 30000 > ~/sar-4nodes-run195-node1.txt & CPU %user %nice %system %iowait %steal %idle02:31:43 PM all 0.38 0.00 0.25 4.25 0.00 95.1202:31:44 PM all 0.25 0.00 0.25 4.12 0.00 95.3802:31:45 PM all 0.12 0.00 0.50 0.25 0.00 99.1302:31:46 PM all 0.62 0.00 0.62 1.12 0.00 97.6202:31:47 PM all 0.87 0.00 1.12 1.99 0.00 96.0102:31:48 PM all 0.13 0.00 0.38 5.63 0.00 93.8702:31:49 PM all 0.62 0.00 0.37 1.87 0.00 97.13...02:33:29 PM all 0.38 0.00 0.12 0.25 0.00 99.2502:33:30 PM all 0.00 0.00 0.25 0.00 0.00 99.7502:33:31 PM all 0.25 0.00 0.62 0.25 0.00 98.8802:33:32 PM all 0.37 0.00 0.25 0.00 0.00 99.3802:33:33 PM all 0.50 0.00 0.50 1.13 0.00 97.8702:33:34 PM all 0.25 0.00 0.25 0.25 0.00 99.25

  7. Performance monitoring • Physical memory utilization: OS Watcher nohup ./startOSW.sh 5 96 & zzz ***Sun Jun 21 23:04:59 CDT 2009 MemTotal: 49434036 kB MemFree: 34270564 kB Buffers: 354892 kB Cached: 12637136 kB SwapCached: 7732 kB Active: 10278772 kB Inactive: 4483804 kB HighTotal: 0 kB HighFree: 0 kB LowTotal: 49434036 kB LowFree: 34270564 kB SwapTotal: 8388600 kB SwapFree: 8239712 kB

  8. Performance monitoring • Storage utilization: EqualLogic SAN HeadQuarters

  9. Tuning Oracle memory size • The optimal size of SGA is the point where the marginal benefit of physical I/O reduction begins to decline • The optimal size of PGA is when the estimated PGA over-allocation size is 0

  10. Testing se rac scalability with 24 GB RAM per node – CPU utilization

  11. Testing se rac scalability with 24 GB RAM per node – memory and storage utilization

  12. Testing SE rac scalability with 24 GB RAM per node – overall performance analysis • As the systems near their maximum memory performance, they progressively utilize more swap space and the database starts to get unstable. • When comparing the CPU graph with the memory graph, at the maximum user load there is still ample CPU capability even once the test fails. • Given that the Oracle memory area (memory_target) is set to 13 GB to minimize physical I/O, even at the maximum 4-node cluster size the storage members were delivering IOPS well below their potential, suggesting that the storage disks were not the bottleneck for the overall performance.

  13. Testing se rac scalability with 48 GB RAM per node – CPU utilization

  14. Testing se rac scalability with 48 GB RAM per node – memory and storage utilization

  15. Testing SE rac scalability with 48 GB RAM per node – overall performance analysis • As the physical memory increased from 24 GB to 48 GB, the system performance was no longer limited by memory, thus the database was able to handle more transactions at higher user loads. Consequently, the PS4000XV members processed more I/O requests and showed much improved IOPS results comparing to the 24 GB configuration. • The corresponding disk latency performance was still within the acceptable range of 20 ms in all test durations of the 48 GB memory configurations, except towards the end of run on the 4-node RAC, when disk latency started to have a delayed response of above 20 ms. This indicates that the PS4000XV showed signs of stress after approximately 7000 user loads on the 4-node RAC.

  16. performance characteristics summary • The 48 GB Oracle RAC cluster scales better than its 24 GB counterpart. For the 24 GB configuration, the TPS measure scales in the range of 40% - 60% with the addition of each node. With 48 GB per node, the results demonstrate a near linear scalability, in the range of 75%-95% and that the benefit of additional memory became more apparent with each additional node. • With 48 GB of physical memory per node, both the CPU and memory resources become limited almost simultaneously. In an ideal situation, all the resources would be constrained simultaneously. Extra CPU capability without complementary memory may not lead to ideal scaling characteristics, and likewise extra memory without CPU capability may be unnecessary. • By adequately balancing memory, CPU and storage you can help make the most out of scaling out an Oracle RAC cluster. A bottleneck in any one component such as storage, CPU, or memory may result in non-optimal scaling. Likewise, scaling up of one component when another component is the bottleneck for the system may not add performance and may be unnecessary.

  17. Resources • Dell whitepaper: Maximizing Performance on a Cost Effective Oracle 11g SE RAC Database on Dell EqualLogic PS4000XV iSCSI Storage http://www.dell.com/downloads/global/solutions/Oracle_SE_RAC.pdf?c=us&cs=555&l=en&s=biz • Dell Oracle Solutions Engineering http://www.dell.com/oracle

  18. Q & A

More Related