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. 

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 OLTP workloads. When configuring ASM AU size, there are factors that influence I/O performance. In this infrastructure, the interconnection between PowerMax and the PowerEdge R740 servers defines how ASM storage performs.

During normal database operations, most changes to the database are captured to 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, a performance gain can be realized 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 to fine-grain striping. The following table shows the baseline configuration used for coarse grain striping for all of 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

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 results showed no improvement for the following:

• New Orders per Minute (NOPM)
• Transactions per Minute (TPM)
• PowerMax IOPS
• Server CPU utilization
• DB File Sequential Read

Overall, the only positive gain was a slight decrease in Log File Parallel Write times. 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 the FINE granted 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 – this can lower I/O latency for sequential writes.

References

 Deployment Best Practices for Oracle Database with Dell EMC PowerMax

Increasing the HBA queue depth can increase storage performance, optimizing data across the connections to storage. In this best practice, the HBA queue depth is increased to optimize performance. 

Overview

Relational database management servers, like Oracle, servicing high throughput online transaction processing applications create bursts of disk I/O activity that benefit from careful design of the complete I/O path between server memory and permanent storage. When using Fibre Channel networking for access to shared storage, the Oracle database server will include one or more host bus adapter (HBA) cards. In Fibre Channel networking, HBAs provide the same type of services to the host that a network interface controller (NIC) would for TCP/IP networks.

A key configuration parameter available on HBA cards are the HBA and LUN queue depths. The availability of an I/O buffer or queue on the HBA allows the host operating system to pass an I/O request on the HBA, even if the target storage device unable to process the request immediately. This reduces the potential for blocking threads at the operating system level that would have to wait for the target to be ready for new I/Os. However, building up a significant number of queued I/Os on the HBA can create a large burst of I/Os to the storage target that could overwhelm the capability of the I/O ports and/or the storage controllers on the shared array.

When a large number of hosts connect to a single shared storage array, the storage administrators may request or require that server administrators decrease the HBA queue depth settings on connected servers, especially database servers. This helps alleviate resource contention concerns that can occur when hosts are saturating the storage front-end ports or controller processors with too many simultaneous requests. Our best practices development environment was dedicated to a single database server host, so this was not a concern for this project.

We focused on answering whether increasing the HBA queue depth would have a beneficial impact on workload performance. The default queue depth of most HBA cards is 32, pending I/Os. Check with your vendor to verify the setting for your equipment and use the management API or application to confirm that the setting has not been changed from the default before proceeding with testing or use in production.

Recommendation

Changing the HBA queue depth showed no performance improvement. Test findings showed no improvement for the following:

• New Orders per Minute (NOPM)
• Transactions per Minute (TPM)
• PowerMax IOPS
• DB File Sequential Read
• Log File Parallel Write 

Server CPU utilization was unchanged.

The Emulex LightPulse HBA cards used in our testing lab had a default queue depth of 32. We tested a scenario with the queue depth increased to 64 and found no significant gain in performance. This indicates that 1) the server is not frequently filling the full queue depth of 32 and 2) the PowerMax is able to process all the queued I/Os that the HBAs can forward with a deeper queue depth.

Implementation Steps

To set lun_queue_depth, open the ESXi console and run this command:

esxcli system module parameters set -p lpfc_lun_queue_depth=64 -m lpfc

for i in `esxcfg-scsidevs -c |awk '{print $1}' | grep naa.60000970000197`; do esxcli storage core device set -d $i -O 64; done

References

VMware’s Set Storage I/O Control Threshold Value 

Many database workloads are sensitive to storage performance. In this best practice, the virtual machine latency sensitivity has been changed to optimize performance. 

Overview

VMware vSphere provides the ability to adjust latency sensitivity to optimize low latency database workloads and similar applications. We used the default value for latency sensitivity (Normal) for our baseline test. This setting optimized ESXi to deliver high throughput. 

This best practice recommends changing the latency sensitivity to HIGH. Changing the latency sensitivity to HIGH causes ESXi to optimize I/O operations for the virtual machine. This latency sensitivity change was completed for all the virtual databases. 

 Recommendation

Changing the latency sensitivity to HIGH showed minor improvements for:

•           New Orders per Minute (NOPM) 

•           Transactions per Minute (TPM) 

•           PowerMax IOPS

•           Log File Parallel Write

Server CPU utilization and database File Sequential Write saw no improvement.

We recommend virtual machine latency sensitivity as a Day 3, Fine-tuning activity. Consider changing the latency sensitivity to High for the virtual machine to optimize storage performance. 

Implementation Steps

Requirements: ESXi 6.7 requires full CPU reservation to power on a VM with hardware version 14 when latency sensitivity is set to HIGH. For more information, see Adjust Latency Sensitivity.

1. Browse to the virtual machine in the vSphere Client and click on the VMs tab. 
2. Right-click the virtual machine and click Edit Settings.
3. Click VM Options and click on Advanced. 
4. Select the HIGH setting from the latency sensitivity drop-down menu. 
5. Click OK.

References 

VMware Docs: Adjust Latency Sensitivity

The threshold value of VMware Storage I/O Control defines the upper limit of latency allowed for a datastore. In this best practice, the threshold for storage latency is lowered to optimize performance.  

Overview

Storage I/O Control allows the administrator to manually set a storage latency threshold for the datastores of the virtual machine. The storage latency threshold instructs the Storage I/O Control to monitor datastore(s) to ensure that performance is prioritized if the storage latencies exceed the threshold. In this best practice, the Storage I/O Control threshold is set to 5 milliseconds—the lowest possible value. 

Before changing the Storage I/O Control threshold, review VMware’s Set Storage I/On Control Threshold Value for more detail. This VMware guide notes that “a very low value (lower than 20 milliseconds) will result in lower device latency and isolation among I/Os at the potential cost of decrease in aggregate datastore throughput.” As the HammerDB TPC-C workload is characterized by many small reads and writes, throughput was less of a concern. Customers should evaluate if setting the Storage I/O Control threshold would benefit their database workload. 

 Recommendation

Changing the Storage I/O Control threshold to 5 milliseconds demonstrated a slight performance improvement. Test findings showed minor improvements for:

• New Orders per Minute (NOPM) 
• Transactions per Minute (TPM) 
• PowerMax IOPS 

Database File Sequential Read and Log File Parallel Write saw significant improvement while server CPU utilization slightly increased.

Both TPC-C metrics NOPM and TPM showed a minor improvement; the number of orders completed, and transactions processed slightly increased. Storage IOPS also marginally increased. 

Both DB File Sequential Read and Log File Parallel Read Write times showed a slight decrease, indicating faster response times.  

Overall, the best practice of changing the Storage Control I/O threshold to 5 milliseconds for the OLTP workload provided minor performance improvements. We recommend implementing this best practice as a post-installation step if further tuning of the database is required to improve storage latencies.  

Implementation Steps

Verify that Storage I/O Control is enabled. 

1. Right-click the datastore in the vSphere Client. 
2. Click the Configure Storage I/O Control tab. 
3. Select the Enable Storage I/O Control check box.
4. Adjust the Congestion Threshold: Select a value from the Manual drop-down menu. In this best practice 5 milliseconds was selected.
5. Click OK. 

References

VMware’s Set Storage I/On Control Threshold Value