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 > ASM Fine Grained Striping of REDO Logs
Automatic Storage Management (ASM) provides the capability to define the allocation unit (AU) size of storage. This best practice validates the recommendation to use fine-grain striping for the database Redo logs.
Category | PowerMax Storage |
Product | Oracle |
Type of best practice | Performance Optimization |
Day and value | Day 3, Fine-tuning |
Overview
Oracle ASM uses AUs to stripe data across each disk in the disk group. The size of the AU determines its stripe-depth. By default, the ASM allocation unit size is 1 MB and 4 MB for version 12.2 and later. The default AU size is named Coarse Striping and is optimal for Online Transaction Processing (OLTP) workloads. When configuring ASM AU size, there are factors that influence I/O performance. For example, in this infrastructure, the interconnection between PowerMax and the PowerEdge R7525 servers influences ASM storage performance.
During normal database operations, most changes to the database are captured in the Redo logs. The process of capturing database changes generates continuous large sequential writes to the Redo logs. As the PowerMax breaks large continuous writes into smaller I/Os, performance increases as storage latencies lower. ASM offers fine-grain striping for the Redo logs, providing the capability to move from a 1 MB / 4 MB allocation unit size down to a smaller 128 KB size.
In this best practice, the database team changed the ASM AU size for Redo and Archive from coarse striping to fine-grain striping. The following table shows the baseline configuration used for coarse-grain striping for all the ASM disk groups. The right-most column of Table 1 shows that Redo and Archive disk groups were changed to fine-grain stripe size.
Table 1: Baseline configuration used for coarse grain striping
Parent storage group | Child storage group | Baseline ASM AU Stripe size | Best Practice: ASM AU Fine-Grain Stripe Size |
REDPEPPER_DB1 | DB1_DATA | Coarse | Coarse |
| DB1_REDO | Coarse | Fine |
| DB1_ARCH | Coarse | Fine |
REDPEPPER_GRID |
| Coarse | Coarse |
REDPEPPER_FRA |
| Coarse | Coarse |
REDPEPPER_TEMP |
| Coarse | Coarse |
It is important to note that Archive logging was not on for validating this best practice, meaning the performance results reflect only the change in AU size for Redo logs.
Recommendation
Changing the Redo log to use ASM fine-grain striping showed no performance improvement. Test findings revealed no improvements for:
While there were no substantial performance improvements for this OLTP work, customers with other workloads might achieve better results. In-memory databases and large batch data workloads could push a greater volume of writes to the Redo logs and would benefit more from fine-grain Redo logs.
The recommendation for fine-grain striping of the Redo logs is a Day 3, fine-tuning activity. Customers with large sequential write workloads on the Redo logs will benefit the most from this best practice.
Implementation Steps
To set fine-grain striping for Redo and Arch disk groups, run these SQL statements in the ASM instance:
ALTER DISKGROUP REDO ALTER TEMPLATE ONLINELOG ATTRIBUTES (FINE);
ALTER DISKGROUP ARCH ALTER TEMPLATE ARCHIVELOG ATTRIBUTES (FINE);
With these fine-striping settings, the fine-striping writes 128 KB data to each ASM Disk in the Redo and Arch disk groups in a Round Robin fashion: 128 KB goes to the first disk, then the next 128 KB goes to the next disk, until complete. The fine-grained stripe size always equals 128 KB in any configuration, which can lower I/O latency for sequential writes.
Additional Resources
Deployment Best Practices for Oracle Database with Dell EMC PowerMax