Mystery of IOPS

Common issue I’ve ran in to over the years is low performing farms, often times the cause is directly related to the disk throughput which is measurable as IOPS (IO Operations Per Second). However gauging and determining both what a good IOPS score is and how to test and measure it is almost an art.

Now if you’re a SQL DBA you’re probably very familiar with SQLIO the tool used in general for testing IOPS. SQLIO is a command line tool that lets you generate a bunch of disk I/O and then measures the result of operations completed over the specified time period. Straight forward so far right? Now the part that seems to catch a lot of people is the IOPS score will change based on a number of variables specified when running SQLIO. So just having an IOPS score as a target is basically meaningless without the background behind it. Here’s some of the key variables to both ask about and test for when determining your systems disk performance.

  • Threads – Number of threads to use when accessing the disk
  • Disk Queue Depth – Number of pending operations to allow at once
  • Read/Write
  • Random/Sequential Operations
  • Buffering (software/hardware/none) – Caching options
  • Block size – Determines the size of each IO block to be requested during the test

Usually when I test I use a command that looks something like this (usually repeated 3x to get a good average):

sqlio -kR -t32 -o16 -b32 -BH -s30 -dC

sqlio -kR -t32 -o16 -b64 -BH -s30 -dC

sqlio -kR -t32 -o16 -b128 -BH -s30 -dC

sqlio -kR -t32 -o16 -b256 -BH -s30 -dC

sqlio -kR -t32 -o16 -b512 -BH -s30 -dC

This will test different workload sizes in a similar style to how SQL accesses disk giving you a good idea of performance at a number of different activity levels. Remember if you copy and paste the above script to change the -dC to the correct drive letter. When you run this just toss it in to a bat file and pipe the results to a log so you can read through them. If you are looking to test write change -kR to -kW. To test random versus sequential add on -frandom.

So the biggest things when judging IOPS is to judge consistently. If you have a system that runs well currently use the benchmark on it first to determine a rule of thumb target range for acceptable IOPS within your environment. In short always ask someone spouting about IOPS regarding how they arrived at that number.

  1. #1 by Eric Schrader on June 15, 2012 - 1:27 pm

    How many IOPS do you recommend for: The WFE, The DB- Log/Data/Temp drives? I ran this on a VM and got 2,500, but I cant find any thresholds or recommended numbers.

(will not be published)

%d bloggers like this: