4.9 Storage Performance Testing
You have likely noticed how we have
stressed that each workload is different. No single storage system will
solve every technical or business problem. Any system must be validated
prior to its production deployment.
You should draw a distinction between validation
and testing. If you are able to re-create your production system with
an exact replica and run exactly the same workload, your results should
mirror production. Unfortunately most administrators are not fortunate
enough to field duplicate test and production systems. Validation
allows these administrators to collect useful data and later apply the
data to real problems.
It is important to understand how your system
will perform given specific parameters. If you are able to predict and
verify the maximum number of IOPS a given system will tolerate, you can
then apply this knowledge to troubleshooting performance issues. If
latency performance counters are high, and you notice that a 20-drive
RAID 1+0 volume is trying to absorb 5,000 IOPS, it should be clear that
you have exceeded the system I/O capacity.
WARNING
Prior to
beginning any storage testing it is important to alert relevant system
owners. Running a peak load test on a shared storage system will impact
all the storage-connected systems. If the testing is executed against
an array with automatic storage tiering, it can cause the system to
migrate test data into higher storage tiers and depreciate important
production data. It is critical to understand the effects of testing,
so be sure to exercise care!
Microsoft has created two tools that are useful
for performance testing: SQLIOSim for system validation, and SQLIO for
performance measurement.
SQLIOSim is designed to simulate SQL Server I/O,
and you can think of it as a functional test tool. It will stress SQL
Server, the Windows Server, and storage, but it won’t push them to
maximum performance. SQLIOSim can be downloaded at http://support.microsoft.com/kb/231619.
Before getting started, note that SQLIOSim will
report an error if latency exceeds 15 seconds. It is perfectly normal
for storage I/O to show occasional high latency. We recommend using the
GUI to run functional system testing (Figure 13), although the tool can be also be executed from a command prompt.
WARNING
Many
advanced storage arrays offer automated tiering and de-duplicating
features. Synthetic test tools like SQLIO and SQLIOSim generate test
files by writing repeating data, usually zeros. Advanced storage arrays
will de-duplicate or return data with artificial velocity. It is
especially important to use a test tool that creates data on top of an
NTFS data partition. If the storage array answers a read request from
data pages that have not been written, it will return zero data
directly from the storage processor. Any of these conditions will
produce performance data that is not representative of actual
production performance. Be sure you understand the capabilities of the
storage platform you are testing. If you receive test performance data
that seems too good to be true, it probably is.
SQLIO is a tool that simply generates I/O. This
application is executed from the command line and does not need SQL
Server to run. Download SQLIO from http://www.microsoft.com/download/en/details.aspx?displaylang=en&id=20163.
We recommend testing the storage system to
performance maximums and setting up a test that mimics your best guess
at a production workload. SQLIO is executed from the Windows command
line. After installing SQLIO, open the param.txt file located in the installation directory. The default path is C:\Program Files (x86)\SQLIO. The param.txt file contains the following code:
c:\testfile.dat 2 0x0 100
#d:\testfile.dat 2 0x0 100
The first line in the param file defines where the test file is located. Use the pound sign (#) to comment out a line in the param.txt
file. The first number following the test file defines the number of
threads per test file. Microsoft recommends setting this to the number
of CPU cores running on the host machine. Leave the mask set at 0x0.
Finally, the size of the test file in MB is defined.
Ensure that the test file is large enough to
exceed the amount of cache dedicated to your storage volumes. If the
host file is too small it will eventually be stored within the array
cache.
SQLIO is designed to run a specific workload per instance. Each time you execute SQLIO.exe
with parameters, it will define a specific type of I/O. It is entirely
permissible to run many instances of SQLIO in parallel against the same
test file.
Be sure to coordinate testing with your storage
administrator before you start these tests, as they are designed to
heavily stress your I/O system. It is best to begin by confirming any
calculated assumptions. If you have 100 10K SAS disks configured in a
RAID 1+0 set, you can assume random I/O will perform 14,000 reads. To
test these assumptions run a single SQLIO.exe instance:
sqlio –kR –s300 –frandom –o1 –b8 –LS –Fparam.txt
The –k option sets either R (read) or W
(write). Set the test run length using the –s function. We recommend
testing for at least a few minutes (this example uses 5 minutes).
Running a test for too short a time may not accurately demonstrate how
a large RAID controller cache will behave. It is also important to
pause in between similar tests. Otherwise, the array cache will reuse
existing data, possibly skewing your results.
The –f option sets the type of I/O to run, either random or sequential. The –o parameter defines the number of outstanding I/O requests. You use the –b
setting to define the size of the I/O request, in KB. This example
tests very small I/O. SQL Server normally reads at least a full 64KB
extent.
You use the –LS setting to collect system latency information. On a 64-bit system you can add the –64 option to enable full use of the 64-bit memory system. Finally, you define the location for the param.txt file using the –F option. Following is the test output:
sqlio v1.5.SG
using system counter for latency timings, 14318180 counts per second
parameter file used: param.txt
file c:\testfile.dat with 2 threads (0-1) using mask 0x0 (0)
2 threads reading for 300 secs from file c:\testfile.dat
using 8KB random IOs
enabling multiple I/Os per thread with 8 outstanding
size of file c:\testfile.dat needs to be: 104857600 bytes
current file size: 0 bytes
need to expand by: 104857600 bytes
expanding c:\testfile.dat ... done.
using specified size: 100 MB for file: c:\testfile.dat
initialization done
CUMULATIVE DATA:
throughput metrics:
IOs/sec: 13080.21
MBs/sec: 102.18
latency metrics:
Min_Latency(ms): 0
Avg_Latency(ms): 0
Max_Latency(ms): 114
histogram:
ms: 0 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24+
%: 37 59 3 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0
This single 8K random test started by
creating the 100MB test file. In this case, the test was generated on a
VM guest using a flash drive, so the results are not representative of
what you might see in a real-world test. The test ran for five minutes
with one outstanding I/O, and generated 13,080 IOPS and 102 MB/sec. It
peaked at 114 milliseconds of latency but averaged under a millisecond
for access.
This test is a perfect demonstration of how a
system can fail to utilize its maximum potential performance. Only
eight outstanding I/Os completed, which failed to push the I/O
subsystem. To properly test a given system, it is a good idea to scale
the workload dynamically to determine how it will perform under
increasing loads. To accomplish this you can script SQLIO:
sqlio –kR –s300 –frandom –o1 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o2 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o4 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o8 –b8 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b16 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b32 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b64 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b128 –LS –Fparam.txt
sqlio –kR –s300 –frandom –o1 –b256 –LS –Fparam.txt
Run this batch file from the command line and capture the results to an output file:
RunSQLIO.bat > Result.txt
Ideally, you will test several different
scenarios. Create a batch file with each scenario that scales from 1 to
256 outstanding I/Os, such as the following:
- Small-block random read performance:
sqlio –kR –s300 –frandom –o1 –b8 –LS –Fparam.txt
- Small-block random write performance:
sqlio –kW –s300 –frandom –o1 –b8 –LS –Fparam.txt
- Large-block sequential read performance:
sqlio –kR –s300 – fsequential –o1 –b8 –LS –Fparam.txt
- Large-block sequential write performance:
sqlio –kR –s300 –fsequential –o1 –b8 –LS –Fparam.txt
Running these scenarios as a single
batch file will automate the I/O system testing process. The most
important data points are the latency and performance measurements.
When the tests exceed your maximum latency tolerance, usually not more
than 20 milliseconds, you have exceeded the capabilities of your I/O
subsystem. If the system does not meet your overall performance
requirements with adequate response times, then you need to investigate
the system for errors or configuration issues. Ultimately, you may need
to optimize the storage system’s performance to ensure that previously
specified requirements are met.
