Home > Workload Solutions > Oracle > Best Practices > AMD-Based Oracle Best Practices on Dell PowerEdge R740 and PowerMax 2000 > Storage Best Practices > Day Three Best Practices > PowerMax: Adding Storage LUNs and ASM Disk Groups
How storage is configured can directly influence database performance. This best practice validates the number of storage LUNs and ASM disk groups used to optimize database performance.
Category | PowerMax Storage |
Product | PowerMax |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine-Tuning |
Overview
To determine if a best practice provides value, we tested it against a baseline configuration of the database to validate performance. Table 1 shows the baseline storage groups and LUNs configuration. A one-to-one relationship from parent storage group to child storage group to LUN exists in this baseline configuration, with the exception of the DB1_GRID child storage group. The DB1_GRID child storage group has three LUNs: Grid1 through Grid3. This streamlined configuration simplifies database storage management and has other benefits.
Another benefit of this baseline storage configuration is that it follows the principle of separating database files into dedicated storage groups. For example, the data files are separate from the redo files, providing the capability to analyze storage performance for different parts of the database. This granularity in storage group design facilitates activities like storage replication and snapshots. Both replication and snapshots only require part of the database to be protected or copied. Not having to replicate or snapshot the temporary files can save disk space and, with replication, network bandwidth.
Table 1: Baseline storage group configuration
Parent storage group | Child storage group | LUNs | Volume size (GB) | Notes |
REDPEPPER_DB1 | DB1_DATA | Data1 | 1,000 | Data files |
REDPEPPER_REDO_DB1 | DB1_REDO | Redo1 | 50 | Redo files |
REDPEPPER_ARCH_DB1 | DB1_ARCH | Arch1 | 50 | Archive files |
REDPEPPER_GRID_DB1 | DB1_GRID | Grid1 | 50 | Grid infrastructure |
Grid2 | 50 |
| ||
Grid3 | 50 |
| ||
REDPEPPER_FRA_DB1 | DB1_FRA | FRA1 | 100 | Flash Recovery Area |
REDPEPPER_TEMP_DB1 | DB1_TEMP | Temp1 | 500 | Temporary files |
Table 2 shows the optimized PowerMax storage configuration. All the parent and child storage groups remain intact, but additional LUNs are added. Using multiple LUNs for the most active portion of an Oracle database can improve performance. The primary benefit of using multiple storage LUNs is that the operating system creates an I/O queue path per LUN. The table shows the optimized PowerMax storage configuration for the Oracle database. In the optimized storage configuration Data, Redo, and Arch have four LUNs per child storage group. Compared to the baseline configuration, there are four times the number of I/O queue paths.
Table 2: Optimized PowerMax storage configuration
Parent storage group | Child storage group | LUNs | Volume size (GB) | Notes |
REDPEPPER_DB1 | DB1_DATA | Data1 | 1,000 | Data files |
Data2 | 1,000 |
| ||
Data3 | 1,000 |
| ||
Data4 | 1,000 |
| ||
REDPEPPER_REDO_DB1 | DB1_REDO | Redo1 | 50 | Redo files |
Redo2 | 50 |
| ||
Redo3 | 50 |
| ||
Redo4 | 50 |
| ||
REDPEPPER_ARCH_DB1 | DB1_ARCH | Arch1 | 50 | Archive files |
Arch2 | 50 |
| ||
Arch3 | 50 |
| ||
Arch4 | 50 |
| ||
REDPEPPER_GRID_DB1 | DB1_GRID | Grid1 | 50 | Grid infrastructure |
Grid2 | 50 |
| ||
Grid3 | 50 |
| ||
REDPEPPER _FRA_DB1 | DB1_FRA | FRA1 | 100 | Flash Recovery Area |
REDPEPPER _TEMP_DB1 | DB1_TEMP | Temp1 | 500 | Temporary files |
Once the additional LUNs were created, the next step was to add ASM disk groups. Initial sizing of the ASM disk groups should reach a management and capacity balance. If the disk groups are sized too small, it may require frequent adding of additional groups. Fewer large disk groups result in excessive underutilized storage capacity.
Recommendation
Results from validating the best practice of adding additional LUNs showed a slight performance improvement. These metrics showed improved performance:
There was no improvement in these metrics:
NOPM and TPM are metrics from the TPC-C benchmark that indicate performance. If NOPM and TPM increase when compared to the prior test, this suggests that the best practice improved performance. As this was the first best practice, the comparison was against the baseline. Test results from validating this best practice showed a slight increase in NOPM and TPM.
An increase in PowerMax IOPS indicates improved performance. By creating four times the number of LUNs for Data, Redo, and Arch, the number of queue paths also increased. The additional queue paths allowed more data to be serviced by the PowerMax storage array, resulting in an increase in PowerMax IOPS and improved storage efficiency.
Server processor utilization can be impacted by the response time of other systems, such as storage. For example, if storage response times are slow the processors will have idle wait times. The number and length of storage wait times in part determines whether the processors are relatively idle or efficiently busy. The results showed a slight increase in server CPU utilization, delivering improved performance and greater processor efficiencies.
DB File Sequential Read and Log File Parallel Write are database metrics that indicate database storage performance. A decrease in the amount of time for both metrics means the database is reading database files and log files faster. The results showed a slight decrease in DB File Sequential Reads, indicating improved storage performance.
During normal database operations the Redo Log files actively record data changes. Thus, most of the activity to the Redo Logs are writes with fewer reads. Test findings show Log File Parallel Writes were no faster with the optimized storage configuration. Although performance was not improved for our test databases, other workloads could show improvement. It is recommended to create additional volumes for the Redo Logs to ensure that the database is using an optimized configuration.
Overall, the best practice of increasing the number of storage groups is recommended and should be considered as a Day 3, fine-tuning 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 resided:
To add the additional three volumes to the ASM disk groups DATA, REDO, and ARCH, we needed to ensure that all four LUNs in the same disk group were the same size. Run the following SQL statement in the ASM instance to add three ASM disk groups, DATA, REDO, and ARCH:
SQL> ALTER DISKGROUP data ADD DISK
'/dev/oracleasm/disks/ora-data2' NAME DATA_0001,
'/dev/oracleasm/disks/ora-data3' NAME DATA_0002,
'/dev/oracleasm/disks/ora-data4' NAME DATA_0003;
SQL> ALTER DISKGROUP redo ADD DISK
'/dev/oracleasm/disks/ora-redo2' NAME REDO_0001,
'/dev/oracleasm/disks/ora-redo3' NAME REDO_0002,
'/dev/oracleasm/disks/ora-redo4' NAME REDO_0003;
SQL> ALTER DISKGROUP arch ADD DISK
'/dev/oracleasm/disks/ora-arch2' NAME ARCH_0001,
'/dev/oracleasm/disks/ora-arch3' NAME ARCH_0002,
'/dev/oracleasm/disks/ora-arch4' NAME ARCH_0003;
You can perform these SQL statements to add ASM disk groups while the database is still running. However, it is preferable to run these statements outside of the database workloads’ peak times 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 1,024). The higher the limit, the faster a rebalance operation may complete and the more performance overhead that is caused by the rebalance.
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 first is done (bottom), the second is running (middle), and the third is waiting (top). For the second row, the report shows:
Additional Resources
This best practice was referenced from the Deployment Best Practices for Oracle Database with Dell EMC PowerMax.