Production Azure Performance

Overview

Microsoft asserts that given 16 data disks, Azure VMs can provide a maximum of 8,000 IOPS in the document “Performance Guidance for SQL Server in Windows Azure Virtual Machines” available from http://msdn.microsoft.com/en-us/library/dn248436.aspx. Achieving near this level of performance in production requires some planning and understanding of the Azure environment. This blog serves to document our production results and as a guide for configuring Azure data disks for high performance.

The following tables summarize the Microsoft published performance outcomes for 1x and 16x disk configurations. A single disk has a maximum of 500 IOPS and the aggregated 16x disks have an aggregated maximum of 8,000 IOPS.

image

Test Results

After performing several tests with less than optimal results, we engaged Microsoft support to verify the test parameters and challenge the published metrics. Although there is no minimum SLA for Azure disk performance, we expected real-world performance to be approximately 75% of the maximum SLA.

Random write IO performance was consistently high, achieving 91-99% of the published maximum. Random read IO performance met our expectations and ranged from 76-78% of our expected performance. Random write and read throughput also met or exceeded our expectations.

Sequential operations did not meet our expectations. Write IO operations fell short of our expectations and ranged from 66-74% of the published maximum performance. Sequential read IO hovered around 50% of the max.

The following tables illustrate the rang of values measured on an aggregate volume consisting of 16 disks.

image

A breakdown of the IOPS performance from table 3 is shown below. Random OLAP Read and Write metrics (indicated by OR and OW) are located on the far left of the chart. Small Write (SW) operations of increasing size appear to the right. Finally, Large Read (LR) and Large Write (LW) operations span the right half of the chart. Values referenced in one of the above tables are highlighted in red.

image

Test Details

During the tests, we discovered that Azure disk performance is dependent upon 3 key areas:

  • Configuration
  • Neighbors
  • Utilization

Configuration

The configuration consisted of Azure disk configuration best practices as identified by Microsoft documents, TechEd 2014 sessions and various Internet resources (details are listed in the Tools and References section). Early testing focused heavily on the information presented by these resources.

The best performance was achieved when utilizing the following configuration:

  1. A dedicated Azure Storage Account with Locally Redundant Storage
  2. Windows 2012 R2 Storage Spaces with Simple Volumes and 16 columns
  3. Windows volume with a 64k File Allocation Unit size

A dedicated Azure Storage Account isolates disk intensive applications and helps ensure that the 20,000 IOPS/Storage Account5 limit is not surpassed. Furthermore, configure the account to use Locally Redundant Storage, rather than Geo-Redundant, to provide higher bandwidth speeds.

Each individual “disk” has a maximum of 500 IOPS. Higher performance is gained by combining multiple disks. RAID 0 is sufficient, since the underlying disk files are replicated. However, the technology for providing aggregate disk volumes makes a difference.

Don’t use Windows Spanned Volumes. The traditional Windows Spanned Volume utilizes space on each disk sequentially; space on the first disk is consumed before the next disk is used.

image

Do use Windows Storage Spaces. Unlike the spanned volume, a Storage Space Simple Volume distributes reads and writes across all disks and can therefore leverage the aggregate IOPS of multiple disks.

image

When configuring the virtual disk with 16 disks, specifying 16 columns, instead of the default 8, provides dramatically better performance. Increasing the column count reduces the flexibility for expansion; however, the performance gains outweigh the expansion limitations in our current environment.

image

The file allocation unit size posed some controversy. Some resources indicated the performance gain was negligible, while the general SQL community overwhelmingly recommended using a 64k File Allocation Unit. Some performance is better than none, and each Windows volume was formatted using a 64k allocation unit size.

The storage environment resembles the following map.

image

The Windows data disk environment was generated via the following commands


New-StoragePool –FriendlyName StoragePool0 –StorageSubsystemFriendlyName "Storage Spaces*" –PhysicalDisks (Get-PhysicalDisk –CanPool $True)

New-VirtualDisk –StoragePoolFriendlyName StoragePool0 –FriendlyName VirtualDisk0 -ResiliencySettingName Simple -NumberOfColumns 16 –UseMaximumSize

Get-VirtualDisk –FriendlyName VirtualDisk0 | Get-Disk | Initialize-Disk –Passthru |
New-Partition –AssignDriveLetter –UseMaximumSize | Format-Volume -AllocationUnitSize 65536 –Confirm:$false

Neighbors

Neighbors are the other Azure tenants that share the same physical disk resources as you do. As for most of us in life, you can’t control who lives next door, and you can’t control how neighboring virtual resources will utilize shared physical resources. The multi-tenancy and inherent utilization unpredictability is the primary reason why a minimum performance SLA does not exist.

Performance will vary depending on the activity on the shared resources.

Utilization

Utilization was not considered during early tests, but became deceptively critical to achieving our expected performance. Without sufficient load, the disk sub-system was not taxed enough to attain higher IOPS. The primary factor was the SQLIO thread count. Contrary to some resources, SQL can utilize multiple threads per disk. Early tests dedicated a single thread for a volume consisting of 4, 8 and 16 physical disks (as far as the Windows OS is concerned, they were physical. In reality, they were Azure data disks and therefore, .vhd files). Increasing the thread count to 32 (2 per disk) resulted in dramatically higher measured IOPS. Without an increased load, the random IOPS stalled around 4,250 IOPS for volumes consisting of 8 and 16 disks.

Configuring and using the SQLIO utility is covered in the Performance Guidance for SQL Server in Windows Azure Virtual Machines document. Although the guide provides an excellent resource for tuning and testing the Azure disk system, a few subtle points should be highlighted.

The SQLIO param.txt file needs to be updated to reflect the number of disks to be tested. The example provided in the guide allocates 1 thread per volume. When a virtual disk and respective volume utilizes multiple disks, increase the thread count (32 in the example below) to match or exceed the number of physical disks. Also, configure the test file to be sufficiently large to ensure that any caching cannot skew the results and provide a wide area for random operations to draw from (we set the test file to 64GB in the example below).

param-60gb.txt

#c:\testfile.dat 2 0x0 100
#d:\testfile.dat 1 0x0 1000
 e:\testfile.dat 32 0x0 65536

Multiple tests were performed to simulate and measure the disk performance. Two batch files performed the tests. The first, exectests-warmup.cmd, performs the disk warmup and then calls the exectests.bat file to store results in a log file. The log file name must be changed to reflect the test.

exectests-warmup.cmd

::Test random 8K reads/writes
 sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt > warmupW.log
 sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt > warmupR.log
 sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt >> warmupW.log
 sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt >> warmupR.log
 sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt >> warmupW.log
 sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt >> warmupR.log
 sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt >> warmupW.log
 sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt >> warmupR.log
 exectests.bat > results-16d-01v-16c-20140630.log

Note: Although the append pipe was used for the warmup log files, the system chose to overwrite the log files instead. These files exist for curiosity, and are not required or referenced in the results. Alternating reads and writes were used to help ensure that the Azure tuning algorithm did not prefer reads or writes.

exectests.bat

::Test random 8K reads/writes
 sqlio -kW -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt
 sqlio -kR -s300 -frandom -o32 -b8 -LS -Fparam-60GB.txt
::Test random 32K reads/writes (for example, SQL Server Analysis Services I/O pattern)
 ::sqlio -kW -s300 -frandom -o32 -b32 -LS -Fparam-60new.txt
 ::sqlio -kR -s300 -frandom -o32 -b32 -LS -Fparam-60new.txt
::Test small sequential writes
 sqlio -kW -s180 -fsequential -o8 -b4 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 -b8 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 -b16 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 -b32 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 -b64 -LS -Fparam-60GB.txt
::Test large sequential reads/writes
 sqlio -kR -s180 -fsequential -o8 –b8 -LS -Fparam-60GB.txt
 sqlio -kR -s180 -fsequential -o8 -b64 -LS -Fparam-60GB.txt
 sqlio -kR -s180 -fsequential -o8 -b128 -LS -Fparam-60GB.txt
 sqlio -kR -s180 -fsequential -o8 -b256 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 –b8 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 -b64 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 -b128 -LS -Fparam-60GB.txt
 sqlio -kW -s180 -fsequential -o8 -b256 -LS -Fparam-60GB.txt

Summary

Higher IOPS can be achieved in Azure with proper storage planning and configuration.

  • Ensure that the Azure Storage Account is not overloaded. The Storage Account IOPS is capped at 20,000. Do not add more disks than the Storage Account can support. Use Locally Redundant Storage instead of Geo-Redundant Storage.
  • Use Windows Storage Spaces with Simple Resiliency Volumes to aggregate disk performance.
  • Set the Virtual Disk Column Count equal to the physical disk count and format the Windows volume using a 64K File Allocation Unit.

Load testing is useful for verifying the configuration, but must be tuned to provide accurate results. And finally, maximum performance is not guaranteed due to shared resources and potentially noisy neighbors.

References

  1. Performance Guidance for SQL Server in Windows Azure Virtual Machines, from http://msdn.microsoft.com/en-us/library/dn248436.aspx,
  2. Microsoft SQLIO available from http://www.microsoft.com/en-us/download/details.aspx?id=20163
  3. SQLIOResults.ps1 available from http://sqlblog.com/blogs/jonathan_kehayias/archive/2010/05/25/parsing-sqlio-output-to-excel-charts-using-regex-in-powershell.aspx
  4. Storage Spaces details, especially columns and their effect on performance, at http://social.technet.microsoft.com/wiki/contents/articles/15200.storage-spaces-designing-for-performance.aspx
  5. Azure Storage Account limits, from http://windowsitpro.com/azure/maximize-azure-storage-performance
  6. General tuning details from DBI-334 SQL Server on Microsoft Azure Virtual Machines: The Important Details. http://channel9.msdn.com/Events/TechEd/NorthAmerica/2014/DBI-B334#fbid=
  7. Breaking the SQL thread myths from http://blogs.technet.com/b/josebda/archive/2009/03/31/sql-server-2008-i-o-performance.aspx and http://blogs.msdn.com/b/psssql/archive/2007/02/21/sql-server-urban-legends-discussed.aspx

Leave a comment