Azure IO Performance for the RDBMS DBA- Part II

by Contributed | Nov 10, 2020 | Technology

This article is contributed. See the original author and article here.

There are considerable focus areas to infrastructure that the IaaS DBA needs to be aware of, so Part I of this blog can be found here. 

Now that we’ve covered storage and VM series in Part I of this blog post on Infrastructure, we can go onto the detail areas for performance.  Let’s start on performance gains with host caching.

Cache it Out Right

Host caching, by default, for premium SSD is turned off.  This is a feature only available on certain VM series, (look for an ‘S’ in the D, E and M-series VMs.)

Turn on Read-Only host caching for the datafiles, archive logs and redo logs, but recognize that this is a feature only available on Premium SSD and that if you size the volume over 4095G, any host caching is turned off again.  The limit is 4095G, so keep that in mind when you choose a P50 disk with it’s 4096G size.  Attempt to allocate it all with read only caching on, as soon as you allocate that last 1G with an LVM create or ASM diskgroup creation command, the host caching will become disabled.  You can use up to 4095G-  that’s it.

Note the difference in cached vs. uncached performance for the VM you chose to use.  We’ve had technical discussions on how “hard” these limits are, as there is also bursting that can cloud the final numbers, as seen in performance tests, but at this time, I recommend sticking to the values shown for the VM you’ve chosen, recognizing the VM values override anything at the storage level unless it’s Azure NetApp Files, (which is only limited by the NIC.)

As stated earlier, I’ll repeat- DON’T  turn on read/write caching.  These are datafiles and no one wants writes going to memory.  We want our data written to disk!  There is a risk of corruption in certain Oracle releases, too if this is accidently turned on. 

Disk Bursting

Disk Bursting is something that has mixed reviews from most database technologists. On one hand, having the ability to “burst” IO performance for up to 30 minutes during batch loads or heavier IO usage is very beneficial, on the other hand, consistent performance is important because users expect this. 

For RDBMS workloads, we can really only focus on the Esv3 series and a few of the Dsv3 series VMs.  Disk bursting, like other IO topics, is a combination between the VM and the disk chosen to receive the feature. 

To enable disk bursting at the disk layer requires smaller disks and isn’t available in all regions, so take the time to ensure you are deploying in the correct region and disk series P20 or below of premium SSD.  If you have a workload that is hitting at the upper limits of the disk series you’ve deployed on, (again, under the P20) then you aren’t going to accumulate credits that can be used towards a bursting period.  No, you aren’t robbing Peter to pay Paul, you’re accumulating credits of IO that you aren’t using in inconsistent workloads to be allocated to when you do need that burst of performance. 

Premium SSD disks, disk bursting capabilities, (red) vs. those which are preferred for read-only host caching, (blue).

Be Smart with Your Linux Volumes

To use ASM or not to use ASM?  That is the question.  The answer is really about the ease of management and less about performance gains.  There isn’t much that ASM can offer us here, but for the DBA, it does offer the ability to add to an existing diskgroup vs. creating a new volume when adding disk. 

• Use 1MB stripe size to ensure the most optimal performance for Oracle, 64K for SQL Server. 


• Stripe multiple disks together to combine them for higher IO capabilities.  You’re still held to the IO limit at the VM, but you can reach those limits where you may not with just a single disk.


• Use the correct queue depths for SQL Server and match the vCPU count to calculate the DOP for Oracle.

Another common mistake we experience with customers-  identify where the swap device is located in Linux.  We’ve come across many customers who have placed the swap device on slower, managed disk vs. fast local disk, creating latency and throttling. 

Remember not to use standard disk for your OS disk and consider the amount of IO required for anything placed on this disk.  As my partner in crime remarked the other day, “I haven’t seen an Oracle environment on a VM perform optimally with a standard SSD for the OS Disk.”  Consider Premium SSD for your OS Disk for any database VM.

Lay out Files Strategically

For small databases, all datafiles, redo logs and archive logs, (or transaction logs in SQL Server) can reside in one volume.  As databases get larger or experience IO latency, the first step is to separate the appropriate files onto appropriate physical volumes.  In Oracle, the redo logs, archive logs and for SQL Server, the transaction logs from the main datafiles.

If the database has exceptionally high IO vs. the managed disk chosen, consider mirroring and striping smaller disks, along with strategically positioning datafiles to get the most performance out of the database.  Match datafile IO demands with the managed disk it resides on.

If you’re experiencing IO throttling at the VM tier, then it’s time to move away from managed disk and onto Azure NetApp Files, (ANF).  ANF is constrained only by the NIC and can offer higher MBPs.  ANF capacity pools can be connected to multiple VMs, offering simpler architecture and cloning solutions that are often part of the reason customers have  moved to the cloud.  When utilizing ANF, also consider using dNFS to benefit the performance, too. 

Stop Backing up Your Databases

Yeah, I said it, but that’s not what I meant-  most backup utilities, (looking at you, Oracle RMAN!) is slow and creates heavy IO situations.  Most specialists don’t think twice about choosing slow, blob storage to backup databases to, but this can create a serious problem in IO throttling and latency issues in the database during night-time batch processing and other jobs. 

Consider moving to snapshot technology that is database platform aware.  For Oracle, that is Azure NetApp Files and Commvault, (although there are others, these are the two I’m most satisfied with.)  Most snapshot technologies not only take a snapshot in a matter of minutes, (while the heavy lifting goes on behind the scenes, but it far from impacting to the database) and they also have the ability to create clones in as short of time, saving considerable resources.  Many of these products provide object level backups, restores and a management interface to  make maintaining the backups easy for the DBAs.  With the time saved on backups, restores and refreshes, DBAs can get to more important and satisfying work.

There are significantly more infrastructure tips, but this is a good list to start with.  If you have time and are registered for PASS Summit, I’ll be presenting on Migrating Oracle Workloads to Azure this week and next blog post, I’ll discuss more on the topic of Oracle optimization in the cloud!

Announcing Az Predictor

by Contributed | Nov 10, 2020 | Technology

This article is contributed. See the original author and article here.

The Azure PowerShell modules expose over 4,000 cmdlets and, on average, ten parameters per cmdlet. Experienced PowerShell users will find the right cmdlet and parameter to achieve their goal but this can be more complicated for casual users.

I have rarely seen people experimenting with a new module being able to execute a command successfully on their first attempt. With time, this is no more an issue as people get accustomed to the cmdlet and remember the associated parameters, unless you have a terrible memory like me and need to refer to the documentation frequently.

Az Predictor annimated GIF

Goals

We built Az Predictor, an intelligent command completion module for Azure Powershell. Az Predictor helps our Azure developers find the cmdlet they are looking for efficiently, identify the required parameters quickly, and experience fewer errors.

We worked closely with the PowerShell team to have Az be the first module that leverages this new interface and bring suggestions to the developer’s fingertips. Az Predictor takes the context of the current session into account in its suggestions. With Az Predictor’s context-aware suggestions users will be guided through the discovery of cmdlets and will not need to go to the online help as often.

To satisfy different working styles, we are offering two modes:

• Inline mode: a suggestion with parameters and values will appear with some opacity on the rest of the command line. We expect this mode to be more suited for the experienced developers who already understand the cmdlets.
  Az Predictor inline mode screen capture
  

   

  
  


• Listview mode: several suggestions will appear below the command line with the complete parameter set displayed. This mode should be more suited to folks who need to see the command’s full line to know the parameters.
  Az Predictor listview mode screen capture

Technical details

Az Predictor is currently a module in preview in the PowerShell gallery. We are looking for feedback on this first preview, and based on what we will hear from customers, we are considering making it part of the Az wrapper module.

The inline view and list view modes are provided by PSReadline to manage the user’s interactions. The plugin is responsible for providing the suggestions to be used by PSReadline.

Az Predictor is built on top of the subsystem plugin model that is available after PowerShell 7.1-preview7 and used by PSReadline 2.2.0-beta1; hence the following configuration is required to try Az Predictor:

• PowerShell 7.1+


• PSReadline 2.2.0-beta1 – Learn more about PSReadline 2.2.0

Note: If you are using Windows PowerShell (5.1), you can install PowerShell 7.1 side by side. More information on this page: https://docs.microsoft.com/en-us/powershell/scripting/install/migrating-from-windows-powershell-51-to-powershell-7?view=powershell-7#using-powershell-7-side-by-side-with-windows-powershell-51 .

To provide the suggestions to PSReadline, Az Predictor makes regular calls to an API that will return suggested commands based on the session’s context.

The full functionality of Az Predictor requires internet access. We are planning to add support for environments with limited or no network capacity (for example, mobile connectivity or Wi-Fi in airplanes).

Once loaded, Az Predictor will identify the value or variable used for resource group name and location and use it in the subsequent suggestions. When you start typing a command, Az Predictor will search for the cmdlet that you are the most likely to use based on the session’s context. As you type, we refine the search and display more accurate examples to use.

Az Predictor understands parametersets, required, and optional parameters and leverages the Azure PowerShell examples in the reference documentation as suggestions. The suggestions will then be adjusted based on the possible parametersets and parameters that have already been typed

The code of Azure PowerShell, including this module, is opensource. If you find a bug and want to contribute, you can submit an issue or open a PR (pull request).

Getting started

We hope that you are as excited as we are with this new interface.

To get started:

1. Install PowerShell 7.1-rc2 or more recent: https://docs.microsoft.com/en-us/powershell/scripting/install/installing-powershell?view=powershell-7.1 


2. Install PSReadline 2.2-beta1 https://github.com/PowerShell/PSReadLine/releases/tag/v2.2.0-beta1
   

   Install-Module PSReadline -AllowPrerelease 

   
   


3. Install Az.Tools.Predictor preview
   

   Install-Module -Name Az.Tools.Predictor

   
   


4. Import the module via
   

   import-module Az.Tools.Predictor

   
   


5. Enable plugins via
   

   Set-PSReadLineOption -PredictionSource HistoryAndPlugin

   
   


6. You can enable the list view mode with
   

   Set-PSReadLineOption -PredictionViewStyle ListView

   
   

If you want to load Az Predictor at the launch of PowerShell, add the following lines to your PowerShell profile (Microsoft.PowerShell_profile.ps1)

Import-Module Az.Tools.Predictor
Set-PSReadLineOption -PredictionSource HistoryAndPlugin
Set-PSReadLineOption -PredictionViewStyle ListView

Log issues, feature requests, or design changes on GitHub: https://github.com/Azure/azure-powershell/issues

We are at the early stage of the module and want to hear from you; please take a moment to fill this brief survey: http://aka.ms/azpredictorsurvey

Tell us about the following:

• Are the predictions useful?


• Would you like to have Az Predictor turned on by default?


• Which mode is the most valuable?

Collaborating with Qualcomm to Improve AI and ML Dev Experiences

by Contributed | Nov 10, 2020 | Technology

This article is contributed. See the original author and article here.

Man using AI glasses with machinery

 It’s an exciting time at Microsoft, with growing investments in edge computing and AI. Much of the future will be influenced by these technologies, and indeed, we are already seeing their power in telco 5G devices, IoT platforms, and low-power wide-area (LPWA) networks. With Microsoft’s recently announced Azure for Operator efforts, we’re in a unique position to drive IoT experiences that leverage edge AI via these emerging 5G and LPWA networks.

Toward that end, I’m so pleased to say we’re now collaborating with Qualcomm Technologies to facilitate a seamless artificial intelligence (AI) and machine learning (ML) developer experience. The efficiency granted by this seamlessness of development and deployment will benefit enterprises using Microsoft’s Azure and AI solutions such as the Azure IoT platform, and Qualcomm Technologies’ IoT processors and AI inferencing products.

Our two companies have produced some great work together over the years, including recent products and solutions for Microsoft’s Surface Pro X and Vision AI Developer Kit. This newest collaboration is just as exciting. Qualcomm Technologies’ position in the mobile ecosystem enables them to drive hardware-accelerated inference capabilities from ultra-low power edge AI solutions all the way to high-performance AI inferencing in the cloud using the Qualcomm® Cloud AI 100, all leveraging LPWA and emerging 5G deployments with commercial customers. This makes them an excellent company to team up with for our new, polished AI and ML developer experience.

Keith Kressin, Senior President and General Manager, Compute and Cloud, Qualcomm Technologies said, “5G and AI are the two key technologies today fueling the convergence of the cloud and devices at the edge of networks. Collaborating with Microsoft to accelerate AI across a wide breadth of connected devices from the edge to the cloud, with our platform that runs single-milliwatt to Snapdragon to the Qualcomm Cloud AI 100, will enable a variety of powerful new applications and services.”

I know I speak for the entire Microsoft Azure Edge Devices team when I say we’re very excited to be working with Qualcomm Technologies on solutions to benefit enterprise developers and deployers across industries.