Optimizing NetApp Block IO for optimal Performance (SQL)

Thanks to

A blog by Nathan Storms (the architect evangelist):

I came across his blog and found it very useful when setting up our Virtual SQL servers. This is focused on optimizing performance within the NetApp Data OnTap solution and not with how you may be connecting.


Thin provisioning, common belief is that thin provisioning weather it is on the volume or the LUN will lead to performance issues due to fragmentation over time and that it is best practice to full reserve your volumes, LUNs and snapshot reserve. This belief is false the WAFL (Write Anywhere File Layout) will fragment sequential blocks over time, environments were you have large files such as virtual disks or large database files are more prone to the effect of a performance impact over time. This is a way by design NetApp writes anywhere on the aggregate in a effort to reduce the latency to write data to disk, the problem is that over time the performance grains of sequential reads are lost as data becomes fragmented and read ahead benefits are lost. Given how data is written their is no reason to fully reserve you volumes and LUN’s. Next lets look at how to easily fix this and other performance issues.
First we need to configure a NetApp option for de-duplication, by default when you enable sis on a volume the schedule is to automatic and started when a percentage of delta occurs on the volume. This is great as long as you a bunch of volumes and multiple volumes don’t decide to de-dupe at the same time, so you could set each volume on a schedule and de-dupe volumes that don’t need it. Or you could limit the number of concurrent volume that can de-dupe at the same. Use the following command to set the limit:

NetApp>options sis.max_active_ops 1

Example: Thin provisioning block storage to a Hyper-V host for a virtualized SQL server, the VM will need 4 virtual disks:

• 60GB: Operating System Disk
• 400GB: SQL Data Disk
• 200GB: SQL TLog Disk
• 100GB: SQL TempDB Disk

The first step is to perform a sizing calculation we are going to allocate a lot of white space on disk be it won’t be lost because it is all thin provisioned.

120GB = 60GB Virtual Disk + 60GB for Hyper-V Config and Hyper-V Snapshots + 24GB (20% Reserve)
480GB = 400GB Virtual Disk + 80GB (20% Reserve)
240GB = 200GB Virtual Disk + 40GB (20% Reserve)
120GB = 100GB Virtual Disk + 20GB (20% Reserve)

Adding it up we will provision 960GB in LUN allocations if we add our NetApp 20% snapshot reserve (196GB) we will end up creating a thin provisioned volume that is 1156GB in size.
Note: If we were building two SQL servers for database mirroring we put LUNs for both virtual SQL servers in the same volume so that de-duplication would reclaim additional storage.
To create the volume and LUNs and map them to a hyper-v host initiator called HV1 we would do the following:

vol create volSQL1 –l en_US –s none aggr0 1156GB
vol options volSQL1 no_atime_update on
vol options volSQL1 snapshot_clone_dependency on
vol options volSQL1 read_realloc space_optimized
snap autodelete volSQL1 on
snap sched volSQL1 0 1 0
sis on /vol/volSQL1
sis config –s auto /vol/volSQL1
reallocate measure /vol/volSQL1
qtree create /vol/volSQL1/qtree
lun create –s 144g –t hyper_v –o noreserve /vol/volSQL1/qtree/SQL1_OS.lun
lun create –s 480g –t hyper_v –o noreserve /vol/volSQL1/qtree/SQL1_DATA.lun
lun create –s 240g –t hyper_v –o noreserve /vol/volSQL1/qtree/SQL1_TLOG.lun
lun create –s 120g –t hyper_v –o noreserve /vol/volSQL1/qtree/SQL1_TEMPDB.lun
lun map /vol/volSQL1/qtree/SQL1_OS.lun HV1
lun map /vol/volSQL1/qtree/SQL1_DATA.lun HV1
lun map /vol/volSQL1/qtree/SQL1_TLOG.lun HV1
lun map /vol/volSQL1/qtree/SQL1_TEMP.lun HV1

The script above does the following tasks:

• Create a new thin provisioned volume in aggr0
• Set the volume to not record the last access timestamps on the LUNs within the volume
• Set  the volume allow us to delete snapshots from the volume even if a busy snap is present
• Set the volume perform read reallocations, (read below for more detail)
• Set the volume to automatically delete snapshots if you are running of space
• Set the volume to not take snapshots as this should be done with a NetApp SnapManager
• Enabling de-duplication on the volume
• Setting de-duplication on the volume to automatic
• Enabling reallocation measurements on the volume
• Creating a qtree within the volume
• Creating multiple thin provisioned LUNs for host type of Hyper-V
• Mapping multiple LUNs to a initiator with a name of HV1

To resolve the defragmentation created over time by WAFL we use reallocation (defragmentation) their are two ways to reallocate:

1. Manual reallocation, if you have never preformed a reallocation on an existing volume you may want to manually run a single pass of a reallocation task. Note to run a manual reallocation you must remove your snapshots from the volume. This may take some time to complete and is an expensive operation on system resources.
2. Read reallocation, as blocks are read the NetApp automatically examines the block and optimizes the data layout by moving the block to an optimized location on disk (small defragmentation)

This is interesting because as data is written to the WAFL in a fragmented manager for performance when your application reads that data it automatically gets optimized for performance. This also has another advantage if your IO pattern changes it will automatically adjust to the new pattern. For example if a SQL table was re-indexed/reorganized on disk or the access pattern was changed by a modification to a stored procedure the NetApp’s read reallocation would automatically detect the change in the IO pattern and optimize it on disk.

Reallocation measurement was turned on so that you can pull a reallocation status to see the state of fragmentation on your volumes with ‘reallocate status’.

Using this design you will obtain the cost benefits of thin provisioning, and de-duplication while maintaining optimal performance over time automatically.

How to optimize disk read performance in NETAPP DataONTAP

Issue:

Read performance on a LUN presented by a Netapp controller is becoming increasingly sluggish or slow.  You may have noticed high latency times (20ms or higher) or the latency of the LUN has steadily increased over time as in the examples shown below.

Example graph showing latency trending over time

Example output from the stats show command placed in a table for ease of display.

	avg_latency
Instance	ms
/vol/volume_name/lun1-Xn/8W3OdfJbw	28.12
/vol/volume_name/lun1-Xn/8W3OdfJbw	9.36
/vol/volume_name/lun1-Xn/8W3OdfJbw	11.38
/vol/volume_name/lun1-Xn/8W3OdfJbw	28.27
/vol/volume_name/lun1-Xn/8W3OdfJbw	22.88
/vol/volume_name/lun1-Xn/8W3OdfJbw	11.75
/vol/volume_name/lun1-Xn/8W3OdfJbw	14.6

Causes:

Just like all other file systems the WAFL file system used by Data Ontap can be a victim of fragmentation in very large files and LUNs.  Most likely your LUN (aka a single large file) has become fragmented and you should perform a defragmentation by running the reallocate command against the LUN or volume.

Solution:

Run the following command against the LUN to check for fragmentation.

reallocate measure -o /vol/volume_name/lun

Check the status by using this command

reallocate status

Example output

/vol/volume_name/lun

        State: Checking: snap 0, public inode 101, block 943500 of 19661543

     Schedule: n/a

     Interval: 1 day

 Optimization: 5 [hot-spots: 18]

  Measure Log: n/a

The optimization scale is from 1(optimized) to 10 (very un-optimized) and is how the WAFL file system identifies fragmentation.  The usually accepted minimum threshold for optimization is 3 or 4 with no hot spots.

To defragment and optimize the LUN run the following command

reallocate start -f -p /vol/volume_name/lun

The -f (force) option performs a one-time full reallocation of a file, LUN or an entire volume.

The -p option reduces the extra storage requirements in a flexible volume when reallocation is run on a volume with snapshots.

Terminating a NetApp NDMP session

If an NDMP session is not responding, you can terminate it using the ndmpd kill command. The ndmp kill command allows non-responding sessions to be cleared without the need for a reboot.

Step

1. To terminate an NDMP session, enter the following command: 

ndmpd kill session

session is the specific NDMP session you want to terminate.

Note: If you want to terminate all NDMP sessions, use the "ndmpd killall" command.


To view any or all running sessions run "ndmpd status"

For example:

> ndmpd status ndmpd ON.
Session: 79
Active
version: 4
Operating on behalf of primary host.
tape device: not open
mover state: Idle
data state: Idle
data operation: None


> backup status
ID State Type Device Start Date Level Path
-- ----------- ---- ------ ------------ ----- ---------------
0 RESERVED NDMP
1 RESERVED NDMP
2 RESERVED NDMP
3 RESERVED NDMP
4 RESERVED NDMP
5 RESERVED NDMP
6 RESERVED NDMP
7 RESERVED NDMP
8 RESERVED NDMP
9 RESERVED NDMP
10 RESERVED NDMP
11 RESERVED NDMP
12 RESERVED NDMP
13 RESERVED NDMP
14 RESERVED NDMP
15 RESERVED NDMP
16 RESERVED NDMP
17 RESERVED NDMP
18 RESERVED NDMP
19 RESERVED NDMP
20 RESERVED NDMP
21 RESERVED NDMP
22 RESERVED NDMP
23 RESERVED NDMP
24 RESERVED NDMP
25 RESERVED NDMP
26 RESERVED NDMP
27 RESERVED NDMP


Now I know I can just do:

> backup terminate #  

Netapp NDMP copy command examples

You can migrate data from the source path to a destination path on the same storage system or to a different destination path on a remote host. You can also migrate data from a source path on a remote host to a destination path on the same host or to a destination path on a remote host.

In these examples, myhost is used for a local storage system and remotehost1 and remotehost2 are used for remote storage systems. If you specify host names when you use the ndmpcopy command, the storage system running the ndmpcopy command should be able to resolve these names to their IP addresses.

Example of migrating data from a source path to a different destination path on the same storage system

This sample command migrates data from a source path (source_path) to a different destination path (destination_path) on the same storage system (myhost).

myhost>ndmpcopy -sa username:password -da username:password 
myhost:/vol/vol0/source_path myhost:/vol/vol0/destination_path

The following shorter form of the command achieves the same purpose:

myhost>ndmpcopy /vol/vol0/source_path
/vol/vol0/destination_path

Because you are running the ndmpcopy command on myhost and the source and destination storage system are the same as myhost, you can omit the source and destination storage system names on the ndmpcopy command line. When your ndmpcopy command is running on the same storage system as the source storage system or destination storage system, you can also omit the -sa or -da options.

Example of migrating data from a source path to a different destination path on a remote host

This sample command migrates data from a source path (source_path) to a different destination path (destination_path) on remotehost1.

myhost>ndmpcopy -da username:password /vol/vol0/source_path 
remotehost1:/vol/vol0/destination_path

The destination storage system must be specified in this case, because it is a remote storage system. The destination authorization is needed, but not the source authorization.

Example of migrating data from a source path on remote host to a destination path on the local storage system

This sample command migrates data from a source path (source_path) on remotehost2 to a destination path (destination_path) on myhost.

myhost>ndmpcopy -sa username:password -st text 
remotehost2:/vol/vol0/source_path /vol/vol0/destination_path

The source authentication type specified by -st is text. The ndmpcopy command tool running on myhost will authenticate with the source storage system using text authentication.

Example of migrating data from a source path on a remote host to a destination path on another remote host

This sample command migrates data from a source path (source_path) on remotehost1 to a destination path (destination_path) on remotehost2.

myhost>ndmpcopy -sa username:password -da username:password -l 1 
remotehost1:/vol/vol0/source_path 
remotehost2:/vol/vol0/destination_path

The -l 1 option is used to do a level 1 transfer.

Example of overwriting the /etc directory during the root volume migration

Without the -f option, the /etc directory and its contents on the root volume of remotehost1 are protected from being overwritten with the/etc directory from myhost. This helps prevent unintentional changing of the system characteristics after the root volume migration is completed.

myhost>ndmpcopy -da username:password /vol/rootvol remotehost1:/vol/rootvol

To intentionally overwrite the/etc directory during the root volume migration, use the -f flag as in the following example.

myhost>ndmpcopy -da username:password -f /vol/rootvol remotehost1:/vol/rootvol

Example of the ndmpcopy command where the address modes are explicitly set to IPv6

This sample command explicitly sets the control connections and the data connection to use IPv6 address mode. In this command remotehost1 is the host name that resolves to an IPv6 address.

myhost>ndmpcopy -sa username:password -da username:password -l 0 -mcs inet6 -mcd 

inet6 -md inet6 remotehost1:/vol/vol0/source_path [2001:0db8::10]:/vol/vol0/desti

nation_path

NetApp Vol Copy commands for Data Ontap

Information:

The vol copy command has several uses the most common use is to copy or move a volume from one aggregate to another aggregate on the same Netapp controller.  The following are the general steps and associated perquisites needed to complete a move (copy and manually delete) operation while keeping the original source in place for a period of time should it be needed.

Note:
If you get the following error while trying to connect to a remote filer. Then enable rsh "options rsh.enable on" on both controllers then run the command.

Source_Filer: Connection refused
VOLCOPY: Could not connect to filer Source_Filer.
VOLCOPY: Cannot Init Input, aborting

Restrictions

• The destination volume can’t be the root volume
• The destination volume must be offline.
• All existing data in the destination volume will be over written
• The destination volume must be >= the source volume size
• The source and destination volumes must be of same type (traditional, flexible, 32bit, or 64bit volumes).  To copy from 32bit to 64bit or from traditional to flexible you must use ndmpcopy.

Note: You can use Netapp Filerview or Netapp System Manager to perform most other steps, however all examples given are using the command line.

General Steps to follow

1)  The target volume options and other settings (snap schedule, etc) will be copied over from the source volume. The one exception to this is; LUN mappings are not copied to the target volume.  LUN mappings must be manually recreated on the target volume. You can use the following commands to review current settings as needed.

vol status -v source_volume

vol options source_volume

vnap sched source_volume

2)  Create the target volume

a.  Command line example

vol create target_volume aggr_01 100g

3)  Configure the target volume to be offline

a.  Command line example

vol offline target_volume

4)  If the source volume is currently in use, you should disable access, shutdown the server accessing the volume, remove the lun mappings, etc.  Which option(s) you use may vary.  The general idea is to make sure no changes are being made to the volume while the copy is in progress.

5)  Start the copy process, select one of the following commands based on your needs.

a.  Copy source volume to the target volume, do not copy the snapshots.

vol copy start source_volume target_volume

b.  Copy source volume to the target volume, and one snapshot (nightly.1)

vol copy start -s nightly.1 source_volume target_volume

c.  Copy the source volume and all its snapshots to the target volume

vol copy start -S source_volume target_volume

d.  Once the copy is started do not press Control – C and do not close the RSH window, this will terminate the copy process.

e.  If using SSH you will loose access to the console while the copy is in progress.  It is recommended you use RSH to initiate multiple vol copy commands, but only over a reliable and secure network connection.

f.  You are limited to 2 vol copy start commands when copying on a single Netapp controller, or 4 vol copy start commands when copying between two Netapp controllers.  This is because each Netapp controller supports up to 4 concurrent copy processes.  However, each vol copy start command initiates two separate processes, one to read and the other to write.

6)  To monitor the copy progress from either an SSH or RSH session use this command.

a.  vol copy status

7)  Bring the new volume and LUN online, change configuration as needed to point to the new volume/LUN.  This could be deleting and recreating the CIFS shares, changing the LUN mapping to the server, etc.  What configuration changes need to be completed will vary.  The general idea is to make sure the new volume is now being used and the old volume is not being used.

8)  On the target volume delete the snap used by the vol copy command

snap list target_volume

snap delete -V target_volume snapshot_for_volcopy

9)  Keep the old volume around as a fail safe for a few days or weeks as needed.

10)  When appropriate take the old volume offline and then delete

a.  Command line example.

vol offline source_volume

vol destroy source_volume

11)  Using Netapp Filerview or Netapp System Manager rename the new volume to the old volume name if needed.

To copy between Netapp Controllers (filers) you must use the following syntax.

vol copy start filer1:source_volume filer2:target_volume

NetApp - ndmpcopy - transfering directory trees between filers using NDMP

Ndmpcopy transfers data between filers/vfilers using the Network Data Management Protocol (NDMP) versions 3 and higher. This process can be thought of as creating a dedicated data pipe between dump running on the source filer and restore running on the destination filer.
 
Ndmpcopy is supported on vfilers, as well as the physical filer named vfiler0. Use vfiler context or vfiler run to issue Ndmpcopy commands on a specific vfiler. See na_vfiler(1) for details on how to issue commands on vfilers. The use of Ndmpcopy on vfilers requires a MultiStore license.

EXAMPLES

In these examples, network host names are used for the filers. ("myhost" is used for a local filer and "remotehost1" and "remotehost2" are used for remote filers.) If you specify host names when you use the ndmpcopy command, the filer running the ndmpcopy command should be able to resolve these names to their IP addresses. You could use the ping command to make sure that host name resolution works.

Before starting the ndmpcopy operation, the NDMP request daemon ndmpd has to be enabled on both the source and destination filers. Issue `ndmpd on' on both filers to enable the request daemon.
Example 1:
This command migrates data from a source path (source_path) to a different destination path (destination_path) on the same filer (myhost).

     myhost> ndmpcopy -sa username:password
             -da username:password
             myhost:/vol/vol0/source_path
             myhost:/vol/vol0/destination_path

You can also use this shorter form of the command to achieve the same result.

     myhost> ndmpcopy /vol/vol0/source_path
             /vol/vol0/destination_path

Because you are running the ndmpcopy command on myhost and the source and destination filer is the same as myhost, you can omit specifying the source and destination filer names on the ndmpcopy command line. Also note that when your ndmpcopy command is running on the same filer as the source filer and/or destination filer, you can omit the -sa and/or -da options. Example 2:
This command migrates data from a source path (source_path) to a different destination path (destination_path) on remotehost1.

     myhost> ndmpcopy -da username:password
             /vol/vol0/source_path
             remotehost1:/vol/vol0/destination_path

The destination filer must be specified in this case, because it is a remote filer. Also the destination authorization is needed, but not the source authorization. Example 3:
This command migrates data from a source path (source_path) on remotehost2 to a destination path (destination_path) on myhost.

     myhost> ndmpcopy -sa username:password -st text
             remotehost2:/vol/vol0/source_path
             /vol/vol0/destination_path

The source authentication type specified by -st has been set to text. The ndmpcopy command tool running on myhost will authenticate with the source filer using text authentication. Example 4:
This command migrates data from a source path (source_path) on remotehost1 to a destination path (destination_path) on remotehost2.

     myhost> ndmpcopy -sa username:password
             -da username:password -l 1
             remotehost1:/vol/vol0/source_path
             remotehost2:/vol/vol0/destination_path

Note that the -l 1 option is used to do a level 1 transfer. Example 5:
This command describes the behaviour of ndmpcopy without the -f option. In this case, the /etc directory and its contents on the root volume of remotehost1 are protected from being overwritten with the /etc directory from myhost. This is intended to avoid the unintentional changing of the system characteristics after the root volume migration is completed.

     myhost> ndmpcopy -da username:password /vol/rootvol
             remotehost1:/vol/rootvol

If you intentionally wish to overwrite the /etc directory, during the root volume migration, then use the -f flag as in the following copy.

     myhost> ndmpcopy -da username:password -f /vol/rootvol
             remotehost1:/vol/rootvol

Enabling Auditing on NetApp CIFS shares/Volumes

Enabling Auditing in NetApp Cifs volume

Enable Auditing
Telnet to filer

NetApp Filer > options cifs.audit.enable on
This will enable auditing on cifs volume. The disadvantage of this we need to save manually to stop the auditing ( i will tell you how we can do it automatically)

Save Cifs auditing
NetApp Filer>cifs audit save -f

Automatically save auditing
NetApp Filer > options cifs.audit.autosave.ontime.enable on
NetApp Filer >cifs.audit.autosave.onsize.enable on

Where we can see the audited logs ?
/etc/log/adtlog.evt

Run –> //filername/etc$
Go to etc folder then log folder. There you can see adtlog.evt

To view in Windows OS:
It’s a event viewer file. Go to Event Viewer –> Right click –> open log file–> show this path
( try to mount this CIFS volume before it shows the path in event viewer). Select security log while selecting open.
You should be able to see similar Windows like Auditing. Object Access, log on/ log off category,...etc

CIFS auditing options on NetApp filer

options cifs.audit
cifs.audit.account_mgmt_events.enable off
cifs.audit.autosave.file.extension timestamp
cifs.audit.autosave.file.limit 0
cifs.audit.autosave.onsize.enable on
cifs.audit.autosave.onsize.threshold 75%
cifs.audit.autosave.ontime.enable on
cifs.audit.autosave.ontime.interval 1d
cifs.audit.enable            on
cifs.audit.file_access_events.enable on
cifs.audit.liveview.enable   off
cifs.audit.logon_events.enable on
cifs.audit.logsize           524288
cifs.audit.nfs.enable        off
cifs.audit.nfs.filter.filename
cifs.audit.saveas            /etc/log/adtlog.evt