Azure IO Performance for the RDBMS DBA- Part I

With my upcoming session on “Migrating Oracle Workloads to Azure IaaS” this week at PASS Virtual Summit 2020, I wanted to take some time to dig deeper onto the performance side. The last thing you want to have happen is to migrate your database to the cloud and have it just screech to a halt.

The truth is, its often a combination of database and infrastructure issues that are the cause.  Although many of you may want me to dig into database performance data, I’m actually going to first focus on infrastructure, as it’s the area that most aren’t privy to for Oracle, or for that matter, any database on Azure IaaS.

The topic of infrastructure is an essential one for any database running in IaaS and even more so VMs on Linux, which can be a bit foreign for the Microsoft data specialist.  Yes, this may be intimidating when doing the shift to Linux and understanding some of the nuances to running a database on Linux, but understanding the infrastructure is a key to removing it from the scenario.  Hopefully these tips will assist you, no matter if you’re running Oracle, (MySQL, PostgreSQL or SQL Server) on Linux VMs on Azure IaaS.

Know Azure Storage

There are several types of storage we use with databases on VMs on IaaS:

• Blob Storage/File Storage: Used for backup, but avoid using this type of storage for external tables and definitely not for any database files.
  • This is created via a storage account in the Azure Portal
• Use Managed disk and Azure NetApp Files, (ANF)for datafiles, redo log, transaction/archive logs, backup files
  • These are created as part of a VM creation or added to a VM post the creation.
  • There is extensive features that are part of ANF that we aren’t going into here, so ANF is not JUST storage.
• Ultra Disk- High performance disk
  • Added via a storage account but attached to a VM

The words standard, premium and ultra are used for all type of resources in Azure, so make sure you know the difference between the types of disk storage you’re using, not just the level of that disk type:

• Standard Hard Disk Drive, (HDD)
  • Please don’t use this for a database server. Just don’t.  There, I said it.
• Solid State Disk, (SSD)
  • Standard and Premium. We start with premium for Oracle databases and in my experience, everything when it comes to a RDBMS workloads.
  • Only premium allows for host level caching, read or readwrite, (the latter that should NEVER be used on a database.) Specific criteria makes host level read caching a great feature for datafiles.
• Ultra Disk
  • I know, Ultra Disk is not in the pricing calculator. This is one of those products that is calculated by the size, the MBPs, the IOPs and the vCPU for the VM it’s attached to.
  • There are definite demands for high IO, but an inability to afford the great options for ANF, that end up falling into this category.
• File Storage
  • Comes in Blob, BlobFile, Premium File, NFS storage and others, but those first four are what we use for databases in most cases and it’s for backups, exports and flat files.
• Azure NetApp Files
  • ANF also comes in a standard, premium and ultra service levels.

Choose the RIGHT Disk for the Job

If you take the time to know the IO and MBPs needs of your database, it  can help you choose the correct storage solution for your database.  Where we use Premium SSD managed disk for most Oracle workloads, we also know when we’re going to throttle at the VM level and head out to either:

Premium SSD Disk for specific skus that will allow us to turn on bursting, mirroring more and using smaller disks.

Higher Premium SSD and VM to surpass the limitations on either to reach what we need, but with volumes allocated just below the threshold for host caching, (covered in Part II of the post).

Be Aware of What VMs to Use for Databases

No, you’re not going to get good performance for a large database, especially Oracle from a General Purpose VM.  We use Memory Optimized VMs, (a few D-series, but mostly E and M-series) for success with RDBMS.

You may want to know what the VM has to do with the IO performance, but there are limits for each VM type and knowing what these limits are is  essential.

• If you want to use Premium SSD, you will want to stick with a VM with an “S” in the size name.
• On average, the v4 in any series is often better than the v3 when it comes to chip and other onboard features. Always check and verify, but if in doubt, I’d choose a v4 over a v3 anyday.
• As you stripe disks, keep in mind that you want to have a max of two disks per every one vCPU.
• If you’re sending a lot of data across the NIC, be aware of the limits at the NIC layer and how many NICs are available on the VM chosen.
• Scale up to get the combination you need-  not the other way around.

Use the OS that You Know

I prefer to use Oracle Linux or Red Hat Enterprise Linux for my Oracle databases.  These aren’t documented very clearly, but there are a full range of release versions for both OS and can be used to create your own base image for reuse in your image gallery.

Want to know how to find Oracle Linux images, check out this blog post.

From here, I’m going to switch blogs and place the Part II in this series on the Microsoft Data Architecture blog and attempt to kill two birds with one stone-  wait, I’m blogging for PASS Summit, too, make that three birds with one stone!  You can find Part II here.