Azure Cloud, IOPS, DTU and VM equivalence, PaaS vs IaaS MSSQL

I am building the next generation of Data Warehousing for a client and because the cloud’s sky is the limit it doesn’t mean they can afford to spend. Millions (25 million) of records need to be analyzed to produce meaningful reports and dashboard.

Cranking the power all the way up gives us the information we need in seconds. The highest database tier offered is something call P11. But what behind P11? Did I say this PaaS option was expensive?

First, it is not readily translatable into regular machines as the various tiers use a mix of computing, memory and disk IOPS and as per Microsoft: “a blended measure of CPU, memory, and data I/O and transaction log I/O…”. This blend unit is the Database Transaction Unit aka DTU.

Based on the performance trending it comes to something like this (courtesy of What the heck is a DTU?):

Number Cores IOPS Memory DTUs Service Tier Comparable Azure VM Size
1 core, 5% utilization 10 ??? 5 Basic Standard_A0, barely used
<1 core 150 ??? 100 Standard S0-S3 Standard_A0, not fully utilized
1 core up to 4000 ??? 500 Premium – P4 Standard_DS1_v2
2-3 cores up to 12000 ??? 1000 Premium – P6 Standard_DS3_v2
4-5 cores up to 20000 ??? 1750 Premium – P11 Standard_DS4_v2

Notice that this is a comparable size…and that P11 DTU is about CAD$15,000/month!!! And while PaaS is great, the price tag is a little too much for the common mortals getting into DWH.

Anyhow, trying to recreate a cheaper P11 I went on a picked a VM size of E8s_v3, because it said  “Great for relational database servers, medium to large caches, and in-memory analytics”. More sizes here: https://docs.microsoft.com/en-us/azure/virtual-machines/windows/sizes 

Size vCPU Memory: GiB Temp storage (SSD) GiB Max data disks Max cached and temp storage throughput: IOPS / MBps (cache size in GiB) Max uncached disk throughput: IOPS / MBps Max NICs / Expected network bandwidth (Mbps)
Standard_E8s_v3 8 64 128 16 16,000128 (200) 12,800 / 192 4 / 4,000

And so to host my data, I am adding the best disk I find on Azure:

This one, because it is closer to the size I need.
P20 disk is 512 GB, it can achieves 2300 IOPS and up to 150 MB/s.

When I could have gone for the highest:
P30 disk is 1024 GB, it can achieves 5000 IOPS and up to 200 MB/s. And only comes in 1TB size

The key thing* to understand is that the VM tier will limit the IOPS of what you connect to it.

And so here, while my disk is rated at 2300 IOPS and 150 MB/s, the machine specifications are going to limit me at 16,000 IOPS (no problem here) / but only 128 MB/s which is ok because P20 is only 150 MB/s anyway.

Once the VM is brought up online and the disk attached, a quick benchmark will match the specifications given:

iaas iops

 

 

135 MB/s for 150 MB/s advertised…

But wait, is this fast? My laptop gives me way more

laptop iops.png

How to attach faster disks to my VMs then?  What if I want to create a VHD with 600 GB and 400 MB/s of throughput?

You will not obtain such throughput if you just create a 600 GB VHD, because Azure will create a 600 GB VHD on a P30 Disk, and then you will have only 200 MB/s.

To achieve that, you should use stripping, and to do that, you can proceed with different ways:

  • Create 2 600 GB VHDs. Azure will create them using P30 disks. Then you use your stripping tool (Storage spaces) to create a 1200 GB volume. This volume will permit 400 MB/s and 10000 IOPS. But in this case, you will have 600 un-needed GB
  • Create 3 VHDs with 200 GB each. Azure will create them using P20 disks. Then you use your stripping tool (Storage spaces) to create a 600 GB volume. This volume will permit 450 MB/s (150 MB/s *3) and 6900 IOPS (2300 IOPS *3).

Wait, I need more! What if I want to create a VHD with 600 GB and 600 MB/s of throughput? Unfortunately, we can’t just dream, and ask Azure to do it, not till today. In fact, the maximum throughput possible is 512 MB/s, we can’t do better.

Ok. You have created the stripped volumes and you are still not getting what you want? Remember the key thing I mentioned above * ? The total data storage, the IOPS and the throughput are limited by the VM series and size. Each Azure Virtual Machine type is limited by a number of disks (total storage size), a maximum IOPS (IOPS) and a maximum throughput (Throughput). For example, you  may achieve a 128 MB/s only in a Standard_E8s_v3.  All the other VM types will throttle your IOPS or throughput when you reach the threshold.

When I was looking at the Memory optimized server size, there are Storage optimized VM sizes.

Size vCPU Memory: GiB Temp storage (SSD) GiB Max data disks Max cached and temp storage throughput: IOPS / MBps (cache size in GiB) Max uncached disk throughput: IOPS / MBps Max NICs / Expected network bandwidth (Mbps)
Standard_E8s_v3 8 64 128 16 16,000128 (200) 12,800 / 192 4 / 4,000
Standard_L8s 8 64 32 32 40,000 / 400 10,000 / 250 4 / 8,000

And so from the disk creation steps above, you will also want to get a VM that will be able to provide you with the throughput you need.

Advertisements

#azure, #database, #dtu, #iaas, #mssql, #paas, #sql

IBM v7000 WebUI not responding

I ran into the case of non-responding WebUI to manage LUNs (and other things) on a IBM Storwize v7000.
You can only run satask commands if you connected to the CLI using the SSH private key which is associated with the user called superuser. No other SSH key will allow you to run satask commands:

IBM_Storwize:SAN1:superuser>sainfo lsservicenodes
panel_name cluster_id cluster_name node_id node_name relation node_status error_data
01-1 00000100204029A2 SAN1 1 node1 local Active
01-2 00000100204029A2 SAN1 2 node2 partner Active

To find out which node is the configuration node, run the following command

sainfo lsservicestatus <Active panel name>

the following line tells you which node is the config node:

IBM_Storwize:SAN1:superuser>sainfo lsservicestatus 01-1
panel_name 01-1
cluster_id 00000100204029a2
cluster_name SAN1
cluster_status Active
cluster_ip_count 2
cluster_port 1
cluster_ip 172.xx.xx.33
cluster_gw 172.xx.xx.252
cluster_mask 255.255.254.0

Use the following command to restart the web service on the configuration node

satask restartservice -service tomcat 01-1

Wait at least 5 minutes for the service to restart before assuming that this has failed

If this do no’t solve your problems, you should restart the cannister with configuration node on it. Remember to connect to the Node that’s not configuration node. And restart the “partner”. If unable to get it reset, reset the canister physically. that is, pull it out a few inches from the chassis and then insert it again after 30 seconds.

#ibm, #storage-2, #v7000, #webui

How to extend a volume on an IBM DS3400

I inherited an old array that seems easy to manage using Storage Mangement Suite called IBM DS Storage Manager.

I had to extend one of the volumes hosted on that box. So I started up the management GUI, found the box, the correct array, and volume (“Logical drive”), and expected to just right click and add capacity.

I could add disks hence capacity to the array using the GUI but could not find anything about extending the volume.

I had to use my favorite search engine – duckduckgo– and found out that this operation can only be performed from the Shell. Which is actually a special shell utility.

PS C:\Program Files (x86)\IBM_DS\client> .\SMcli.exe 172.17.1.114 -p $#$#@$password
Please type desired command.

show logicaldrive[“COMPANY-FS2-F”]
;
VOLUME DETAILS

STANDARD LOGICAL DRIVES——————————

Logical Drive name: COMPANY-FS2-F

Logical Drive status: Optimal

Capacity: 557.793 GB
Logical Drive ID: 60:0a:0b:80:00:75:26:3d:00:00:01:c7:4d:9e:d3:d0
Subsystem ID (SSID): 0
Associated array: RAID5_ARRAY1
RAID level: 5

Drive type: Serial Attached SCSI (SAS)
Enclosure loss protection: No

Preferred owner: Controller in slot A
Current owner: Controller in slot A
Segment size: 128 KB
Capacity reserved for future segment size changes: Yes
Maximum future segment size: 2,048 KB
Modification priority: High
Read cache: Enabled
Write cache: Enabled
Write cache without batteries: Disabled
Write cache with mirroring: Enabled
Flush write cache after (in seconds): 10.00
Dynamic cache read prefetch: Enabled

Enable background media scan: Enabled
Media scan with redundancy check: Disabled

Pre-Read redundancy check: Disabled

After checking the details about the volume, I was able to extend the volume using:

set logicalDrive [COMPANY-TOR-FS2-F”] addCapacity=500 GB;

show logicalDrive [“COMPANY-TOR-FS2-F”] actionProgress;
Logical Drive COMPANY-TOR-FS2-F
Action: Initialization
Percent Complete: 53%

when growing the volume it does tell you much. Be sure to use the other command to verify the status.

And if you are looking for more information, I found the CLI manual here and not on the IBM website.

 

#cli, #ds3400, #ibm, #management, #smcli, #storage-2

Add EMC Storage – PowerPath Driven – on Server 2012 R2 Core or Hyper-V 2012 R2 (well core as well)

For this recipe you will need:

  • 1 LUN (setup in a storage group that holds the server)
  • 1 PowerPath (here after PP) Key
  • 1 Diskpart

First, locate your latest PP bundle and install in CLI mode.

PS C:\windows\system32&gt; etsn ptbtt-go-hv
[ptbtt-go-hv]: PS C:\&gt; ls
Directory: C:\
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----        11/21/2014   2:46 PM            logs
-a---         5/15/2014   4:45 PM   92347200 EMCPower.X64.signed.5.7.SP3.b509.exe

[ptbtt-go-hv]: PS C:\&gt; EMCPower.X64.signed.5.7.SP3.b509.exe /s /v&quot;/L*v C:\logs\PPsetup.log LICENSENUM=BUPJ-XB4E-LFC3-QYPY-MM92-QDWB NO_REBOOT=1&quot;

Once finished you can log there to see if  all was successful.

[ptbtt-go-hv]: PS C:\logs&gt; Get-Content .\PPsetup.log | Select-String &quot;Installation completed successfully&quot;

MSI (s) (E4:94) [14:48:24:606]: Product: EMC PowerPath 5.7 Service Pack 3 (64bit) -- Installation completed
successfully.

Hooray? well, go play with PowerMT.exe

[ptbtt-go-hv]: PS C:\&gt; cd 'C:\Program Files\EMC\PowerPath'
[ptbtt-go-hv]: PS C:\Program Files\EMC\PowerPath&gt; ls
Directory: C:\Program Files\EMC\PowerPath
Mode                LastWriteTime     Length Name
----                -------------     ------ ----
d----        11/21/2014   2:47 PM            Drivers
d----        11/21/2014   2:53 PM            Logs
d----        11/21/2014   2:47 PM            x86
-a---          2/7/2013   1:08 PM      17728 db_recover.exe
-a---         11/1/2010   4:13 AM        385 db_recover.exe.intermediate.manifest
-a---        11/24/2010   2:04 AM     531512 difxapi.dll
-a---          2/7/2013   1:10 PM      49472 EmcAdminProxy.dll
-a---         5/15/2014   5:20 PM    1125184 EmcAdminSvr.exe
-a---          2/7/2013   1:12 PM     516416 EmcLicTool.exe
-a---          2/7/2013   1:13 PM      27456 EmcLicTool_CHS.dll
-a---          2/7/2013   1:14 PM      27968 EmcLicTool_DEU.dll
-a---          2/7/2013   1:15 PM      27968 EmcLicTool_ESP.dll
-a---          2/7/2013   1:15 PM      27968 EmcLicTool_FRA.dll
-a---          2/7/2013   1:16 PM      27968 EmcLicTool_ITA.dll
-a---          2/7/2013   1:17 PM      27456 EmcLicTool_JPN.dll
-a---          2/7/2013   1:17 PM      27456 EmcLicTool_KOR.dll
-a---          2/7/2013   1:18 PM      27968 EmcLicTool_PTB.dll
-a---          2/7/2013   1:20 PM      29504 emcphostid.exe
-a---          2/7/2013   1:20 PM      27968 EmcpLogMsgs.dll
-a---         5/15/2014   5:20 PM    1117504 EmcpMgmtComp.exe
-a---         5/15/2014   5:20 PM     494400 EmcPowerPathAdmin.dll
-a---         5/15/2014   5:20 PM      39232 EmcPowMon.exe
-a---          2/7/2013   1:24 PM      31552 EmcPowPN22.dll
-a---          2/7/2013   1:25 PM      21312 EmcPowRes.dll
-a---         5/15/2014   5:20 PM     869696 EmcPowSrv.exe
-a---          2/7/2013   1:26 PM      39232 EmcpPerfmonPmiPrvdr.dll
-a---          2/7/2013   1:27 PM      36160 emcpreg.exe
-a---          2/7/2013   1:28 PM      10048 EmcpSvcErr.dll
-a---          2/7/2013   1:28 PM      26432 emcp_lic_rtl.dll
-a---         5/15/2014   5:20 PM    1137472 Emcp_mpapi_rtl.dll
-a---          2/7/2013   1:31 PM      91968 emcp_mp_rtl.dll
-a---         6/10/2003  10:00 PM      43430 Emc_PowerPath_Console.msc
-a---          2/7/2013   1:33 PM    1404224 libdb51.dll
-a---         11/1/2010   4:14 AM        385 libdb51.dll.intermediate.manifest
-a---         5/15/2014   5:20 PM    1748480 Libeay32.dll
-a---         5/15/2014   5:20 PM     303424 ManagementComp_Config.exe
-ar--        11/21/2014   3:38 PM         22 mpaa.excluded
-ar--        11/21/2014   3:38 PM        244 mpaa.lams
-a---         5/15/2014   5:20 PM     332608 powermig.exe
-a---         5/15/2014   5:20 PM     270144 powermigcl.exe
-a---         5/15/2014   5:20 PM    1181504 powermt.exe
-a---        12/18/2012   6:12 PM      23639 powerpath.man
-a---         5/15/2014   5:20 PM     304448 PowMigSrvc.exe
-a---         5/15/2014   5:20 PM     222016 ppinstall.exe
-a---         5/15/2014   5:20 PM     490304 ppRemoveAll.exe
-a---          2/7/2013   1:49 PM      55104 pthreadVC2.dll
-a---         5/15/2014   5:20 PM       5279 server.pem
-a---         5/15/2014   5:20 PM     360448 Ssleay32.dll

Found powermt, let’s use it.

[ptbtt-go-hv]: PS C:\Program Files\EMC\PowerPath&gt; .\powermt.exe display dev=all
Pseudo name=harddisk1
VNX ID=APM00141276882 [TTBTT-GO-HV_SG]
Logical device ID=6006016009B03800F71D7ED4B571E411 [FSP_LUN31_SPB_6882_PTBTT-GO-HV_X_200G]
state=alive; policy=CLAROpt; queued-IOs=0
Owner: default=SP B, current=SP B       Array failover mode: 4
==============================================================================
--------------- Host ---------------   - Stor -  -- I/O Path --   -- Stats ---
###  HW Path               I/O Paths    Interf.  Mode     State   Q-IOs Errors
==============================================================================
2 port2\path0\tgt1\lun0  c2t1d0      SP A3    active   alive      0      0
2 port2\path0\tgt0\lun0  c2t0d0      SP A2    active   alive      0      0
1 port1\path0\tgt1\lun0  c1t1d0      SP B3    active   alive      0      0
1 port1\path0\tgt0\lun0  c1t0d0      SP B2    active   alive      0      0

Looks like you see your disk? Expose it to windows for storing let’s VMs – this is pretty useful for hyper-v hypervisor!

[ptbtt-go-hv]: PS C:\Program Files\EMC\PowerPath&gt; diskpart
Microsoft DiskPart version 6.3.9600
Copyright (C) 1999-2013 Microsoft Corporation.
On computer: PTBTT-GO-HV
DISKPART&gt;
[ptbtt-go-hv]: PS C:\Program Files\EMC\PowerPath&gt; exit

Sometimes powershell session don’t play nice with output redirection and things like diskpart from let you in the interactive mode. Fear not, use psexec instead and use diskpart to create that disk.

PS H:\&gt; cd .\Tools\sysinternals
PS H:\Tools\sysinternals&gt; .\psexec.exe \\ptbtt-go-hv cmd
PsExec v2.11 - Execute processes remotely
Copyright (C) 2001-2014 Mark Russinovich
Sysinternals - www.sysinternals.com
Microsoft Windows [Version 6.3.9600]
(c) 2013 Microsoft Corporation. All rights reserved.
C:\Windows\system32&gt;diskpart
Microsoft DiskPart version 6.3.9600
Copyright (C) 1999-2013 Microsoft Corporation.
On computer: PTBTT-GO-HV
DISKPART&gt; list disk
Disk ###  Status         Size     Free     Dyn  Gpt
--------  -------------  -------  -------  ---  ---
Disk 0    Online          135 GB      0 B
Disk 1    Offline         200 GB   200 GB
DISKPART&gt; select disk 1
Disk 1 is now the selected disk.
DISKPART&gt; online disk
DiskPart successfully onlined the selected disk.
DISKPART&gt; list disk
Disk ###  Status         Size     Free     Dyn  Gpt
--------  -------------  -------  -------  ---  ---
Disk 0    Online          135 GB      0 B
* Disk 1    Online          200 GB   200 GB
DISKPART&gt; select disk 1
Disk 1 is now the selected disk.
DISKPART&gt; list volume
Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
----------  ---  -----------  -----  ----------  -------  ---------  --------
Volume 0     E   IRM_SHV_X64  UDF    CD-ROM      1917 MB  Healthy
Volume 1     D   RECOVERY     NTFS   Partition   3072 MB  Healthy
Volume 2         System Rese  NTFS   Partition    350 MB  Healthy    System
Volume 3     C                NTFS   Partition    132 GB  Healthy    Boot
DISKPART&gt; create partition primary

DiskPart has encountered an error: The media is write protected.
See the System Event Log for more information. 

Oops, I am not sure what a a disk is defaulted to read-only. Let’s change it manually.

DISKPART&gt; attributes disk
Current Read-only State : Yes
Read-only  : Yes
Boot Disk  : No
Pagefile Disk  : No
Hibernation File Disk  : No
Crashdump Disk  : No
Clustered Disk  : No

DISKPART&gt; attributes disk clear readonly

Disk attributes cleared successfully.

DISKPART&gt; list disk

Disk ###  Status         Size     Free     Dyn  Gpt
--------  -------------  -------  -------  ---  ---
Disk 0    Online          135 GB      0 B
* Disk 1    Online          200 GB   200 GB

DISKPART&gt; create partition primary

DiskPart succeeded in creating the specified partition.

DISKPART&gt; list volume

Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
----------  ---  -----------  -----  ----------  -------  ---------  --------
Volume 0     E   IRM_SHV_X64  UDF    CD-ROM      1917 MB  Healthy
Volume 1     D   RECOVERY     NTFS   Partition   3072 MB  Healthy
Volume 2         System Rese  NTFS   Partition    350 MB  Healthy    System
Volume 3     C                NTFS   Partition    132 GB  Healthy    Boot
* Volume 4                      RAW    Partition    199 GB  Healthy

DISKPART&gt; select volume 4

Volume 4 is the selected volume.

DISKPART&gt; format fs=ntfs quick

100 percent completed

DiskPart successfully formatted the volume.

DISKPART&gt; list volume

Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
----------  ---  -----------  -----  ----------  -------  ---------  --------
Volume 0     E   IRM_SHV_X64  UDF    CD-ROM      1917 MB  Healthy
Volume 1     D   RECOVERY     NTFS   Partition   3072 MB  Healthy
Volume 2         System Rese  NTFS   Partition    350 MB  Healthy    System
Volume 3     C                NTFS   Partition    132 GB  Healthy    Boot
* Volume 4                      NTFS   Partition    199 GB  Healthy

DISKPART&gt; assign letter=f

DiskPart successfully assigned the drive letter or mount point.

DISKPART&gt; list volume

Volume ###  Ltr  Label        Fs     Type        Size     Status     Info
----------  ---  -----------  -----  ----------  -------  ---------  --------
Volume 0     E   IRM_SHV_X64  UDF    CD-ROM      1917 MB  Healthy
Volume 1     D   RECOVERY     NTFS   Partition   3072 MB  Healthy
Volume 2         System Rese  NTFS   Partition    350 MB  Healthy    System
Volume 3     C                NTFS   Partition    132 GB  Healthy    Boot
* Volume 4     F                NTFS   Partition    199 GB  Healthy

DISKPART&gt; exit

Leaving DiskPart...

C:\Windows\system32&gt;f:
f:


F:\&gt;mkdir &quot;Virtual Machines&quot;
mkdir &quot;Virtual Machines&quot;

F:\&gt;dir
dir
Volume in drive F has no label.
Volume Serial Number is 6C59-E0A3

Directory of F:\

11/21/2014  03:49 PM    &lt;DIR&gt;          Virtual Machines
0 File(s)              0 bytes
1 Dir(s)  214,617,317,376 bytes free


cmd exited on ptbtt-go-hv with error code 0.
PS H:\Tools\sysinternals&gt;

#core, #diskpart, #emc, #microsoft, #partition, #powerpath, #powershell, #sysinternal, #windows-2

Working with EMC NAR Files

I had the (un) pleasure to work with EMC arrays again. While I though i had been saved their market share caught up to me and now I have to deal with it.

The context is the following: I have a few VNX arrays and I need to understand how they perform prior to trying doing some sancopy lun migrations.

The logic is the following:
1. turn logging on (obviously )
2. generate NAR files
3. work with those NAR files (you will need unisphere analyzer)

Turning Logging on
In order to actually get anything, you need to enable data logging.

1. on the array Storage System Properties, on the general tab, in the Configuration section, Check the “Statistics logging” box.

2. under system> monitoring and alerts launch the Statistics screen. In the settings section, configure the Performance Data Logging with the following:

Real Time interval, I like to use 300 for preliminary findings, I would use less if I need to correlate with event more specific events.

Archive interval, I am not sure why but I use double the value to cover 2 intervals in 1 NAR file. Yes, the archive interval will give you the number of NAR files.

Check Periodic Archiving in order to create the NAR file inline with your archive interval as per above. If you don’t you’ll want to use the performance screen as you sit in front of them and as they would be update as fast as the Real Time Interval you had set.

For the sake of not forgetting, always set a end date for the capture.

 

Generate the NAR files

If you had checked the Periodic Archiving, then your NAR files will be available. Use the Archive Management section from the System > Monitoring and Alerts> Statistics to Retrieve your Archive.

Download them to your machine in order to play with them. Note that you can also Delete those if you see fit. And yes those files will stay there after the Data logging is over.

Use them

Once you had downloaded the ones from the time frame you need or all of them in order to get a full picture, you may need to merge them. Indeed, you will need to use the Archive Management to load one file at a time and review the statistics.

You can use the powershell script below to have ns do the merging of many NAR files. If you only have 2 you need to merge, Unisphere’s merge archive feature will work for you.

$TempFile = “C:\Temp\nar_out\Temp.nar”
$OutputFile = “C:\Temp\nar_out\NAR_Merge.nar”
$NarFiles = Get-Childitem “C:\Temp\nar_in”
$filecount = 0
foreach($NFile in $NarFiles){
if($filecount -eq 0){Copy-Item $NFile.FullName $OutputFile}Else{
Copy-Item $OutputFile $TempFile
Remove-Item $OutputFile
./NaviSECCli.exe analyzer -archivemerge -data $TempFile $NFile.FullName -out $OutputFile
}
$filecount ++
}

Then load the result in unisphere analyzer and off you go for hours of fun tracking which SP or LUN is performing worst.

Also I had used the DYI heatmap script here, but also remember something called Analyzer Help that kind of do the same thing but I cannot figure how where to download it from.

How do you process your information (NAR  files) for them to make sense?

#emc, #nar, #nar-files, #storage-2, #vnx