Hello again, It has been a long time, but the day job takes precedence. Recently, I have been working on a little pet project I thought I would share. The EMC VNXe is a great entry level storage array which has been popular with SMB and remote office branch office deployments in the enterprise. I was recently asked to look at performance on one of these arrays. While investigating this request I discovered the VNXe has reduced functionality around monitoring compared to big brother, VNX. Performance information is collected differently and the VNXe does not currently have the ability to utilize Unisphere Analyzer.
While searching on the web I found the following article http://henriwithani.wordpress.com/2011/12/01/hidden-vnxe-performance-statistics/ . Henri shows where to find additional performance statistics on the VNXe. He also shows how to export the data to CSV for reporting. The VNXe performance data is stored in a public domain C library based SQL database called SQLite. While investigating the use of the SQLite database and tools I discovered there is an ADO.Net provider assembly available for SQLite. Since I am somewhat partial to PowerShell and any .NET object is accessable from PowerShell. I thought I would take a shot at building a PowerShell Module for looking at VNXe performance data.
Here is what I came up with, the VNXePerformance.psm1 PowerShell module. The module currently consists of 21 CmdLets. One CmdLet is to set the location of the SQLite database tables and the remaining 20 are to return data stored in those tables. The CmdLets currently work with the following three SQLite tables:
- capacity.db
- stats_basic_summary.db
- stats_basic_default.db
These three tables appear to hold most of the interesting information so I concentrated on them first. The primary purpose of the module is to query the database and return PowerShell objects from the data. A secondary purpose of the module is to add calculated properties to some of those returned objects. The calculated properties currently implemented provide bytes per second for each of the three IP storage protocols supported NFS, CIFS, and iSCSI and also read and write IO’s per second. This was the first info I needed and was pretty simple to figure out. I plan to add additional calculated fields in the future as I learn more about the data.
Using the module
Install the SQLite ADO.Net Provider Assembly
The VNXePerformance module requires the SQLite ADO.Net Provider to be installed, which can be downloaded from the following page http://system.data.sqlite.org/index.html/doc/trunk/www/downloads.wiki
I recommend using the setup package for the framework and OS version you are using.
The next step is to download the module, unzip it to the modules folder. The proper modules folder can be identified with the $env:PSModulePath variable.
Download the module VnxePerformance.zip
Unzip the module to the PowerShell modules folder, open PowerShell and verify module is available. The VNXePerformance module should be listed when executing the get-module -ListAvailable command, if installed in the proper location.
|
1 2 3 4 5 |
PS C:> get-module -ListAvailable ModuleType Name ExportedCommands ---------- ---- ---------------- Manifest VNXePerformance {Get-VNXeBasicSummaryGMSSPADetail, Get-VNXeSystemTotalsDetail, Get-VNXeB... |
Then set the following line to the location of the System.Data.SQLite.dll and save changes as necessary. The location defined in the module is the default installation path for the assembly if installed with the prepackaged installation.
|
1 |
$AssemblyPath="C:Program FilesSystem.Data.SQLite2010binSystem.Data.SQLite.dll" |
We are now ready to use the module so open a PowerShell prompt and import the module.
|
1 |
PS C:>Import-Module VNXePerformance |
We can now use the get-command cmdlet to see the new functionality provided by our module.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
PS C:>Get-Command -Module vnxeperformance | select name Name ---- Get-VNXeApplicationsDetail Get-VNXeApplicationTotalsDetail Get-VNXeBasicDefaultDart2Detail Get-VNXeBasicDefaultDart3Detail Get-VNXeBasicDefaultFlareSPADetail Get-VNXeBasicDefaultFlareSPBDetail Get-VNXeBasicDefaultOSSPADetail Get-VNXeBasicDefaultOSSPBDetail Get-VNXeBasicSummaryDart2Detail Get-VNXeBasicSummaryDart3Detail Get-VNXeBasicSummaryFlareSPADetail Get-VNXeBasicSummaryFlareSPBDetail Get-VNXeBasicSummaryGMSSPADetail Get-VNXeBasicSummaryGMSSPBDetail Get-VNXeBasicSummaryOSSPADetail Get-VNXeBasicSummaryOSSPBDetail Get-VNXeInitTimeDetail Get-VNXePoolsDetail Get-VNXePoolTotalsDetail Get-VNXeSystemTotalsDetail Set-VNXeSQLiteLocation |
We can also use the get-help CmdLet to find out the functionality provided by each function. A particulary helpful piece of info in the function help is the example field list and data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 |
PS C:> get-help Get-VNXeSystemTotalsDetail -full NAME Get-VNXeSystemTotalsDetail SYNOPSIS Returns a psobject for the SQLite capacity.db system_totals table SYNTAX Get-VNXeSystemTotalsDetail [<commonparameters>] DESCRIPTION Returns a psobject for the SQLite capacity.db system_totals table PARAMETERS <commonparameters> This cmdlet supports the common parameters: Verbose, Debug, ErrorAction, ErrorVariable, WarningAction, WarningVariable, OutBuffer and OutVariable. For more information, see about_CommonParameters (http://go.microsoft.com/fwlink/?LinkID=113216). INPUTS None, Only requires set-SQLiteLocation to be called prior in session OUTPUTS Array of PSObjects representing table records Record Example: system_totals -------------------------------------------------------- timestamp : 2011-05-03 23:59:15 closing_time : 2011-05-03 23:59:15 allocated_space : 0 total_space : 0 free_space : 0 allocated_protection : 0 total_protection : 0 -------------------------------------------------------- -------------------------- EXAMPLE 1 -------------------------- C:PS>$detailrecords = Get-VNXeSystemTotalsDetail RELATED LINKS </commonparameters></commonparameters> |
Now we can set the location to our downloaded SQLite database. Please see the following post http://henriwithani.wordpress.com/2011/12/01/hidden-vnxe-performance-statistics/ to learn how to retrieve these files. The command is as follows.
|
1 |
PS C:>set-vnxesqlitelocation -path C:TempSQLLiteDB |
Now we can use the other functions to retrieve data. The following example will give use the last record for the System_Totals table in the capacity.db database.
|
1 2 3 4 5 6 7 8 9 |
PS C:>Get-VNXeSystemTotalsDetail | select -Last 1 timestamp : 2012-09-18 12:40:00 closing_time : 2012-09-19 00:00:00 allocated_space : 813556563968 total_space : 939276632064 free_space : 125720068096 allocated_protection : 152458756096 total_protection : 154076708864 |
This also enables some powerful one line commands such as the following which uses a charting tool called PowerGadgets to produce a historical chart that shows total space and allocated space for the system. PowerGadgets is a pay tool which I use, but there are other free ways to do charting such as the MSChart controls included in the .Net framework.
|
1 |
PS C:>Get-VNXeSystemTotalsDetail | out-chart -Values allocated_space,total_space -Label timestamp -Caption "Historical Capacity" -Group "Capacity" -Name "System" |
The following one line command is executed on one of the calculated properties to return basic stats on LUNDiskReadsPerSec
|
1 |
PS C:>Get-VNXeBasicSummaryFlareSPADetail | Measure-Object -Property LUNDiskReadsPerSec -Average -Maximum -Minimum |
After combining some of these techniques and creating a script. The following simple report was produced for a VNXe system in a lab. The system is only configured for iSCSI and has a single performance pool. The report shows statistics and graphs on Capacity, iSCSI bandwitdh, Disk IO, and Cache.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 |
System Total Usable Space GB: 874.76953125 System Allocated Space Rollup ------------------------------------------ 99thPercentile : 813556563968 95thPercentile : 813556563968 Median : 603247869952 Count : 362 Average : 644237841266.563 Sum : Maximum : 813556563968 Minimum : 0 Property : allocated_space Dart2 iSCSI Rollup ------------------------------------------ 99thPercentile : 1494 95thPercentile : 1494 Median : 1492 Count : 1345 Average : 1552.06840148699 Sum : Maximum : 17670 Minimum : 0 Property : iSCSIReadBytesPerSec 99thPercentile : 3 95thPercentile : 3 Median : 3 Count : 1345 Average : 3.07583643122677 Sum : Maximum : 21 Minimum : 0 Property : iSCSIWriteBytesPerSec Dart3 iSCSI Rollup ------------------------------------------ 99thPercentile : 0 95thPercentile : 0 Median : 0 Count : 1345 Average : 0 Sum : Maximum : 0 Minimum : 0 Property : iSCSIReadBytesPerSec 99thPercentile : 0 95thPercentile : 0 Median : 0 Count : 1345 Average : 0 Sum : Maximum : 0 Minimum : 0 Property : iSCSIWriteBytesPerSec Flare SPA Read Cache Hit Ratio Rollup ------------------------------------------ 99thPercentile : 63 95thPercentile : 63 Median : 61 Count : 1345 Average : 60.8565055762082 Sum : Maximum : 73 Minimum : 57 Property : ReadHitRatio Flare SPB Read Cache Hit Ratio Rollup ------------------------------------------ 99thPercentile : 62 95thPercentile : 62 Median : 57 Count : 1345 Average : 56.7970260223048 Sum : Maximum : 63 Minimum : 48 Property : ReadHitRatio Flare SPA IO/Sec Rollup ------------------------------------------ 99thPercentile : 2 95thPercentile : 2 Median : 0 Count : 1345 Average : 0.361338289962825 Sum : Maximum : 3 Minimum : 0 Property : LUNDiskReadsPerSec 99thPercentile : 0 95thPercentile : 0 Median : 0 Count : 1345 Average : 0.0379182156133829 Sum : Maximum : 1 Minimum : 0 Property : LUNDiskWritesPerSec Flare SPB IO/Sec Rollup ------------------------------------------ 99thPercentile : 8235 95thPercentile : 8235 Median : 1094 Count : 1345 Average : 4487.45278810409 Sum : Maximum : 8239 Minimum : 0 Property : LUNDiskReadsPerSec 99thPercentile : 2 95thPercentile : 2 Median : 0 Count : 1345 Average : 0.379925650557621 Sum : Maximum : 2 Minimum : 0 Property : LUNDiskWritesPerSec |
The report above shows us some good basic information about this array. The information about other protocols has been omitted as it did not apply to this system. This system has a very consistent and repetitive workload which is primarily read. The read IOPS of approximately 8000 is seems high for this system, but this is also due to the fact that a large portion of these reads are being served from cache. We see a Read Hit Cache ratio of around 60 which shows the majority of reads are being served from cache which would allow for high IO performance. This system is in a lab and has some VM’s running on it so I assume there is a simulator or something of that sort providing the uniform load.
I hope someone finds this useful and if so please provide feedback on potential improvements. I will try to make periodic updates to the module and also post additional examples here. The next topic will be more on the scripting to utilize the module and produce reports.
I would like to end by saying EMC does not provide any documentation on the SQLite database that I could find. So please use the information and provided code with caution and without warranty of any kind. If anyone from EMC reads this and is willing to provide me with documentation on the SQLite database, particularly the field value definitions/explanations, it would be greatly appreciated.
Regards,
Dave
24 comments
Dave,
Nice work! I have had several requests for performance analytics for the VNXe over the past several months. This is a fantastic start to supporting these requests.
Thank you!!
Disclosure – EMCer here…
This is great work – and shows the incredible power of the partner (and frankly the individual)…. Keep it coming!
(wait until you see what’s in store for the VNXe! 🙂
Great work! Sad that even EMC does not have a tool to do this, but thank goodness for people like you and HenriwithanI.
EMC needs to stop treating the VNXe like a red headed step child and step up with some decent performance analysis!
(thank you for posting this work around)
Great work Dave – this will come in handy for numerous customers!
Awesome awesome awesome. Thanks so much
[…] and packaged them into a nice little module you can download.
[…] Further details can be found at http://muegge.com/blog/?p=172 […]
Maybe it’s obvious but I’m firing away my Q anyway:
Can you apply this “tool” to VNX 5100, 5300, 5500, 5700 (not only VNXe) as well?
Thanks Alfoons
@Alfoons
No this would not be applicable to the VNX as it uses another method to monitor performance. EMC does have a tool called Unisphere Analyzer for he VNX. This software is included in the FAST software suite. So if you purchased FAST with the VNX then you will have this available to you.
Regards,
Dave
I am trying to run this on a Windows 8 64 bit installation. I installed the 64 bit SQL Lite version. All seems well until I try to run the command “Get-VNXeSystemTotalsDetail | select -Last 1”. When I run this, I get the following error:
New-Object : Cannot find type [System.Data.SQLite.SQLiteConnection]: make sure the assembly containing this type is loaded.
I ran the Assembly line to point to the proper DLL. It looks like my Powershell is x86 where as SQLLite is x64. Do you think this may be a problem?
@Shawn,
On a 64bit machine there should be both 32 and 64 bit versions of PowerShell, although I have not used Win8 much yet, so not positive. You are correct, I receive the below error when I try to use the 32bit version of PowerShell on a Win7 machine.
New-Object : Exception calling “.ctor” with “0” argument(s): “An attempt was made to load a program with an incorrect
format. (Exception from HRESULT: 0x8007000B)”
I would try using the 64bit version of PowerShell. Although one thing is odd, this error does not match the error you provided above. The error you provided above indicates the assembly has not been loaded. I would also verify the following line in the VNXePerformance module points to the correct location of the SQLite DLL.
$AssemblyPath = “C:Program FilesSystem.Data.SQLite2010binSystem.Data.SQLite.dll”
This line in the module defines the assembly/DLL location and the following line is what loads it.
Add-Type -Path $AssemblyPath
Regards,
Dave
I am trying to run this on a Windows 2008 R2 64 bit installation.
I installed the 64 bit SQL Lite version.
All seems well until I try to run the command “ Get-VNXeBasicSummaryFlareSPADetail″.
When I run this, I get the following error:
Get-TableDetail : Cannot bind argument to parameter ‘path’ because it is null.
At C:Windowssystem32WindowsPowerShellv1.0ModulesVNXePerformanceVNXePerformance.psm1:1012 char:58
+ $VNXeBasicSummaryFlareSPADetail = Get-TableDetail -path <<<< $path -filename $filename -tablename $tablename
+ CategoryInfo : InvalidData: (:) [Get-TableDetail], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Get-TableDetail
Add-BasicSummaryFlareIOPerSec : Cannot bind argument to parameter ‘InputObject’ because it is null.
At C:Windowssystem32WindowsPowerShellv1.0ModulesVNXePerformanceVNXePerformance.psm1:1014 char:45
+ Add-BasicSummaryFlareIOPerSec -InputObject <<<< $VNXeBasicSummaryFlareSPADetail
+ CategoryInfo : InvalidData: (:) [Add-BasicSummaryFlareIOPerSec], ParameterBindingValidationException
+ FullyQualifiedErrorId : ParameterArgumentValidationErrorNullNotAllowed,Add-BasicSummaryFlareIOPerSec
Do you think this may be a problem?
@watarai
Can you please post your source code?
It appears the function does not have the data it requires to run properly. You may want to make sure the following line is in your code and it points to the location of the SQLite files being used.
C:>set-vnxesqlitelocation -path C:TempSQLLiteDB
Regards,
Dave
Hi Dave
Sorry Late Reply and Thank you your Reply.
Please download from the following URL.
https://psft.networld.co.jp/courier/1000@/mail_user_download.html?url=https%3A%2F%2Fpsft.networld.co.jp%2Fseos%2F1000%2Fmpd%2Fx1262f0%5Ebd057fc5e112af62b8302bd6ed598153%5E2013-04-01%2011%3A59%3A59%5E*%2F1262%2Ffiles%2Fw20130317141624263%2FVNXePerformance.zip
Regards
I have tried this on both Windows 8 64-bit and Windows 2008 R2 but I get the same result. I have tried downloading both the 32 bit and 64 bit version of the SQLite plugin.
New-Object : Cannot find type [System.Data.SQLite.SQLiteConnection]: make sure
the assembly containing this type is loaded.
At C:Windowssystem32WindowsPowerShellv1.0ModulesVNxeperformanceVNXePerfo
rmance.psm1:66 char:21
+ $conn = New-Object <<<< -TypeName System.Data.SQLite.SQLiteConnectio
n
+ CategoryInfo : InvalidType: (:) [New-Object], PSArgumentExcepti
on
+ FullyQualifiedErrorId : TypeNotFound,Microsoft.PowerShell.Commands.NewOb
jectCommand
Property ‘ConnectionString’ cannot be found on this object; make sure it exists
and is settable.
This looks really good and would like to get it working. Thanks!
It seems a common issue people are having is loading the SQLite DLL. Please refer to the comment above @Shawn. Also here is some code to help test SQLite.
# ———— cut ———————————–
# Update the assembly path below to the location of the dll. it is easiest if you use the installer and install to the default location below
$AssemblyPath = “C:Program FilesSystem.Data.SQLite2010binSystem.Data.SQLite.dll”
# Update to the full path of the sqlite file to open
$filename = “c:tempcapacity.db”
Add-Type -Path $AssemblyPath
$conn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
$connstring = “Data Source=” + $filename
$conn.ConnectionString = $connstring
$conn.Open()
# ———— cut ———————————–
Just paste the above code into the ISE, update the paths as necessary and use it to test SQLite. If this code runs successfully then the module should also work.
Regards,
Dave
There are several options to get right when selecting your SQLite version.
First there is the x64/x86 issue which you can avoid by choosing a powershell to match your SQLite installation. On x64 Windows you get both x86 and x64 versions of powershell.
Second there is the version of the .NET framework. You have to have the version which is at least as old as the powershell you are using. If you are using Windows 7 SP1, don’t use the .NETFX 4.5 version of SQLite. The version of the .NET framework released with Windows 7 is 3.5 so that’s your best bet.
The other options are around using setup or not or using bundle or not. If I read the SQLite doc correctly they recommend using the download with setup in the name and without bundle in the name.
Use these commands (in powershell) to test if you have the right bits:
Add-Type -Path “C:Program FilesSystem.Data.SQLite2008binSystem.Data.SQLite.dll”
$conn = New-Object -TypeName System.Data.SQLite.SQLiteConnection
If they fail you’ll get an error message which is both specific and helpful. A rare thing even today.
[…] details can be found at http://muegge.com/blog/?p=172 Diaxon […]
Can some one post an updated link for VNXePerformance.zip ? The current link is broken! Thanks!
Can some repost VNXePerformance.zip / VNXePerformance.zip.psm1 ? It seems the file is unavailable again. Thanks!
so great and informative
thank you
This is super awesomely amazing!
Anyone figure out how to pipe the output from this stuff to Excel instead of PowerGadgets?
Ben,
Thanks and yes you can send the output to Excel. Doug Finke has an excel module at https://github.com/dfinke/ImportExcel. I also have a module I use at https://github.com/dmuegge/MTS_PoSH_Modules.
Also, take a look at the newer post on this topic http://muegge.com/blog/emc-vnxe-performance-powershell-module/.
Regards,
Dave
[…] and packaged them into a nice little module you can download. Â What does this module do? Â Well, check out his blog post here but in a nutshell it queries a ton of stats and performance metrics on your EMC VNXe and presents […]