RAID, IOPS, MBps and more Excel Cheat Sheet

[stextbox id=alert]Hi Everyone!  I have taken the XLS file offline.  There were a ton of bugs, as pointed out in comments and through emails/Twitter.  I am working on solidifying the functions and migrating this to an actual application instead of Excel.   In the meantime, you can check out other calculators here.[/stextbox]
Updated:  I am going to take this offline for awhile.  I really need to sit back down and go over what I had intended this to be used for since I’m starting to mix all types of things on this sheet and my terminology I’m sure does not match correctly with what I’m trying to express in the sheet.  I’ll repost the download when I have had time to review.
Updated:  You can download the Excel workbook RAID-and-IOPS which has updates/bugfixes through December 3, 2012 as noted in comments below.
I’m working on an Excel spreadsheet (cheatsheet) that will allow for user input to calculate some of the following:

  • Calculate IOPS, Usable Space, MB/s  based on Number of Disks, Spindle Speed, RAID type and Read/Write Percentages.
  • Calculate Number of Disks required for IOPs
  • Calculate/Convert MB/s to IOPS
  • Calculate/Convert IOPS to MB/s

It also contains some basic information and formulas:

  • Formula for Total Raw IOPS
  • Formula for Functional IOPS
  • Formula for MB/s from IOPS
  • Formula for IOPS from MB/s
  • Formula to determine number disks required for IOPs based on RAID type and spindle speed.

Here’s a screenshot:

RAID/IOPS Calculator Cheat Sheet


Here are some of the formulas used:

  • Total Raw IOPS = Disk Speed IOPS * Number of Disks
  • Functional IOPS = (((Total Raw IOPS*Write%))/(RAID Penalty))+(Total Raw IOPS*Read%)
  • MB/s = (IOPS * KB per IO) / 1024
  • IOPS=(MBps Throughput / KB per IO) * 1024
  • Formula to determine disks required for IOPs (total required IOPS * read%) + (total required IOPS * write% * RAID penalty) = total IOPS required take that and divide by IOPS provided by disk type (15k=175, 10k=125, etc.)

 

14 thoughts on “RAID, IOPS, MBps and more Excel Cheat Sheet

    1. Rich Kreider Post author

      The IO Type is more or less the workload characterization such as 8K request size, 32K request size, 64K, etc. This is the size of the reads/writes workload which has an impact on overall performance. IO Type (or size this sheet) can impact latency and throughput.

      1. Just

        Thanks Rich. So it sounds like in order to specify this you would need to know how the application or o/s presents the i/o type e.g. 4k, 8k etc? I guess what i am asking is where do you get this information from so that you can specify it in the spread sheet.
        Thanks
        Justin

        1. Rich Kreider Post author

          If you are in a Windows shop, you can use perfmon counters such as Avg. Disk Bytes/Read to determine average I/O size. If you are running a VMware-virtualized workload, you can take advantage of a great tool – vscsiStats – to identify your I/O request size.

  1. Pingback: IOPs Calculator and RAID Calculators / Estimators | techish.net

    1. Rich Kreider Post author

      Hi Jeremy,
      Thanks for the update. While working on a few calculations for Just I had noticed the range of vlookup was off a bit and corrected my local copy but did not upload. Thanks again!
      Rich

  2. Pingback: IOPS Calculator - My VMware blog

  3. Pingback: IOPS Calculator | techish.net

  4. Michael Carver

    What is the difference between RAW IOPS and backend IOPS? I was under the impression that they were the same thing.
    I calculate the RAW amount of IOPS by multiplying the amount of disks by the IOPS a single disk can handle (ie. 5 [15k] disks * 175 IOPS = 875 IOPS). Then, I thought you took the RAW IOPS to find the functional IOPS for an aggregate/RAID group by using the formula : (Raw IOPS * Write % / RAID Penalty) + (Raw IOPS * Read %).
    I noticed that before you made the most recent updates to the sheet, these were the formulas you were using.
    After looking at the way the sheet is now, I am confused. How can the total backend IOPS be a higher number than the amount of RAW IOPS available from a group of disks? What is the difference between funcitonal IOPS and frontend IOPS? If you could please share your thoughts to help me understand, I would appreciate it.

Comments are closed.