Space: the final frontier – Oracle production workloads have stringent Storage & IO requirements with database Archivelog generation and database backup’s adding to performance and space demands with the ever-increasing database size.
Enabling, sustaining, and ensuring the highest possible performance along with continued application availability is a major goal for all mission critical Oracle applications to meet the demanding business SLA’s, all the way from on-premises to VMware Hybrid Clouds
- Slow database Archivelog performance will considerably slow the database down resulting in breach of SLA’s.
- Slow database Backups will result in database backup’s spilling outside the production backup window resulting in disruption of database operations and breach of SLA’s as well.
Key points to take away from this blog
This blog is an exercise to evaluate the feasibility and showcase the advantages of using VMware Cloud Flex Storage for business-critical Oracle workloads on VMware Cloud on AWS as a viable & comparable Storage option for
- Oracle Archivelog files destination
- Oracle Backup dump destination
Offloading Archivelog files and Backup dumps to VMware Cloud Flex Storage results in
- Providing storage & comparable performance to Archivelog & Backup activities for the database
- Ensuring Tier-1 vSAN Internal NVMe storage is reserved for hot/warm database data for capacity and performance.
This blog is not meant to be a performance benchmarking-oriented blog in any way. Remember, any performance data is a result of the combination of hardware configuration, software configuration, test methodology, test tool, and workload profile used in the testing, so the performance improvement I got with my workload in my lab is in no way representative of any real production workload which means the performance improvements for real world workloads will be better.
VMware Cloud Flex Storage
VMware Cloud Flex Storage is a Storage-as-a-Service offering for VMware Cloud on AWS that provides the ability to provision and scale storage independently of their SDDC hosts. VMware Cloud Flex Storage is fully VMware-managed and natively integrated, so you can cost-effectively supplement your capacity needs rather than purchasing additional SDDC hosts.
VMware Cloud Flex Storage provides disaggregated cloud storage for non-mission critical applications, such as file servers, analytics, and lower-tier databases. Disaggregated storage provides the performance of local storage with the flexibility of storage area networks.
More information on VMware Cloud Flex Storage can be found here and here.
Oracle Archivelog
Oracle Database lets you save filled groups of redo log files to one or more offline destinations, known collectively as the archived redo log.
The process of turning redo log files into archived redo log files is called archiving. This process is only possible if the database is running in ARCHIVELOG mode
When the database is running in ARCHIVELOG mode, the log writer process (LGWR) cannot reuse and hence overwrite a redo log group until it has been archived. The background process ARCn automates archiving operations when automatic archiving is enabled. The database starts multiple archiver processes as needed to ensure that the archiving of filled redo logs does not fall behind.
More information on Oracle Archivelog can be found here.
Oracle Backup
In general, the purpose of a backup and recovery strategy is to protect the database against data loss and reconstruct the database after data loss.
Typically, backup administration tasks include the following:
- Planning and testing responses to different kinds of failures
- Configuring the database environment for backup and recovery
- Setting up a backup schedule
- Monitoring the backup and recovery environment
- Troubleshooting backup problems
- Recovering from data loss if the need arises
More information on Oracle Backup can be found here.
Test Bed
The Test bed is a 4 Node VMware Cloud on AWS cluster with the setup shown as below –
- vCenter version was 8.0.0 build 20432146
- 4 Amazon EC2 i3.metal ESXi servers
- VMware ESXi, 8.0.0, 20430035
- VMware Cloud Flex Storage (NFS v3)
- Oracle 21.5 with Grid Infrastructure, ASM Storage and ASMLIB
- OEL UEK 8.7
VMware Cloud Flex Storage datastore details are shown as below.
‘vSAN Default Storage Policy’ details are shown as below.
Custom Oracle Policy ‘Oracle Custom Policy – No Mirroring´ was also created to check the effects Mirroring v/s No Mirroring on the performance metrics.
Oracle VM ‘Oracle21C-OL8-Customer-DB-FlexStorage’ details are shown as below.
The VM has 18 vCPU’s, 128GB RAM, the single instance database ‘ORA21C’ was created with created with multi-tenant option & provisioned with Oracle Grid Infrastructure (ASM) and Database version 21.5 on O/S OEL UEK 8.7.
Oracle ASM was the storage platform with Oracle ASMLIB for device persistence. Oracle SGA & PGA set to 64G and 10G respectively.
All Oracle on VMware platform best practices were followed.
The vmdk’s for the Oracle VM ‘Oracle21C-OL8-Customer’ are shown as below –
- Hard Disk 1 (SCSI 0:0) – 80G for OS (/)
- Hard Disk 2 (SCSI 0:1) – 80G for Oracle Grid Infrastructure & RDBMS binaries
- Hard Disk 3 (SCSI 1:0) – 100G – ASM Disk Group GRID_DG (Grid Infrastructure)
- Hard Disk 4 (SCSI 1:1) – 200G – ASM Disk Group DATA_DG (Oracle database)
- Hard Disk 5 (SCSI 3:0) – 100G – ASM Disk Group REDO_DG (Redo Log Files)
- Hard Disk 6 (SCSI 2:0) – 250G – ASM Disk Group SLOB_DG (SLOB load generator)
- Hard Disk 7 (SCSI 1:2) – 125G – ASM Disk Group FRA_VSAN_DG (vSAN WorkloadDatastore for Oracle Archivelog files)
- Hard Disk 8 (SCSI 1:3) – 130G – ASM Disk Group FRA_FLEX_DG (VMware Cloud Flex Storage for Oracle Archivelog files)
Oracle VM ‘Oracle21C-OL8-Customer’ vmdk’s provisioning and storage policy details
- vmdk (Hard Disk 8) for ASM Disk group FRA_FLEX_DG is provisioned on VMware Cloud Flex Storage
- Rest of the vmdk’s are provisioned on vSAN WorkloadDatastore
- vmdk (Hard Disk 6) for ASM Disk group SLOB_DG and vmdk (Hard Disk 7) for ASM Disk group FRA_VSAN_DG
- provisioned first for Test 1 using custom storage policy – ‘Oracle Custom Policy – No Mirroring´
- provisioned next for Test 2 using default VSAN storage policy – ‘vSAN Default Storage Policy’
- Rest of vmdk’s provisioned again for Test 2 using default VSAN storage policy – ‘vSAN Default Storage Policy’
Details of vSAN & Flex Storage vmdk’s for Oracle Archivelog files locations (FRA_VSAN_DG & FRA_FLEX_DG) using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ are as shown below.
Oracle ASM Disk Group details are as below:
grid@oracle21c-ol8:+ASM:/home/grid> asmcmd lsdg
State Type Rebal Sector Logical_Sector Block AU Total_MB Free_MB Req_mir_free_MB Usable_file_MB Offline_disks Voting_files Name
MOUNTED EXTERN N 512 512 4096 1048576 204799 0 0 0 0 N DATA_DG/
MOUNTED EXTERN N 512 512 4096 4194304 133116 119464 0 119464 0 N FRA_FLEX_DG/
MOUNTED EXTERN N 512 512 4096 4194304 127996 127900 0 127900 0 N FRA_VSAN_DG/
MOUNTED EXTERN N 512 512 4096 4194304 102396 102292 0 102292 0 N GRID_DG/
MOUNTED EXTERN N 512 512 4096 1048576 102399 23755 0 23755 0 N REDO_DG/
MOUNTED EXTERN N 512 512 4096 4194304 255996 9900 0 9900 0 N SLOB_DG/
grid@oracle21c-ol8:+ASM:/home/grid>
Oracle ASM disks details are as below:
grid@oracle21c-ol8:+ASM:/home/grid> asmcmd lsdsk -k
Total_MB Free_MB OS_MB Name Failgroup Site_Name Site_GUID Site_Status Failgroup_Type Library Label Failgroup_Label Site_Label UDID Product Redund Path
204799 0 204799 DATA_DISK01 DATA_DISK01 00000000000000000000000000000000 REGULAR ASM Library – Generic Linux, version 2.0.17 (KABI_V2) DATA_DISK01 UNKNOWN ORCL:DATA_DISK01
133116 119464 133119 FRA_FLEX_DISK01 FRA_FLEX_DISK01 00000000000000000000000000000000 REGULAR ASM Library – Generic Linux, version 2.0.17 (KABI_V2) FRA_FLEX_DISK01 UNKNOWN ORCL:FRA_FLEX_DISK01
127996 127900 127999 FRA_VSAN_DISK01 FRA_VSAN_DISK01 00000000000000000000000000000000 REGULAR ASM Library – Generic Linux, version 2.0.17 (KABI_V2) FRA_VSAN_DISK01 UNKNOWN ORCL:FRA_VSAN_DISK01
102396 102292 102399 GRID_DISK01 GRID_DISK01 00000000000000000000000000000000 REGULAR ASM Library – Generic Linux, version 2.0.17 (KABI_V2) GRID_DISK01 UNKNOWN ORCL:GRID_DISK01
102399 23755 102399 REDO_DISK01 REDO_DISK01 00000000000000000000000000000000 REGULAR ASM Library – Generic Linux, version 2.0.17 (KABI_V2) REDO_DISK01 UNKNOWN ORCL:REDO_DISK01
255996 9900 255999 SLOB_DISK01 SLOB_DISK01 00000000000000000000000000000000 REGULAR ASM Library – Generic Linux, version 2.0.17 (KABI_V2) SLOB_DISK01 UNKNOWN ORCL:SLOB_DISK01
grid@oracle21c-ol8:+ASM:/home/grid>
Test Use case
This blog vetted out the Oracle Archivelog performance, using vSAN WorkloadDatastore first and then using VMware Cloud Flex Storage as the archivelogs destination.
The second blog in this series Providing Storage savings and Comparable performance using VMware Cloud Flex Storage for Oracle Backups is based on on vetting out the Oracle Backup performance, using vSAN WorkloadDatastore first and then using VMware Cloud Flex Storage as the Backup destination.
Our 1st test was to run SLOB workload generator against a 250G ASM disk backed vmdk to study Oracle Archivelog performance with the below storage locations and study the workload test results
- vSAN WorkloadDatastore
- VMware Cloud Flex Storage
We used 2 different storage policies to study the effect of storage policies with Oracle Archivelog metrics
- Test 1 using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ for SLOB & Archivelog vmdk
- Test 2 using default VSAN storage policy – ‘vSAN Default Storage Policy’ for SLOB & Archivelog vmdk
SLOB 2.5.4.0 was chosen as the load generator for this exercise with following SLOB parameters set as below:
- UPDATE_PCT=100
- RUN_TIME=300
- SCALE=40G
- WORK_UNIT=3
We deliberately chose the minimum Work Unit size to drive the most amount of IO without stressing REDO to study the performance metrics differences between the 2 scenarios, Oracle Archivelog performance with both vSAN WorkloadDatastore and VMware Cloud Flex Storage. The number of threads was set to 50.
We ran multiple SLOB runs for the 2 above use cases below and compared the Oracle & GOS metrics;
With the Oracle Archivelog location on vSAN WorkloadDatastore v/s VMware Cloud Flex Storage, we achieved comparable performance
- using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ for SLOB & Archivelog vmdk
- Executes (SQL)/sec – Difference was 4.1% and run results were predictable and consistent
- Transactions/sec – Difference was 4.1% and run results were predictable and consistent
- using default VSAN storage policy – ‘vSAN Default Storage Policy’ for SLOB & Archivelog vmdk
- Executes (SQL)/sec – Difference was 2.2% and run results were predictable and consistent
- Transactions/sec – Difference was 2.2% and run results were predictable and consistent
In conclusion, we were able to get comparable results with the Oracle Archivelogs on VMware Cloud Flex Storage as compared to vSAN WorkloadDatastore.
For real production workloads, the best practice is use vSAN component level protection (RAID 1 , RAID 5/6) for greater resiliency , in addition, OSR=100% is not needed as well , we did this anyways jut to prove that vSAN performs equally well without those options.
For SLOB & Archivelog vmdk’s using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ & Rest of vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy – looking at GOS Load statistics, we can see that the %user , %system, %idle & %IO wait time statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk.
For all vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy’ – looking at GOS Load statistics, we can see that the %user , %system, %idle & %IO wait time statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk.
For SLOB & Archivelog vmdk’s using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ & Rest of vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy – looking at GOS disk statistics, we can see that the disk statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk.
For all vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy’ – looking at GOS disk statistics, we can see that the disk statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk.
Remember, any performance data is a result of the combination of hardware configuration, software configuration, test methodology, test tool, and workload profile used in the testing, so the performance improvement I got with my workload in my lab is in no way representative of any real production workload which means the performance improvements for real world workloads will be better.
Summary
This blog is an exercise to evaluate the feasibility and showcase the advantages of using VMware Cloud Flex Storage for business-critical Oracle workloads on VMware Cloud on AWS as a viable & comparable Storage option for
- Oracle Archivelog files destination
- Oracle Backup dump destination
Offloading Archivelog files and Backup dumps to VMware Cloud Flex Storage results in
- Providing storage & comparable performance to Archivelog & Backup activities for the database
- Ensuring Tier-1 vSAN Internal NVMe storage is reserved for hot/warm database data for capacity and performance.
This blog vetted out the Oracle Archivelog performance, using vSAN WorkloadDatastore first and then using VMware Cloud Flex Storage as the archivelogs destination. The 2nd blog in this series will be based on vetting out the Oracle Backup performance, using vSAN WorkloadDatastore first and then using VMware Cloud Flex Storage as the Backup destination.
Our 1st test was to run SLOB workload generator against a 1 TB ASM disk backed vmdk to study Oracle Archivelog performance with the below storage locations and study the workload test results
- vSAN WorkloadDatastore
- VMware Cloud Flex Storage
We used 2 different storage policies to study the effect of storage policies with Oracle Archivelog metrics
- Test 1 using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ for SLOB & Archivelog vmdk
- Test 2 using default VSAN storage policy – ‘vSAN Default Storage Policy’ for SLOB & Archivelog vmdk
With the Oracle Archivelog location on vSAN WorkloadDatastore v/s VMware Cloud Flex Storage, we achieved comparable performance
- using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ for SLOB & Archivelog vmdk
- Executes (SQL)/sec – Difference was 4.1% and run results were predictable and consistent
- Transactions/sec – Difference was 4.1% and run results were predictable and consistent
- using default VSAN storage policy – ‘vSAN Default Storage Policy’ for SLOB & Archivelog vmdk
- Executes (SQL)/sec – Difference was 2.2% and run results were predictable and consistent
- Transactions/sec – Difference was 2.2% and run results were predictable and consistent
- Looking at GOS Load statistics
- For SLOB & Archivelog vmdk’s using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ & Rest of vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy -, we can see that the %user , %system, %idle & %IO wait time statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk.
- For all vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy’ – looking at GOS Load statistics, we can see that the %user , %system, %idle & %IO wait time statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk.
- Looking at GOS disk statistics
- For SLOB & Archivelog vmdk’s using custom storage policy – ‘Oracle Custom Policy – No Mirroring´ & Rest of vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy -, we can see that the disk statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk
- For all vmdk’s using default VSAN storage policy – ‘vSAN Default Storage Policy’ – looking at GOS disk statistics, we can see that the disk statistics are comparable across vSAN WorkloadDatastore and VMware Cloud Flex Storage vmdk.
This blog is not meant to be a performance benchmarking-oriented blog in any way. Remember, any performance data is a result of the combination of hardware configuration, software configuration, test methodology, test tool, and workload profile used in the testing, so the performance improvement I got with my workload in my lab is in no way representative of any real production workload which means the performance improvements for real world workloads will be better.
Acknowledgements
This blog was authored by Sudhir Balasubramanian, Senior Staff Solution Architect & Global Oracle Lead – VMware.
Conclusion
Space: the final frontier – Oracle production workloads have stringent Storage & IO requirements with database archiving, database backup’s adding to the space demands with the ever-increasing database size.
Enabling, sustaining, and ensuring the highest possible performance along with continued application availability is a major goal for all mission critical Oracle applications to meet the demanding business SLA’s, all the way from on-premises to VMware Hybrid Clouds
- Slow database Archivelog performance will considerably slow the database down which will result in a breach of SLA’s.
- Slow database Backups will result in database backup’s spilling outside the production backup window resulting in disruption of database operations and a breach of SLA’s as well.
All Oracle on VMware vSphere collaterals can be found in the url below.
Oracle on VMware Collateral – One Stop Shop
https://blogs.vmware.com/apps/2017/01/oracle-vmware-collateral-one-stop-shop.html
