Improving the performance of single instance Oracle on file systems, January 2008

ManualsBrandsHP ManualsSoftwareHP Serviceguard Storage Management Suite Software

Improving the performance of single instance

Oracle on file systems:

Analyzing the impact of Oracle Disk Manager (ODM) — a

feature of HP Serviceguard Storage Management Suite

Executive summary............................................................................................................................... 2

Introduction......................................................................................................................................... 3

Purpose .......................................................................................................................................... 3

Audience ........................................................................................................................................ 4

Terminology .................................................................................................................................... 4

Related documents ........................................................................................................................... 5

Oracle Disk Manager (ODM)................................................................................................................ 5

Enabling ODM ................................................................................................................................ 6

Online JFS File System Mount Options ................................................................................................ 6

Experimental configuration ................................................................................................................... 7

Test results .......................................................................................................................................... 8

Results: Single-Instance Oracle 9.2.0.7............................................................................................... 9

Experiment 1: Raw Volumes on VxVM 4.1 ...................................................................................... 9

Experiment 2: Online JFS on VxVM volumes with ODM enabled...................................................... 10

Experiment 3: Comparison of Online JFS without ODM enabled...................................................... 10

Experiment 4: Comparing two common LVM configurations to Online JFS with ODM enabled ............ 11

Oracle 9.2.0.7 results summary....................................................................................................... 13

Oracle 10.2.0.2 results summary..................................................................................................... 14

Results .............................................................................................................................................. 14

Test results and production environments........................................................................................... 15

Conclusions ...................................................................................................................................... 15

Appendix A – SMS bundles containing ODM ....................................................................................... 16

Appendix B – Experimental setup and test method.................................................................................16

Test method................................................................................................................................... 17

For more information.......................................................................................................................... 20

Summary of content (20 pages)

PAGE 1
Improving the performance of single instance Oracle on file systems: Analyzing the impact of Oracle Disk Manager (ODM) — a feature of HP Serviceguard Storage Management Suite Executive summary............................................................................................................................... 2 Introduction......................................................................................................................................... 3 Purpose .............................
PAGE 2
Executive summary An Oracle® database that runs on a file system is easier to manage than one that uses raw partitions or volumes, but raw volumes or partitions are often chosen because file systems are generally slower. However, Oracle Disk Manager (ODM) can bridge this gap in performance, making file systems an acceptable choice.
PAGE 3
Introduction HP SG SMS includes seven product bundles2, four of which provide a robust, feature-rich environment for Oracle database installations. In combination, HP Serviceguard and Veritas Storage Foundation provide a solid base for enterprise applications that must run with exceptional availability while providing a foundation that is reliable, is easy-to-manage, and gets the best performance from the hardware platform it runs on.
PAGE 4
Audience This paper is for customers, presales personnel, and field personnel wanting to understand the possible performance benefits of ODM included in SG SMS for Oracle bundles. The reader must have some knowledge of the following: • Oracle databases • Volume managers (LVM and VxVM) • Performance benchmarking Terminology Term Definition Balanced Configuration An end-to-end system tuned so there are no bottlenecks to limit performance.
PAGE 5
Related documents The following documents were valuable when conducting the benchmarks and writing this white paper. • “Application use cases for the HP Serviceguard Storage Management Suite,” in HP Serviceguard Storage Management Suite at http://docs.hp.com Æ High Availability Æ HP Serviceguard Storage Management Suite. • “Oracle Disk Manager,” April 2001, at http://www.oracle.com/technology/deploy/availability/pdf/odm_wp.pdf . • “Oracle Disk Manager” by Nitin Vengurlekar, at http://www.oracle.
PAGE 6
For ODM to function properly on HP-UX, the asynchronous disk device driver must be installed and enabled for the Oracle instance. When raw volumes are in use, you should disable ODM. The asynchronous disk device driver is sufficient for providing the needed performance.4 Enabling ODM After installing the SG SMS bundles, the ODM library for Online JFS is in /opt/VRTSodm/lib/libodm.sl. After Oracle 9i or 10g is installed, a stub library is present in the $ORACLE_HOME/lib directory.
PAGE 7
The mount options used in these experiments are as follows: Table 2 Online JFS mount options used Option Meaning ioerror=mwdisable This option (meta-data write disable) is the default policy for handling I/O errors. On meta-data write errors, the file system would be disabled; otherwise, it is degraded. qio By default, Quick I/O is enabled, meaning that any Quick I/O files are recognized as such by the file system.
PAGE 8
• A 1 Gb dedicated private network between driver and SUT was used to provide sufficient network bandwidth. (Typically, this can handle up to 106 TPM, two orders of magnitude higher than the TPM measured in these experiments.) • Because we wanted to compare the relative performance of I/O subsystems (above the level of the disk array), two high performance HP StorageWorks EVA 3000 disk arrays were connected to the SUT. Each disk array contained 55 spindles (each 36 GB @15K rpm).
PAGE 9
In addition, Online JFS and VxVM version 4.1 were used in both benchmarks, and all servers were running 11iv2 HP-UX 11.23.0609 MCOE with hardware enablement patches for 11i v2 September 2006. Results: Single-Instance Oracle 9.2.0.7 The following results were obtained. Experiment 1: Raw Volumes on VxVM 4.1 The TPM numbers for a workload of 20 - 140 clients is shown in Figure 2, along with CPU usage. For 80 clients or more, the measured TPM leveled out at about 47,000, and %idle time fell to about 1%.
PAGE 10
Experiment 2: Online JFS on VxVM volumes with ODM enabled The next configuration measured the TPM on Online JFS with ODM enabled, to find the relative performance of the ODM-enabled file system versus raw volumes on VxVM 4.1. Figure 3 Oracle 9.2.0.7 – Comparing Online JFS and ODM on VxVM 4.1 versus raw VxVM volumes The results show a nearly 8% degradation in performance relative to raw volumes on VxVM v4.1 for workloads of 80 or more clients.
PAGE 11
Figure 4 Oracle 9.2.0.7 – The loss in performance when ODM is not present Expressed as a percentage drop relative to raw volumes on VxVM, about 94% of transactions are lost. Doing the same calculation but relative to VxFS/VxVM+ODM, about 92% of transactions are lost. With greater partitioning and many more data files, the loss would likely not be as dramatic, although implementing such partitioning would tend to negate the benefits in manageability that the use of a file system can bring.
PAGE 12
Figure 5 Oracle 9.2.0.7 – Comparing two common LVM configurations with ODM To allow a comparison of raw-on-LVM volumes and raw-on-VxVM volumes, the chart including CPU utilization is shown in Figure 6. The TPM levels off at about 49,000, with a user CPU consumption of 85% and system CPU usage of 14%. These values account for an approximately 4% increase in performance over the raw VxVM configuration measured in Experiment 1. Figure 6 Oracle 9.2.0.
PAGE 13
Oracle 9.2.0.7 results summary Figure 7 summarizes the measurements made in the Oracle 9i benchmark. The TPM numbers were charted together and normalized to 100% for each workload’s raw-on-LVM TPM value. Figure 7 Oracle 9.2.0.
PAGE 14
Oracle 10.2.0.2 results summary The second benchmark was performed using Oracle 10.2.0.2. The combined results for four configurations are shown in Figure 8. Figure 8 Oracle 10.2.0.2 – Results summary with results normalized to LVM=100% Results This review did not intend to use specially modified versions, tuned for optimal performance, to achieve the highest possible TPM numbers.
PAGE 15
In the Oracle 10g benchmark, the reduction in performance (without ODM) was still significant though lower. There was a reduction in performance of about 68%7 for the same workload of 80 clients. The benchmarks used different OLTP applications modified for the two versions of Oracle, with slight differences in the number of data files and database warehouse structure built.
PAGE 16
Appendix A – SMS bundles containing ODM Oracle Disk Manager is included in the Oracle and Oracle Premium bundles of HP SG SMS. SG SMS vA.01.00 is supported on HP-UX 11i v2 and contains the Veritas 4.1 products. Table 3 lists the specific products that contain ODM. Table 3 SG SMS A.01.00 bundles containing ODM Bundles with ODM, including SG A.11.
PAGE 17
This provided a workaround to a problem on 11i v2 where it takes a long time for Oracle processes to attach to the database.8 This workaround is not needed on 11i v3. For the Online JFS configurations, an 8 K block size was chosen and mounted on the logical volumes. Test method To help to ensure consistency of results, different workloads were used in increments of 20 clients and each data point was repeated.
PAGE 18
Figure B-1 Automation program excerpt • Reboot the SUT and reconfigure volume groups • Remount file systems if necessary server=sut1 • Establish links identifying Oracle data files driver=driver1 • Enable or disable ODM, depending on the configuration • Start and shut down the Oracle Database on the SUT export RAW_LVM_DB=/dev/vgram_lvm • Start and stop the Oracle listener for the database instance on the SUT export RAW_LVM_LOGS=/dev/vgram_lvm_logs • Restore a gold (backup) copy of the datab
PAGE 19
After running the Oracle database on the SUT for a period of time and with the default HP-UX kernel setting dbc_max_pct=50%, some variability in the TPM results occurred. To solve this, rebooting the SUT between test runs eliminated variation between measurements and the kernel parameter dbc_max_pct was set to 5%, to ensure that the HP-UX buffer cache did not grow to a point that would lead to memory pressure on Oracle, skewing the results.
PAGE 20
For more information http://docs.hp.com http://www.oracle.com/technology/deploy/availability/pdf/odm_wp.pdf . http://www.oracle.com/technology/deploy/availability/pdf/nitin_ODM.pdf © 2008 Hewlett-Packard Development Company, L.P. The information contained herein is subject to change without notice. The only warranties for HP products and services are set forth in the express warranty statements accompanying such products and services.