Home > Workload Solutions > Oracle > Best Practices > Intel-Based Oracle Best Practices on Dell PowerEdge R750xs and PowerStore 5000T > Storage Best Practices > Day One Best Practices > PowerStore: Adding Storage LUNs to ASM Disk Groups
Storage configuration can directly influence database performance. This best practice validates that adding multiple storage LUNs for ASM disk groups (DG) optimizes database performance.
Category | PowerStore Storage |
Product | PowerStore 5000T |
Type of best practice | Performance Optimization |
Day and value | Day 1, Highly recommended |
Overview
To determine if a best practice provides value, it had to be tested against a baseline configuration of the database to validate performance. In the baseline configuration for database volumes, the DATA, REDO, and FRA disk groups each consists of one LUN. Table 1 shows the baseline volume groups and LUNs configuration.
The baseline storage configuration involved separating database files into dedicated volume groups. For example, the Data files, Online Redo logs, and Flash Recovery Area (FRA) files are in the same volume group but separated from the Grid volume group and the Temp volume group. This allows the DBA to only snapshot the database without including the Grid and Temp files which are not necessary for a functional database snapshot. This also provides space savings if replication is used.
Table 1: Baseline storage group configuration
Volume Group | Volume Name | VMware Datastore | Volume size (GB) | Notes |
orabp22-vm1-os | orabp22-vm1-os | orabp22-vm1-os-ds | 400 | Operating System |
orabp22-vm1-grid | orabp22-vm1-grid-001 | orabp22-vm1-grid-001-ds | 50 | GRID |
orabp22-vm1-grid-002 | orabp22-vm1-grid-002-ds | 50 | ||
orabp22-vm1-grid-003 | orabp22-vm1-grid-003-ds | 50 | ||
orabp22-vm1-db1 | orabp22-vm1-db1-data-001 | orabp22-vm1-data-001-ds | 1000 | Data files |
orabp22-vm1-db1-redo-001 | orabp22-vm1-redo-001-ds | 55 | Online Redo logs | |
orabp22-vm1-db1-fra-001 | orabp22-vm1-fra-001-ds | 60 | Flash Recovery Area | |
orabp22-vm1-temp | orabp22-vm1-db1-temp | orabp22-vm1-temp-ds | 500 | Temp files |
Table 2 shows the optimized PowerStore storage configuration. The volume groups remain intact, but additional LUNs were added for the DATA, REDO, and FRA disk groups. Using multiple LUNs for Oracle ASM disk groups improves database performance. The primary benefit of using multiple LUNs is that the operating system creates an I/O queue path per LUN. Since Oracle ASM spreads data evenly across all disks in a disk group, this would reduce any I/O bottleneck and provide higher I/O concurrency providing performance optimization and utilization. The following table shows the optimized PowerStore storage configuration for the Oracle database. In the optimized storage configuration, DATA, REDO, and FRA disk groups each has four LUNs. Compared to the baseline configuration, there are four times the number of I/O queue paths.
Table 2: Optimized PowerStore storage configuration
Volume Group | Volume Name | VMware Datastore | Volume size (GB) | Notes |
orabp22-vm1-os | orabp22-vm1-os | orabp22-vm1-os-ds | 400 | Operating System |
orabp22-vm1-grid | orabp22-vm1-grid-001 | orabp22-vm1-grid-001-ds | 50 | GRID |
orabp22-vm1-grid-002 | orabp22-vm1-grid-002-ds | 50 | ||
orabp22-vm1-grid-003 | orabp22-vm1-grid-003-ds | 50 | ||
orabp22-vm1-db1 | orabp22-vm1-db1-data-001 | orabp22-vm1-data-001-ds | 1000 | Data files |
orabp22-vm1-db1-data-002 | orabp22-vm1-data-002-ds | 1000 | ||
orabp22-vm1-db1-data-003 | orabp22-vm1-data-003-ds | 1000 | ||
orabp22-vm1-db1-data-004 | orabp22-vm1-data-004-ds | 1000 | ||
orabp22-vm1-db1-redo-001 | orabp22-vm1-redo-001-ds | 55 | Online Redo logs | |
orabp22-vm1-db1-redo-002 | orabp22-vm1-redo-002-ds | 55 | ||
orabp22-vm1-db1-redo-003 | orabp22-vm1-redo-003-ds | 55 | ||
orabp22-vm1-db1-redo-004 | orabp22-vm1-redo-004-ds | 55 | ||
orabp22-vm1-db1-fra-001 | orabp22-vm1-fra-001-ds | 60 | FRA files | |
orabp22-vm1-db1-fra-002 | orabp22-vm1-fra-002-ds | 60 | ||
orabp22-vm1-db1-fra-003 | orabp22-vm1-fra-003-ds | 60 | ||
orabp22-vm1-db1-fra-004 | orabp22-vm1-fra-004-ds | 60 | ||
orabp22-vm1-temp | orabp22-vm1-db1-temp | orabp22-vm1-temp-ds | 500 | Temp files |
Once the additional LUNs are created, these LUNS can be added to the ASM disk groups. The size of the LUNs for each ASM disk group should be determined by the size of the database. The PowerStore storage array has thin provisioning feature, meaning LUNs can be created larger than required. However, to truly benefit using thin-provisioned LUNs on the PowerStore, the use of Oracle “autoextend” clause when creating tablespaces is required. For detailed PowerStore best practices and features, see Storage Best Practices.
Recommendation
The results indicated that having multiple LUNs for the ASM disk groups increased performance significantly for the following metrics:
Based on these results, this practice is considered as a Day 1, Highly Recommended best practice.
Implementation Steps
Before adding additional three LUNS/volumes to each ASM disk group, we performed the following tasks to present the additional volumes to the guest VM RHEL OS where the Oracle database resides.
After performing these tasks, the following table shows the device names, their corresponding volume groups, and the ASM disk groups.
Table 3: Volume groups, data stores, UDEV devices and ASM disk groups
Volume Group | VMware Datastore | UDEV device name | ASM DG |
orabp22-vm1-db1 | orabp22-vm1-data-001-ds | /dev/oraudev/disks/ora-data1 | DATA |
orabp22-vm1-data-002-ds | /dev/oraudev/disks/ora-data2 | ||
orabp22-vm1-data-003-ds | /dev/oraudev/disks/ora-data3 | ||
orabp22-vm1-data-004-ds | /dev/oraudev/disks/ora-data4 | ||
orabp22-vm1-redo-001-ds | /dev/oraudev/disks/ora-redo1 | REDO | |
orabp22-vm1-redo-002-ds | /dev/oraudev/disks/ora-redo2 | ||
orabp22-vm1-redo-003-ds | /dev/oraudev/disks/ora-redo3 | ||
orabp22-vm1-redo-004-ds | /dev/oraudev/disks/ora-redo4 | ||
orabp22-vm1-fra-001-ds | /dev/oraudev/disks/ora-fra1 | FRA | |
orabp22-vm1-fra-002-ds | /dev/oraudev/disks/ora-fra2 | ||
orabp22-vm1-fra-003-ds | /dev/oraudev/disks/ora-fra3 | ||
orabp22-vm1-fra-004-ds | /dev/oraudev/disks/ora-fra4 |
For the baseline configuration, each ASM disk group of DATA, REDO, and FRA only has a single volume. For example, DATA disk group has one volume: ‘/dev/oraudev/disks/ora-data1’. To add the additional three volumes to the ASM disk groups, DATA, REDO, and ARCH, we need to ensure that all four LUNs in the same disk group are of the same size. Perform the following SQL statement in the ASM instance to add the three ASM disk groups, DATA, REDO, and FRA:
SQL> ALTER DISKGROUP data ADD DISK
'/dev/oraudev/disks/ora-data2' NAME DATA_0001,
'/dev/oraudev/disks/ora-data3' NAME DATA_0002,
'/dev/oraudev/disks/ora-data4' NAME DATA_0003;
SQL> ALTER DISKGROUP redo ADD DISK
'/dev/oraudev/disks/ora-redo2' NAME REDO_0001,
'/dev/oraudev/disks/ora-redo3' NAME REDO_0002,
'/dev/oraudev/disks/ora-redo4' NAME REDO_0003;
SQL> ALTER DISKGROUP fra ADD DISK
'/dev/oraudev/disks/ora-fra2' NAME FRA_0001,
'/dev/oraudev/disks/ora-fra3' NAME FRA_0002,
'/dev/oraudev/disks/ora-fra4' NAME FRA_0003;
It is recommended to set up the ASM disk groups with multiple LUNs during the initial configuration but if this is an existing database, these SQL statements can add volumes to ASM disk groups while the database is still running. It is preferable to run these statements outside of the database workloads’ peak time, as adding disk volume operations results in data rebalancing and may cause a significant performance overhead. The rebalancing power is defined by the ASM_POWER_LIMIT initialization parameter (default value = 1, range to 1024). The higher the limit, the faster a rebalance operation may complete, and the rebalance results in more performance overhead.
To monitor the status of the rebalancing operation, run this SQL statement to check the operation status:
SQL> select INST_ID, OPERATION, STATE, POWER, SOFAR, EST_WORK, EST_RATE, EST_MINUTES from GV$ASM OPERATION;
INST_ID OPERA STAT POWER SOFAR EST_WORK EST_RATE EST_MINUTES
---------- ----- ---- ---------- ---------- ---------- ---------- -----------
1 REBAL WAIT 1 0 0 0 0
1 REBAL RUN 1 14185 179546 7085 23
1 REBAL DONE 1 0 0 0 0
In this example, the output shows three rebalancing operations: the last row (REBAL DONE) is done, the second row (REBAL RUN) is running, and the third (REBAL WAIT) is waiting. The report for the second row shows the following: