Improving Oracle OLTP database performance with Dell Fluid Cache for DAS This technical whitepaper describes how the performance of an Oracle Online Transaction Processing database can be improved by using Dell Fluid Cache for Direct Attach Storage.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS 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. © 2013 Dell Inc. All rights reserved. Dell and its affiliates cannot be responsible for errors or omissions in typography or photography. Dell, the Dell logo, and PowerEdge are trademarks of Dell Inc.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Contents Executive summary ..................................................................................................... 5 Introduction .............................................................................................................. 5 Dell Fluid Cache for DAS Overview ................................................................................. 6 Solution design and reference architecture ....................
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Table 5. Virtual Disk configuration for Fluid Cache based solution. ....................................... 23 Table 6. Software Versions ........................................................................................ 23 Table 7. ASM disk group configuration for the baseline ...................................................... 28 Table 8. ASM disk group configuration for the Fluid Cache based solution ................
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Executive summary With increasing capacity and more affordable prices, the very fast, low-latency, flash-based Peripheral Component Interconnect Express (PCIe) Solid State Devices (SSDs) are finding a place in modern enterprise data centers. As a result, many enterprise hardware companies have begun to integrate and offer these PCIe SSDs in their commodity servers.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS This whitepaper studies the performance of an Oracle OLTP database in a Fluid Cache based solution. It compares the performance of the Fluid Cache based solution against a traditional HDD-based storage solution used as the baseline configuration.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS To accurately and systematically evaluate both the solutions, the first step was to design a baseline configuration based on the traditional HDD-based storage. This baseline configuration was enhanced with Fluid Cache solution. This approach allows a fair comparison of the performance merits or demerits of Fluid Cache solution against the baseline configuration.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Figure 1. Architecture: Baseline configuration As shown in Figure 1, eight virtual disks (VDs) were configured for Oracle data files and four VDs were configured for Oracle Flash Recovery Area (FRA) across the four enclosures. Twelve disks were configured as global hot spares to ensure that there is at least one hot spare for each of the twelve VDs used for Oracle database.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS For more details on the configuration of the ASM disk groups, refer to Table 7. For the rest of the paper, the ASM disks added to the ‘DATA_DG’ will be referred to as data ASM disks and the ASM disks added to the ‘FRA_DG’ will be referred to as FRA ASM disks. For more details on the hardware and software configuration of the baseline, refer to the appendices.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Figure 2. Architecture: Fluid Cache based storage solution When Fluid Cache is configured with the cache pool (SSDs) and the caching on the disks to be cached (data ASM disks) are enabled, a new Fluid Cache disk is created for each of the data ASM disks. Example: If /dev/sdc is the block device configured as an ASM disk, then a new Fluid Cache disk named /dev/fldc0 gets created that maps to this ASM disk.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Test methodology This section describes the test environment, test methodologies and the tools used to benchmark the two solutions described in the Solution design and reference architecture section. Most of the steps described in the Baseline configuration test methodology sub-section are used for Fluid Cache based solution testing as well.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS 2.1 The BIOS System Profile was changed to ‘Performance’. Rest of the BIOS options were at default settings. 2.2 For details on VD configuration, refer to Table 4. 2.3 The Oracle ASM disk ownership and permission settings were configured in the /etc/udev rules. For more details, refer to Appendix C.2.1. Udev settings for baseline configuration. 2.4 The OS kernel parameters were tuned for performance testing.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS 9. 10. 11. 12. 8.1 Before each test, it was ensured that the Open Manage ‘dataeng’ service was stopped to avoid any performance overhead. 8.2 Before each test, it was ensured that Oracle AWR report was not being collected to avoid any performance overhead. Test start: 9.1 The OSWatcher script was started on the database server to capture the performance metrics. 9.2 The OLTP workload setup in step 8 was started from the BMF server.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS 6.4 Re-setup Fluid Cache as noted in Appendix C.1.1 Enabling Fluid Cache. 6.5 Repeat step 5 to run the test again, if necessary. 7. Disabling Fluid Cache: 7.1 Fluid Cache was disabled. For details on the steps followed, refer to Appendix C.1.2 Removing Fluid Cache.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS TPS OLTP (R720) - Transactions/Sec (TPS) 1800 1600 1400 1200 1000 800 600 400 200 0 500 900 1300 1700 2100 2500 2900 3300 3700 4100 4500 User Load Baseline Figure 4. Fluid Cache Performance graph: TPS behavior spanning entire test duration As seen in Figure 4, both the storage solutions deliver similar TPS until a certain user load.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS The CPU utilization captured during the benchmarking tests using the OSWatcher tool helps to further investigate the cause of the sharp increase in the ART. As seen in Figure 6, the peak CPU IOWait for the baseline configuration is around 60%, while the peak CPU IOWait for the Fluid Cache based solution is around 10%. A high CPU IOWait typically indicates that the CPU is waiting on the backend storage I/O to finish.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS OLTP (R720) - Total CPU Utilization 100 80 % 60 40 20 0 Increasing User Load --> Baseline Figure 7. Fluid Cache Performance graph: Total CPU Utilization Figure 7 shows, that in the case of the Fluid Cache based solution, as the user load keeps increasing, the total CPU utilization also keeps increasing.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS OLTP (R720) - Max TPS 1800 1577 1600 1400 60% TPS 1200 983 1000 800 600 400 200 0 Baseline Figure 8. Fluid Cache Performance graph: Max TPS performance Figure 8 compares the max TPS performance of the baseline and the Fluid Cache based solution. As can be seen from that graph, Fluid Cache based solution delivers 60% more TPS than the baseline configuration. OLTP (R720) - ART at 3100 User Load 1.8 1.712 1.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Figure 9 compares the average response time (ART) performance of both the storage solutions at 3100 user load. As can be seen from the graph, the Fluid Cache based solution delivers 95% lower ART at 3100 user load, which is near the peak performance of the baseline configuration. Figure 10 compares the maximum user load that the two storage solutions were able to deliver while keeping the response time at two seconds or less.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Existing traditional spinning HDD-based storage environments that cannot grow in performance because storage controller has reached its maximum processing capabilities. This can be achieved by replacing their existing single node database server with a Dell’s PowerEdge server that supports Dell PowerEdge Express Flash PCIe SSDs and configured with Dell Fluid Cache for DAS.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Appendix A. Server configuration This section describes the details of the hardware configuration used for each of the storage solutions described in the Solution design and reference architecture section. Table 1. Server configuration details for baseline configuration Server Configuration Database Server PowerEdge R720 Bios: v1.5.1 iDRAC7 ESM FW: v1.30.30 LC2: v1.1.0.1108 Backplane, 12G SEP FW: v1.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Appendix B. Backend storage configuration This section describes the details of the backend storage configuration used for each of the storage solutions described in the Solution design and reference architecture section. Table 3. Backend storage configuration Backend Storage Configuration Storage 4 x Dell PowerVault MD1220s storage enclosures Backplane (primary & secondary) HW rev. A01; FRU configuration Image rev.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS * Virtual Disk (VD) OS Block Device RAID Group (RG) Type #HDDs/ RG VD Size Cache Policy Segment Size FRA4 /dev/sdn1* 5 5 544.50GB r=ARA^;w=WB 64KB + ^ - single disk partition spanning the entire disk; - No Read Ahead; - Adaptive Read Ahead Table 5. Virtual Disk configuration for Fluid Cache based solution.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Appendix C.1. Fluid Cache based solution configuration Appendix C.1.1 Enabling Fluid Cache This section describes the steps to configure Fluid Cache on the Express Flash PCIe SSDs and on the backend store. This section assumes that the Fluid Cache for DAS v1.0.0 software has already been installed and the fluid-cache service is up and running. 1. Stop the Oracle High Availability Service (OHAS) stack 1.1. $> su – grid 1.2.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS 6.4. SQL> exit 6.5. Run the following command as grid user to check if the entire OHAS stack is up and running 6.5.1. grid@$> crsctl stat –res -t Appendix C.1.2 Removing Fluid Cache This section describes the steps to disable and remove Fluid Cache. 1. Stop the Oracle High Availability Service (OHAS) stack 1.1. $> su – grid 1.2. grid@$> sqlplus / as sysasm 1.3. SQL> crsctl stop has 1.4. SQL> exit 2.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS 7.1. $> su – grid 7.2. grid@$> sqlplus / as sysasm 7.3. SQL> crsctl start has 7.4. SQL> exit 7.5. Run the following command as grid user to check if the entire OHAS stack is up and running 7.5.1. grid@$> crsctl stat –res -t Appendix C.2. Setting up ownership and permission for Oracle disks This section describes the udev rules that need to be set for both the baseline and the Fluid Cache based storage solution. Appendix C.2.1.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS KERNEL=="sd*", PROGRAM="scsi_id --page=0x83 --whitelisted -device=/dev/%k", RESULT=="", OWNER:="grid", GROUP:="asmadmin", MODE:=”0660” KERNEL=="sd*", PROGRAM="scsi_id --page=0x83 --whitelisted -device=/dev/%k", RESULT=="", OWNER:="grid", GROUP:="asmadmin", MODE:=”0660” KERNEL=="sd*", PROGRAM="scsi_id --page=0x83 --whitelisted -device=/dev/%k", RESULT=="", OWNER:="grid", GROUP:="asmadmin", MODE
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS KERNEL=="sd*", PROGRAM="scsi_id --page=0x83 --whitelisted -device=/dev/%k", RESULT=="", OWNER:="grid", GROUP:="asmadmin", MODE:=”0660” # KERNEL=="sd*", PROGRAM="scsi_id --page=0x83 --whitelisted -device=/dev/%k", RESULT=="", OWNER:="grid", GROUP:="asmadmin", MODE:=”0660” # KERNEL=="sd*", PROGRAM="scsi_id --page=0x83 --whitelisted -device=/dev/%k", RESULT=="", OWNER:="grid", GROUP:="asmadmin",
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS ASM Diskgroup Name FRA_DG Diskgroup Member Disks (ASM disks) ASM Size Redundancy Allocation Unit (AU) Size Grid and Database Compatibility /dev/sde1, /dev/sdh1, /dev/sdk1, /dev/sdn1 external 1MB (default) 11.2.0 Table 8.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Parameter Value kernel.shmall 1073741824 kernel.shmmax 135400749056 kernel.shmmni 4096 kernel.sem 250 32000 100 142 Appendix C.4.2. User security limits settings The following user security limits were set and appended to the /etc/security/limits.conf file according to Dell and Oracle’s recommendations and best practices for all the solution testing.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS Appendix D. Next Steps Appendix D.1.
Improving Oracle OLTP database performance with Dell Fluid Cache for DAS is thriving to help eliminate deployment risks. Foglight for Oracle empowers you to take action immediately and maximize your Oracle database resources. SharePlex® for Oracle is a simple, affordable, impact-free Oracle database replication solution. SharePlex is a mature, high-performance, high availability technology that offers a low-cost alternative to other Oracle replication tools.
