Storage – Strip Unit Size

Background

A Storage disk’s Strip Unit Size is often taken into consideration as one considers storage alignment.

So I am finishing up and closing all opened Google’s Chrome Tab, and went back and read:

Disk Partition Alignment (Sector Alignment) for SQL Server: Part 4: Essentials (Cheat Sheet)
http://blogs.msdn.com/b/jimmymay/archive/2008/12/04/disk-partition-alignment-sector-alignment-for-sql-server-part-4-essentials-cheat-sheet.aspx

Jimmy May’s paper principally deals with Microsoft products: SQL Server on Windows OS, but his formula is very generalized.

 

Strip Unit Size – What does it matter?

Let us see how it plays out:

Here is Jimmy May’s formula:

Three Values - Two Essential Correlations

Jimmy’s document says:

  • Perform these calculations for each partition which must result in integer values
  • Of the two, the first is far more important.  Use the information below to divine this information.

And, here is how he says to get “File Allocation Unit Size” and also “Starting Offset”:

GetJimmysNumbers

It is left to the inquiring mind how to get “Stripe Unit Size” as that number is not based on the OS, Microsoft Windows, in this case; but based on the Vendor and each Application.

Matrix – Vendor

Here are some numbers for various vendors and models:

Strip Unit Size
Vendor Model# Strip Unit Size
EMC Clarion  64k
NetApp (all models)  4k
HP (all models)  64k -to- 256k

Matrix – Application

Here are popular applications along with their Block Size.

Block Size
Vendor Application Block Size
Microsoft SQL Server  64 kb
Hadoop HBASE  64 kb
MySQL\Percona InnoDB  512 Bytes

Matrix – Application – MySQL/Inno DB

http://www.percona.com/doc/percona-server/5.5/scalability/innodb_io_55.html?id=percona-server:features:innodb_io_55

This variable changes the size of transaction log records. The default size of 512 bytes is good in most situations. However, setting it to 4096 may be a good optimization with SSD cards. While settings other than 512 and 4096 are possible, as a practical matter these are really the only two that it makes sense to use. Clean restart and removal of the old logs is needed for the variable innodb_log_block_size to be changed.

Calculation

Calculation – NetApp

  1. NetApp’s default Strip Unit Size is 4K and Microsoft’s SQL Server best practice suggest using a File Allocation Unit Size of 64K.
  2. It does not take much calculation to deduce that 4K / 64K will not render an integer value, but a fractional value
  3. Please keep in mind that for NTFS, the default Strip Unit Size is 4K and one will get a whole number of 1 (Strip Unit Size / File Allocation Unit Size = 4 K / 4K = 1)

References

References – Vendor – Microsoft

References – Vendor – NetApp

References – Vendor – EMC

References – Vendor – HP

References – Vendor – Oracle

References – Vendor – MySQL

References – Technology – Hadoop

References – Changing Block Size

Technical: NetApp – MPIO – Path Details

Technical: NetApp – MPIO – Path Details

As part of NetApp diagnostic, you might need to dig deep into which paths are actually being used.

MPIO Path Details

  1. Launch Computer Management
  2. In the left panel, Access Storage \ Data OnTap(R) DSM Management \ Disk Management
  3. In the right panel, select the Disk
  4. Right Click on the the Disk Nth, and in the ensuring “Drop-down” menu, select the “Properties”
  5. The “NETAPP LUN Multi-Path Disk Device Properties” window appears
  6. The paths are listed in the “This device has the following paths”
  7. Double-clicked on the path you want to dig into …
  8. The “MPIO Path Details” window appears

NetApp - MPIO Path Details

The following areas are displayed:

  • Number of Reads
  • Number of Writes
  • Bytes Read
  • Bytes Written

 

Note that in the instructions above, you can not select the “Logical Disk”.

 

Computer Management

 

So in the screen above, please select “Disk 4” and right click on that selection.

 

Microsoft – SQL Server – Datafiles – Log File Write Patterns

One of the first emails I received this morning was one detailing one of my wrong assumptions about NetApp LUN (mis) alignment determination:

NetApp Lun Aligning
https://danieladeniji.wordpress.com/2012/12/13/netapp-lun-aligning/

And, so I read up some more and tried argumenting that blog with any new data on the Net.

And, I still came away with something that I did not fully disclose earlier.  And, that is that seemingly LUNS dedicated to MS SQL Server log files are registering as mis-aligned when ‘profiled’ within NetApp.

The specific NetApp commands for validating alignment:

  • priv set diag; lun alignment show <lun-name>
  • lun show -v <lun-name>

so what to do, but take to Google.  It is a bit difficult to get a good Google “Search Item”, but managed to do OK.

Here are some relevant entries:

Since both Kendra back in May 29th, 2012 and MS Premier SQL Server Engineering on May 23rd, 2013 says to use SysInternals’s Process Monitor and I am ‘ve a big fan of Mark Russinovich,  I took to it.

In SysInternals – Process Monitor, filtered for :

  • Process Name –> sqlservr.exe
  • Operation –> WriteFile

Here is SysInternal’s Process Monitor results page:

MS SQL Server - Log File - Write Patterns

This much is obvious:

  • In the “Details” column, the length of most Log Files writes are 61,440 bytes (60 KB)
  • This holds true for both entries written to our local drive (D:) and our Network Drive (Y:)
  • Testimonial to how MS SQL Server Log files are written, our entries are written sequentially and when one adds up the offset to the size, one will arrive at the new line — i.e if one takes 127,044,608 (offset) + 61,440 (length), one will arrive @ 127,106,048 (the start of the next line)
  • Other important facts are the I/O Flags : Non-cached and Write-Through.  SQL Servers writes are not cached, as they are persisted directly to disk

The fact that log entries are persisted directly to disk might explain what we see on our SAN.  The SAN is reporting that our writes are misaligned – The SAN is expecting us to come in bursts, but we write out to the Lun per each transaction commits.

In conclusion, it appears that for SQL Server Log files we will more likely than not report misaligned LUNs.

Addendum

Addendum – 2013.04.02

Data Storage for VDI – Part 8 – Misalignment
http://storagewithoutborders.com/2010/07/21/data-storage-for-vdi-part-8-misalignment/

On March 3rd, 2013, John Martin said…

Partial writes on an Oracle redo log file (or any other file which is written to sequentially) are handled pretty well by the existing partial write mechanisms inside of ONTAP. For the most part these are held in memory until the subsequent writes to the log file come in and these are combined internally into a single 4K block. The real killer is partial overwrites which I think I covered off in a blog post here

 

References

NetApp – nSanity

NetApp – nSanity

http://support.netapp.com/NOW/download/tools/nsanity/

nSANity Data Collector is a support tool designed to aide users and technical support in troubleshooting complex issues. nSANity is able to collect diagnostic and configuration data from a variety of components including:

To get it, you need a NetApp account.

Here are a couple of usage documentation.



Syntax:

    nsanity windows:[domain-name]\[user-name]:*@[host-name]

Sample - connect to local computer, using current user's credentials

   nsanity windows://localhost

Sample - use current user's credentials

   nsanity windows://dbHR

Sample (enter password in clear text)

   nsanity windows://corp\daniel:mypwd@dbHR

Sample (enter password when prompted)

   nsanity windows://corp\daniel:*@dbHR

There are some important details when you run this on a MS Windows platform and target a MS Windows host.  Here they are:

  • If you enter credentials and target your current machine, ensure that the username and password are correct; as things will not work otherwise.  Even though, this requirement must be met you will subsequently be told “User credentials can not be specified for local connections, retrying with current user credentials
  • Based on one the comments posted by a user, the password entered can not be more than 8 characters. I can confirm that as version 1.2.10 that is no longer the case.

On MS Windows Host:

On MS Windows host, please make sure that you have the following installed:

http://updates.mistral.net/netapp/Tools/nsanity/nsanity_userguide.pdf

MFC90 – Requirement

The Windows executable requires MFC90 Runtime libraries, which are included with Windows 7. If your Windows host does not have the required libraries then they may be downloaded from Microsoft at the following URL.

MFC 90 is bundled with Microsoft Visual C++ 2008 SP1

FCINFO for Windows 2003

Microsoft Windows 2003 hosts require an additional package in order to allow complete data collection of HBA information. The Microsoft package is call fcinfo, which provides the HBAFAPI and WMI classes to access the API.

This package may be downloaded directly from Microsoft:

http://www.microsoft.com/downloads/details.aspx?familyid=73d7b879F
55b2F4629F8734Fb0698096d3b1&displaylang=en

References:

NetApp – Lun Aligning

NetApp – LUN Aligning

Determining Alignment on NetApp LUNS

Command:

Syntax:


   priv set diag
   lun show -m
   lun alignment show [lun-name]

Sample:


    priv set diag
    lun show -m
    lun alignment show /vol/volDBBackup/q0/bkDaily

Result:


  /vol/volDBBackup/q0/bkDaily 
    Multiprotocol type: windows_2008
    Alignment: aligned
    Write alignment histogram percentage: 99, 0, 0, 0, 0, 0, 0, 0 
    Read alignment histogram percentage: 100, 0, 0, 0, 0, 0, 0, 0
    Partial writes percentage: 0
    Partial reads percentage: 0

In our case:

  1. We have a volume correctly “formatted” as a MS Windows 2008 Volume
  2. Based on the Alignment entry, we are Aligned
  3. The Write and Read Alignment histogram percentage has most of our non-zero entries on the left ; this indicates that data is being written and read during the initial cycles
  4. And, finally our partial writes and reads percentile are close to zero

Crediting:
http://www.warmetal.nl/lunaligning

Now how does this work? See the alignment line, that is determined from the amount of misaligned requests. Note that request can be made as well as from the OS as from applications like databases. The numbers in line after the histogram are divided in 8 equal percentages. Every number is the amount 12.5% aligned steps, from really aligned to not aligned at all. So the higher the number on the left, the better.
Sample of a MisAligned LUN:

 
    /vol/volDBBackup/q0/weekly
    Multiprotocol type: windows_2008
    Alignment: misaligned
    Write alignment histogram percentage: 4, 3, 4, 5, 5, 4, 4, 3
    Read alignment histogram percentage: 6, 4, 5, 18, 11, 4, 3, 2
    Partial writes percentage: 64
    Partial reads percentage: 43

In conclusion, as a DBA you ‘re staying up late at night and during the day ‘ving Joshua Ladder’s fights and conversations with your Storage Engineers.  Next in line are calls to OS, Database, and Application Vendors.

A bit like trying to make sense of it all …

You have read Jimmy May and Denny Lee’s published papers on “Disk Partition Alignment”.   And, have taking a liking to Joe Chang’s Database & Storage poetries.

And, so now you ‘re wondering what to do.

Well like all problems worth your time, it depends:

  1. Understand your Application and read through everything the Vendor has placed in the Public Domain per I/O interaction
  2. Classify your Application – Is it doing straight through or batched writing
  3. How much pre-fetch is occurring
  4. If Database, are you really following best practice and have dedicated LUNS for Data, Log, Temporary, and Backup purpose
  5. Are your files pre-sized; and if not, do you have well orchestrated growth strategy
  6. Back to the purpose of the LUN, if SQL Server, is this the Active or Passive side of a Mirrored Pair

Addendum

Addendum

Addendum – 2013-04-02

For me the principal basis for quickly getting information out and broadly sharing is that you allow that information to be read and washed out in public.

Early today, received a comment via email that some of my reading about NetApp LUN Alignment is a bit wrong:
The part about the NetApp filer is wrong. The 8 figures in the histogram row are not about the percentage of overall misalignment, they are about which 512 Byte “sector” of a 4k wafl block got hit the most. Obviously you want to have 100% at the first figure, and all zeroes on the rest. Such a histogram would tell you that everything is aligned perfectly. If you have the highest value on the 7th figure (or 6th? cant remember of the top of my head) its probably a misaligned XP or windows 2003 system accessing the LUN. if you have spikes on the 1st and the 7th value, then the LUN is probably a vmware datastore, with some aligned and some misaligned VMs.
And, so here I am trying to properly credit the commenter and see if there is new information on the Net along same lines.

And, as always Google brought us joy.

LUN (mis) alignment:

gdefevere
lun (mis)alignment
‎2011-07-14 06:47 AM – edited ‎2015-12-18 01:25 AM
LABELS: SnapManager
Link

 

Command:

Syntax:


   priv set -q advanced;
   lun show -v

Sample:


    priv set -q advanced;
    lun show -v

NetApp - Lun Show -v

On this new Filer, returned to running “lun alignment show”:

Command:

Syntax:


   priv set advanced
   lun alignment show 

Sample:


    priv set advanced
    lun alignment show -m

NetApp - Lun alignment show

Explanation:

  • The “partial writes percentage” and “partial reads percentage” numbers returned from “lun alignment show” is congruent with the summative data from “lun show -v”; specifically from “Alignment entry” –> misaligned /aligned.
  • In our case, the two LUNS that indicate “misaligned” are the luns dedicated to SQL Server database logs.

On the Windows Host, to determine the “bytes per cluster” for a logical drive, issue:

Syntax:


    fsutil fsinfo ntfsinfo [logical-drive]:

Sample:


    -- get bytes per cluster for logical drive L:
    fsutil fsinfo ntfsinfo L:

MS - Windows - Drive - Bytes per cluster

Again for Microsoft OS, you want to see “Bytes per cluster” coming in as 65536.
On the Windows Host, to determine the Partition “StartingOffset” for a logical drive, issue:

Syntax:

    wmic partition get BlockSize, StartingOffset, Name

WMIC Partition

 

Based on NetApp Published guidance, this number can be one any of 3 numbers depending on the Version of Windows and the Disk Format Style:

How to diagnose misaligned I/O on Windows hosts
Link

  • Windows (MBR): This number should be 32256, 31.5kb offset is used when the LUN is created with the Windows LUN type (31.5 * 1024 = 32256).
  • Windows (GPT): This number should be either 33,571,840B (32MB+17KB if LUN is <16GB) or  134,235,136 (128MB + 17KB if LUN >=16GB).
  • Windows (2000): This number should be 65535 bytes for LUNs smaller than 4GB or 1048576 bytes for LUNs that are 4GB or larger.

Our Starting Offset is 1048576 and so it matches what we should per the NetApp KB listed above.

Addendum – 2013-05-16

Here is mostly the same information, but looked at with more understanding.

It is a silly thing, but like Snoop said:
… You don’t think of all of that, until you talk about all of that
https://danieladeniji.wordpress.com/2013/04/02/technical-microsoft-sql-server-datafiles-log-file-write-patterns/

References

  1. Lun Aligning
    http://www.warmetal.nl/lunaligning
  2. New VDMK Misalignment Detection Tools in dataontap 725 and 802
    https://communities.netapp.com/community/netapp-blogs/getvirtical/blog/2011/05/13/new-vmdk-misalignment-detection-tools-in-data-ontap-735-and-802
  3. LUN (Mis)alignment
    https://communities.netapp.com/message/58686
  4. How fragmentation on incorrectly formatted NTFS Volumes affects Exchange
    http://blogs.technet.com/b/mikelag/archive/2011/02/09/how-fragmentation-on-incorrectly-formatted-ntfs-volumes-affects-exchange.aspx
  5. How to diagnose misaligned I/O on Windows hosts
    https://kb.netapp.com/support/index?page=content&id=1010803